1 2--echo # 3--echo # WL#2111: GET DIAGNOSTICS tests 4--echo # 5 6--echo # 7--echo # Test reserved keywords: GET 8--echo # 9 10--disable_warnings 11DROP TABLE IF EXISTS t1; 12DROP PROCEDURE IF EXISTS p1; 13--enable_warnings 14 15--error ER_PARSE_ERROR 16CREATE TABLE t1 (get INT); 17 18DELIMITER |; 19--error ER_PARSE_ERROR 20CREATE PROCEDURE p1() 21BEGIN 22 DECLARE get INT DEFAULT 1; 23END| 24DELIMITER ;| 25 26--echo # Quoting 27 28CREATE TABLE t1 (`get` INT); 29INSERT INTO t1 (`get`) values (1); 30SELECT `get` FROM t1 WHERE `get` = 1; 31DROP TABLE t1; 32 33DELIMITER |; 34CREATE PROCEDURE p1() 35BEGIN 36 DECLARE `get` INT DEFAULT 1; 37 SELECT `get`; 38END| 39DELIMITER ;| 40 41CALL p1(); 42 43DROP PROCEDURE p1; 44 45--echo # 46--echo # Test non-reserved keywords: CURRENT, DIAGNOSTICS, NUMBER, RETURNED_SQLSTATE 47--echo # 48 49--disable_warnings 50DROP TABLE IF EXISTS t1; 51DROP PROCEDURE IF EXISTS p1; 52--enable_warnings 53 54CREATE TABLE t1 (current INT, diagnostics INT, number INT, returned_sqlstate INT); 55INSERT INTO t1 (current, diagnostics, number, returned_sqlstate) values (1,2,3,4); 56SELECT current, diagnostics, number, returned_sqlstate FROM t1 WHERE number = 3; 57SELECT `current`, `number` FROM t1 WHERE `current` = 1 AND `number` = 3; 58DROP TABLE t1; 59 60DELIMITER |; 61CREATE PROCEDURE p1() 62BEGIN 63 DECLARE current INT DEFAULT 1; 64 DECLARE diagnostics INT DEFAULT 2; 65 DECLARE number INT DEFAULT 3; 66 DECLARE returned_sqlstate INT DEFAULT 4; 67 SELECT current, diagnostics, number, returned_sqlstate; 68END| 69DELIMITER ;| 70 71CALL p1(); 72 73DROP PROCEDURE p1; 74 75--echo # 76--echo # Test GET DIAGNOSTICS syntax 77--echo # 78 79--disable_warnings 80DROP PROCEDURE IF EXISTS p1; 81--enable_warnings 82 83--error ER_PARSE_ERROR 84GET; 85--error ER_PARSE_ERROR 86GET CURRENT; 87--error ER_PARSE_ERROR 88GET DIAGNOSTICS; 89--error ER_PARSE_ERROR 90GET CURRENT DIAGNOSTICS; 91 92--echo 93--echo # Statement information syntax 94--echo 95 96--error ER_PARSE_ERROR 97GET DIAGNOSTICS @var; 98 99--error ER_SP_UNDECLARED_VAR 100GET DIAGNOSTICS var; 101 102DELIMITER |; 103--error ER_SP_UNDECLARED_VAR 104CREATE PROCEDURE p1() 105BEGIN 106 GET DIAGNOSTICS var; 107END| 108DELIMITER ;| 109 110DELIMITER |; 111--error ER_PARSE_ERROR 112CREATE PROCEDURE p1() 113BEGIN 114 DECLARE var INT; 115 GET DIAGNOSTICS var; 116END| 117DELIMITER ;| 118 119--error ER_PARSE_ERROR 120GET DIAGNOSTICS @var =; 121--error ER_PARSE_ERROR 122GET DIAGNOSTICS @var = INVALID; 123--error ER_PARSE_ERROR 124GET DIAGNOSTICS @var = MORE; 125--error ER_PARSE_ERROR 126GET DIAGNOSTICS @var = CLASS_ORIGIN; 127--error ER_PARSE_ERROR 128GET DIAGNOSTICS @var = INVALID,; 129--error ER_PARSE_ERROR 130GET DIAGNOSTICS @var1 = NUMBER, @var2; 131--error ER_PARSE_ERROR 132GET DIAGNOSTICS @var1 = NUMBER, @var2 = INVALID; 133--error ER_PARSE_ERROR 134GET DIAGNOSTICS @@var1 = NUMBER; 135--error ER_PARSE_ERROR 136GET DIAGNOSTICS @var1 = NUMBER, @@var2 = NUMBER; 137 138DELIMITER |; 139--error ER_PARSE_ERROR 140CREATE PROCEDURE p1() 141BEGIN 142 DECLARE var INT; 143 GET DIAGNOSTICS var = INVALID; 144END| 145DELIMITER ;| 146 147DELIMITER |; 148--error ER_SP_UNDECLARED_VAR 149CREATE PROCEDURE p1() 150BEGIN 151 DECLARE var CONDITION FOR SQLSTATE '12345'; 152 GET DIAGNOSTICS var = NUMBER; 153END| 154DELIMITER ;| 155 156DELIMITER |; 157--error ER_SP_UNDECLARED_VAR 158CREATE PROCEDURE p1() 159BEGIN 160 DECLARE var INT; 161 GET DIAGNOSTICS var = NUMBER, var1 = ROW_COUNT; 162END| 163DELIMITER ;| 164 165GET DIAGNOSTICS @var = NUMBER; 166GET DIAGNOSTICS @var = ROW_COUNT; 167GET DIAGNOSTICS @var1 = NUMBER, @var2 = ROW_COUNT; 168GET DIAGNOSTICS @var1 = ROW_COUNT, @var2 = NUMBER; 169 170DELIMITER |; 171CREATE PROCEDURE p1() 172BEGIN 173 DECLARE var INT; 174 DECLARE var1 INT; 175 DECLARE var2 INT; 176 GET DIAGNOSTICS var = NUMBER; 177 GET DIAGNOSTICS var = ROW_COUNT; 178 GET DIAGNOSTICS var1 = NUMBER, var2 = ROW_COUNT; 179 GET DIAGNOSTICS var1 = ROW_COUNT, var2 = NUMBER; 180END| 181DELIMITER ;| 182 183DROP PROCEDURE p1; 184 185--echo 186--echo # Condition information syntax 187--echo 188 189--error ER_PARSE_ERROR 190GET DIAGNOSTICS CONDITION; 191--error ER_PARSE_ERROR 192GET DIAGNOSTICS CONDITION a; 193--error ER_PARSE_ERROR 194GET DIAGNOSTICS CONDITION 1; 195 196--error ER_PARSE_ERROR 197GET DIAGNOSTICS CONDITION 1 @var; 198 199--error ER_SP_UNDECLARED_VAR 200GET DIAGNOSTICS CONDITION 1 var; 201 202DELIMITER |; 203--error ER_SP_UNDECLARED_VAR 204CREATE PROCEDURE p1() 205BEGIN 206 GET DIAGNOSTICS CONDITION 1 var; 207END| 208DELIMITER ;| 209 210DELIMITER |; 211--error ER_PARSE_ERROR 212CREATE PROCEDURE p1() 213BEGIN 214 DECLARE var INT; 215 GET DIAGNOSTICS CONDITION 1 var; 216END| 217DELIMITER ;| 218 219--error ER_PARSE_ERROR 220GET DIAGNOSTICS CONDITION 1 @var =; 221--error ER_PARSE_ERROR 222GET DIAGNOSTICS CONDITION 1 @var = INVALID; 223--error ER_PARSE_ERROR 224GET DIAGNOSTICS CONDITION 1 @var = NUMBER; 225--error ER_PARSE_ERROR 226GET DIAGNOSTICS CONDITION 1 @var = INVALID,; 227--error ER_PARSE_ERROR 228GET DIAGNOSTICS CONDITION 1 @var1 = CLASS_ORIGIN, @var2; 229--error ER_PARSE_ERROR 230GET DIAGNOSTICS CONDITION 1 @var1 = CLASS_ORIGIN, @var2 = INVALID; 231--error ER_PARSE_ERROR 232GET DIAGNOSTICS CONDITION 1 @@var1 = CLASS_ORIGIN; 233--error ER_PARSE_ERROR 234GET DIAGNOSTICS CONDITION 1 @var1 = CLASS_ORIGIN, @@var2 = CLASS_ORIGIN; 235 236DELIMITER |; 237--error ER_PARSE_ERROR 238CREATE PROCEDURE p1() 239BEGIN 240 DECLARE var INT; 241 GET DIAGNOSTICS CONDITION 1 var = INVALID; 242END| 243DELIMITER ;| 244 245DELIMITER |; 246--error ER_SP_UNDECLARED_VAR 247CREATE PROCEDURE p1() 248BEGIN 249 DECLARE var CONDITION FOR SQLSTATE '12345'; 250 GET DIAGNOSTICS CONDITION 1 var = NUMBER; 251END| 252DELIMITER ;| 253 254DELIMITER |; 255--error ER_SP_UNDECLARED_VAR 256CREATE PROCEDURE p1() 257BEGIN 258 DECLARE var INT; 259 GET DIAGNOSTICS CONDITION 1 var = CLASS_ORIGIN, var1 = SUBCLASS_ORIGIN; 260END| 261DELIMITER ;| 262 263GET DIAGNOSTICS CONDITION 1 @var = CLASS_ORIGIN; 264GET DIAGNOSTICS CONDITION 1 @var = SUBCLASS_ORIGIN; 265GET DIAGNOSTICS CONDITION 1 @var1 = CLASS_ORIGIN, @var2 = SUBCLASS_ORIGIN; 266GET DIAGNOSTICS CONDITION 1 @var1 = SUBCLASS_ORIGIN, @var2 = CLASS_ORIGIN; 267 268DELIMITER |; 269CREATE PROCEDURE p1() 270BEGIN 271 DECLARE var INT; 272 DECLARE var1 INT; 273 DECLARE var2 INT; 274 GET DIAGNOSTICS CONDITION 1 var = CLASS_ORIGIN; 275 GET DIAGNOSTICS CONDITION 1 var = SUBCLASS_ORIGIN; 276 GET DIAGNOSTICS CONDITION 1 var1 = CLASS_ORIGIN, var2 = SUBCLASS_ORIGIN; 277 GET DIAGNOSTICS CONDITION 1 var1 = SUBCLASS_ORIGIN, var2 = CLASS_ORIGIN; 278END| 279DELIMITER ;| 280 281DROP PROCEDURE p1; 282 283--echo # Condition number expression 284 285--error ER_PARSE_ERROR 286GET DIAGNOSTICS CONDITION -1 @var = CLASS_ORIGIN; 287--error ER_PARSE_ERROR 288GET DIAGNOSTICS CONDITION 1+1 @var = CLASS_ORIGIN; 289--error ER_PARSE_ERROR 290GET DIAGNOSTICS CONDITION ? @var = CLASS_ORIGIN; 291--error ER_PARSE_ERROR 292GET DIAGNOSTICS CONDITION (1) @var = CLASS_ORIGIN; 293--error ER_PARSE_ERROR 294GET DIAGNOSTICS CONDITION p1() @var = CLASS_ORIGIN; 295--error ER_PARSE_ERROR 296GET DIAGNOSTICS CONDITION ABS(2) @var = CLASS_ORIGIN; 297 298# Unfortunate side effects... 299GET DIAGNOSTICS CONDITION 1.1 @var = CLASS_ORIGIN; 300GET DIAGNOSTICS CONDITION "1" @var = CLASS_ORIGIN; 301 302# Reset warnings 303SELECT COUNT(max_questions) INTO @var FROM mysql.user; 304 305GET DIAGNOSTICS CONDITION 9999 @var = CLASS_ORIGIN; 306GET DIAGNOSTICS CONDITION NULL @var = CLASS_ORIGIN; 307GET DIAGNOSTICS CONDITION a @var = CLASS_ORIGIN; 308 309# Reset warnings 310SELECT COUNT(max_questions) INTO @var FROM mysql.user; 311 312SET @cond = 1; 313GET DIAGNOSTICS CONDITION @cond @var1 = CLASS_ORIGIN; 314 315SET @cond = "invalid"; 316GET DIAGNOSTICS CONDITION @cond @var1 = CLASS_ORIGIN; 317 318# Reset warnings 319SELECT COUNT(max_questions) INTO @var FROM mysql.user; 320 321DELIMITER |; 322CREATE PROCEDURE p1() 323BEGIN 324 DECLARE cond INT DEFAULT 1; 325 DECLARE var INT; 326 GET DIAGNOSTICS CONDITION cond var = CLASS_ORIGIN; 327END| 328DELIMITER ;| 329 330DROP PROCEDURE p1; 331 332DELIMITER |; 333CREATE PROCEDURE p1() 334BEGIN 335 DECLARE cond TEXT; 336 DECLARE var INT; 337 GET DIAGNOSTICS CONDITION cond var = CLASS_ORIGIN; 338END| 339DELIMITER ;| 340 341CALL p1(); 342 343DROP PROCEDURE p1; 344 345--echo # 346--echo # Test GET DIAGNOSTICS runtime 347--echo # 348 349--echo 350--echo # GET DIAGNOSTICS cannot be the object of a PREPARE statement. 351--echo 352 353--error ER_UNSUPPORTED_PS 354PREPARE stmt FROM "GET DIAGNOSTICS CONDITION 1 @var = CLASS_ORIGIN"; 355--error ER_UNSUPPORTED_PS 356PREPARE stmt FROM "GET DIAGNOSTICS @var = NUMBER"; 357 358--echo 359--echo # GET DIAGNOSTICS does not clear the diagnostics area. 360--echo 361 362SELECT CAST(-19999999999999999999 AS SIGNED); 363GET DIAGNOSTICS @var = NUMBER; 364SHOW WARNINGS; 365 366--echo # 367--echo # If GET DIAGNOSTICS itself causes an error, an error message is appended. 368--echo # 369 370SELECT CAST(-19999999999999999999 AS SIGNED); 371GET DIAGNOSTICS CONDITION 99999 @var = CLASS_ORIGIN; 372SHOW WARNINGS; 373 374--echo 375--echo # Statement information runtime 376--echo 377SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 378SELECT CAST(-19999999999999999999 AS SIGNED), 379 CAST(-19999999999999999999 AS SIGNED); 380GET DIAGNOSTICS @var = NUMBER; 381SELECT @var; 382 383SELECT COUNT(max_questions) INTO @var FROM mysql.user; 384GET DIAGNOSTICS @var = NUMBER; 385SELECT @var; 386 387SELECT 1; 388GET DIAGNOSTICS @var = ROW_COUNT; 389SELECT @var; 390 391CREATE TABLE t1 (a INT); 392INSERT INTO t1 VALUES (1),(2),(3); 393GET DIAGNOSTICS @var = ROW_COUNT; 394SELECT @var; 395DROP TABLE t1; 396 397DELIMITER |; 398CREATE PROCEDURE p1() 399BEGIN 400 DECLARE number INT; 401 DECLARE row_count INT; 402 403 SELECT CAST(-19999999999999999999 AS SIGNED), 404 CAST(-19999999999999999999 AS SIGNED); 405 406 GET DIAGNOSTICS number = NUMBER; 407 408 CREATE TABLE t1 (a INT); 409 INSERT INTO t1 VALUES (1),(2),(3); 410 GET DIAGNOSTICS row_count = ROW_COUNT; 411 DROP TABLE t1; 412 413 SELECT number, row_count; 414 415END| 416DELIMITER ;| 417 418CALL p1(); 419 420DROP PROCEDURE p1; 421 422--echo 423--echo # Condition information runtime 424--echo 425 426SELECT CAST(-19999999999999999999 AS SIGNED); 427 428GET DIAGNOSTICS CONDITION 1 429 @class_origin = CLASS_ORIGIN, 430 @subclass_origin = SUBCLASS_ORIGIN, 431 @constraint_catalog = CONSTRAINT_CATALOG, 432 @constraint_schema = CONSTRAINT_SCHEMA, 433 @constraint_name = CONSTRAINT_NAME, 434 @catalog_name = CATALOG_NAME, 435 @schema_name = SCHEMA_NAME, 436 @table_name = TABLE_NAME, 437 @column_name = COLUMN_NAME, 438 @cursor_name = CURSOR_NAME, 439 @message_text = MESSAGE_TEXT, 440 @mysql_errno = MYSQL_ERRNO, 441 @returned_sqlstate = RETURNED_SQLSTATE; 442 443--vertical_results 444SELECT 445 @class_origin, 446 @subclass_origin, 447 @constraint_catalog, 448 @constraint_schema, 449 @constraint_name, 450 @catalog_name, 451 @schema_name, 452 @table_name, 453 @column_name, 454 @cursor_name, 455 @message_text, 456 @mysql_errno, 457 @returned_sqlstate; 458--horizontal_results 459 460DELIMITER |; 461CREATE PROCEDURE p1() 462BEGIN 463 DECLARE class_origin TEXT DEFAULT "a"; 464 DECLARE subclass_origin TEXT DEFAULT "a"; 465 DECLARE constraint_catalog TEXT DEFAULT "a"; 466 DECLARE constraint_schema TEXT DEFAULT "a"; 467 DECLARE constraint_name TEXT DEFAULT "a"; 468 DECLARE catalog_name TEXT DEFAULT "a"; 469 DECLARE schema_name TEXT DEFAULT "a"; 470 DECLARE table_name TEXT DEFAULT "a"; 471 DECLARE column_name TEXT DEFAULT "a"; 472 DECLARE cursor_name TEXT DEFAULT "a"; 473 DECLARE message_text TEXT DEFAULT "a"; 474 DECLARE mysql_errno INT DEFAULT 1; 475 DECLARE returned_sqlstate TEXT DEFAULT "a"; 476 477 SELECT CAST(-19999999999999999999 AS SIGNED); 478 479 GET DIAGNOSTICS CONDITION 1 480 class_origin = CLASS_ORIGIN, 481 subclass_origin = SUBCLASS_ORIGIN, 482 constraint_catalog = CONSTRAINT_CATALOG, 483 constraint_schema = CONSTRAINT_SCHEMA, 484 constraint_name = CONSTRAINT_NAME, 485 catalog_name = CATALOG_NAME, 486 schema_name = SCHEMA_NAME, 487 table_name = TABLE_NAME, 488 column_name = COLUMN_NAME, 489 cursor_name = CURSOR_NAME, 490 message_text = MESSAGE_TEXT, 491 mysql_errno = MYSQL_ERRNO, 492 returned_sqlstate = RETURNED_SQLSTATE; 493 494 SELECT 495 class_origin, 496 subclass_origin, 497 constraint_catalog, 498 constraint_schema, 499 constraint_name, 500 catalog_name, 501 schema_name, 502 table_name, 503 column_name, 504 cursor_name, 505 message_text, 506 mysql_errno, 507 returned_sqlstate; 508END| 509DELIMITER ;| 510 511--vertical_results 512CALL p1(); 513--horizontal_results 514 515DROP PROCEDURE p1; 516 517DELIMITER |; 518CREATE PROCEDURE p1() 519BEGIN 520 DECLARE errno1 INT; 521 DECLARE errno2 INT; 522 DECLARE msg1 TEXT; 523 DECLARE msg2 TEXT; 524 525 SELECT CAST(-19999999999999999999 AS SIGNED); 526 GET DIAGNOSTICS CONDITION 99999 msg1 = MESSAGE_TEXT; 527 528 GET DIAGNOSTICS CONDITION 1 errno1 = MYSQL_ERRNO, msg1 = MESSAGE_TEXT; 529 GET DIAGNOSTICS CONDITION 2 errno2 = MYSQL_ERRNO, msg2 = MESSAGE_TEXT; 530 531 SELECT errno1, msg1, errno2, msg2; 532END| 533DELIMITER ;| 534 535--vertical_results 536CALL p1(); 537--horizontal_results 538 539DROP PROCEDURE p1; 540 541--echo 542--echo # Interaction with SIGNAL 543--echo 544 545DELIMITER |; 546CREATE PROCEDURE p1() 547BEGIN 548 DECLARE errno INT DEFAULT 0; 549 DECLARE msg TEXT DEFAULT "foo"; 550 DECLARE cond CONDITION FOR SQLSTATE "01234"; 551 DECLARE CONTINUE HANDLER for 1012 552 BEGIN 553 GET DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT; 554 END; 555 556 SIGNAL cond SET MESSAGE_TEXT = "Signal message", MYSQL_ERRNO = 1012; 557 558 SELECT errno, msg; 559END| 560DELIMITER ;| 561 562--vertical_results 563CALL p1(); 564--horizontal_results 565 566DROP PROCEDURE p1; 567 568DELIMITER |; 569CREATE PROCEDURE p1() 570BEGIN 571 SIGNAL SQLSTATE '77777' SET MYSQL_ERRNO = 1000, MESSAGE_TEXT='ÁÂÃÅÄ'; 572END| 573DELIMITER ;| 574 575--error 1000 576CALL p1(); 577 578GET DIAGNOSTICS CONDITION 1 579 @mysql_errno = MYSQL_ERRNO, @message_text = MESSAGE_TEXT, 580 @returned_sqlstate = RETURNED_SQLSTATE, @class_origin = CLASS_ORIGIN; 581 582--vertical_results 583SELECT @mysql_errno, @message_text, @returned_sqlstate, @class_origin; 584--horizontal_results 585 586DROP PROCEDURE p1; 587 588DELIMITER |; 589CREATE PROCEDURE p1() 590BEGIN 591 DECLARE cond CONDITION FOR SQLSTATE '12345'; 592 SIGNAL cond SET 593 CLASS_ORIGIN = 'CLASS_ORIGIN text', 594 SUBCLASS_ORIGIN = 'SUBCLASS_ORIGIN text', 595 CONSTRAINT_CATALOG = 'CONSTRAINT_CATALOG text', 596 CONSTRAINT_SCHEMA = 'CONSTRAINT_SCHEMA text', 597 CONSTRAINT_NAME = 'CONSTRAINT_NAME text', 598 CATALOG_NAME = 'CATALOG_NAME text', 599 SCHEMA_NAME = 'SCHEMA_NAME text', 600 TABLE_NAME = 'TABLE_NAME text', 601 COLUMN_NAME = 'COLUMN_NAME text', 602 CURSOR_NAME = 'CURSOR_NAME text', 603 MESSAGE_TEXT = 'MESSAGE_TEXT text', 604 MYSQL_ERRNO = 54321; 605END| 606DELIMITER ;| 607 608--error 54321 609CALL p1(); 610 611GET DIAGNOSTICS CONDITION 1 612 @class_origin = CLASS_ORIGIN, 613 @subclass_origin = SUBCLASS_ORIGIN, 614 @constraint_catalog = CONSTRAINT_CATALOG, 615 @constraint_schema = CONSTRAINT_SCHEMA, 616 @constraint_name = CONSTRAINT_NAME, 617 @catalog_name = CATALOG_NAME, 618 @schema_name = SCHEMA_NAME, 619 @table_name = TABLE_NAME, 620 @column_name = COLUMN_NAME, 621 @cursor_name = CURSOR_NAME, 622 @message_text = MESSAGE_TEXT, 623 @mysql_errno = MYSQL_ERRNO, 624 @returned_sqlstate = RETURNED_SQLSTATE; 625 626--vertical_results 627SELECT 628 @class_origin, 629 @subclass_origin, 630 @constraint_catalog, 631 @constraint_schema, 632 @constraint_name, 633 @catalog_name, 634 @schema_name, 635 @table_name, 636 @column_name, 637 @cursor_name, 638 @message_text, 639 @mysql_errno, 640 @returned_sqlstate; 641--horizontal_results 642 643DROP PROCEDURE p1; 644 645--echo # 646--echo # Demonstration 647--echo # 648 649--echo 650--echo # The same statement information item can be used multiple times. 651--echo 652 653DELIMITER |; 654CREATE PROCEDURE p1() 655BEGIN 656 DECLARE var INT; 657 GET DIAGNOSTICS var = NUMBER, @var = NUMBER; 658 SELECT var, @var; 659END| 660DELIMITER ;| 661 662CALL p1(); 663 664DROP PROCEDURE p1; 665 666--echo 667--echo # Setting TABLE_NAME is currently not implemented. 668--echo 669 670DELIMITER |; 671CREATE PROCEDURE p1() 672BEGIN 673 DECLARE v VARCHAR(64); 674 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 675 GET DIAGNOSTICS CONDITION 1 v = TABLE_NAME; 676 DROP TABLE no_such_table; 677 SELECT v; 678END| 679DELIMITER ;| 680 681CALL p1(); 682 683DROP PROCEDURE p1; 684 685--echo 686--echo # Message is truncated to fit into target. No truncation warning. 687--echo 688 689DELIMITER |; 690CREATE PROCEDURE p1() 691BEGIN 692 DECLARE v CHAR(1); 693 CREATE TABLE IF NOT EXISTS t1 (a INT); 694 GET DIAGNOSTICS CONDITION 1 v = MESSAGE_TEXT; 695 SELECT v; 696END| 697DELIMITER ;| 698 699CREATE TABLE t1 (a INT); 700CALL p1(); 701DROP TABLE t1; 702 703DROP PROCEDURE p1; 704 705--echo 706--echo # Returns number of rows updated by the UPDATE statements. 707--echo 708 709DELIMITER |; 710CREATE PROCEDURE p1(IN param INT) 711LANGUAGE SQL 712BEGIN 713 DECLARE v INT DEFAULT 0; 714 DECLARE rcount_each INT; 715 DECLARE rcount_total INT DEFAULT 0; 716 WHILE v < 5 DO 717 UPDATE t1 SET a = a * 1.1 WHERE b = param; 718 GET DIAGNOSTICS rcount_each = ROW_COUNT; 719 SET rcount_total = rcount_total + rcount_each; 720 SET v = v + 1; 721 END WHILE; 722 SELECT rcount_total; 723END| 724DELIMITER ;| 725 726CREATE TABLE t1 (a REAL, b INT); 727INSERT INTO t1 VALUES (1.1, 1); 728CALL p1(1); 729DROP TABLE t1; 730 731DROP PROCEDURE p1; 732 733--echo 734--echo # GET DIAGNOSTICS doesn't clear the diagnostics area. 735--echo 736 737DELIMITER |; 738CREATE PROCEDURE p1() 739BEGIN 740 DECLARE CONTINUE HANDLER FOR SQLWARNING 741 BEGIN 742 GET CURRENT DIAGNOSTICS CONDITION 1 @x = RETURNED_SQLSTATE; 743 SIGNAL SQLSTATE '01002'; 744 GET CURRENT DIAGNOSTICS CONDITION 1 @y = RETURNED_SQLSTATE; 745 END; 746 SIGNAL SQLSTATE '01001'; 747END| 748DELIMITER ;| 749 750CALL p1(); 751SELECT @x, @y; 752 753DROP PROCEDURE p1; 754 755--echo 756--echo # Using OUT and INOUT parameters as the target variables. 757--echo 758 759DELIMITER |; 760CREATE PROCEDURE p1(OUT number INT, INOUT message TEXT) 761BEGIN 762 DECLARE warn CONDITION FOR SQLSTATE "01234"; 763 DECLARE CONTINUE HANDLER FOR SQLWARNING 764 BEGIN 765 GET DIAGNOSTICS number = NUMBER; 766 GET DIAGNOSTICS CONDITION 1 message = MESSAGE_TEXT; 767 END; 768 SELECT message; 769 SIGNAL warn SET MESSAGE_TEXT = "inout parameter"; 770END| 771DELIMITER ;| 772 773SET @var1 = 0; 774SET @var2 = "message text"; 775CALL p1(@var1, @var2); 776SELECT @var1, @var2; 777 778DROP PROCEDURE p1; 779 780--echo 781--echo # Using an IN parameter as the target variable. 782--echo 783 784DELIMITER |; 785CREATE PROCEDURE p1(IN number INT) 786BEGIN 787 SELECT number; 788 GET DIAGNOSTICS number = NUMBER; 789 SELECT number; 790END| 791DELIMITER ;| 792 793SET @var1 = 9999; 794CALL p1(@var1); 795SELECT @var1; 796 797DROP PROCEDURE p1; 798 799--echo 800--echo # Using GET DIAGNOSTICS in a stored function. 801--echo 802 803DELIMITER |; 804CREATE FUNCTION f1() RETURNS TEXT 805BEGIN 806 DECLARE message TEXT; 807 DECLARE warn CONDITION FOR SQLSTATE "01234"; 808 DECLARE CONTINUE HANDLER FOR SQLWARNING 809 BEGIN 810 GET DIAGNOSTICS CONDITION 1 message = MESSAGE_TEXT; 811 END; 812 SIGNAL warn SET MESSAGE_TEXT = "message text"; 813 return message; 814END| 815DELIMITER ;| 816 817SELECT f1(); 818 819DROP FUNCTION f1; 820 821--echo 822--echo # Using GET DIAGNOSTICS in a trigger. 823--echo 824 825CREATE TABLE t1 (a INT); 826 827DELIMITER |; 828CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW 829BEGIN 830 DECLARE var INT DEFAULT row_count(); 831 GET DIAGNOSTICS @var1 = ROW_COUNT; 832 SET @var2 = var; 833END| 834DELIMITER ;| 835 836SET @var1 = 9999, @var2 = 9999; 837INSERT INTO t1 VALUES (1), (2); 838SELECT @var1, @var2; 839 840DROP TRIGGER trg1; 841DROP TABLE t1; 842 843--echo 844--echo # GET DIAGNOSTICS does not reset ROW_COUNT 845--echo 846 847CREATE TABLE t1 (a INT); 848INSERT INTO t1 VALUES (1); 849GET DIAGNOSTICS @var1 = ROW_COUNT; 850GET DIAGNOSTICS @var2 = ROW_COUNT; 851SELECT @var1, @var2; 852DROP TABLE t1; 853 854--echo 855--echo # Items are UTF8 (utf8_general_ci default collation) 856--echo 857 858SELECT CAST(-19999999999999999999 AS SIGNED); 859GET DIAGNOSTICS CONDITION 1 @var1 = MESSAGE_TEXT, @var2 = CLASS_ORIGIN; 860SELECT CHARSET(@var1), COLLATION(@var1), COERCIBILITY(@var1); 861SELECT CHARSET(@var2), COLLATION(@var2), COERCIBILITY(@var2); 862 863--echo # 864--echo # Command statistics 865--echo # 866 867FLUSH STATUS; 868SHOW STATUS LIKE 'Com%get_diagnostics'; 869GET DIAGNOSTICS @var1 = NUMBER; 870SHOW STATUS LIKE 'Com%get_diagnostics'; 871 872 873--echo # 874--echo # WL#6406 Stacked diagnostic areas 875--echo # 876 877--echo # 878--echo # Test non-reserved keywords: STACKED 879 880CREATE TABLE t1 (stacked INT); 881INSERT INTO t1 (stacked) values (1); 882SELECT stacked FROM t1 WHERE stacked = 1; 883SELECT `stacked` FROM t1 WHERE `stacked` = 1; 884DROP TABLE t1; 885 886DELIMITER |; 887CREATE PROCEDURE p1() 888BEGIN 889 DECLARE stacked INT DEFAULT 1; 890 SELECT stacked; 891END| 892DELIMITER ;| 893 894CALL p1(); 895 896DROP PROCEDURE p1; 897 898--echo # 899--echo # Test GET STACKED DIAGNOSTICS syntax 900 901--error ER_PARSE_ERROR 902GET STACKED; 903--error ER_PARSE_ERROR 904GET STACKED DIAGNOSTICS; 905 906--echo # 907--echo # Error if used without active HANDLER 908 909--error ER_GET_STACKED_DA_WITHOUT_ACTIVE_HANDLER 910GET STACKED DIAGNOSTICS @var1 = NUMBER; 911 912CREATE PROCEDURE p1() GET STACKED DIAGNOSTICS @var1 = NUMBER; 913--error ER_GET_STACKED_DA_WITHOUT_ACTIVE_HANDLER 914CALL p1(); 915DROP PROCEDURE p1; 916 917delimiter |; 918CREATE FUNCTION f1() RETURNS INT 919BEGIN 920 GET STACKED DIAGNOSTICS @var1 = NUMBER; 921 RETURN 1; 922END| 923delimiter ;| 924--error ER_GET_STACKED_DA_WITHOUT_ACTIVE_HANDLER 925SELECT f1(); 926DROP FUNCTION f1; 927 928--echo # 929--echo # GET CURRENT DIAGNOSTICS = GET STACKED DIAGNOSTICS 930--echo # when handler is first activated 931--echo # GET STACKED DIAGNOSTICS doesn't change during handler 932 933CREATE TABLE t1(a INT); 934 935delimiter |; 936CREATE PROCEDURE p1() 937BEGIN 938 DECLARE EXIT HANDLER FOR SQLEXCEPTION 939 BEGIN 940 # Should be identical 941 GET CURRENT DIAGNOSTICS CONDITION 1 @msg1 = MESSAGE_TEXT, @errno1 = MYSQL_ERRNO; 942 GET STACKED DIAGNOSTICS CONDITION 1 @msg2 = MESSAGE_TEXT, @errno2 = MYSQL_ERRNO; 943 944 SELECT @msg1, @errno1; 945 SELECT @msg2, @errno2; 946 947 SELECT * FROM t1; # Clear first diagnostics area 948 949 # CURRENT should be empty, STACKED unchanged 950 GET CURRENT DIAGNOSTICS @cno = NUMBER; 951 SELECT @cno; 952 GET STACKED DIAGNOSTICS CONDITION 1 @msg4 = MESSAGE_TEXT, @errno4 = MYSQL_ERRNO; 953 SELECT @msg4, @errno4; 954 END; 955 DROP TABLE non_existent; 956END| 957delimiter ;| 958CALL p1(); 959DROP PROCEDURE p1; 960DROP TABLE t1; 961 962--echo # 963--echo # RESIGNAL of a warning should modify the warning both in first and 964--echo # second diagnostics area. 965 966delimiter |; 967CREATE PROCEDURE p1() 968BEGIN 969 DECLARE CONTINUE HANDLER FOR SQLWARNING 970 BEGIN 971 # Should be identical 972 GET CURRENT DIAGNOSTICS CONDITION 1 @msg1 = MESSAGE_TEXT, @errno1 = MYSQL_ERRNO; 973 SELECT @msg1, @errno1; 974 GET STACKED DIAGNOSTICS CONDITION 1 @msg2 = MESSAGE_TEXT, @errno2 = MYSQL_ERRNO; 975 SELECT @msg2, @errno2; 976 977 RESIGNAL SET MYSQL_ERRNO= 9999, MESSAGE_TEXT= 'Changed by resignal'; 978 979 # Should be changed, but still identical 980 GET CURRENT DIAGNOSTICS CONDITION 1 @msg3 = MESSAGE_TEXT, @errno3 = MYSQL_ERRNO; 981 SELECT @msg3, @errno3; 982 GET STACKED DIAGNOSTICS CONDITION 1 @msg4 = MESSAGE_TEXT, @errno4 = MYSQL_ERRNO; 983 SELECT @msg4, @errno4; 984 985 RESIGNAL SET MYSQL_ERRNO= 9999, MESSAGE_TEXT= 'Changed by resignal, for caller'; 986 END; 987 SELECT 10 + 'a'; 988END| 989delimiter ;| 990CALL p1(); 991DROP PROCEDURE p1; 992SET sql_mode = default; 993