1DROP PROCEDURE IF EXISTS sp_vars_check_dflt; 2DROP PROCEDURE IF EXISTS sp_vars_check_assignment; 3DROP FUNCTION IF EXISTS sp_vars_check_ret1; 4DROP FUNCTION IF EXISTS sp_vars_check_ret2; 5DROP FUNCTION IF EXISTS sp_vars_check_ret3; 6DROP FUNCTION IF EXISTS sp_vars_check_ret4; 7DROP FUNCTION IF EXISTS sp_vars_div_zero; 8SET @@sql_mode = 'ansi'; 9CREATE PROCEDURE sp_vars_check_dflt() 10BEGIN 11DECLARE v1 TINYINT DEFAULT 1e200; 12DECLARE v1u TINYINT UNSIGNED DEFAULT 1e200; 13DECLARE v2 TINYINT DEFAULT -1e200; 14DECLARE v2u TINYINT UNSIGNED DEFAULT -1e200; 15DECLARE v3 TINYINT DEFAULT 300; 16DECLARE v3u TINYINT UNSIGNED DEFAULT 300; 17DECLARE v4 TINYINT DEFAULT -300; 18DECLARE v4u TINYINT UNSIGNED DEFAULT -300; 19DECLARE v5 TINYINT DEFAULT 10 * 10 * 10; 20DECLARE v5u TINYINT UNSIGNED DEFAULT 10 * 10 * 10; 21DECLARE v6 TINYINT DEFAULT -10 * 10 * 10; 22DECLARE v6u TINYINT UNSIGNED DEFAULT -10 * 10 * 10; 23DECLARE v7 TINYINT DEFAULT '10'; 24DECLARE v8 TINYINT DEFAULT '10 '; 25DECLARE v9 TINYINT DEFAULT ' 10 '; 26DECLARE v10 TINYINT DEFAULT 'String 10 '; 27DECLARE v11 TINYINT DEFAULT 'String10'; 28DECLARE v12 TINYINT DEFAULT '10 String'; 29DECLARE v13 TINYINT DEFAULT '10String'; 30DECLARE v14 TINYINT DEFAULT concat('10', ' '); 31DECLARE v15 TINYINT DEFAULT concat(' ', '10'); 32DECLARE v16 TINYINT DEFAULT concat('Hello, ', 'world'); 33DECLARE v17 DECIMAL(64, 2) DEFAULT 12; 34DECLARE v18 DECIMAL(64, 2) DEFAULT 12.123; 35DECLARE v19 DECIMAL(64, 2) DEFAULT 11 + 1; 36DECLARE v20 DECIMAL(64, 2) DEFAULT 12 + 0.123; 37SELECT v1, v1u, v2, v2u, v3, v3u, v4, v4u; 38SELECT v5, v5u, v6, v6u; 39SELECT v7, v8, v9, v10, v11, v12, v13, v14, v15, v16; 40SELECT v17, v18, v19, v20; 41END| 42CREATE PROCEDURE sp_vars_check_assignment() 43BEGIN 44DECLARE i1, i2, i3, i4 TINYINT; 45DECLARE u1, u2, u3, u4 TINYINT UNSIGNED; 46DECLARE d1, d2, d3 DECIMAL(64, 2); 47SET i1 = 1e200; 48SET i2 = -1e200; 49SET i3 = 300; 50SET i4 = -300; 51SELECT i1, i2, i3, i4; 52SET i1 = 10 * 10 * 10; 53SET i2 = -10 * 10 * 10; 54SET i3 = sign(10 * 10) * 10 * 20; 55SET i4 = sign(-10 * 10) * -10 * 20; 56SELECT i1, i2, i3, i4; 57SET u1 = 1e200; 58SET u2 = -1e200; 59SET u3 = 300; 60SET u4 = -300; 61SELECT u1, u2, u3, u4; 62SET u1 = 10 * 10 * 10; 63SET u2 = -10 * 10 * 10; 64SET u3 = sign(10 * 10) * 10 * 20; 65SET u4 = sign(-10 * 10) * -10 * 20; 66SELECT u1, u2, u3, u4; 67SET d1 = 1234; 68SET d2 = 1234.12; 69SET d3 = 1234.1234; 70SELECT d1, d2, d3; 71SET d1 = 12 * 100 + 34; 72SET d2 = 12 * 100 + 34 + 0.12; 73SET d3 = 12 * 100 + 34 + 0.1234; 74SELECT d1, d2, d3; 75END| 76CREATE FUNCTION sp_vars_check_ret1() RETURNS TINYINT 77BEGIN 78RETURN 1e200; 79END| 80CREATE FUNCTION sp_vars_check_ret2() RETURNS TINYINT 81BEGIN 82RETURN 10 * 10 * 10; 83END| 84CREATE FUNCTION sp_vars_check_ret3() RETURNS TINYINT 85BEGIN 86RETURN 'Hello, world'; 87END| 88CREATE FUNCTION sp_vars_check_ret4() RETURNS DECIMAL(64, 2) 89BEGIN 90RETURN 12 * 10 + 34 + 0.1234; 91END| 92CREATE FUNCTION sp_vars_div_zero() RETURNS INTEGER 93BEGIN 94DECLARE div_zero INTEGER; 95SELECT 1/0 INTO div_zero; 96RETURN div_zero; 97END| 98 99--------------------------------------------------------------- 100Calling the routines, created in ANSI mode. 101--------------------------------------------------------------- 102 103CALL sp_vars_check_dflt(); 104v1 v1u v2 v2u v3 v3u v4 v4u 105127 255 -128 0 127 255 -128 0 106v5 v5u v6 v6u 107127 255 -128 0 108v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 10910 10 10 0 0 10 10 10 10 0 110v17 v18 v19 v20 11112.00 12.12 12.00 12.12 112Warnings: 113Note 1265 Data truncated for column 'v20' at row 1 114CALL sp_vars_check_assignment(); 115i1 i2 i3 i4 116127 -128 127 -128 117i1 i2 i3 i4 118127 -128 127 127 119u1 u2 u3 u4 120255 0 255 0 121u1 u2 u3 u4 122255 0 200 200 123d1 d2 d3 1241234.00 1234.12 1234.12 125d1 d2 d3 1261234.00 1234.12 1234.12 127Warnings: 128Note 1265 Data truncated for column 'd3' at row 1 129SELECT sp_vars_check_ret1(); 130sp_vars_check_ret1() 131127 132Warnings: 133Warning 1264 Out of range value for column 'sp_vars_check_ret1()' at row 1 134SELECT sp_vars_check_ret2(); 135sp_vars_check_ret2() 136127 137Warnings: 138Warning 1264 Out of range value for column 'sp_vars_check_ret2()' at row 1 139SELECT sp_vars_check_ret3(); 140sp_vars_check_ret3() 1410 142Warnings: 143Warning 1366 Incorrect integer value: 'Hello, world' for column ``.``.`sp_vars_check_ret3()` at row 1 144SELECT sp_vars_check_ret4(); 145sp_vars_check_ret4() 146154.12 147Warnings: 148Note 1265 Data truncated for column 'sp_vars_check_ret4()' at row 1 149SELECT sp_vars_div_zero(); 150sp_vars_div_zero() 151NULL 152SET @@sql_mode = 'traditional'; 153 154--------------------------------------------------------------- 155Calling in TRADITIONAL mode the routines, created in ANSI mode. 156--------------------------------------------------------------- 157 158CALL sp_vars_check_dflt(); 159v1 v1u v2 v2u v3 v3u v4 v4u 160127 255 -128 0 127 255 -128 0 161v5 v5u v6 v6u 162127 255 -128 0 163v7 v8 v9 v10 v11 v12 v13 v14 v15 v16 16410 10 10 0 0 10 10 10 10 0 165v17 v18 v19 v20 16612.00 12.12 12.00 12.12 167Warnings: 168Note 1265 Data truncated for column 'v20' at row 1 169CALL sp_vars_check_assignment(); 170i1 i2 i3 i4 171127 -128 127 -128 172i1 i2 i3 i4 173127 -128 127 127 174u1 u2 u3 u4 175255 0 255 0 176u1 u2 u3 u4 177255 0 200 200 178d1 d2 d3 1791234.00 1234.12 1234.12 180d1 d2 d3 1811234.00 1234.12 1234.12 182Warnings: 183Note 1265 Data truncated for column 'd3' at row 1 184SELECT sp_vars_check_ret1(); 185sp_vars_check_ret1() 186127 187Warnings: 188Warning 1264 Out of range value for column 'sp_vars_check_ret1()' at row 1 189SELECT sp_vars_check_ret2(); 190sp_vars_check_ret2() 191127 192Warnings: 193Warning 1264 Out of range value for column 'sp_vars_check_ret2()' at row 1 194SELECT sp_vars_check_ret3(); 195sp_vars_check_ret3() 1960 197Warnings: 198Warning 1366 Incorrect integer value: 'Hello, world' for column ``.``.`sp_vars_check_ret3()` at row 1 199SELECT sp_vars_check_ret4(); 200sp_vars_check_ret4() 201154.12 202Warnings: 203Note 1265 Data truncated for column 'sp_vars_check_ret4()' at row 1 204SELECT sp_vars_div_zero(); 205sp_vars_div_zero() 206NULL 207DROP PROCEDURE sp_vars_check_dflt; 208DROP PROCEDURE sp_vars_check_assignment; 209DROP FUNCTION sp_vars_check_ret1; 210DROP FUNCTION sp_vars_check_ret2; 211DROP FUNCTION sp_vars_check_ret3; 212DROP FUNCTION sp_vars_check_ret4; 213DROP FUNCTION sp_vars_div_zero; 214CREATE PROCEDURE sp_vars_check_dflt() 215BEGIN 216DECLARE v1 TINYINT DEFAULT 1e200; 217DECLARE v1u TINYINT UNSIGNED DEFAULT 1e200; 218DECLARE v2 TINYINT DEFAULT -1e200; 219DECLARE v2u TINYINT UNSIGNED DEFAULT -1e200; 220DECLARE v3 TINYINT DEFAULT 300; 221DECLARE v3u TINYINT UNSIGNED DEFAULT 300; 222DECLARE v4 TINYINT DEFAULT -300; 223DECLARE v4u TINYINT UNSIGNED DEFAULT -300; 224DECLARE v5 TINYINT DEFAULT 10 * 10 * 10; 225DECLARE v5u TINYINT UNSIGNED DEFAULT 10 * 10 * 10; 226DECLARE v6 TINYINT DEFAULT -10 * 10 * 10; 227DECLARE v6u TINYINT UNSIGNED DEFAULT -10 * 10 * 10; 228DECLARE v7 TINYINT DEFAULT '10'; 229DECLARE v8 TINYINT DEFAULT '10 '; 230DECLARE v9 TINYINT DEFAULT ' 10 '; 231DECLARE v10 TINYINT DEFAULT 'String 10 '; 232DECLARE v11 TINYINT DEFAULT 'String10'; 233DECLARE v12 TINYINT DEFAULT '10 String'; 234DECLARE v13 TINYINT DEFAULT '10String'; 235DECLARE v14 TINYINT DEFAULT concat('10', ' '); 236DECLARE v15 TINYINT DEFAULT concat(' ', '10'); 237DECLARE v16 TINYINT DEFAULT concat('Hello, ', 'world'); 238DECLARE v17 DECIMAL(64, 2) DEFAULT 12; 239DECLARE v18 DECIMAL(64, 2) DEFAULT 12.123; 240DECLARE v19 DECIMAL(64, 2) DEFAULT 11 + 1; 241DECLARE v20 DECIMAL(64, 2) DEFAULT 12 + 0.123; 242SELECT v1, v1u, v2, v2u, v3, v3u, v4, v4u; 243SELECT v5, v5u, v6, v6u; 244SELECT v7, v8, v9, v10, v11, v12, v13, v14, v15, v16; 245SELECT v17, v18, v19, v20; 246END| 247CREATE PROCEDURE sp_vars_check_assignment() 248BEGIN 249DECLARE i1, i2, i3, i4 TINYINT; 250DECLARE u1, u2, u3, u4 TINYINT UNSIGNED; 251DECLARE d1, d2, d3 DECIMAL(64, 2); 252SET i1 = 1e200; 253SET i2 = -1e200; 254SET i3 = 300; 255SET i4 = -300; 256SELECT i1, i2, i3, i4; 257SET i1 = 10 * 10 * 10; 258SET i2 = -10 * 10 * 10; 259SET i3 = sign(10 * 10) * 10 * 20; 260SET i4 = sign(-10 * 10) * -10 * 20; 261SELECT i1, i2, i3, i4; 262SET u1 = 1e200; 263SET u2 = -1e200; 264SET u3 = 300; 265SET u4 = -300; 266SELECT u1, u2, u3, u4; 267SET u1 = 10 * 10 * 10; 268SET u2 = -10 * 10 * 10; 269SET u3 = sign(10 * 10) * 10 * 20; 270SET u4 = sign(-10 * 10) * -10 * 20; 271SELECT u1, u2, u3, u4; 272SET d1 = 1234; 273SET d2 = 1234.12; 274SET d3 = 1234.1234; 275SELECT d1, d2, d3; 276SET d1 = 12 * 100 + 34; 277SET d2 = 12 * 100 + 34 + 0.12; 278SET d3 = 12 * 100 + 34 + 0.1234; 279SELECT d1, d2, d3; 280END| 281CREATE FUNCTION sp_vars_check_ret1() RETURNS TINYINT 282BEGIN 283RETURN 1e200; 284END| 285CREATE FUNCTION sp_vars_check_ret2() RETURNS TINYINT 286BEGIN 287RETURN 10 * 10 * 10; 288END| 289CREATE FUNCTION sp_vars_check_ret3() RETURNS TINYINT 290BEGIN 291RETURN 'Hello, world'; 292END| 293CREATE FUNCTION sp_vars_check_ret4() RETURNS DECIMAL(64, 2) 294BEGIN 295RETURN 12 * 10 + 34 + 0.1234; 296END| 297CREATE FUNCTION sp_vars_div_zero() RETURNS INTEGER 298BEGIN 299DECLARE div_zero INTEGER; 300SELECT 1/0 INTO div_zero; 301RETURN div_zero; 302END| 303 304--------------------------------------------------------------- 305Calling the routines, created in TRADITIONAL mode. 306--------------------------------------------------------------- 307 308CALL sp_vars_check_dflt(); 309ERROR 22003: Out of range value for column 'v1' at row 1 310CALL sp_vars_check_assignment(); 311ERROR 22003: Out of range value for column 'i1' at row 1 312SELECT sp_vars_check_ret1(); 313ERROR 22003: Out of range value for column 'sp_vars_check_ret1()' at row 1 314SELECT sp_vars_check_ret2(); 315ERROR 22003: Out of range value for column 'sp_vars_check_ret2()' at row 1 316SELECT sp_vars_check_ret3(); 317ERROR 22007: Incorrect integer value: 'Hello, world' for column ``.``.`sp_vars_check_ret3()` at row 1 318SELECT sp_vars_check_ret4(); 319sp_vars_check_ret4() 320154.12 321Warnings: 322Note 1265 Data truncated for column 'sp_vars_check_ret4()' at row 1 323SELECT sp_vars_div_zero(); 324ERROR 22012: Division by 0 325SET @@sql_mode = 'ansi'; 326DROP PROCEDURE sp_vars_check_dflt; 327DROP PROCEDURE sp_vars_check_assignment; 328DROP FUNCTION sp_vars_check_ret1; 329DROP FUNCTION sp_vars_check_ret2; 330DROP FUNCTION sp_vars_check_ret3; 331DROP FUNCTION sp_vars_check_ret4; 332DROP FUNCTION sp_vars_div_zero; 333 334--------------------------------------------------------------- 335BIT data type tests 336--------------------------------------------------------------- 337 338DROP PROCEDURE IF EXISTS p1; 339CREATE PROCEDURE p1() 340BEGIN 341DECLARE v1 BIT; 342DECLARE v2 BIT(1); 343DECLARE v3 BIT(3) DEFAULT b'101'; 344DECLARE v4 BIT(64) DEFAULT 0x5555555555555555; 345DECLARE v5 BIT(3); 346DECLARE v6 BIT(64); 347DECLARE v7 BIT(8) DEFAULT 128; 348DECLARE v8 BIT(8) DEFAULT '128'; 349DECLARE v9 BIT(8) DEFAULT ' 128'; 350DECLARE v10 BIT(8) DEFAULT 'x 128'; 351SET v1 = v4; 352SET v2 = 0; 353SET v5 = v4; # check overflow 354SET v6 = v3; # check padding 355SELECT HEX(v1); 356SELECT HEX(v2); 357SELECT HEX(v3); 358SELECT HEX(v4); 359SELECT HEX(v5); 360SELECT HEX(v6); 361SELECT HEX(v7); 362SELECT HEX(v8); 363SELECT HEX(v9); 364SELECT HEX(v10); 365END| 366CALL p1(); 367HEX(v1) 3681 369HEX(v2) 3700 371HEX(v3) 3725 373HEX(v4) 3745555555555555555 375HEX(v5) 3767 377HEX(v6) 3785 379HEX(v7) 38080 381HEX(v8) 382FF 383HEX(v9) 384FF 385HEX(v10) 386FF 387Warnings: 388Warning 1264 Out of range value for column 'v5' at row 1 389DROP PROCEDURE p1; 390 391--------------------------------------------------------------- 392CASE expression tests. 393--------------------------------------------------------------- 394 395DROP PROCEDURE IF EXISTS p1; 396Warnings: 397Note 1305 PROCEDURE test.p1 does not exist 398DROP PROCEDURE IF EXISTS p2; 399Warnings: 400Note 1305 PROCEDURE test.p2 does not exist 401DROP TABLE IF EXISTS t1; 402Warnings: 403Note 1051 Unknown table 'test.t1' 404CREATE TABLE t1(log_msg VARCHAR(1024)); 405CREATE PROCEDURE p1(arg VARCHAR(255)) 406BEGIN 407INSERT INTO t1 VALUES('p1: step1'); 408CASE arg * 10 409WHEN 10 * 10 THEN 410INSERT INTO t1 VALUES('p1: case1: on 10'); 411WHEN 10 * 10 + 10 * 10 THEN 412BEGIN 413CASE arg / 10 414WHEN 1 THEN 415INSERT INTO t1 VALUES('p1: case1: case2: on 1'); 416WHEN 2 THEN 417BEGIN 418DECLARE i TINYINT DEFAULT 10; 419WHILE i > 0 DO 420INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i)); 421CASE MOD(i, 2) 422WHEN 0 THEN 423INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even'); 424WHEN 1 THEN 425INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd'); 426ELSE 427INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR'); 428END CASE; 429SET i = i - 1; 430END WHILE; 431END; 432ELSE 433INSERT INTO t1 VALUES('p1: case1: case2: ERROR'); 434END CASE; 435CASE arg 436WHEN 10 THEN 437INSERT INTO t1 VALUES('p1: case1: case3: on 10'); 438WHEN 20 THEN 439INSERT INTO t1 VALUES('p1: case1: case3: on 20'); 440ELSE 441INSERT INTO t1 VALUES('p1: case1: case3: ERROR'); 442END CASE; 443END; 444ELSE 445INSERT INTO t1 VALUES('p1: case1: ERROR'); 446END CASE; 447CASE arg * 10 448WHEN 10 * 10 THEN 449INSERT INTO t1 VALUES('p1: case4: on 10'); 450WHEN 10 * 10 + 10 * 10 THEN 451BEGIN 452CASE arg / 10 453WHEN 1 THEN 454INSERT INTO t1 VALUES('p1: case4: case5: on 1'); 455WHEN 2 THEN 456BEGIN 457DECLARE i TINYINT DEFAULT 10; 458WHILE i > 0 DO 459INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i)); 460CASE MOD(i, 2) 461WHEN 0 THEN 462INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even'); 463WHEN 1 THEN 464INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd'); 465ELSE 466INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR'); 467END CASE; 468SET i = i - 1; 469END WHILE; 470END; 471ELSE 472INSERT INTO t1 VALUES('p1: case4: case5: ERROR'); 473END CASE; 474CASE arg 475WHEN 10 THEN 476INSERT INTO t1 VALUES('p1: case4: case6: on 10'); 477WHEN 20 THEN 478INSERT INTO t1 VALUES('p1: case4: case6: on 20'); 479ELSE 480INSERT INTO t1 VALUES('p1: case4: case6: ERROR'); 481END CASE; 482END; 483ELSE 484INSERT INTO t1 VALUES('p1: case4: ERROR'); 485END CASE; 486END| 487CREATE PROCEDURE p2() 488BEGIN 489DECLARE i TINYINT DEFAULT 3; 490WHILE i > 0 DO 491IF MOD(i, 2) = 0 THEN 492SET @_test_session_var = 10; 493ELSE 494SET @_test_session_var = 'test'; 495END IF; 496CASE @_test_session_var 497WHEN 10 THEN 498INSERT INTO t1 VALUES('p2: case: numerical type'); 499WHEN 'test' THEN 500INSERT INTO t1 VALUES('p2: case: string type'); 501ELSE 502INSERT INTO t1 VALUES('p2: case: ERROR'); 503END CASE; 504SET i = i - 1; 505END WHILE; 506END| 507CALL p1(10); 508CALL p1(20); 509CALL p2(); 510SELECT * FROM t1; 511log_msg 512p1: step1 513p1: case1: on 10 514p1: case4: on 10 515p1: step1 516p1: case1: case2: loop: i: 10 517p1: case1: case2: loop: i is even 518p1: case1: case2: loop: i: 9 519p1: case1: case2: loop: i is odd 520p1: case1: case2: loop: i: 8 521p1: case1: case2: loop: i is even 522p1: case1: case2: loop: i: 7 523p1: case1: case2: loop: i is odd 524p1: case1: case2: loop: i: 6 525p1: case1: case2: loop: i is even 526p1: case1: case2: loop: i: 5 527p1: case1: case2: loop: i is odd 528p1: case1: case2: loop: i: 4 529p1: case1: case2: loop: i is even 530p1: case1: case2: loop: i: 3 531p1: case1: case2: loop: i is odd 532p1: case1: case2: loop: i: 2 533p1: case1: case2: loop: i is even 534p1: case1: case2: loop: i: 1 535p1: case1: case2: loop: i is odd 536p1: case1: case3: on 20 537p1: case4: case5: loop: i: 10 538p1: case4: case5: loop: i is even 539p1: case4: case5: loop: i: 9 540p1: case4: case5: loop: i is odd 541p1: case4: case5: loop: i: 8 542p1: case4: case5: loop: i is even 543p1: case4: case5: loop: i: 7 544p1: case4: case5: loop: i is odd 545p1: case4: case5: loop: i: 6 546p1: case4: case5: loop: i is even 547p1: case4: case5: loop: i: 5 548p1: case4: case5: loop: i is odd 549p1: case4: case5: loop: i: 4 550p1: case4: case5: loop: i is even 551p1: case4: case5: loop: i: 3 552p1: case4: case5: loop: i is odd 553p1: case4: case5: loop: i: 2 554p1: case4: case5: loop: i is even 555p1: case4: case5: loop: i: 1 556p1: case4: case5: loop: i is odd 557p1: case4: case6: on 20 558p2: case: string type 559p2: case: numerical type 560p2: case: string type 561DROP PROCEDURE p1; 562DROP PROCEDURE p2; 563DROP TABLE t1; 564 565--------------------------------------------------------------- 566BUG#14161 567--------------------------------------------------------------- 568 569DROP TABLE IF EXISTS t1; 570DROP PROCEDURE IF EXISTS p1; 571CREATE TABLE t1(col BIGINT UNSIGNED); 572INSERT INTO t1 VALUE(18446744073709551614); 573CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED) 574BEGIN 575SELECT arg; 576SELECT * FROM t1; 577SELECT * FROM t1 WHERE col = arg; 578END| 579CALL p1(18446744073709551614); 580arg 58118446744073709551614 582col 58318446744073709551614 584col 58518446744073709551614 586DROP TABLE t1; 587DROP PROCEDURE p1; 588 589--------------------------------------------------------------- 590BUG#13705 591--------------------------------------------------------------- 592 593DROP PROCEDURE IF EXISTS p1; 594CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA 595BEGIN 596SELECT x, y; 597END| 598CALL p1('alpha', 'abc'); 599x y 600alpha abc 601CALL p1('alpha', 'abcdef'); 602x y 603alpha abc 604Warnings: 605Warning 1265 Data truncated for column 'y' at row 1 606DROP PROCEDURE p1; 607 608--------------------------------------------------------------- 609BUG#13675 610--------------------------------------------------------------- 611 612DROP PROCEDURE IF EXISTS p1; 613DROP TABLE IF EXISTS t1; 614CREATE PROCEDURE p1(x DATETIME) 615BEGIN 616CREATE TABLE t1 SELECT x; 617SHOW CREATE TABLE t1; 618DROP TABLE t1; 619END| 620CALL p1(NOW()); 621Table Create Table 622t1 CREATE TABLE "t1" ( 623 "x" datetime DEFAULT NULL 624) 625CALL p1('test'); 626Table Create Table 627t1 CREATE TABLE "t1" ( 628 "x" datetime DEFAULT NULL 629) 630Warnings: 631Warning 1265 Data truncated for column 'x' at row 1 632DROP PROCEDURE p1; 633 634--------------------------------------------------------------- 635BUG#12976 636--------------------------------------------------------------- 637 638DROP TABLE IF EXISTS t1; 639DROP PROCEDURE IF EXISTS p1; 640DROP PROCEDURE IF EXISTS p2; 641CREATE TABLE t1(b BIT(1)); 642INSERT INTO t1(b) VALUES(b'0'), (b'1'); 643CREATE PROCEDURE p1() 644BEGIN 645SELECT HEX(b), 646b = 0, 647b = FALSE, 648b IS FALSE, 649b = 1, 650b = TRUE, 651b IS TRUE 652FROM t1; 653END| 654CREATE PROCEDURE p2() 655BEGIN 656DECLARE vb BIT(1); 657SELECT b INTO vb FROM t1 WHERE b = 0; 658SELECT HEX(vb), 659vb = 0, 660vb = FALSE, 661vb IS FALSE, 662vb = 1, 663vb = TRUE, 664vb IS TRUE; 665SELECT b INTO vb FROM t1 WHERE b = 1; 666SELECT HEX(vb), 667vb = 0, 668vb = FALSE, 669vb IS FALSE, 670vb = 1, 671vb = TRUE, 672vb IS TRUE; 673END| 674call p1(); 675HEX(b) b = 0 b = FALSE b IS FALSE b = 1 b = TRUE b IS TRUE 6760 1 1 1 0 0 0 6771 0 0 0 1 1 1 678call p2(); 679HEX(vb) vb = 0 vb = FALSE vb IS FALSE vb = 1 vb = TRUE vb IS TRUE 6800 1 1 1 0 0 0 681HEX(vb) vb = 0 vb = FALSE vb IS FALSE vb = 1 vb = TRUE vb IS TRUE 6821 0 0 0 1 1 1 683DROP TABLE t1; 684DROP PROCEDURE p1; 685DROP PROCEDURE p2; 686DROP TABLE IF EXISTS table_12976_a; 687DROP TABLE IF EXISTS table_12976_b; 688DROP PROCEDURE IF EXISTS proc_12976_a; 689DROP PROCEDURE IF EXISTS proc_12976_b; 690CREATE TABLE table_12976_a (val bit(1)); 691CREATE TABLE table_12976_b( 692appname varchar(15), 693emailperm bit not null default 1, 694phoneperm bit not null default 0); 695insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0'); 696CREATE PROCEDURE proc_12976_a() 697BEGIN 698declare localvar bit(1); 699SELECT val INTO localvar FROM table_12976_a; 700SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a; 701END|| 702CREATE PROCEDURE proc_12976_b( 703name varchar(15), 704out ep bit, 705out msg varchar(10)) 706BEGIN 707SELECT emailperm into ep FROM table_12976_b where (appname = name); 708IF ep is true THEN 709SET msg = 'True'; 710ELSE 711SET msg = 'False'; 712END IF; 713END|| 714INSERT table_12976_a VALUES (0); 715call proc_12976_a(); 716coalesce(localvar, 1)+1 coalesce(val, 1)+1 7171 1 718UPDATE table_12976_a set val=1; 719call proc_12976_a(); 720coalesce(localvar, 1)+1 coalesce(val, 1)+1 7212 2 722call proc_12976_b('A', @ep, @msg); 723select @ep, @msg; 724@ep @msg 7251 True 726call proc_12976_b('B', @ep, @msg); 727select @ep, @msg; 728@ep @msg 7290 False 730DROP TABLE table_12976_a; 731DROP TABLE table_12976_b; 732DROP PROCEDURE proc_12976_a; 733DROP PROCEDURE proc_12976_b; 734 735--------------------------------------------------------------- 736BUG#9572 737--------------------------------------------------------------- 738 739DROP PROCEDURE IF EXISTS p1; 740DROP PROCEDURE IF EXISTS p2; 741DROP PROCEDURE IF EXISTS p3; 742DROP PROCEDURE IF EXISTS p4; 743DROP PROCEDURE IF EXISTS p5; 744DROP PROCEDURE IF EXISTS p6; 745SET @@sql_mode = 'traditional'; 746CREATE PROCEDURE p1() 747BEGIN 748DECLARE v TINYINT DEFAULT 1e200; 749SELECT v; 750END| 751CREATE PROCEDURE p2() 752BEGIN 753DECLARE v DECIMAL(5) DEFAULT 1e200; 754SELECT v; 755END| 756CREATE PROCEDURE p3() 757BEGIN 758DECLARE v CHAR(5) DEFAULT 'abcdef'; 759SELECT v LIKE 'abc___'; 760END| 761CREATE PROCEDURE p4(arg VARCHAR(2)) 762BEGIN 763DECLARE var VARCHAR(1); 764SET var := arg; 765SELECT arg, var; 766END| 767CREATE PROCEDURE p5(arg CHAR(2)) 768BEGIN 769DECLARE var CHAR(1); 770SET var := arg; 771SELECT arg, var; 772END| 773CREATE PROCEDURE p6(arg DECIMAL(2)) 774BEGIN 775DECLARE var DECIMAL(1); 776SET var := arg; 777SELECT arg, var; 778END| 779CALL p1(); 780ERROR 22003: Out of range value for column 'v' at row 1 781CALL p2(); 782ERROR 22003: Out of range value for column 'v' at row 1 783CALL p3(); 784ERROR 22001: Data too long for column 'v' at row 1 785CALL p4('aaa'); 786ERROR 22001: Data too long for column 'arg' at row 1 787CALL p5('aa'); 788ERROR 22001: Data too long for column 'var' at row 1 789CALL p6(10); 790ERROR 22003: Out of range value for column 'var' at row 1 791SET @@sql_mode = 'ansi'; 792DROP PROCEDURE p1; 793DROP PROCEDURE p2; 794DROP PROCEDURE p3; 795DROP PROCEDURE p4; 796DROP PROCEDURE p5; 797DROP PROCEDURE p6; 798 799--------------------------------------------------------------- 800BUG#9078 801--------------------------------------------------------------- 802 803DROP PROCEDURE IF EXISTS p1; 804CREATE PROCEDURE p1 (arg DECIMAL(64,2)) 805BEGIN 806DECLARE var DECIMAL(64,2); 807SET var = arg; 808SELECT var; 809END| 810CALL p1(1929); 811var 8121929.00 813CALL p1(1929.00); 814var 8151929.00 816CALL p1(1929.003); 817var 8181929.00 819Warnings: 820Note 1265 Data truncated for column 'arg' at row 1 821DROP PROCEDURE p1; 822 823--------------------------------------------------------------- 824BUG#8768 825--------------------------------------------------------------- 826 827DROP FUNCTION IF EXISTS f1; 828CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT 829BEGIN 830RETURN arg; 831END| 832SELECT f1(-2500); 833f1(-2500) 8340 835Warnings: 836Warning 1264 Out of range value for column 'arg' at row 1 837SET @@sql_mode = 'traditional'; 838SELECT f1(-2500); 839ERROR 22003: Out of range value for column 'arg' at row 1 840DROP FUNCTION f1; 841CREATE FUNCTION f1(arg TINYINT UNSIGNED) RETURNS TINYINT 842BEGIN 843RETURN arg; 844END| 845SELECT f1(-2500); 846ERROR 22003: Out of range value for column 'arg' at row 1 847SET @@sql_mode = 'ansi'; 848DROP FUNCTION f1; 849 850--------------------------------------------------------------- 851BUG#8769 852--------------------------------------------------------------- 853 854DROP FUNCTION IF EXISTS f1; 855CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT 856BEGIN 857RETURN arg; 858END| 859SELECT f1(8388699); 860f1(8388699) 8618388607 862Warnings: 863Warning 1264 Out of range value for column 'arg' at row 1 864SET @@sql_mode = 'traditional'; 865SELECT f1(8388699); 866ERROR 22003: Out of range value for column 'arg' at row 1 867DROP FUNCTION f1; 868CREATE FUNCTION f1(arg MEDIUMINT) RETURNS MEDIUMINT 869BEGIN 870RETURN arg; 871END| 872SELECT f1(8388699); 873ERROR 22003: Out of range value for column 'arg' at row 1 874SET @@sql_mode = 'ansi'; 875DROP FUNCTION f1; 876 877--------------------------------------------------------------- 878BUG#8702 879--------------------------------------------------------------- 880 881DROP PROCEDURE IF EXISTS p1; 882DROP TABLE IF EXISTS t1; 883CREATE TABLE t1(col VARCHAR(255)); 884INSERT INTO t1(col) VALUES('Hello, world!'); 885CREATE PROCEDURE p1() 886BEGIN 887DECLARE sp_var INTEGER; 888SELECT col INTO sp_var FROM t1 LIMIT 1; 889SET @user_var = sp_var; 890SELECT sp_var; 891SELECT @user_var; 892END| 893CALL p1(); 894sp_var 8950 896@user_var 8970 898Warnings: 899Warning 1366 Incorrect integer value: 'Hello, world!' for column ``.``.`sp_var` at row 1 900DROP PROCEDURE p1; 901DROP TABLE t1; 902 903--------------------------------------------------------------- 904BUG#12903 905--------------------------------------------------------------- 906 907DROP FUNCTION IF EXISTS f1; 908DROP TABLE IF EXISTS t1; 909CREATE TABLE t1(txt VARCHAR(255)); 910CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255) 911BEGIN 912DECLARE v1 VARCHAR(255); 913DECLARE v2 VARCHAR(255); 914SET v1 = CONCAT(LOWER(arg), UPPER(arg)); 915SET v2 = CONCAT(LOWER(v1), UPPER(v1)); 916INSERT INTO t1 VALUES(v1), (v2); 917RETURN CONCAT(LOWER(arg), UPPER(arg)); 918END| 919SELECT f1('_aBcDe_'); 920f1('_aBcDe_') 921_abcde__ABCDE_ 922SELECT * FROM t1; 923txt 924_abcde__ABCDE_ 925_abcde__abcde__ABCDE__ABCDE_ 926DROP FUNCTION f1; 927DROP TABLE t1; 928 929--------------------------------------------------------------- 930BUG#13808 931--------------------------------------------------------------- 932 933DROP PROCEDURE IF EXISTS p1; 934DROP PROCEDURE IF EXISTS p2; 935DROP FUNCTION IF EXISTS f1; 936CREATE PROCEDURE p1(arg ENUM('a', 'b')) 937BEGIN 938SELECT arg; 939END| 940CREATE PROCEDURE p2(arg ENUM('a', 'b')) 941BEGIN 942DECLARE var ENUM('c', 'd') DEFAULT arg; 943SELECT arg, var; 944END| 945CREATE FUNCTION f1(arg ENUM('a', 'b')) RETURNS ENUM('c', 'd') 946BEGIN 947RETURN arg; 948END| 949CALL p1('c'); 950arg 951 952Warnings: 953Warning 1265 Data truncated for column 'arg' at row 1 954CALL p2('a'); 955arg var 956a 957Warnings: 958Warning 1265 Data truncated for column 'var' at row 1 959SELECT f1('a'); 960f1('a') 961 962Warnings: 963Warning 1265 Data truncated for column 'f1('a')' at row 1 964DROP PROCEDURE p1; 965DROP PROCEDURE p2; 966DROP FUNCTION f1; 967 968--------------------------------------------------------------- 969BUG#13909 970--------------------------------------------------------------- 971 972DROP PROCEDURE IF EXISTS p1; 973DROP PROCEDURE IF EXISTS p2; 974CREATE PROCEDURE p1(arg VARCHAR(255)) 975BEGIN 976SELECT CHARSET(arg); 977END| 978CREATE PROCEDURE p2(arg VARCHAR(255) CHARACTER SET UTF8) 979BEGIN 980SELECT CHARSET(arg); 981END| 982CALL p1('t'); 983CHARSET(arg) 984latin1 985CALL p1(_UTF8 't'); 986CHARSET(arg) 987latin1 988CALL p2('t'); 989CHARSET(arg) 990utf8 991CALL p2(_LATIN1 't'); 992CHARSET(arg) 993utf8 994DROP PROCEDURE p1; 995DROP PROCEDURE p2; 996 997--------------------------------------------------------------- 998BUG#14188 999--------------------------------------------------------------- 1000 1001DROP PROCEDURE IF EXISTS p1; 1002CREATE PROCEDURE p1(arg1 BINARY(2), arg2 VARBINARY(2)) 1003BEGIN 1004DECLARE var1 BINARY(2) DEFAULT 0x41; 1005DECLARE var2 VARBINARY(2) DEFAULT 0x42; 1006SELECT HEX(arg1), HEX(arg2); 1007SELECT HEX(var1), HEX(var2); 1008END| 1009CALL p1(0x41, 0x42); 1010HEX(arg1) HEX(arg2) 10114100 42 1012HEX(var1) HEX(var2) 10134100 42 1014DROP PROCEDURE p1; 1015 1016--------------------------------------------------------------- 1017BUG#15148 1018--------------------------------------------------------------- 1019 1020DROP PROCEDURE IF EXISTS p1; 1021DROP TABLE IF EXISTS t1; 1022CREATE TABLE t1(col1 TINYINT, col2 TINYINT); 1023INSERT INTO t1 VALUES(1, 2), (11, 12); 1024CREATE PROCEDURE p1(arg TINYINT) 1025BEGIN 1026SELECT arg; 1027END| 1028CALL p1((1, 2)); 1029ERROR 21000: Operand should contain 1 column(s) 1030CALL p1((SELECT * FROM t1 LIMIT 1)); 1031ERROR 21000: Operand should contain 1 column(s) 1032CALL p1((SELECT col1, col2 FROM t1 LIMIT 1)); 1033ERROR 21000: Operand should contain 1 column(s) 1034DROP PROCEDURE p1; 1035DROP TABLE t1; 1036 1037--------------------------------------------------------------- 1038BUG#13613 1039--------------------------------------------------------------- 1040 1041DROP PROCEDURE IF EXISTS p1; 1042DROP FUNCTION IF EXISTS f1; 1043CREATE PROCEDURE p1(x VARCHAR(50)) 1044BEGIN 1045SET x = SUBSTRING(x, 1, 3); 1046SELECT x; 1047END| 1048CREATE FUNCTION f1(x VARCHAR(50)) RETURNS VARCHAR(50) 1049BEGIN 1050RETURN SUBSTRING(x, 1, 3); 1051END| 1052CALL p1('abcdef'); 1053x 1054abc 1055SELECT f1('ABCDEF'); 1056f1('ABCDEF') 1057ABC 1058DROP PROCEDURE p1; 1059DROP FUNCTION f1; 1060 1061--------------------------------------------------------------- 1062BUG#13665 1063--------------------------------------------------------------- 1064 1065DROP FUNCTION IF EXISTS f1; 1066CREATE FUNCTION f1() RETURNS VARCHAR(20000) 1067BEGIN 1068DECLARE var VARCHAR(2000); 1069SET var = ''; 1070SET var = CONCAT(var, 'abc'); 1071SET var = CONCAT(var, ''); 1072RETURN var; 1073END| 1074SELECT f1(); 1075f1() 1076abc 1077DROP FUNCTION f1; 1078DROP PROCEDURE IF EXISTS p1; 1079CREATE PROCEDURE p1() 1080BEGIN 1081DECLARE v_char VARCHAR(255); 1082DECLARE v_text TEXT DEFAULT ''; 1083SET v_char = 'abc'; 1084SET v_text = v_char; 1085SET v_char = 'def'; 1086SET v_text = concat(v_text, '|', v_char); 1087SELECT v_text; 1088END| 1089CALL p1(); 1090v_text 1091abc|def 1092DROP PROCEDURE p1; 1093DROP PROCEDURE IF EXISTS bug27415_text_test| 1094DROP PROCEDURE IF EXISTS bug27415_text_test2| 1095CREATE PROCEDURE bug27415_text_test(entity_id_str_in text) 1096BEGIN 1097DECLARE str_remainder text; 1098SET str_remainder = entity_id_str_in; 1099select 'before substr', str_remainder; 1100SET str_remainder = SUBSTRING(str_remainder, 3); 1101select 'after substr', str_remainder; 1102END| 1103CREATE PROCEDURE bug27415_text_test2(entity_id_str_in text) 1104BEGIN 1105DECLARE str_remainder text; 1106DECLARE str_remainder2 text; 1107SET str_remainder2 = entity_id_str_in; 1108select 'before substr', str_remainder2; 1109SET str_remainder = SUBSTRING(str_remainder2, 3); 1110select 'after substr', str_remainder; 1111END| 1112CALL bug27415_text_test('a,b,c')| 1113before substr str_remainder 1114before substr a,b,c 1115after substr str_remainder 1116after substr b,c 1117CALL bug27415_text_test('a,b,c')| 1118before substr str_remainder 1119before substr a,b,c 1120after substr str_remainder 1121after substr b,c 1122CALL bug27415_text_test2('a,b,c')| 1123before substr str_remainder2 1124before substr a,b,c 1125after substr str_remainder 1126after substr b,c 1127CALL bug27415_text_test('a,b,c')| 1128before substr str_remainder 1129before substr a,b,c 1130after substr str_remainder 1131after substr b,c 1132DROP PROCEDURE bug27415_text_test| 1133DROP PROCEDURE bug27415_text_test2| 1134drop function if exists f1; 1135drop table if exists t1; 1136create function f1() returns int 1137begin 1138if @a=1 then set @b='abc'; 1139else set @b=1; 1140end if; 1141set @a=1; 1142return 0; 1143end| 1144create table t1 (a int)| 1145insert into t1 (a) values (1), (2)| 1146set @b=1| 1147set @a=0| 1148select f1(), @b from t1| 1149f1() @b 11500 1 11510 0 1152set @b:='test'| 1153set @a=0| 1154select f1(), @b from t1| 1155f1() @b 11560 1 11570 abc 1158drop function f1; 1159drop table t1; 1160 1161--------------------------------------------------------------- 1162BUG#28299 1163--------------------------------------------------------------- 1164 1165CREATE PROCEDURE ctest() 1166BEGIN 1167DECLARE i CHAR(16); 1168DECLARE j INT; 1169SET i= 'string'; 1170SET j= 1 + i; 1171END| 1172CALL ctest(); 1173Warnings: 1174Warning 1292 Truncated incorrect DOUBLE value: 'string ' 1175DROP PROCEDURE ctest; 1176CREATE PROCEDURE vctest() 1177BEGIN 1178DECLARE i VARCHAR(16); 1179DECLARE j INT; 1180SET i= 'string'; 1181SET j= 1 + i; 1182END| 1183CALL vctest(); 1184Warnings: 1185Warning 1292 Truncated incorrect DOUBLE value: 'string' 1186DROP PROCEDURE vctest; 1187# 1188# Start of 10.3 tests 1189# 1190# 1191# MDEV-12876 Wrong data type for CREATE..SELECT sp_var 1192# 1193CREATE PROCEDURE p1() 1194BEGIN 1195DECLARE i8 TINYINT; 1196DECLARE i16 SMALLINT; 1197DECLARE i32 INT; 1198DECLARE i64 BIGINT; 1199DECLARE f FLOAT; 1200DECLARE d DOUBLE; 1201DECLARE b8 BIT(8); 1202DECLARE y YEAR; 1203DECLARE t1 TINYTEXT; 1204DECLARE t2 TEXT; 1205DECLARE t3 MEDIUMTEXT; 1206DECLARE t4 LONGTEXT; 1207CREATE TABLE t1 AS SELECT i8, i16, i32, i64, f, d, b8, y, t1, t2, t3, t4; 1208END; 1209$$ 1210CALL p1; 1211DESCRIBE t1; 1212Field Type Null Key Default Extra 1213i8 tinyint(4) YES NULL 1214i16 smallint(6) YES NULL 1215i32 int(11) YES NULL 1216i64 bigint(20) YES NULL 1217f float YES NULL 1218d double YES NULL 1219b8 bit(8) YES NULL 1220y year(4) YES NULL 1221t1 tinytext YES NULL 1222t2 text YES NULL 1223t3 mediumtext YES NULL 1224t4 longtext YES NULL 1225DROP TABLE t1; 1226DROP PROCEDURE p1; 1227# 1228# MDEV-12917 Wrong data type for CREATE..SELECT year_sp_variable 1229# 1230CREATE PROCEDURE p1() 1231BEGIN 1232DECLARE a YEAR; 1233CREATE OR REPLACE TABLE t1 AS SELECT a; 1234SHOW CREATE TABLE t1; 1235DROP TABLE t1; 1236END; 1237$$ 1238CALL p1; 1239Table Create Table 1240t1 CREATE TABLE "t1" ( 1241 "a" year(4) DEFAULT NULL 1242) 1243DROP PROCEDURE p1; 1244# 1245# MDEV-15960 Wrong data type on CREATE..SELECT char_or_enum_or_text_spvar 1246# 1247BEGIN NOT ATOMIC 1248DECLARE var TINYTEXT CHARACTER SET utf8; 1249CREATE TABLE t1 AS SELECT var; 1250END; 1251$$ 1252SHOW CREATE TABLE t1; 1253Table Create Table 1254t1 CREATE TABLE "t1" ( 1255 "var" tinytext CHARACTER SET utf8 DEFAULT NULL 1256) 1257DROP TABLE t1; 1258BEGIN NOT ATOMIC 1259DECLARE var TEXT CHARACTER SET utf8; 1260CREATE TABLE t1 AS SELECT var; 1261END; 1262$$ 1263SHOW CREATE TABLE t1; 1264Table Create Table 1265t1 CREATE TABLE "t1" ( 1266 "var" text CHARACTER SET utf8 DEFAULT NULL 1267) 1268DROP TABLE t1; 1269BEGIN NOT ATOMIC 1270DECLARE var MEDIUMTEXT CHARACTER SET utf8; 1271CREATE TABLE t1 AS SELECT var; 1272END; 1273$$ 1274SHOW CREATE TABLE t1; 1275Table Create Table 1276t1 CREATE TABLE "t1" ( 1277 "var" mediumtext CHARACTER SET utf8 DEFAULT NULL 1278) 1279DROP TABLE t1; 1280BEGIN NOT ATOMIC 1281DECLARE var LONGTEXT CHARACTER SET utf8; 1282CREATE TABLE t1 AS SELECT var; 1283END; 1284$$ 1285SHOW CREATE TABLE t1; 1286Table Create Table 1287t1 CREATE TABLE "t1" ( 1288 "var" longtext CHARACTER SET utf8 DEFAULT NULL 1289) 1290DROP TABLE t1; 1291BEGIN NOT ATOMIC 1292DECLARE var CHAR(1); 1293CREATE TABLE t1 AS SELECT var; 1294END; 1295$$ 1296SHOW CREATE TABLE t1; 1297Table Create Table 1298t1 CREATE TABLE "t1" ( 1299 "var" char(1) DEFAULT NULL 1300) 1301DROP TABLE t1; 1302BEGIN NOT ATOMIC 1303DECLARE var ENUM('a'); 1304CREATE TABLE t1 AS SELECT var; 1305END; 1306$$ 1307SHOW CREATE TABLE t1; 1308Table Create Table 1309t1 CREATE TABLE "t1" ( 1310 "var" char(1) DEFAULT NULL 1311) 1312DROP TABLE t1; 1313# 1314# MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable 1315# 1316# 1317# Simple cases (without subqueries) - the most typical problem: 1318# a typo in an SP variable name 1319# 1320CREATE PROCEDURE p1(a INT) 1321BEGIN 1322DECLARE res INT DEFAULT 0; 1323IF (a < 0) THEN 1324SET res= a_long_variable_name_with_a_typo; 1325END IF; 1326END; 1327$$ 1328ERROR 42000: Undeclared variable: a_long_variable_name_with_a_typo 1329CREATE PROCEDURE p1(a INT) 1330BEGIN 1331DECLARE res INT DEFAULT 0; 1332IF (a < 0) THEN 1333SET res= 1 + a_long_variable_name_with_a_typo; 1334END IF; 1335END; 1336$$ 1337ERROR 42000: Undeclared variable: a_long_variable_name_with_a_typo 1338# 1339# Complex cases with subqueries 1340# 1341# 1342# Maybe a table field identifier (there are some tables) - no error 1343# 1344CREATE PROCEDURE p1() 1345BEGIN 1346DECLARE res INT DEFAULT 0; 1347SET res=(SELECT * FROM t1 LEFT OUTER JOIN t2 USING (c1)); 1348END; 1349$$ 1350DROP PROCEDURE p1; 1351CREATE PROCEDURE p1() 1352BEGIN 1353DECLARE res INT DEFAULT 0; 1354SET res=(SELECT * FROM t1 LEFT OUTER JOIN t2 ON (c1=c2)); 1355END; 1356$$ 1357DROP PROCEDURE p1; 1358# 1359# One unknown identifier, no tables 1360# 1361CREATE PROCEDURE p1() 1362BEGIN 1363DECLARE a INT; 1364SET a=unknown_ident; 1365END; 1366$$ 1367ERROR 42000: Undeclared variable: unknown_ident 1368CREATE PROCEDURE p1() 1369BEGIN 1370DECLARE a INT; 1371SET a=unknown_ident1.unknown_ident2; 1372END; 1373$$ 1374ERROR 42000: Undeclared variable: unknown_ident1 1375CREATE PROCEDURE p1() 1376BEGIN 1377DECLARE a INT; 1378SET a=unknown_ident1.unknown_ident2.unknown_ident3; 1379END; 1380$$ 1381ERROR 42000: Undeclared variable: unknown_ident1 1382CREATE PROCEDURE p1() 1383BEGIN 1384DECLARE a INT; 1385SET a=(SELECT unknown_ident); 1386END; 1387$$ 1388ERROR 42000: Undeclared variable: unknown_ident 1389CREATE PROCEDURE p1() 1390BEGIN 1391DECLARE a INT; 1392SET a=(SELECT unknown_ident FROM dual); 1393END; 1394$$ 1395ERROR 42000: Undeclared variable: unknown_ident 1396CREATE PROCEDURE p1() 1397BEGIN 1398DECLARE a INT; 1399SET a=(SELECT (SELECT unknown_ident)); 1400END; 1401$$ 1402ERROR 42000: Undeclared variable: unknown_ident 1403CREATE PROCEDURE p1() 1404BEGIN 1405DECLARE a INT; 1406SET a=(SELECT (SELECT unknown_ident FROM dual)); 1407END; 1408$$ 1409ERROR 42000: Undeclared variable: unknown_ident 1410CREATE PROCEDURE p1() 1411BEGIN 1412DECLARE a INT; 1413SET a=(SELECT 1 WHERE unknown_ident); 1414END; 1415$$ 1416ERROR 42000: Undeclared variable: unknown_ident 1417CREATE PROCEDURE p1() 1418BEGIN 1419DECLARE a INT; 1420SET a=(SELECT 1 WHERE unknown_ident=1); 1421END; 1422$$ 1423ERROR 42000: Undeclared variable: unknown_ident 1424CREATE PROCEDURE p1() 1425BEGIN 1426DECLARE a INT; 1427SET a=(SELECT 1 LIMIT unknown_ident); 1428END; 1429$$ 1430ERROR 42000: Undeclared variable: unknown_ident 1431# 1432# GROUP, HAVING, ORDER are not tested yet for unknown identifiers 1433# 1434CREATE PROCEDURE p1() 1435BEGIN 1436DECLARE a INT; 1437SET a=(SELECT 1 GROUP BY unknown_ident); 1438END; 1439$$ 1440DROP PROCEDURE p1; 1441CREATE PROCEDURE p1() 1442BEGIN 1443DECLARE res INT DEFAULT 0; 1444SET res=(SELECT 1 HAVING unknown_ident); 1445END; 1446$$ 1447DROP PROCEDURE p1; 1448CREATE PROCEDURE p1() 1449BEGIN 1450DECLARE a INT; 1451SET a=(SELECT 1 ORDER BY unknown_ident); 1452END; 1453$$ 1454DROP PROCEDURE p1; 1455# 1456# HAVING + aggregate_function(unknown_identifier) is a special case 1457# 1458CREATE PROCEDURE p1() 1459BEGIN 1460DECLARE res INT DEFAULT 0; 1461SET res=(SELECT 1 HAVING SUM(unknown_ident)); 1462END; 1463$$ 1464ERROR 42000: Undeclared variable: unknown_ident 1465# 1466# Known indentifier + unknown identifier, no tables 1467# 1468CREATE PROCEDURE p1() 1469BEGIN 1470DECLARE a INT; 1471SET a=a+unknown_ident; 1472END; 1473$$ 1474ERROR 42000: Undeclared variable: unknown_ident 1475CREATE PROCEDURE p1() 1476BEGIN 1477DECLARE a INT; 1478SET a=a+(SELECT unknown_ident); 1479END; 1480$$ 1481ERROR 42000: Undeclared variable: unknown_ident 1482CREATE PROCEDURE p1() 1483BEGIN 1484DECLARE a INT; 1485SET a=a+(SELECT unknown_ident FROM dual); 1486END; 1487$$ 1488ERROR 42000: Undeclared variable: unknown_ident 1489CREATE PROCEDURE p1() 1490BEGIN 1491DECLARE a INT; 1492SET a=(SELECT (a+(SELECT unknown_ident))); 1493END; 1494$$ 1495ERROR 42000: Undeclared variable: unknown_ident 1496CREATE PROCEDURE p1() 1497BEGIN 1498DECLARE a INT; 1499SET a=(SELECT (a+(SELECT unknown_ident FROM dual))); 1500END; 1501$$ 1502ERROR 42000: Undeclared variable: unknown_ident 1503# 1504# Unknown indentifier + known identifier, no tables 1505# 1506CREATE PROCEDURE p1() 1507BEGIN 1508DECLARE a INT; 1509SET a=unknown_ident+a; 1510END; 1511$$ 1512ERROR 42000: Undeclared variable: unknown_ident 1513CREATE PROCEDURE p1() 1514BEGIN 1515DECLARE a INT; 1516SET a=(SELECT unknown_ident)+a; 1517END; 1518$$ 1519ERROR 42000: Undeclared variable: unknown_ident 1520CREATE PROCEDURE p1() 1521BEGIN 1522DECLARE a INT; 1523SET a=(SELECT unknown_ident FROM dual)+a; 1524END; 1525$$ 1526ERROR 42000: Undeclared variable: unknown_ident 1527CREATE PROCEDURE p1() 1528BEGIN 1529DECLARE a INT; 1530SET a=(SELECT (SELECT unknown_ident)+a); 1531END; 1532$$ 1533ERROR 42000: Undeclared variable: unknown_ident 1534CREATE PROCEDURE p1() 1535BEGIN 1536DECLARE a INT; 1537SET a=(SELECT (SELECT unknown_ident FROM dual)+a); 1538END; 1539$$ 1540ERROR 42000: Undeclared variable: unknown_ident 1541# 1542# Maybe a table field indentifier + unknown identifier 1543# 1544CREATE PROCEDURE p1() 1545BEGIN 1546DECLARE a INT; 1547SET a=(SELECT c1 FROM t1)+unknown_ident; 1548END; 1549$$ 1550ERROR 42000: Undeclared variable: unknown_ident 1551CREATE PROCEDURE p1() 1552BEGIN 1553DECLARE a INT; 1554SET a=(SELECT c1 FROM t1)+(SELECT unknown_ident); 1555END; 1556$$ 1557ERROR 42000: Undeclared variable: unknown_ident 1558CREATE PROCEDURE p1() 1559BEGIN 1560DECLARE a INT; 1561SET a=(SELECT c1 FROM t1)+(SELECT unknown_ident FROM dual); 1562END; 1563$$ 1564ERROR 42000: Undeclared variable: unknown_ident 1565CREATE PROCEDURE p1() 1566BEGIN 1567DECLARE a INT; 1568SET a=(SELECT ((SELECT c1 FROM t1)+(SELECT unknown_ident))); 1569END; 1570$$ 1571ERROR 42000: Undeclared variable: unknown_ident 1572CREATE PROCEDURE p1() 1573BEGIN 1574DECLARE a INT; 1575SET a=(SELECT ((SELECT c1 FROM t1)+(SELECT unknown_ident FROM dual))); 1576END; 1577$$ 1578ERROR 42000: Undeclared variable: unknown_ident 1579# 1580# Unknown indentifier + maybe a table field identifier 1581# 1582CREATE PROCEDURE p1() 1583BEGIN 1584DECLARE a INT; 1585SET a=unknown_ident+(SELECT c1 FROM t1); 1586END; 1587$$ 1588ERROR 42000: Undeclared variable: unknown_ident 1589CREATE PROCEDURE p1() 1590BEGIN 1591DECLARE a INT; 1592SET a=(SELECT unknown_ident)+(SELECT c1 FROM t1); 1593END; 1594$$ 1595ERROR 42000: Undeclared variable: unknown_ident 1596CREATE PROCEDURE p1() 1597BEGIN 1598DECLARE a INT; 1599SET a=(SELECT unknown_ident FROM dual)+(SELECT c1 FROM t1); 1600END; 1601$$ 1602ERROR 42000: Undeclared variable: unknown_ident 1603CREATE PROCEDURE p1() 1604BEGIN 1605DECLARE a INT; 1606SET a=(SELECT (SELECT unknown_ident)+(SELECT c1 FROM t1)); 1607END; 1608$$ 1609ERROR 42000: Undeclared variable: unknown_ident 1610CREATE PROCEDURE p1() 1611BEGIN 1612DECLARE a INT; 1613SET a=(SELECT (SELECT unknown_ident FROM dual)+(SELECT c1 FROM t1)); 1614END; 1615$$ 1616ERROR 42000: Undeclared variable: unknown_ident 1617# 1618# Maybe a table field identifier + maybe a field table identifier 1619# 1620CREATE PROCEDURE p1() 1621BEGIN 1622DECLARE a INT; 1623-- c2 does not have a table on its level 1624-- but it can be a field of a table on the uppder level, i.e. t1 1625SET a=(SELECT c1+(SELECT c2) FROM t1); 1626END; 1627$$ 1628DROP PROCEDURE p1; 1629# 1630# TVC - unknown identifier 1631# 1632CREATE PROCEDURE p1(a INT) 1633BEGIN 1634DECLARE res INT DEFAULT 0; 1635SET res=(VALUES(unknown_ident)); 1636END; 1637$$ 1638ERROR 42000: Undeclared variable: unknown_ident 1639CREATE PROCEDURE p1(a INT) 1640BEGIN 1641DECLARE res INT DEFAULT 0; 1642SET res=(VALUES(1),(unknown_ident)); 1643END; 1644$$ 1645ERROR 42000: Undeclared variable: unknown_ident 1646CREATE PROCEDURE p1(a INT) 1647BEGIN 1648DECLARE res INT DEFAULT 0; 1649SET res=(VALUES((SELECT unknown_ident))); 1650END; 1651$$ 1652ERROR 42000: Undeclared variable: unknown_ident 1653CREATE PROCEDURE p1(a INT) 1654BEGIN 1655DECLARE res INT DEFAULT 0; 1656SET res=(VALUES(1),((SELECT unknown_ident))); 1657END; 1658$$ 1659ERROR 42000: Undeclared variable: unknown_ident 1660CREATE PROCEDURE p1(a INT) 1661BEGIN 1662DECLARE res INT DEFAULT 0; 1663SET res=(VALUES(1) LIMIT unknown_ident); 1664END; 1665$$ 1666ERROR 42000: Undeclared variable: unknown_ident 1667# 1668# TVC - ORDER BY - not tested yet for unknown identifiers 1669# 1670CREATE PROCEDURE p1(a INT) 1671BEGIN 1672DECLARE res INT DEFAULT 0; 1673SET res=(VALUES(1) ORDER BY unknown_ident); 1674END; 1675$$ 1676DROP PROCEDURE p1; 1677# 1678# TVC - maybe a table field identifier - no error 1679# 1680CREATE PROCEDURE p1(a INT) 1681BEGIN 1682DECLARE res INT DEFAULT 0; 1683SET res=(VALUES((SELECT c1 FROM t1))); 1684END; 1685$$ 1686DROP PROCEDURE p1; 1687CREATE PROCEDURE p1(a INT) 1688BEGIN 1689DECLARE res INT DEFAULT 0; 1690SET res=(VALUES(1),((SELECT c1 FROM t1))); 1691END; 1692$$ 1693DROP PROCEDURE p1; 1694# 1695# Functions DEFAULT(x) and VALUE(x) 1696# 1697CREATE PROCEDURE p1() 1698BEGIN 1699DECLARE res INT DEFAULT 0; 1700SET res=DEFAULT(unknown_ident); 1701SELECT res; 1702END; 1703$$ 1704ERROR 42000: Undeclared variable: unknown_ident 1705CREATE PROCEDURE p1() 1706BEGIN 1707DECLARE res INT DEFAULT 0; 1708SET res=VALUE(unknown_ident); 1709SELECT res; 1710END; 1711$$ 1712ERROR 42000: Undeclared variable: unknown_ident 1713# 1714# End of MDEV-14347 CREATE PROCEDURE returns no error when using an unknown variable 1715# 1716