1########################################################################### 2# 3# Cleanup. 4# 5########################################################################### 6 7--disable_warnings 8 9# Drop stored routines (if any) for general SP-vars test cases. These routines 10# are created in include/sp-vars.inc file. 11 12DROP PROCEDURE IF EXISTS sp_vars_check_dflt; 13DROP PROCEDURE IF EXISTS sp_vars_check_assignment; 14DROP FUNCTION IF EXISTS sp_vars_check_ret1; 15DROP FUNCTION IF EXISTS sp_vars_check_ret2; 16DROP FUNCTION IF EXISTS sp_vars_check_ret3; 17DROP FUNCTION IF EXISTS sp_vars_check_ret4; 18DROP FUNCTION IF EXISTS sp_vars_div_zero; 19 20--enable_warnings 21 22########################################################################### 23# 24# Some general tests for SP-vars functionality. 25# 26########################################################################### 27 28# Create the procedure in ANSI mode. Check that all necessary warnings are 29# emitted properly. 30 31SET @@sql_mode = 'ansi'; 32 33--source include/sp-vars.inc 34 35--echo 36--echo --------------------------------------------------------------- 37--echo Calling the routines, created in ANSI mode. 38--echo --------------------------------------------------------------- 39--echo 40 41CALL sp_vars_check_dflt(); 42 43CALL sp_vars_check_assignment(); 44 45SELECT sp_vars_check_ret1(); 46 47SELECT sp_vars_check_ret2(); 48 49SELECT sp_vars_check_ret3(); 50 51SELECT sp_vars_check_ret4(); 52 53SELECT sp_vars_div_zero(); 54 55# Check that changing sql_mode after creating a store procedure does not 56# matter. 57 58SET @@sql_mode = 'traditional'; 59 60--echo 61--echo --------------------------------------------------------------- 62--echo Calling in TRADITIONAL mode the routines, created in ANSI mode. 63--echo --------------------------------------------------------------- 64--echo 65 66CALL sp_vars_check_dflt(); 67 68CALL sp_vars_check_assignment(); 69 70SELECT sp_vars_check_ret1(); 71 72SELECT sp_vars_check_ret2(); 73 74SELECT sp_vars_check_ret3(); 75 76SELECT sp_vars_check_ret4(); 77 78SELECT sp_vars_div_zero(); 79 80# Create the procedure in TRADITIONAL mode. Check that error will be thrown on 81# execution. 82 83DROP PROCEDURE sp_vars_check_dflt; 84DROP PROCEDURE sp_vars_check_assignment; 85DROP FUNCTION sp_vars_check_ret1; 86DROP FUNCTION sp_vars_check_ret2; 87DROP FUNCTION sp_vars_check_ret3; 88DROP FUNCTION sp_vars_check_ret4; 89DROP FUNCTION sp_vars_div_zero; 90 91--source include/sp-vars.inc 92 93--echo 94--echo --------------------------------------------------------------- 95--echo Calling the routines, created in TRADITIONAL mode. 96--echo --------------------------------------------------------------- 97--echo 98 99--error ER_WARN_DATA_OUT_OF_RANGE 100CALL sp_vars_check_dflt(); 101 102--error ER_WARN_DATA_OUT_OF_RANGE 103CALL sp_vars_check_assignment(); 104 105--error ER_WARN_DATA_OUT_OF_RANGE 106SELECT sp_vars_check_ret1(); 107 108--error ER_WARN_DATA_OUT_OF_RANGE 109SELECT sp_vars_check_ret2(); 110 111--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 112SELECT sp_vars_check_ret3(); 113 114# TODO: Is it an error, that only a warning is emitted here? Check the same 115# behaviour with tables. 116 117SELECT sp_vars_check_ret4(); 118 119--error ER_DIVISION_BY_ZERO 120SELECT sp_vars_div_zero(); 121 122SET @@sql_mode = 'ansi'; 123 124# 125# Cleanup. 126# 127 128DROP PROCEDURE sp_vars_check_dflt; 129DROP PROCEDURE sp_vars_check_assignment; 130DROP FUNCTION sp_vars_check_ret1; 131DROP FUNCTION sp_vars_check_ret2; 132DROP FUNCTION sp_vars_check_ret3; 133DROP FUNCTION sp_vars_check_ret4; 134DROP FUNCTION sp_vars_div_zero; 135 136########################################################################### 137# 138# Tests for BIT data type. 139# 140########################################################################### 141 142--echo 143--echo --------------------------------------------------------------- 144--echo BIT data type tests 145--echo --------------------------------------------------------------- 146--echo 147 148# 149# Prepare. 150# 151 152--disable_warnings 153DROP PROCEDURE IF EXISTS p1; 154--enable_warnings 155 156# 157# Test case. 158# 159 160delimiter |; 161CREATE PROCEDURE p1() 162BEGIN 163 DECLARE v1 BIT; 164 DECLARE v2 BIT(1); 165 DECLARE v3 BIT(3) DEFAULT b'101'; 166 DECLARE v4 BIT(64) DEFAULT 0x5555555555555555; 167 DECLARE v5 BIT(3); 168 DECLARE v6 BIT(64); 169 DECLARE v7 BIT(8) DEFAULT 128; 170 DECLARE v8 BIT(8) DEFAULT '128'; 171 DECLARE v9 BIT(8) DEFAULT ' 128'; 172 DECLARE v10 BIT(8) DEFAULT 'x 128'; 173 174 SET v1 = v4; 175 SET v2 = 0; 176 SET v5 = v4; # check overflow 177 SET v6 = v3; # check padding 178 179 SELECT HEX(v1); 180 SELECT HEX(v2); 181 SELECT HEX(v3); 182 SELECT HEX(v4); 183 SELECT HEX(v5); 184 SELECT HEX(v6); 185 SELECT HEX(v7); 186 SELECT HEX(v8); 187 SELECT HEX(v9); 188 SELECT HEX(v10); 189END| 190delimiter ;| 191 192CALL p1(); 193 194# 195# Cleanup. 196# 197 198DROP PROCEDURE p1; 199 200########################################################################### 201# 202# Tests for CASE statements functionality: 203# - test for general functionality (scopes, nested cases, CASE in loops); 204# - test that if type of the CASE expression is changed on each iteration, 205# the execution will be correct. 206# 207########################################################################### 208 209--echo 210--echo --------------------------------------------------------------- 211--echo CASE expression tests. 212--echo --------------------------------------------------------------- 213--echo 214 215# 216# Prepare. 217# 218 219DROP PROCEDURE IF EXISTS p1; 220DROP PROCEDURE IF EXISTS p2; 221DROP TABLE IF EXISTS t1; 222 223# 224# Test case. 225# 226 227CREATE TABLE t1(log_msg VARCHAR(1024)); 228 229delimiter |; 230 231CREATE PROCEDURE p1(arg VARCHAR(255)) 232BEGIN 233 INSERT INTO t1 VALUES('p1: step1'); 234 235 CASE arg * 10 236 WHEN 10 * 10 THEN 237 INSERT INTO t1 VALUES('p1: case1: on 10'); 238 WHEN 10 * 10 + 10 * 10 THEN 239 BEGIN 240 CASE arg / 10 241 WHEN 1 THEN 242 INSERT INTO t1 VALUES('p1: case1: case2: on 1'); 243 WHEN 2 THEN 244 BEGIN 245 DECLARE i TINYINT DEFAULT 10; 246 247 WHILE i > 0 DO 248 INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i)); 249 250 CASE MOD(i, 2) 251 WHEN 0 THEN 252 INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even'); 253 WHEN 1 THEN 254 INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd'); 255 ELSE 256 INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR'); 257 END CASE; 258 259 SET i = i - 1; 260 END WHILE; 261 END; 262 ELSE 263 INSERT INTO t1 VALUES('p1: case1: case2: ERROR'); 264 END CASE; 265 266 CASE arg 267 WHEN 10 THEN 268 INSERT INTO t1 VALUES('p1: case1: case3: on 10'); 269 WHEN 20 THEN 270 INSERT INTO t1 VALUES('p1: case1: case3: on 20'); 271 ELSE 272 INSERT INTO t1 VALUES('p1: case1: case3: ERROR'); 273 END CASE; 274 END; 275 ELSE 276 INSERT INTO t1 VALUES('p1: case1: ERROR'); 277 END CASE; 278 279 CASE arg * 10 280 WHEN 10 * 10 THEN 281 INSERT INTO t1 VALUES('p1: case4: on 10'); 282 WHEN 10 * 10 + 10 * 10 THEN 283 BEGIN 284 CASE arg / 10 285 WHEN 1 THEN 286 INSERT INTO t1 VALUES('p1: case4: case5: on 1'); 287 WHEN 2 THEN 288 BEGIN 289 DECLARE i TINYINT DEFAULT 10; 290 291 WHILE i > 0 DO 292 INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i)); 293 294 CASE MOD(i, 2) 295 WHEN 0 THEN 296 INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even'); 297 WHEN 1 THEN 298 INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd'); 299 ELSE 300 INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR'); 301 END CASE; 302 303 SET i = i - 1; 304 END WHILE; 305 END; 306 ELSE 307 INSERT INTO t1 VALUES('p1: case4: case5: ERROR'); 308 END CASE; 309 310 CASE arg 311 WHEN 10 THEN 312 INSERT INTO t1 VALUES('p1: case4: case6: on 10'); 313 WHEN 20 THEN 314 INSERT INTO t1 VALUES('p1: case4: case6: on 20'); 315 ELSE 316 INSERT INTO t1 VALUES('p1: case4: case6: ERROR'); 317 END CASE; 318 END; 319 ELSE 320 INSERT INTO t1 VALUES('p1: case4: ERROR'); 321 END CASE; 322END| 323 324CREATE PROCEDURE p2() 325BEGIN 326 DECLARE i TINYINT DEFAULT 3; 327 328 WHILE i > 0 DO 329 IF MOD(i, 2) = 0 THEN 330 SET @_test_session_var = 10; 331 ELSE 332 SET @_test_session_var = 'test'; 333 END IF; 334 335 CASE @_test_session_var 336 WHEN 10 THEN 337 INSERT INTO t1 VALUES('p2: case: numerical type'); 338 WHEN 'test' THEN 339 INSERT INTO t1 VALUES('p2: case: string type'); 340 ELSE 341 INSERT INTO t1 VALUES('p2: case: ERROR'); 342 END CASE; 343 344 SET i = i - 1; 345 END WHILE; 346END| 347 348delimiter ;| 349 350CALL p1(10); 351CALL p1(20); 352 353CALL p2(); 354 355SELECT * FROM t1; 356 357# 358# Cleanup. 359# 360 361DROP PROCEDURE p1; 362DROP PROCEDURE p2; 363DROP TABLE t1; 364 365########################################################################### 366# 367# Test case for BUG#14161: Stored procedure cannot retrieve bigint unsigned. 368# 369########################################################################### 370 371--echo 372--echo --------------------------------------------------------------- 373--echo BUG#14161 374--echo --------------------------------------------------------------- 375--echo 376 377# 378# Prepare. 379# 380 381--disable_warnings 382DROP TABLE IF EXISTS t1; 383DROP PROCEDURE IF EXISTS p1; 384--enable_warnings 385 386# 387# Test case. 388# 389 390CREATE TABLE t1(col BIGINT UNSIGNED); 391 392INSERT INTO t1 VALUE(18446744073709551614); 393 394delimiter |; 395CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED) 396BEGIN 397 SELECT arg; 398 SELECT * FROM t1; 399 SELECT * FROM t1 WHERE col = arg; 400END| 401delimiter ;| 402 403CALL p1(18446744073709551614); 404 405# 406# Cleanup. 407# 408 409DROP TABLE t1; 410DROP PROCEDURE p1; 411 412########################################################################### 413# 414# Test case for BUG#13705: parameters to stored procedures are not verified. 415# 416########################################################################### 417 418--echo 419--echo --------------------------------------------------------------- 420--echo BUG#13705 421--echo --------------------------------------------------------------- 422--echo 423 424# 425# Prepare. 426# 427 428--disable_warnings 429DROP PROCEDURE IF EXISTS p1; 430--enable_warnings 431 432# 433# Test case. 434# 435 436delimiter |; 437CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA 438BEGIN 439 SELECT x, y; 440END| 441delimiter ;| 442 443CALL p1('alpha', 'abc'); 444CALL p1('alpha', 'abcdef'); 445 446# 447# Cleanup. 448# 449 450DROP PROCEDURE p1; 451 452########################################################################### 453# 454# Test case for BUG#13675: DATETIME/DATE type in store proc param seems to be 455# converted as varbinary. 456# 457# TODO: test case failed. 458# 459########################################################################### 460 461--echo 462--echo --------------------------------------------------------------- 463--echo BUG#13675 464--echo --------------------------------------------------------------- 465--echo 466 467# 468# Prepare. 469# 470 471--disable_warnings 472DROP PROCEDURE IF EXISTS p1; 473DROP TABLE IF EXISTS t1; 474--enable_warnings 475 476# 477# Test case. 478# 479 480delimiter |; 481CREATE PROCEDURE p1(x DATETIME) 482BEGIN 483 CREATE TABLE t1 SELECT x; 484 SHOW CREATE TABLE t1; 485 DROP TABLE t1; 486END| 487delimiter ;| 488 489CALL p1(NOW()); 490 491CALL p1('test'); 492 493# 494# Cleanup. 495# 496 497DROP PROCEDURE p1; 498 499########################################################################### 500# 501# Test case for BUG#12976: Boolean values reversed in stored procedures? 502# 503########################################################################### 504 505--echo 506--echo --------------------------------------------------------------- 507--echo BUG#12976 508--echo --------------------------------------------------------------- 509--echo 510 511# 512# Prepare. 513# 514 515--disable_warnings 516DROP TABLE IF EXISTS t1; 517DROP PROCEDURE IF EXISTS p1; 518DROP PROCEDURE IF EXISTS p2; 519--enable_warnings 520 521# 522# Test case. 523# 524 525CREATE TABLE t1(b BIT(1)); 526 527INSERT INTO t1(b) VALUES(b'0'), (b'1'); 528 529delimiter |; 530CREATE PROCEDURE p1() 531BEGIN 532 SELECT HEX(b), 533 b = 0, 534 b = FALSE, 535 b IS FALSE, 536 b = 1, 537 b = TRUE, 538 b IS TRUE 539 FROM t1; 540END| 541 542CREATE PROCEDURE p2() 543BEGIN 544 DECLARE vb BIT(1); 545 SELECT b INTO vb FROM t1 WHERE b = 0; 546 547 SELECT HEX(vb), 548 vb = 0, 549 vb = FALSE, 550 vb IS FALSE, 551 vb = 1, 552 vb = TRUE, 553 vb IS TRUE; 554 555 SELECT b INTO vb FROM t1 WHERE b = 1; 556 557 SELECT HEX(vb), 558 vb = 0, 559 vb = FALSE, 560 vb IS FALSE, 561 vb = 1, 562 vb = TRUE, 563 vb IS TRUE; 564END| 565delimiter ;| 566 567call p1(); 568 569call p2(); 570 571# 572# Cleanup. 573# 574 575DROP TABLE t1; 576DROP PROCEDURE p1; 577DROP PROCEDURE p2; 578 579# Additional tests for Bug#12976 580 581--disable_warnings 582DROP TABLE IF EXISTS table_12976_a; 583DROP TABLE IF EXISTS table_12976_b; 584DROP PROCEDURE IF EXISTS proc_12976_a; 585DROP PROCEDURE IF EXISTS proc_12976_b; 586--enable_warnings 587 588CREATE TABLE table_12976_a (val bit(1)); 589 590CREATE TABLE table_12976_b( 591 appname varchar(15), 592 emailperm bit not null default 1, 593 phoneperm bit not null default 0); 594 595insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0'); 596 597delimiter ||; 598CREATE PROCEDURE proc_12976_a() 599BEGIN 600 declare localvar bit(1); 601 SELECT val INTO localvar FROM table_12976_a; 602 SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a; 603END|| 604 605CREATE PROCEDURE proc_12976_b( 606 name varchar(15), 607 out ep bit, 608 out msg varchar(10)) 609BEGIN 610 SELECT emailperm into ep FROM table_12976_b where (appname = name); 611 IF ep is true THEN 612 SET msg = 'True'; 613 ELSE 614 SET msg = 'False'; 615 END IF; 616END|| 617 618delimiter ;|| 619 620INSERT table_12976_a VALUES (0); 621call proc_12976_a(); 622UPDATE table_12976_a set val=1; 623call proc_12976_a(); 624 625call proc_12976_b('A', @ep, @msg); 626select @ep, @msg; 627 628call proc_12976_b('B', @ep, @msg); 629select @ep, @msg; 630 631DROP TABLE table_12976_a; 632DROP TABLE table_12976_b; 633DROP PROCEDURE proc_12976_a; 634DROP PROCEDURE proc_12976_b; 635 636 637########################################################################### 638# 639# Test case for BUG#9572: Stored procedures: variable type declarations 640# ignored. 641# 642########################################################################### 643 644--echo 645--echo --------------------------------------------------------------- 646--echo BUG#9572 647--echo --------------------------------------------------------------- 648--echo 649 650# 651# Prepare. 652# 653 654--disable_warnings 655DROP PROCEDURE IF EXISTS p1; 656DROP PROCEDURE IF EXISTS p2; 657DROP PROCEDURE IF EXISTS p3; 658 659DROP PROCEDURE IF EXISTS p4; 660DROP PROCEDURE IF EXISTS p5; 661DROP PROCEDURE IF EXISTS p6; 662--enable_warnings 663 664# 665# Test case. 666# 667 668SET @@sql_mode = 'traditional'; 669 670delimiter |; 671 672CREATE PROCEDURE p1() 673BEGIN 674 DECLARE v TINYINT DEFAULT 1e200; 675 SELECT v; 676END| 677 678CREATE PROCEDURE p2() 679BEGIN 680 DECLARE v DECIMAL(5) DEFAULT 1e200; 681 SELECT v; 682END| 683 684CREATE PROCEDURE p3() 685BEGIN 686 DECLARE v CHAR(5) DEFAULT 'abcdef'; 687 SELECT v LIKE 'abc___'; 688END| 689 690CREATE PROCEDURE p4(arg VARCHAR(2)) 691BEGIN 692 DECLARE var VARCHAR(1); 693 SET var := arg; 694 SELECT arg, var; 695END| 696 697CREATE PROCEDURE p5(arg CHAR(2)) 698BEGIN 699 DECLARE var CHAR(1); 700 SET var := arg; 701 SELECT arg, var; 702END| 703 704CREATE PROCEDURE p6(arg DECIMAL(2)) 705BEGIN 706 DECLARE var DECIMAL(1); 707 SET var := arg; 708 SELECT arg, var; 709END| 710 711delimiter ;| 712 713--error ER_WARN_DATA_OUT_OF_RANGE 714CALL p1(); 715--error ER_WARN_DATA_OUT_OF_RANGE 716CALL p2(); 717--error ER_DATA_TOO_LONG 718CALL p3(); 719 720--error ER_DATA_TOO_LONG 721CALL p4('aaa'); 722--error ER_DATA_TOO_LONG 723CALL p5('aa'); 724--error ER_WARN_DATA_OUT_OF_RANGE 725CALL p6(10); 726 727# 728# Cleanup. 729# 730 731SET @@sql_mode = 'ansi'; 732 733DROP PROCEDURE p1; 734DROP PROCEDURE p2; 735DROP PROCEDURE p3; 736 737DROP PROCEDURE p4; 738DROP PROCEDURE p5; 739DROP PROCEDURE p6; 740 741########################################################################### 742# 743# Test case for BUG#9078: STORED PROCDURE: Decimal digits are not displayed 744# when we use DECIMAL datatype. 745# 746########################################################################### 747 748--echo 749--echo --------------------------------------------------------------- 750--echo BUG#9078 751--echo --------------------------------------------------------------- 752--echo 753 754# 755# Prepare. 756# 757 758--disable_warnings 759DROP PROCEDURE IF EXISTS p1; 760--enable_warnings 761 762# 763# Test case. 764# 765 766delimiter |; 767CREATE PROCEDURE p1 (arg DECIMAL(64,2)) 768BEGIN 769 DECLARE var DECIMAL(64,2); 770 771 SET var = arg; 772 SELECT var; 773END| 774delimiter ;| 775 776CALL p1(1929); 777CALL p1(1929.00); 778CALL p1(1929.003); 779 780# 781# Cleanup. 782# 783 784DROP PROCEDURE p1; 785 786########################################################################### 787# 788# Test case for BUG#8768: Functions: For any unsigned data type, -ve values can 789# be passed and returned. 790# 791# TODO: there is a bug here -- the function created in ANSI mode should not 792# throw errors instead of warnings if called in TRADITIONAL mode. 793# 794########################################################################### 795 796--echo 797--echo --------------------------------------------------------------- 798--echo BUG#8768 799--echo --------------------------------------------------------------- 800--echo 801 802# 803# Prepare. 804# 805 806--disable_warnings 807DROP FUNCTION IF EXISTS f1; 808--enable_warnings 809 810# 811# Test case. 812# 813 814# Create a function in ANSI mode. 815 816delimiter |; 817CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT 818BEGIN 819 RETURN arg; 820END| 821delimiter ;| 822 823SELECT f1(-2500); 824 825# Call in TRADITIONAL mode the function created in ANSI mode. 826 827SET @@sql_mode = 'traditional'; 828 829# TODO: a warning should be emitted here. 830--error ER_WARN_DATA_OUT_OF_RANGE 831SELECT f1(-2500); 832 833# Recreate the function in TRADITIONAL mode. 834 835DROP FUNCTION f1; 836 837delimiter |; 838CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT 839BEGIN 840 RETURN arg; 841END| 842delimiter ;| 843 844--error ER_WARN_DATA_OUT_OF_RANGE 845SELECT f1(-2500); 846 847# 848# Cleanup. 849# 850 851SET @@sql_mode = 'ansi'; 852 853DROP FUNCTION f1; 854 855########################################################################### 856# 857# Test case for BUG#8769: Functions: For Int datatypes, out of range values can 858# be passed and returned. 859# 860# TODO: there is a bug here -- the function created in ANSI mode should not 861# throw errors instead of warnings if called in TRADITIONAL mode. 862# 863########################################################################### 864 865--echo 866--echo --------------------------------------------------------------- 867--echo BUG#8769 868--echo --------------------------------------------------------------- 869--echo 870 871# 872# Prepare. 873# 874 875--disable_warnings 876DROP FUNCTION IF EXISTS f1; 877--enable_warnings 878 879# 880# Test case. 881# 882 883# Create a function in ANSI mode. 884 885delimiter |; 886CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT 887BEGIN 888 RETURN arg; 889END| 890delimiter ;| 891 892SELECT f1(8388699); 893 894# Call in TRADITIONAL mode the function created in ANSI mode. 895 896SET @@sql_mode = 'traditional'; 897 898# TODO: a warning should be emitted here. 899--error ER_WARN_DATA_OUT_OF_RANGE 900SELECT f1(8388699); 901 902# Recreate the function in TRADITIONAL mode. 903 904DROP FUNCTION f1; 905 906delimiter |; 907CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT 908BEGIN 909 RETURN arg; 910END| 911delimiter ;| 912 913--error ER_WARN_DATA_OUT_OF_RANGE 914SELECT f1(8388699); 915 916# 917# Cleanup. 918# 919 920SET @@sql_mode = 'ansi'; 921 922DROP FUNCTION f1; 923 924########################################################################### 925# 926# Test case for BUG#8702: Stored Procedures: No Error/Warning shown for 927# inappropriate data type matching. 928# 929########################################################################### 930 931--echo 932--echo --------------------------------------------------------------- 933--echo BUG#8702 934--echo --------------------------------------------------------------- 935--echo 936 937# 938# Prepare. 939# 940 941--disable_warnings 942DROP PROCEDURE IF EXISTS p1; 943DROP TABLE IF EXISTS t1; 944--enable_warnings 945 946# 947# Test case. 948# 949 950CREATE TABLE t1(col VARCHAR(255)); 951 952INSERT INTO t1(col) VALUES('Hello, world!'); 953 954delimiter |; 955CREATE PROCEDURE p1() 956BEGIN 957 DECLARE sp_var INTEGER; 958 959 SELECT col INTO sp_var FROM t1 LIMIT 1; 960 SET @user_var = sp_var; 961 962 SELECT sp_var; 963 SELECT @user_var; 964END| 965delimiter ;| 966 967CALL p1(); 968 969# 970# Cleanup. 971# 972 973DROP PROCEDURE p1; 974DROP TABLE t1; 975 976########################################################################### 977# 978# Test case for BUG#12903: upper function does not work inside a function. 979# 980########################################################################### 981 982--echo 983--echo --------------------------------------------------------------- 984--echo BUG#12903 985--echo --------------------------------------------------------------- 986--echo 987 988# 989# Prepare. 990# 991 992--disable_warnings 993DROP FUNCTION IF EXISTS f1; 994DROP TABLE IF EXISTS t1; 995--enable_warnings 996 997# 998# Test case. 999# 1000 1001CREATE TABLE t1(txt VARCHAR(255)); 1002 1003delimiter |; 1004CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255) 1005BEGIN 1006 DECLARE v1 VARCHAR(255); 1007 DECLARE v2 VARCHAR(255); 1008 1009 SET v1 = CONCAT(LOWER(arg), UPPER(arg)); 1010 SET v2 = CONCAT(LOWER(v1), UPPER(v1)); 1011 1012 INSERT INTO t1 VALUES(v1), (v2); 1013 1014 RETURN CONCAT(LOWER(arg), UPPER(arg)); 1015END| 1016delimiter ;| 1017 1018SELECT f1('_aBcDe_'); 1019 1020SELECT * FROM t1; 1021 1022# 1023# Cleanup. 1024# 1025 1026DROP FUNCTION f1; 1027DROP TABLE t1; 1028 1029########################################################################### 1030# 1031# Test case for BUG#13808: ENUM type stored procedure parameter accepts 1032# non-enumerated data. 1033# 1034########################################################################### 1035 1036--echo 1037--echo --------------------------------------------------------------- 1038--echo BUG#13808 1039--echo --------------------------------------------------------------- 1040--echo 1041 1042# 1043# Prepare. 1044# 1045 1046--disable_warnings 1047DROP PROCEDURE IF EXISTS p1; 1048DROP PROCEDURE IF EXISTS p2; 1049DROP FUNCTION IF EXISTS f1; 1050--enable_warnings 1051 1052# 1053# Test case. 1054# 1055 1056delimiter |; 1057 1058CREATE PROCEDURE p1(arg ENUM('a', 'b')) 1059BEGIN 1060 SELECT arg; 1061END| 1062 1063CREATE PROCEDURE p2(arg ENUM('a', 'b')) 1064BEGIN 1065 DECLARE var ENUM('c', 'd') DEFAULT arg; 1066 1067 SELECT arg, var; 1068END| 1069 1070CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd') 1071BEGIN 1072 RETURN arg; 1073END| 1074 1075delimiter ;| 1076 1077CALL p1('c'); 1078 1079CALL p2('a'); 1080 1081SELECT f1('a'); 1082 1083# 1084# Cleanup. 1085# 1086 1087DROP PROCEDURE p1; 1088DROP PROCEDURE p2; 1089DROP FUNCTION f1; 1090 1091########################################################################### 1092# 1093# Test case for BUG#13909: Varchar Stored Procedure Parameter always BINARY 1094# string (ignores CHARACTER SET). 1095# 1096########################################################################### 1097 1098--echo 1099--echo --------------------------------------------------------------- 1100--echo BUG#13909 1101--echo --------------------------------------------------------------- 1102--echo 1103 1104# 1105# Prepare. 1106# 1107 1108--disable_warnings 1109DROP PROCEDURE IF EXISTS p1; 1110DROP PROCEDURE IF EXISTS p2; 1111--enable_warnings 1112 1113# 1114# Test case. 1115# 1116 1117delimiter |; 1118 1119CREATE PROCEDURE p1(arg VARCHAR(255)) 1120BEGIN 1121 SELECT CHARSET(arg); 1122END| 1123 1124CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8) 1125BEGIN 1126 SELECT CHARSET(arg); 1127END| 1128 1129delimiter ;| 1130 1131CALL p1('t'); 1132CALL p1(_UTF8 't'); 1133 1134 1135CALL p2('t'); 1136CALL p2(_LATIN1 't'); 1137 1138# 1139# Cleanup. 1140# 1141 1142DROP PROCEDURE p1; 1143DROP PROCEDURE p2; 1144 1145########################################################################### 1146# 1147# Test case for BUG#14188: BINARY variables have no 0x00 padding. 1148# 1149########################################################################### 1150 1151--echo 1152--echo --------------------------------------------------------------- 1153--echo BUG#14188 1154--echo --------------------------------------------------------------- 1155--echo 1156 1157# 1158# Prepare. 1159# 1160 1161--disable_warnings 1162DROP PROCEDURE IF EXISTS p1; 1163--enable_warnings 1164 1165# 1166# Test case. 1167# 1168 1169delimiter |; 1170CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2)) 1171BEGIN 1172 DECLARE var1 BINARY(2) DEFAULT 0x41; 1173 DECLARE var2 VARBINARY(2) DEFAULT 0x42; 1174 1175 SELECT HEX(arg1), HEX(arg2); 1176 SELECT HEX(var1), HEX(var2); 1177END| 1178delimiter ;| 1179 1180CALL p1(0x41, 0x42); 1181 1182# 1183# Cleanup. 1184# 1185 1186DROP PROCEDURE p1; 1187 1188########################################################################### 1189# 1190# Test case for BUG#15148: Stored procedure variables accept non-scalar values. 1191# 1192########################################################################### 1193 1194--echo 1195--echo --------------------------------------------------------------- 1196--echo BUG#15148 1197--echo --------------------------------------------------------------- 1198--echo 1199 1200# 1201# Prepare. 1202# 1203 1204--disable_warnings 1205DROP PROCEDURE IF EXISTS p1; 1206DROP TABLE IF EXISTS t1; 1207--enable_warnings 1208 1209# 1210# Test case. 1211# 1212 1213CREATE TABLE t1(col1 TINYINT, col2 TINYINT); 1214 1215INSERT INTO t1 VALUES(1, 2), (11, 12); 1216 1217delimiter |; 1218CREATE PROCEDURE p1(arg TINYINT) 1219BEGIN 1220 SELECT arg; 1221END| 1222delimiter ;| 1223 1224--error ER_OPERAND_COLUMNS 1225CALL p1((1, 2)); 1226 1227--error ER_OPERAND_COLUMNS 1228CALL p1((SELECT * FROM t1 LIMIT 1)); 1229 1230--error ER_OPERAND_COLUMNS 1231CALL p1((SELECT col1, col2 FROM t1 LIMIT 1)); 1232 1233# 1234# Cleanup. 1235# 1236 1237DROP PROCEDURE p1; 1238DROP TABLE t1; 1239 1240########################################################################### 1241# 1242# Test case for BUG#13613: substring function in stored procedure. 1243# 1244########################################################################### 1245 1246--echo 1247--echo --------------------------------------------------------------- 1248--echo BUG#13613 1249--echo --------------------------------------------------------------- 1250--echo 1251 1252# 1253# Prepare. 1254# 1255 1256--disable_warnings 1257DROP PROCEDURE IF EXISTS p1; 1258DROP FUNCTION IF EXISTS f1; 1259--enable_warnings 1260 1261# 1262# Test case. 1263# 1264 1265delimiter |; 1266 1267CREATE PROCEDURE p1(x VARCHAR(50)) 1268BEGIN 1269 SET x = SUBSTRING(x, 1, 3); 1270 SELECT x; 1271END| 1272 1273CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50) 1274BEGIN 1275 RETURN SUBSTRING(x, 1, 3); 1276END| 1277 1278delimiter ;| 1279 1280CALL p1('abcdef'); 1281 1282SELECT f1('ABCDEF'); 1283 1284# 1285# Cleanup. 1286# 1287 1288DROP PROCEDURE p1; 1289DROP FUNCTION f1; 1290 1291########################################################################### 1292# 1293# Test case for BUG#13665: concat with '' produce incorrect results in SP. 1294# 1295########################################################################### 1296 1297--echo 1298--echo --------------------------------------------------------------- 1299--echo BUG#13665 1300--echo --------------------------------------------------------------- 1301--echo 1302 1303# 1304# Prepare. 1305# 1306 1307--disable_warnings 1308DROP FUNCTION IF EXISTS f1; 1309--enable_warnings 1310 1311# 1312# Test case. 1313# 1314 1315delimiter |; 1316CREATE FUNCTION f1() RETURNS VARCHAR(20000) 1317BEGIN 1318 DECLARE var VARCHAR(2000); 1319 1320 SET var = ''; 1321 SET var = CONCAT(var, 'abc'); 1322 SET var = CONCAT(var, ''); 1323 1324 RETURN var; 1325END| 1326delimiter ;| 1327 1328SELECT f1(); 1329 1330# 1331# Cleanup. 1332# 1333 1334DROP FUNCTION f1; 1335 1336 1337# 1338# Bug#17226: Variable set in cursor on first iteration is assigned 1339# second iterations value 1340# 1341# The problem was in incorrect handling of local variables of type 1342# TEXT (BLOB). 1343# 1344--disable_warnings 1345DROP PROCEDURE IF EXISTS p1; 1346--enable_warnings 1347 1348delimiter |; 1349CREATE PROCEDURE p1() 1350BEGIN 1351 DECLARE v_char VARCHAR(255); 1352 DECLARE v_text TEXT DEFAULT ''; 1353 1354 SET v_char = 'abc'; 1355 1356 SET v_text = v_char; 1357 1358 SET v_char = 'def'; 1359 1360 SET v_text = concat(v_text, '|', v_char); 1361 1362 SELECT v_text; 1363END| 1364delimiter ;| 1365 1366CALL p1(); 1367 1368DROP PROCEDURE p1; 1369 1370# 1371# Bug #27415 Text Variables in stored procedures 1372# If the SP varible was also referenced on the right side 1373# the result was corrupted. 1374# 1375DELIMITER |; 1376 1377--disable_warnings 1378DROP PROCEDURE IF EXISTS bug27415_text_test| 1379DROP PROCEDURE IF EXISTS bug27415_text_test2| 1380--enable_warnings 1381 1382CREATE PROCEDURE bug27415_text_test(entity_id_str_in text) 1383BEGIN 1384 DECLARE str_remainder text; 1385 1386 SET str_remainder = entity_id_str_in; 1387 1388 select 'before substr', str_remainder; 1389 SET str_remainder = SUBSTRING(str_remainder, 3); 1390 select 'after substr', str_remainder; 1391END| 1392 1393CREATE PROCEDURE bug27415_text_test2(entity_id_str_in text) 1394BEGIN 1395 DECLARE str_remainder text; 1396 DECLARE str_remainder2 text; 1397 1398 SET str_remainder2 = entity_id_str_in; 1399 select 'before substr', str_remainder2; 1400 SET str_remainder = SUBSTRING(str_remainder2, 3); 1401 select 'after substr', str_remainder; 1402END| 1403 1404CALL bug27415_text_test('a,b,c')| 1405CALL bug27415_text_test('a,b,c')| 1406CALL bug27415_text_test2('a,b,c')| 1407CALL bug27415_text_test('a,b,c')| 1408 1409DROP PROCEDURE bug27415_text_test| 1410DROP PROCEDURE bug27415_text_test2| 1411 1412DELIMITER ;| 1413 1414# End of 5.0 tests. 1415 1416# 1417# Bug #26277 User variable returns one type in SELECT @v and other for CREATE as SELECT @v 1418# 1419--disable_warnings 1420drop function if exists f1; 1421drop table if exists t1; 1422--enable_warnings 1423 1424delimiter |; 1425create function f1() returns int 1426begin 1427 if @a=1 then set @b='abc'; 1428 else set @b=1; 1429 end if; 1430 set @a=1; 1431 return 0; 1432end| 1433 1434create table t1 (a int)| 1435insert into t1 (a) values (1), (2)| 1436 1437set @b=1| 1438set @a=0| 1439select f1(), @b from t1| 1440 1441set @b:='test'| 1442set @a=0| 1443select f1(), @b from t1| 1444 1445delimiter ;| 1446 1447drop function f1; 1448drop table t1; 1449# End of 5.1 tests. 1450 1451 1452########################################################################### 1453# 1454# Test case for BUG#28299: To-number conversion warnings work 1455# differenly with CHAR and VARCHAR sp variables 1456# 1457########################################################################### 1458 1459--echo 1460--echo --------------------------------------------------------------- 1461--echo BUG#28299 1462--echo --------------------------------------------------------------- 1463--echo 1464 1465DELIMITER |; 1466CREATE PROCEDURE ctest() 1467BEGIN 1468 DECLARE i CHAR(16); 1469 DECLARE j INT; 1470 SET i= 'string'; 1471 SET j= 1 + i; 1472END| 1473DELIMITER ;| 1474 1475CALL ctest(); 1476DROP PROCEDURE ctest; 1477 1478DELIMITER |; 1479CREATE PROCEDURE vctest() 1480BEGIN 1481 DECLARE i VARCHAR(16); 1482 DECLARE j INT; 1483 SET i= 'string'; 1484 SET j= 1 + i; 1485END| 1486DELIMITER ;| 1487 1488CALL vctest(); 1489DROP PROCEDURE vctest; 1490 1491--echo # 1492--echo # Start of 10.3 tests 1493--echo # 1494 1495--echo # 1496--echo # MDEV-12876 Wrong data type for CREATE..SELECT sp_var 1497--echo # 1498 1499DELIMITER $$; 1500CREATE PROCEDURE p1() 1501BEGIN 1502 DECLARE i8 TINYINT; 1503 DECLARE i16 SMALLINT; 1504 DECLARE i32 INT; 1505 DECLARE i64 BIGINT; 1506 DECLARE f FLOAT; 1507 DECLARE d DOUBLE; 1508 DECLARE b8 BIT(8); 1509 DECLARE y YEAR; 1510 DECLARE t1 TINYTEXT; 1511 DECLARE t2 TEXT; 1512 DECLARE t3 MEDIUMTEXT; 1513 DECLARE t4 LONGTEXT; 1514 CREATE TABLE t1 AS SELECT i8, i16, i32, i64, f, d, b8, y, t1, t2, t3, t4; 1515END; 1516$$ 1517DELIMITER ;$$ 1518CALL p1; 1519DESCRIBE t1; 1520DROP TABLE t1; 1521DROP PROCEDURE p1; 1522 1523--echo # 1524--echo # MDEV-12917 Wrong data type for CREATE..SELECT year_sp_variable 1525--echo # 1526 1527DELIMITER $$; 1528CREATE PROCEDURE p1() 1529BEGIN 1530 DECLARE a YEAR; 1531 CREATE OR REPLACE TABLE t1 AS SELECT a; 1532 SHOW CREATE TABLE t1; 1533 DROP TABLE t1; 1534END; 1535$$ 1536DELIMITER ;$$ 1537CALL p1; 1538DROP PROCEDURE p1; 1539 1540 1541--echo # 1542--echo # MDEV-15960 Wrong data type on CREATE..SELECT char_or_enum_or_text_spvar 1543--echo # 1544 1545DELIMITER $$; 1546BEGIN NOT ATOMIC 1547 DECLARE var TINYTEXT CHARACTER SET utf8; 1548 CREATE TABLE t1 AS SELECT var; 1549END; 1550$$ 1551DELIMITER ;$$ 1552SHOW CREATE TABLE t1; 1553DROP TABLE t1; 1554 1555DELIMITER $$; 1556BEGIN NOT ATOMIC 1557 DECLARE var TEXT CHARACTER SET utf8; 1558 CREATE TABLE t1 AS SELECT var; 1559END; 1560$$ 1561DELIMITER ;$$ 1562SHOW CREATE TABLE t1; 1563DROP TABLE t1; 1564 1565DELIMITER $$; 1566BEGIN NOT ATOMIC 1567 DECLARE var MEDIUMTEXT CHARACTER SET utf8; 1568 CREATE TABLE t1 AS SELECT var; 1569END; 1570$$ 1571DELIMITER ;$$ 1572SHOW CREATE TABLE t1; 1573DROP TABLE t1; 1574 1575DELIMITER $$; 1576BEGIN NOT ATOMIC 1577 DECLARE var LONGTEXT CHARACTER SET utf8; 1578 CREATE TABLE t1 AS SELECT var; 1579END; 1580$$ 1581DELIMITER ;$$ 1582SHOW CREATE TABLE t1; 1583DROP TABLE t1; 1584 1585 1586DELIMITER $$; 1587BEGIN NOT ATOMIC 1588 DECLARE var CHAR(1); 1589 CREATE TABLE t1 AS SELECT var; 1590END; 1591$$ 1592DELIMITER ;$$ 1593SHOW CREATE TABLE t1; 1594DROP TABLE t1; 1595 1596 1597DELIMITER $$; 1598BEGIN NOT ATOMIC 1599 DECLARE var ENUM('a'); 1600 CREATE TABLE t1 AS SELECT var; 1601END; 1602$$ 1603DELIMITER ;$$ 1604SHOW CREATE TABLE t1; 1605DROP TABLE t1; 1606 1607 1608 1609--echo # 1610--echo # MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable 1611--echo # 1612 1613--echo # 1614--echo # Simple cases (without subqueries) - the most typical problem: 1615--echo # a typo in an SP variable name 1616--echo # 1617 1618DELIMITER $$; 1619--error ER_SP_UNDECLARED_VAR 1620CREATE PROCEDURE p1(a INT) 1621BEGIN 1622 DECLARE res INT DEFAULT 0; 1623 IF (a < 0) THEN 1624 SET res= a_long_variable_name_with_a_typo; 1625 END IF; 1626END; 1627$$ 1628DELIMITER ;$$ 1629 1630DELIMITER $$; 1631--error ER_SP_UNDECLARED_VAR 1632CREATE PROCEDURE p1(a INT) 1633BEGIN 1634 DECLARE res INT DEFAULT 0; 1635 IF (a < 0) THEN 1636 SET res= 1 + a_long_variable_name_with_a_typo; 1637 END IF; 1638END; 1639$$ 1640DELIMITER ;$$ 1641 1642 1643--echo # 1644--echo # Complex cases with subqueries 1645--echo # 1646 1647--echo # 1648--echo # Maybe a table field identifier (there are some tables) - no error 1649--echo # 1650 1651DELIMITER $$; 1652CREATE PROCEDURE p1() 1653BEGIN 1654 DECLARE res INT DEFAULT 0; 1655 SET res=(SELECT * FROM t1 LEFT OUTER JOIN t2 USING (c1)); 1656END; 1657$$ 1658DELIMITER ;$$ 1659DROP PROCEDURE p1; 1660 1661DELIMITER $$; 1662CREATE PROCEDURE p1() 1663BEGIN 1664 DECLARE res INT DEFAULT 0; 1665 SET res=(SELECT * FROM t1 LEFT OUTER JOIN t2 ON (c1=c2)); 1666END; 1667$$ 1668DELIMITER ;$$ 1669DROP PROCEDURE p1; 1670 1671 1672--echo # 1673--echo # One unknown identifier, no tables 1674--echo # 1675 1676DELIMITER $$; 1677--error ER_SP_UNDECLARED_VAR 1678CREATE PROCEDURE p1() 1679BEGIN 1680 DECLARE a INT; 1681 SET a=unknown_ident; 1682END; 1683$$ 1684DELIMITER ;$$ 1685 1686DELIMITER $$; 1687--error ER_SP_UNDECLARED_VAR 1688CREATE PROCEDURE p1() 1689BEGIN 1690 DECLARE a INT; 1691 SET a=unknown_ident1.unknown_ident2; 1692END; 1693$$ 1694DELIMITER ;$$ 1695 1696DELIMITER $$; 1697--error ER_SP_UNDECLARED_VAR 1698CREATE PROCEDURE p1() 1699BEGIN 1700 DECLARE a INT; 1701 SET a=unknown_ident1.unknown_ident2.unknown_ident3; 1702END; 1703$$ 1704DELIMITER ;$$ 1705 1706 1707DELIMITER $$; 1708--error ER_SP_UNDECLARED_VAR 1709CREATE PROCEDURE p1() 1710BEGIN 1711 DECLARE a INT; 1712 SET a=(SELECT unknown_ident); 1713END; 1714$$ 1715DELIMITER ;$$ 1716 1717DELIMITER $$; 1718--error ER_SP_UNDECLARED_VAR 1719CREATE PROCEDURE p1() 1720BEGIN 1721 DECLARE a INT; 1722 SET a=(SELECT unknown_ident FROM dual); 1723END; 1724$$ 1725DELIMITER ;$$ 1726 1727 1728DELIMITER $$; 1729--error ER_SP_UNDECLARED_VAR 1730CREATE PROCEDURE p1() 1731BEGIN 1732 DECLARE a INT; 1733 SET a=(SELECT (SELECT unknown_ident)); 1734END; 1735$$ 1736DELIMITER ;$$ 1737 1738DELIMITER $$; 1739--error ER_SP_UNDECLARED_VAR 1740CREATE PROCEDURE p1() 1741BEGIN 1742 DECLARE a INT; 1743 SET a=(SELECT (SELECT unknown_ident FROM dual)); 1744END; 1745$$ 1746DELIMITER ;$$ 1747 1748DELIMITER $$; 1749--error ER_SP_UNDECLARED_VAR 1750CREATE PROCEDURE p1() 1751BEGIN 1752 DECLARE a INT; 1753 SET a=(SELECT 1 WHERE unknown_ident); 1754END; 1755$$ 1756DELIMITER ;$$ 1757 1758DELIMITER $$; 1759--error ER_SP_UNDECLARED_VAR 1760CREATE PROCEDURE p1() 1761BEGIN 1762 DECLARE a INT; 1763 SET a=(SELECT 1 WHERE unknown_ident=1); 1764END; 1765$$ 1766DELIMITER ;$$ 1767 1768DELIMITER $$; 1769--error ER_SP_UNDECLARED_VAR 1770CREATE PROCEDURE p1() 1771BEGIN 1772 DECLARE a INT; 1773 SET a=(SELECT 1 LIMIT unknown_ident); 1774END; 1775$$ 1776DELIMITER ;$$ 1777 1778 1779--echo # 1780--echo # GROUP, HAVING, ORDER are not tested yet for unknown identifiers 1781--echo # 1782 1783DELIMITER $$; 1784CREATE PROCEDURE p1() 1785BEGIN 1786 DECLARE a INT; 1787 SET a=(SELECT 1 GROUP BY unknown_ident); 1788END; 1789$$ 1790DELIMITER ;$$ 1791DROP PROCEDURE p1; 1792 1793 1794DELIMITER $$; 1795CREATE PROCEDURE p1() 1796BEGIN 1797 DECLARE res INT DEFAULT 0; 1798 SET res=(SELECT 1 HAVING unknown_ident); 1799END; 1800$$ 1801DELIMITER ;$$ 1802DROP PROCEDURE p1; 1803 1804 1805DELIMITER $$; 1806CREATE PROCEDURE p1() 1807BEGIN 1808 DECLARE a INT; 1809 SET a=(SELECT 1 ORDER BY unknown_ident); 1810END; 1811$$ 1812DELIMITER ;$$ 1813DROP PROCEDURE p1; 1814 1815 1816--echo # 1817--echo # HAVING + aggregate_function(unknown_identifier) is a special case 1818--echo # 1819 1820DELIMITER $$; 1821--error ER_SP_UNDECLARED_VAR 1822CREATE PROCEDURE p1() 1823BEGIN 1824 DECLARE res INT DEFAULT 0; 1825 SET res=(SELECT 1 HAVING SUM(unknown_ident)); 1826END; 1827$$ 1828DELIMITER ;$$ 1829 1830 1831--echo # 1832--echo # Known indentifier + unknown identifier, no tables 1833--echo # 1834 1835DELIMITER $$; 1836--error ER_SP_UNDECLARED_VAR 1837CREATE PROCEDURE p1() 1838BEGIN 1839 DECLARE a INT; 1840 SET a=a+unknown_ident; 1841END; 1842$$ 1843DELIMITER ;$$ 1844 1845DELIMITER $$; 1846--error ER_SP_UNDECLARED_VAR 1847CREATE PROCEDURE p1() 1848BEGIN 1849 DECLARE a INT; 1850 SET a=a+(SELECT unknown_ident); 1851END; 1852$$ 1853DELIMITER ;$$ 1854 1855DELIMITER $$; 1856--error ER_SP_UNDECLARED_VAR 1857CREATE PROCEDURE p1() 1858BEGIN 1859 DECLARE a INT; 1860 SET a=a+(SELECT unknown_ident FROM dual); 1861END; 1862$$ 1863DELIMITER ;$$ 1864 1865 1866DELIMITER $$; 1867--error ER_SP_UNDECLARED_VAR 1868CREATE PROCEDURE p1() 1869BEGIN 1870 DECLARE a INT; 1871 SET a=(SELECT (a+(SELECT unknown_ident))); 1872END; 1873$$ 1874DELIMITER ;$$ 1875 1876 1877DELIMITER $$; 1878--error ER_SP_UNDECLARED_VAR 1879CREATE PROCEDURE p1() 1880BEGIN 1881 DECLARE a INT; 1882 SET a=(SELECT (a+(SELECT unknown_ident FROM dual))); 1883END; 1884$$ 1885DELIMITER ;$$ 1886 1887 1888--echo # 1889--echo # Unknown indentifier + known identifier, no tables 1890--echo # 1891 1892DELIMITER $$; 1893--error ER_SP_UNDECLARED_VAR 1894CREATE PROCEDURE p1() 1895BEGIN 1896 DECLARE a INT; 1897 SET a=unknown_ident+a; 1898END; 1899$$ 1900DELIMITER ;$$ 1901 1902DELIMITER $$; 1903--error ER_SP_UNDECLARED_VAR 1904CREATE PROCEDURE p1() 1905BEGIN 1906 DECLARE a INT; 1907 SET a=(SELECT unknown_ident)+a; 1908END; 1909$$ 1910DELIMITER ;$$ 1911 1912DELIMITER $$; 1913--error ER_SP_UNDECLARED_VAR 1914CREATE PROCEDURE p1() 1915BEGIN 1916 DECLARE a INT; 1917 SET a=(SELECT unknown_ident FROM dual)+a; 1918END; 1919$$ 1920DELIMITER ;$$ 1921 1922DELIMITER $$; 1923--error ER_SP_UNDECLARED_VAR 1924CREATE PROCEDURE p1() 1925BEGIN 1926 DECLARE a INT; 1927 SET a=(SELECT (SELECT unknown_ident)+a); 1928END; 1929$$ 1930DELIMITER ;$$ 1931 1932DELIMITER $$; 1933--error ER_SP_UNDECLARED_VAR 1934CREATE PROCEDURE p1() 1935BEGIN 1936 DECLARE a INT; 1937 SET a=(SELECT (SELECT unknown_ident FROM dual)+a); 1938END; 1939$$ 1940DELIMITER ;$$ 1941 1942 1943--echo # 1944--echo # Maybe a table field indentifier + unknown identifier 1945--echo # 1946 1947DELIMITER $$; 1948--error ER_SP_UNDECLARED_VAR 1949CREATE PROCEDURE p1() 1950BEGIN 1951 DECLARE a INT; 1952 SET a=(SELECT c1 FROM t1)+unknown_ident; 1953END; 1954$$ 1955DELIMITER ;$$ 1956 1957DELIMITER $$; 1958--error ER_SP_UNDECLARED_VAR 1959CREATE PROCEDURE p1() 1960BEGIN 1961 DECLARE a INT; 1962 SET a=(SELECT c1 FROM t1)+(SELECT unknown_ident); 1963END; 1964$$ 1965DELIMITER ;$$ 1966 1967DELIMITER $$; 1968--error ER_SP_UNDECLARED_VAR 1969CREATE PROCEDURE p1() 1970BEGIN 1971 DECLARE a INT; 1972 SET a=(SELECT c1 FROM t1)+(SELECT unknown_ident FROM dual); 1973END; 1974$$ 1975DELIMITER ;$$ 1976 1977 1978DELIMITER $$; 1979--error ER_SP_UNDECLARED_VAR 1980CREATE PROCEDURE p1() 1981BEGIN 1982 DECLARE a INT; 1983 SET a=(SELECT ((SELECT c1 FROM t1)+(SELECT unknown_ident))); 1984END; 1985$$ 1986DELIMITER ;$$ 1987 1988DELIMITER $$; 1989--error ER_SP_UNDECLARED_VAR 1990CREATE PROCEDURE p1() 1991BEGIN 1992 DECLARE a INT; 1993 SET a=(SELECT ((SELECT c1 FROM t1)+(SELECT unknown_ident FROM dual))); 1994END; 1995$$ 1996DELIMITER ;$$ 1997 1998 1999--echo # 2000--echo # Unknown indentifier + maybe a table field identifier 2001--echo # 2002 2003DELIMITER $$; 2004--error ER_SP_UNDECLARED_VAR 2005CREATE PROCEDURE p1() 2006BEGIN 2007 DECLARE a INT; 2008 SET a=unknown_ident+(SELECT c1 FROM t1); 2009END; 2010$$ 2011DELIMITER ;$$ 2012 2013DELIMITER $$; 2014--error ER_SP_UNDECLARED_VAR 2015CREATE PROCEDURE p1() 2016BEGIN 2017 DECLARE a INT; 2018 SET a=(SELECT unknown_ident)+(SELECT c1 FROM t1); 2019END; 2020$$ 2021DELIMITER ;$$ 2022 2023DELIMITER $$; 2024--error ER_SP_UNDECLARED_VAR 2025CREATE PROCEDURE p1() 2026BEGIN 2027 DECLARE a INT; 2028 SET a=(SELECT unknown_ident FROM dual)+(SELECT c1 FROM t1); 2029END; 2030$$ 2031DELIMITER ;$$ 2032 2033DELIMITER $$; 2034--error ER_SP_UNDECLARED_VAR 2035CREATE PROCEDURE p1() 2036BEGIN 2037 DECLARE a INT; 2038 SET a=(SELECT (SELECT unknown_ident)+(SELECT c1 FROM t1)); 2039END; 2040$$ 2041DELIMITER ;$$ 2042 2043DELIMITER $$; 2044--error ER_SP_UNDECLARED_VAR 2045CREATE PROCEDURE p1() 2046BEGIN 2047 DECLARE a INT; 2048 SET a=(SELECT (SELECT unknown_ident FROM dual)+(SELECT c1 FROM t1)); 2049END; 2050$$ 2051DELIMITER ;$$ 2052 2053 2054--echo # 2055--echo # Maybe a table field identifier + maybe a field table identifier 2056--echo # 2057 2058DELIMITER $$; 2059CREATE PROCEDURE p1() 2060BEGIN 2061 DECLARE a INT; 2062 -- c2 does not have a table on its level 2063 -- but it can be a field of a table on the uppder level, i.e. t1 2064 SET a=(SELECT c1+(SELECT c2) FROM t1); 2065END; 2066$$ 2067DELIMITER ;$$ 2068DROP PROCEDURE p1; 2069 2070 2071--echo # 2072--echo # TVC - unknown identifier 2073--echo # 2074 2075DELIMITER $$; 2076--error ER_SP_UNDECLARED_VAR 2077CREATE PROCEDURE p1(a INT) 2078BEGIN 2079 DECLARE res INT DEFAULT 0; 2080 SET res=(VALUES(unknown_ident)); 2081END; 2082$$ 2083DELIMITER ;$$ 2084 2085DELIMITER $$; 2086--error ER_SP_UNDECLARED_VAR 2087CREATE PROCEDURE p1(a INT) 2088BEGIN 2089 DECLARE res INT DEFAULT 0; 2090 SET res=(VALUES(1),(unknown_ident)); 2091END; 2092$$ 2093DELIMITER ;$$ 2094 2095DELIMITER $$; 2096--error ER_SP_UNDECLARED_VAR 2097CREATE PROCEDURE p1(a INT) 2098BEGIN 2099 DECLARE res INT DEFAULT 0; 2100 SET res=(VALUES((SELECT unknown_ident))); 2101END; 2102$$ 2103DELIMITER ;$$ 2104 2105DELIMITER $$; 2106--error ER_SP_UNDECLARED_VAR 2107CREATE PROCEDURE p1(a INT) 2108BEGIN 2109 DECLARE res INT DEFAULT 0; 2110 SET res=(VALUES(1),((SELECT unknown_ident))); 2111END; 2112$$ 2113DELIMITER ;$$ 2114 2115 2116DELIMITER $$; 2117--error ER_SP_UNDECLARED_VAR 2118CREATE PROCEDURE p1(a INT) 2119BEGIN 2120 DECLARE res INT DEFAULT 0; 2121 SET res=(VALUES(1) LIMIT unknown_ident); 2122END; 2123$$ 2124DELIMITER ;$$ 2125 2126 2127--echo # 2128--echo # TVC - ORDER BY - not tested yet for unknown identifiers 2129--echo # 2130 2131DELIMITER $$; 2132CREATE PROCEDURE p1(a INT) 2133BEGIN 2134 DECLARE res INT DEFAULT 0; 2135 SET res=(VALUES(1) ORDER BY unknown_ident); 2136END; 2137$$ 2138DELIMITER ;$$ 2139DROP PROCEDURE p1; 2140 2141 2142--echo # 2143--echo # TVC - maybe a table field identifier - no error 2144--echo # 2145 2146DELIMITER $$; 2147CREATE PROCEDURE p1(a INT) 2148BEGIN 2149 DECLARE res INT DEFAULT 0; 2150 SET res=(VALUES((SELECT c1 FROM t1))); 2151END; 2152$$ 2153DELIMITER ;$$ 2154DROP PROCEDURE p1; 2155 2156DELIMITER $$; 2157CREATE PROCEDURE p1(a INT) 2158BEGIN 2159 DECLARE res INT DEFAULT 0; 2160 SET res=(VALUES(1),((SELECT c1 FROM t1))); 2161END; 2162$$ 2163DELIMITER ;$$ 2164DROP PROCEDURE p1; 2165 2166 2167--echo # 2168--echo # Functions DEFAULT(x) and VALUE(x) 2169--echo # 2170 2171DELIMITER $$; 2172--error ER_SP_UNDECLARED_VAR 2173CREATE PROCEDURE p1() 2174BEGIN 2175 DECLARE res INT DEFAULT 0; 2176 SET res=DEFAULT(unknown_ident); 2177 SELECT res; 2178END; 2179$$ 2180DELIMITER ;$$ 2181 2182DELIMITER $$; 2183--error ER_SP_UNDECLARED_VAR 2184CREATE PROCEDURE p1() 2185BEGIN 2186 DECLARE res INT DEFAULT 0; 2187 SET res=VALUE(unknown_ident); 2188 SELECT res; 2189END; 2190$$ 2191DELIMITER ;$$ 2192 2193 2194--echo # 2195--echo # End of MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable 2196--echo # 2197