PageRenderTime 17ms CodeModel.GetById 2ms app.highlight 7ms RepoModel.GetById 1ms app.codeStats 1ms

/mysql-test/r/signal.result

https://bitbucket.org/Habibutsu/mysql
Unknown | 2392 lines | 2392 code | 0 blank | 0 comment | 0 complexity | feb3946d798fe8cbd73f5176f83b72e5 MD5 | raw file

Large files files are truncated, but you can click here to view the full file

   1#
   2# PART 1: syntax
   3#
   4#
   5# Test every new reserved and non reserved keywords
   6#
   7drop table if exists signal_non_reserved;
   8create table signal_non_reserved (
   9class_origin int,
  10subclass_origin int,
  11constraint_catalog int,
  12constraint_schema int,
  13constraint_name int,
  14catalog_name int,
  15schema_name int,
  16table_name int,
  17column_name int,
  18cursor_name int,
  19message_text int,
  20sqlcode int
  21);
  22drop table signal_non_reserved;
  23drop table if exists diag_non_reserved;
  24create table diag_non_reserved (
  25diagnostics int,
  26current int,
  27stacked int,
  28exception int
  29);
  30drop table diag_non_reserved;
  31drop table if exists diag_cond_non_reserved;
  32create table diag_cond_non_reserved (
  33condition_identifier int,
  34condition_number int,
  35condition_name int,
  36connection_name int,
  37message_length int,
  38message_octet_length int,
  39parameter_mode int,
  40parameter_name int,
  41parameter_ordinal_position int,
  42returned_sqlstate int,
  43routine_catalog int,
  44routine_name int,
  45routine_schema int,
  46server_name int,
  47specific_name int,
  48trigger_catalog int,
  49trigger_name int,
  50trigger_schema int
  51);
  52drop table diag_cond_non_reserved;
  53drop table if exists diag_stmt_non_reserved;
  54create table diag_stmt_non_reserved (
  55number int,
  56more int,
  57command_function int,
  58command_function_code int,
  59dynamic_function int,
  60dynamic_function_code int,
  61row_count int,
  62transactions_committed int,
  63transactions_rolled_back int,
  64transaction_active int
  65);
  66drop table diag_stmt_non_reserved;
  67drop table if exists test_reserved;
  68create table test_reserved (signal int);
  69ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'signal int)' at line 1
  70create table test_reserved (resignal int);
  71ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'resignal int)' at line 1
  72create table test_reserved (condition int);
  73ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition int)' at line 1
  74#
  75# Test the SIGNAL syntax
  76#
  77drop procedure if exists test_invalid;
  78drop procedure if exists test_signal_syntax;
  79drop function if exists test_signal_func;
  80create procedure test_invalid()
  81begin
  82SIGNAL;
  83end $$
  84ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';
  85end' at line 3
  86create procedure test_invalid()
  87begin
  88SIGNAL foo;
  89end $$
  90ERROR 42000: Undefined CONDITION: foo
  91create procedure test_invalid()
  92begin
  93DECLARE foo CONDITION FOR 1234;
  94SIGNAL foo;
  95end $$
  96ERROR HY000: SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE
  97create procedure test_signal_syntax()
  98begin
  99DECLARE foo CONDITION FOR SQLSTATE '12345';
 100SIGNAL foo;
 101end $$
 102drop procedure test_signal_syntax $$
 103create procedure test_signal_syntax()
 104begin
 105SIGNAL SQLSTATE '23000';
 106end $$
 107drop procedure test_signal_syntax $$
 108create procedure test_signal_syntax()
 109begin
 110SIGNAL SQLSTATE VALUE '23000';
 111end $$
 112drop procedure test_signal_syntax $$
 113create procedure test_signal_syntax()
 114begin
 115DECLARE foo CONDITION FOR SQLSTATE '12345';
 116SIGNAL foo SET CLASS_ORIGIN = 'foo';
 117end $$
 118drop procedure test_signal_syntax $$
 119create procedure test_signal_syntax()
 120begin
 121DECLARE foo CONDITION FOR SQLSTATE '12345';
 122SIGNAL foo SET SUBCLASS_ORIGIN = 'foo';
 123end $$
 124drop procedure test_signal_syntax $$
 125create procedure test_signal_syntax()
 126begin
 127DECLARE foo CONDITION FOR SQLSTATE '12345';
 128SIGNAL foo SET CONSTRAINT_CATALOG = 'foo';
 129end $$
 130drop procedure test_signal_syntax $$
 131create procedure test_signal_syntax()
 132begin
 133DECLARE foo CONDITION FOR SQLSTATE '12345';
 134SIGNAL foo SET CONSTRAINT_SCHEMA = 'foo';
 135end $$
 136drop procedure test_signal_syntax $$
 137create procedure test_signal_syntax()
 138begin
 139DECLARE foo CONDITION FOR SQLSTATE '12345';
 140SIGNAL foo SET CONSTRAINT_NAME = 'foo';
 141end $$
 142drop procedure test_signal_syntax $$
 143create procedure test_signal_syntax()
 144begin
 145DECLARE foo CONDITION FOR SQLSTATE '12345';
 146SIGNAL foo SET CATALOG_NAME = 'foo';
 147end $$
 148drop procedure test_signal_syntax $$
 149create procedure test_signal_syntax()
 150begin
 151DECLARE foo CONDITION FOR SQLSTATE '12345';
 152SIGNAL foo SET SCHEMA_NAME = 'foo';
 153end $$
 154drop procedure test_signal_syntax $$
 155create procedure test_signal_syntax()
 156begin
 157DECLARE foo CONDITION FOR SQLSTATE '12345';
 158SIGNAL foo SET TABLE_NAME = 'foo';
 159end $$
 160drop procedure test_signal_syntax $$
 161create procedure test_signal_syntax()
 162begin
 163DECLARE foo CONDITION FOR SQLSTATE '12345';
 164SIGNAL foo SET COLUMN_NAME = 'foo';
 165end $$
 166drop procedure test_signal_syntax $$
 167create procedure test_signal_syntax()
 168begin
 169DECLARE foo CONDITION FOR SQLSTATE '12345';
 170SIGNAL foo SET CURSOR_NAME = 'foo';
 171end $$
 172drop procedure test_signal_syntax $$
 173create procedure test_signal_syntax()
 174begin
 175DECLARE foo CONDITION FOR SQLSTATE '12345';
 176SIGNAL foo SET MESSAGE_TEXT = 'foo';
 177end $$
 178drop procedure test_signal_syntax $$
 179create procedure test_signal_syntax()
 180begin
 181DECLARE foo CONDITION FOR SQLSTATE '12345';
 182SIGNAL foo SET MYSQL_ERRNO = 'foo';
 183end $$
 184drop procedure test_signal_syntax $$
 185create procedure test_invalid()
 186begin
 187DECLARE foo CONDITION FOR SQLSTATE '12345';
 188SIGNAL foo SET CLASS_ORIGIN = 'foo', CLASS_ORIGIN = 'bar';
 189end $$
 190ERROR 42000: Duplicate condition information item 'CLASS_ORIGIN'
 191create procedure test_invalid()
 192begin
 193DECLARE foo CONDITION FOR SQLSTATE '12345';
 194SIGNAL foo SET MESSAGE_TEXT = 'foo', MESSAGE_TEXT = 'bar';
 195end $$
 196ERROR 42000: Duplicate condition information item 'MESSAGE_TEXT'
 197create procedure test_invalid()
 198begin
 199DECLARE foo CONDITION FOR SQLSTATE '12345';
 200SIGNAL foo SET MYSQL_ERRNO = 'foo', MYSQL_ERRNO = 'bar';
 201end $$
 202ERROR 42000: Duplicate condition information item 'MYSQL_ERRNO'
 203create procedure test_signal_syntax()
 204begin
 205DECLARE foo CONDITION FOR SQLSTATE '12345';
 206SIGNAL foo SET
 207CLASS_ORIGIN = 'foo',
 208SUBCLASS_ORIGIN = 'foo',
 209CONSTRAINT_CATALOG = 'foo',
 210CONSTRAINT_SCHEMA = 'foo',
 211CONSTRAINT_NAME = 'foo',
 212CATALOG_NAME = 'foo',
 213SCHEMA_NAME = 'foo',
 214TABLE_NAME = 'foo',
 215COLUMN_NAME = 'foo',
 216CURSOR_NAME = 'foo',
 217MESSAGE_TEXT = 'foo',
 218MYSQL_ERRNO = 'foo';
 219end $$
 220drop procedure test_signal_syntax $$
 221SIGNAL SQLSTATE '00000' $$
 222ERROR 42000: Bad SQLSTATE: '00000'
 223SIGNAL SQLSTATE '00001' $$
 224ERROR 42000: Bad SQLSTATE: '00001'
 225create procedure test_invalid()
 226begin
 227SIGNAL SQLSTATE '00000';
 228end $$
 229ERROR 42000: Bad SQLSTATE: '00000'
 230create procedure test_invalid()
 231begin
 232SIGNAL SQLSTATE '00001';
 233end $$
 234ERROR 42000: Bad SQLSTATE: '00001'
 235#
 236# Test conditions information that SIGNAL can not set
 237#
 238create procedure test_invalid()
 239begin
 240SIGNAL SQLSTATE '12345' SET bla_bla = 'foo';
 241end $$
 242ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bla_bla = 'foo';
 243end' at line 3
 244create procedure test_invalid()
 245begin
 246SIGNAL SQLSTATE '12345' SET CONDITION_IDENTIFIER = 'foo';
 247end $$
 248ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONDITION_IDENTIFIER = 'foo';
 249end' at line 3
 250create procedure test_invalid()
 251begin
 252SIGNAL SQLSTATE '12345' SET CONDITION_NUMBER = 'foo';
 253end $$
 254ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONDITION_NUMBER = 'foo';
 255end' at line 3
 256create procedure test_invalid()
 257begin
 258SIGNAL SQLSTATE '12345' SET CONNECTION_NAME = 'foo';
 259end $$
 260ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONNECTION_NAME = 'foo';
 261end' at line 3
 262create procedure test_invalid()
 263begin
 264SIGNAL SQLSTATE '12345' SET MESSAGE_LENGTH = 'foo';
 265end $$
 266ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MESSAGE_LENGTH = 'foo';
 267end' at line 3
 268create procedure test_invalid()
 269begin
 270SIGNAL SQLSTATE '12345' SET MESSAGE_OCTET_LENGTH = 'foo';
 271end $$
 272ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MESSAGE_OCTET_LENGTH = 'foo';
 273end' at line 3
 274create procedure test_invalid()
 275begin
 276SIGNAL SQLSTATE '12345' SET PARAMETER_MODE = 'foo';
 277end $$
 278ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARAMETER_MODE = 'foo';
 279end' at line 3
 280create procedure test_invalid()
 281begin
 282SIGNAL SQLSTATE '12345' SET PARAMETER_NAME = 'foo';
 283end $$
 284ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARAMETER_NAME = 'foo';
 285end' at line 3
 286create procedure test_invalid()
 287begin
 288SIGNAL SQLSTATE '12345' SET PARAMETER_ORDINAL_POSITION = 'foo';
 289end $$
 290ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARAMETER_ORDINAL_POSITION = 'foo';
 291end' at line 3
 292create procedure test_invalid()
 293begin
 294SIGNAL SQLSTATE '12345' SET RETURNED_SQLSTATE = 'foo';
 295end $$
 296ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNED_SQLSTATE = 'foo';
 297end' at line 3
 298create procedure test_invalid()
 299begin
 300SIGNAL SQLSTATE '12345' SET ROUTINE_CATALOG = 'foo';
 301end $$
 302ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROUTINE_CATALOG = 'foo';
 303end' at line 3
 304create procedure test_invalid()
 305begin
 306SIGNAL SQLSTATE '12345' SET ROUTINE_NAME = 'foo';
 307end $$
 308ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROUTINE_NAME = 'foo';
 309end' at line 3
 310create procedure test_invalid()
 311begin
 312SIGNAL SQLSTATE '12345' SET ROUTINE_SCHEMA = 'foo';
 313end $$
 314ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROUTINE_SCHEMA = 'foo';
 315end' at line 3
 316create procedure test_invalid()
 317begin
 318SIGNAL SQLSTATE '12345' SET SERVER_NAME = 'foo';
 319end $$
 320ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SERVER_NAME = 'foo';
 321end' at line 3
 322create procedure test_invalid()
 323begin
 324SIGNAL SQLSTATE '12345' SET SPECIFIC_NAME = 'foo';
 325end $$
 326ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SPECIFIC_NAME = 'foo';
 327end' at line 3
 328create procedure test_invalid()
 329begin
 330SIGNAL SQLSTATE '12345' SET TRIGGER_CATALOG = 'foo';
 331end $$
 332ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGER_CATALOG = 'foo';
 333end' at line 3
 334create procedure test_invalid()
 335begin
 336SIGNAL SQLSTATE '12345' SET TRIGGER_NAME = 'foo';
 337end $$
 338ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGER_NAME = 'foo';
 339end' at line 3
 340create procedure test_invalid()
 341begin
 342SIGNAL SQLSTATE '12345' SET TRIGGER_SCHEMA = 'foo';
 343end $$
 344ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGER_SCHEMA = 'foo';
 345end' at line 3
 346#
 347# Test the RESIGNAL syntax
 348#
 349drop procedure if exists test_invalid;
 350drop procedure if exists test_resignal_syntax;
 351create procedure test_invalid()
 352begin
 353RESIGNAL foo;
 354end $$
 355ERROR 42000: Undefined CONDITION: foo
 356create procedure test_resignal_syntax()
 357begin
 358RESIGNAL;
 359end $$
 360drop procedure test_resignal_syntax $$
 361create procedure test_invalid()
 362begin
 363DECLARE foo CONDITION FOR 1234;
 364RESIGNAL foo;
 365end $$
 366ERROR HY000: SIGNAL/RESIGNAL can only use a CONDITION defined with SQLSTATE
 367create procedure test_resignal_syntax()
 368begin
 369DECLARE foo CONDITION FOR SQLSTATE '12345';
 370RESIGNAL foo;
 371end $$
 372drop procedure test_resignal_syntax $$
 373create procedure test_resignal_syntax()
 374begin
 375RESIGNAL SQLSTATE '23000';
 376end $$
 377drop procedure test_resignal_syntax $$
 378create procedure test_resignal_syntax()
 379begin
 380RESIGNAL SQLSTATE VALUE '23000';
 381end $$
 382drop procedure test_resignal_syntax $$
 383create procedure test_resignal_syntax()
 384begin
 385RESIGNAL SET CLASS_ORIGIN = 'foo';
 386end $$
 387drop procedure test_resignal_syntax $$
 388create procedure test_resignal_syntax()
 389begin
 390DECLARE foo CONDITION FOR SQLSTATE '12345';
 391RESIGNAL foo SET CLASS_ORIGIN = 'foo';
 392end $$
 393drop procedure test_resignal_syntax $$
 394create procedure test_resignal_syntax()
 395begin
 396RESIGNAL SET SUBCLASS_ORIGIN = 'foo';
 397end $$
 398drop procedure test_resignal_syntax $$
 399create procedure test_resignal_syntax()
 400begin
 401DECLARE foo CONDITION FOR SQLSTATE '12345';
 402RESIGNAL foo SET SUBCLASS_ORIGIN = 'foo';
 403end $$
 404drop procedure test_resignal_syntax $$
 405create procedure test_resignal_syntax()
 406begin
 407RESIGNAL SET CONSTRAINT_CATALOG = 'foo';
 408end $$
 409drop procedure test_resignal_syntax $$
 410create procedure test_resignal_syntax()
 411begin
 412DECLARE foo CONDITION FOR SQLSTATE '12345';
 413RESIGNAL foo SET CONSTRAINT_CATALOG = 'foo';
 414end $$
 415drop procedure test_resignal_syntax $$
 416create procedure test_resignal_syntax()
 417begin
 418RESIGNAL SET CONSTRAINT_SCHEMA = 'foo';
 419end $$
 420drop procedure test_resignal_syntax $$
 421create procedure test_resignal_syntax()
 422begin
 423DECLARE foo CONDITION FOR SQLSTATE '12345';
 424RESIGNAL foo SET CONSTRAINT_SCHEMA = 'foo';
 425end $$
 426drop procedure test_resignal_syntax $$
 427create procedure test_resignal_syntax()
 428begin
 429RESIGNAL SET CONSTRAINT_NAME = 'foo';
 430end $$
 431drop procedure test_resignal_syntax $$
 432create procedure test_resignal_syntax()
 433begin
 434DECLARE foo CONDITION FOR SQLSTATE '12345';
 435RESIGNAL foo SET CONSTRAINT_NAME = 'foo';
 436end $$
 437drop procedure test_resignal_syntax $$
 438create procedure test_resignal_syntax()
 439begin
 440RESIGNAL SET CATALOG_NAME = 'foo';
 441end $$
 442drop procedure test_resignal_syntax $$
 443create procedure test_resignal_syntax()
 444begin
 445DECLARE foo CONDITION FOR SQLSTATE '12345';
 446RESIGNAL foo SET CATALOG_NAME = 'foo';
 447end $$
 448drop procedure test_resignal_syntax $$
 449create procedure test_resignal_syntax()
 450begin
 451RESIGNAL SET SCHEMA_NAME = 'foo';
 452end $$
 453drop procedure test_resignal_syntax $$
 454create procedure test_resignal_syntax()
 455begin
 456DECLARE foo CONDITION FOR SQLSTATE '12345';
 457RESIGNAL foo SET SCHEMA_NAME = 'foo';
 458end $$
 459drop procedure test_resignal_syntax $$
 460create procedure test_resignal_syntax()
 461begin
 462RESIGNAL SET TABLE_NAME = 'foo';
 463end $$
 464drop procedure test_resignal_syntax $$
 465create procedure test_resignal_syntax()
 466begin
 467DECLARE foo CONDITION FOR SQLSTATE '12345';
 468RESIGNAL foo SET TABLE_NAME = 'foo';
 469end $$
 470drop procedure test_resignal_syntax $$
 471create procedure test_resignal_syntax()
 472begin
 473RESIGNAL SET COLUMN_NAME = 'foo';
 474end $$
 475drop procedure test_resignal_syntax $$
 476create procedure test_resignal_syntax()
 477begin
 478DECLARE foo CONDITION FOR SQLSTATE '12345';
 479RESIGNAL foo SET COLUMN_NAME = 'foo';
 480end $$
 481drop procedure test_resignal_syntax $$
 482create procedure test_resignal_syntax()
 483begin
 484RESIGNAL SET CURSOR_NAME = 'foo';
 485end $$
 486drop procedure test_resignal_syntax $$
 487create procedure test_resignal_syntax()
 488begin
 489DECLARE foo CONDITION FOR SQLSTATE '12345';
 490RESIGNAL foo SET CURSOR_NAME = 'foo';
 491end $$
 492drop procedure test_resignal_syntax $$
 493create procedure test_resignal_syntax()
 494begin
 495RESIGNAL SET MESSAGE_TEXT = 'foo';
 496end $$
 497drop procedure test_resignal_syntax $$
 498create procedure test_resignal_syntax()
 499begin
 500DECLARE foo CONDITION FOR SQLSTATE '12345';
 501RESIGNAL foo SET MESSAGE_TEXT = 'foo';
 502end $$
 503drop procedure test_resignal_syntax $$
 504create procedure test_resignal_syntax()
 505begin
 506RESIGNAL SET MYSQL_ERRNO = 'foo';
 507end $$
 508drop procedure test_resignal_syntax $$
 509create procedure test_resignal_syntax()
 510begin
 511DECLARE foo CONDITION FOR SQLSTATE '12345';
 512RESIGNAL foo SET MYSQL_ERRNO = 'foo';
 513end $$
 514drop procedure test_resignal_syntax $$
 515create procedure test_invalid()
 516begin
 517DECLARE foo CONDITION FOR SQLSTATE '12345';
 518RESIGNAL foo SET CLASS_ORIGIN = 'foo', CLASS_ORIGIN = 'bar';
 519end $$
 520ERROR 42000: Duplicate condition information item 'CLASS_ORIGIN'
 521create procedure test_invalid()
 522begin
 523DECLARE foo CONDITION FOR SQLSTATE '12345';
 524RESIGNAL foo SET MESSAGE_TEXT = 'foo', MESSAGE_TEXT = 'bar';
 525end $$
 526ERROR 42000: Duplicate condition information item 'MESSAGE_TEXT'
 527create procedure test_invalid()
 528begin
 529DECLARE foo CONDITION FOR SQLSTATE '12345';
 530RESIGNAL foo SET MYSQL_ERRNO = 'foo', MYSQL_ERRNO = 'bar';
 531end $$
 532ERROR 42000: Duplicate condition information item 'MYSQL_ERRNO'
 533create procedure test_resignal_syntax()
 534begin
 535DECLARE foo CONDITION FOR SQLSTATE '12345';
 536RESIGNAL foo SET
 537CLASS_ORIGIN = 'foo',
 538SUBCLASS_ORIGIN = 'foo',
 539CONSTRAINT_CATALOG = 'foo',
 540CONSTRAINT_SCHEMA = 'foo',
 541CONSTRAINT_NAME = 'foo',
 542CATALOG_NAME = 'foo',
 543SCHEMA_NAME = 'foo',
 544TABLE_NAME = 'foo',
 545COLUMN_NAME = 'foo',
 546CURSOR_NAME = 'foo',
 547MESSAGE_TEXT = 'foo';
 548end $$
 549drop procedure test_resignal_syntax $$
 550create procedure test_invalid()
 551begin
 552RESIGNAL SQLSTATE '00000';
 553end $$
 554ERROR 42000: Bad SQLSTATE: '00000'
 555create procedure test_invalid()
 556begin
 557RESIGNAL SQLSTATE '00001';
 558end $$
 559ERROR 42000: Bad SQLSTATE: '00001'
 560#
 561# PART 2: non preparable statements
 562#
 563prepare stmt from 'SIGNAL SQLSTATE \'23000\'';
 564ERROR HY000: This command is not supported in the prepared statement protocol yet
 565prepare stmt from 'RESIGNAL SQLSTATE \'23000\'';
 566ERROR HY000: This command is not supported in the prepared statement protocol yet
 567#
 568# PART 3: runtime execution
 569#
 570drop procedure if exists test_signal;
 571drop procedure if exists test_resignal;
 572drop table if exists t_warn;
 573drop table if exists t_cursor;
 574create table t_warn(a integer(2));
 575create table t_cursor(a integer);
 576#
 577# SIGNAL can also appear in a query
 578#
 579SIGNAL foo;
 580ERROR 42000: Undefined CONDITION: foo
 581SIGNAL SQLSTATE '01000';
 582Warnings:
 583Warning	1642	Unhandled user-defined warning condition
 584SIGNAL SQLSTATE '02000';
 585ERROR 02000: Unhandled user-defined not found condition
 586SIGNAL SQLSTATE '23000';
 587ERROR 23000: Unhandled user-defined exception condition
 588SIGNAL SQLSTATE VALUE '23000';
 589ERROR 23000: Unhandled user-defined exception condition
 590SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 65536;
 591ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '65536'
 592SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 99999;
 593ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '99999'
 594SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 4294967295;
 595ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '4294967295'
 596SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 0;
 597ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '0'
 598SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = -1;
 599ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1
 600SIGNAL SQLSTATE 'HY000' SET MYSQL_ERRNO = 65535;
 601ERROR HY000: Unhandled user-defined exception condition
 602#
 603# RESIGNAL can also appear in a query
 604#
 605RESIGNAL;
 606ERROR 0K000: RESIGNAL when handler not active
 607RESIGNAL foo;
 608ERROR 42000: Undefined CONDITION: foo
 609RESIGNAL SQLSTATE '12345';
 610ERROR 0K000: RESIGNAL when handler not active
 611RESIGNAL SQLSTATE VALUE '12345';
 612ERROR 0K000: RESIGNAL when handler not active
 613#
 614# Different kind of SIGNAL conditions
 615#
 616create procedure test_signal()
 617begin
 618# max range
 619DECLARE foo CONDITION FOR SQLSTATE 'AABBB';
 620SIGNAL foo SET MYSQL_ERRNO = 65535;
 621end $$
 622call test_signal() $$
 623ERROR AABBB: Unhandled user-defined exception condition
 624drop procedure test_signal $$
 625create procedure test_signal()
 626begin
 627# max range
 628DECLARE foo CONDITION FOR SQLSTATE 'AABBB';
 629SIGNAL foo SET MYSQL_ERRNO = 65536;
 630end $$
 631call test_signal() $$
 632ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '65536'
 633drop procedure test_signal $$
 634create procedure test_signal()
 635begin
 636# Error
 637DECLARE foo CONDITION FOR SQLSTATE '99999';
 638SIGNAL foo SET MYSQL_ERRNO = 9999;
 639end $$
 640call test_signal() $$
 641ERROR 99999: Unhandled user-defined exception condition
 642drop procedure test_signal $$
 643create procedure test_signal()
 644begin
 645# warning
 646DECLARE too_few_records CONDITION FOR SQLSTATE '01000';
 647SIGNAL too_few_records SET MYSQL_ERRNO = 1261;
 648end $$
 649call test_signal() $$
 650Warnings:
 651Warning	1261	Unhandled user-defined warning condition
 652drop procedure test_signal $$
 653create procedure test_signal()
 654begin
 655# Not found
 656DECLARE sp_fetch_no_data CONDITION FOR SQLSTATE '02000';
 657SIGNAL sp_fetch_no_data SET MYSQL_ERRNO = 1329;
 658end $$
 659call test_signal() $$
 660ERROR 02000: Unhandled user-defined not found condition
 661drop procedure test_signal $$
 662create procedure test_signal()
 663begin
 664# Error
 665DECLARE sp_cursor_already_open CONDITION FOR SQLSTATE '24000';
 666SIGNAL sp_cursor_already_open SET MYSQL_ERRNO = 1325;
 667end $$
 668call test_signal() $$
 669ERROR 24000: Unhandled user-defined exception condition
 670drop procedure test_signal $$
 671create procedure test_signal()
 672begin
 673# Severe error
 674DECLARE lock_deadlock CONDITION FOR SQLSTATE '40001';
 675SIGNAL lock_deadlock SET MYSQL_ERRNO = 1213;
 676end $$
 677call test_signal() $$
 678ERROR 40001: Unhandled user-defined exception condition
 679drop procedure test_signal $$
 680create procedure test_signal()
 681begin
 682# Unknown -> error
 683DECLARE foo CONDITION FOR SQLSTATE "99999";
 684SIGNAL foo;
 685end $$
 686call test_signal() $$
 687ERROR 99999: Unhandled user-defined exception condition
 688drop procedure test_signal $$
 689create procedure test_signal()
 690begin
 691# warning, no subclass
 692DECLARE warn CONDITION FOR SQLSTATE "01000";
 693SIGNAL warn;
 694end $$
 695call test_signal() $$
 696Warnings:
 697Warning	1642	Unhandled user-defined warning condition
 698drop procedure test_signal $$
 699create procedure test_signal()
 700begin
 701# warning, with subclass
 702DECLARE warn CONDITION FOR SQLSTATE "01123";
 703SIGNAL warn;
 704end $$
 705call test_signal() $$
 706Warnings:
 707Warning	1642	Unhandled user-defined warning condition
 708drop procedure test_signal $$
 709create procedure test_signal()
 710begin
 711# Not found, no subclass
 712DECLARE not_found CONDITION FOR SQLSTATE "02000";
 713SIGNAL not_found;
 714end $$
 715call test_signal() $$
 716ERROR 02000: Unhandled user-defined not found condition
 717drop procedure test_signal $$
 718create procedure test_signal()
 719begin
 720# Not found, with subclass
 721DECLARE not_found CONDITION FOR SQLSTATE "02XXX";
 722SIGNAL not_found;
 723end $$
 724call test_signal() $$
 725ERROR 02XXX: Unhandled user-defined not found condition
 726drop procedure test_signal $$
 727create procedure test_signal()
 728begin
 729# Error, no subclass
 730DECLARE error CONDITION FOR SQLSTATE "12000";
 731SIGNAL error;
 732end $$
 733call test_signal() $$
 734ERROR 12000: Unhandled user-defined exception condition
 735drop procedure test_signal $$
 736create procedure test_signal()
 737begin
 738# Error, with subclass
 739DECLARE error CONDITION FOR SQLSTATE "12ABC";
 740SIGNAL error;
 741end $$
 742call test_signal() $$
 743ERROR 12ABC: Unhandled user-defined exception condition
 744drop procedure test_signal $$
 745create procedure test_signal()
 746begin
 747# Severe error, no subclass
 748DECLARE error CONDITION FOR SQLSTATE "40000";
 749SIGNAL error;
 750end $$
 751call test_signal() $$
 752ERROR 40000: Unhandled user-defined exception condition
 753drop procedure test_signal $$
 754create procedure test_signal()
 755begin
 756# Severe error, with subclass
 757DECLARE error CONDITION FOR SQLSTATE "40001";
 758SIGNAL error;
 759end $$
 760call test_signal() $$
 761ERROR 40001: Unhandled user-defined exception condition
 762drop procedure test_signal $$
 763#
 764# Test the scope of condition
 765#
 766create procedure test_signal()
 767begin
 768DECLARE foo CONDITION FOR SQLSTATE '99999';
 769begin
 770DECLARE foo CONDITION FOR 8888;
 771end;
 772SIGNAL foo SET MYSQL_ERRNO=9999; /* outer */
 773end $$
 774call test_signal() $$
 775ERROR 99999: Unhandled user-defined exception condition
 776drop procedure test_signal $$
 777create procedure test_signal()
 778begin
 779DECLARE foo CONDITION FOR 9999;
 780begin
 781DECLARE foo CONDITION FOR SQLSTATE '88888';
 782SIGNAL foo SET MYSQL_ERRNO=8888; /* inner */
 783end;
 784end $$
 785call test_signal() $$
 786ERROR 88888: Unhandled user-defined exception condition
 787drop procedure test_signal $$
 788#
 789# Test SET MYSQL_ERRNO
 790#
 791create procedure test_signal()
 792begin
 793DECLARE foo CONDITION FOR SQLSTATE '99999';
 794SIGNAL foo SET MYSQL_ERRNO = 1111;
 795end $$
 796call test_signal() $$
 797ERROR 99999: Unhandled user-defined exception condition
 798drop procedure test_signal $$
 799create procedure test_signal()
 800begin
 801DECLARE warn CONDITION FOR SQLSTATE "01000";
 802SIGNAL warn SET MYSQL_ERRNO = 1111;
 803end $$
 804call test_signal() $$
 805Warnings:
 806Warning	1111	Unhandled user-defined warning condition
 807drop procedure test_signal $$
 808create procedure test_signal()
 809begin
 810DECLARE not_found CONDITION FOR SQLSTATE "02000";
 811SIGNAL not_found SET MYSQL_ERRNO = 1111;
 812end $$
 813call test_signal() $$
 814ERROR 02000: Unhandled user-defined not found condition
 815drop procedure test_signal $$
 816create procedure test_signal()
 817begin
 818DECLARE error CONDITION FOR SQLSTATE "55000";
 819SIGNAL error SET MYSQL_ERRNO = 1111;
 820end $$
 821call test_signal() $$
 822ERROR 55000: Unhandled user-defined exception condition
 823drop procedure test_signal $$
 824#
 825# Test SET MESSAGE_TEXT
 826#
 827SIGNAL SQLSTATE '77777' SET MESSAGE_TEXT='' $$
 828ERROR 77777: 
 829create procedure test_signal()
 830begin
 831DECLARE foo CONDITION FOR SQLSTATE '77777';
 832SIGNAL foo SET
 833MESSAGE_TEXT = "",
 834MYSQL_ERRNO=5678;
 835end $$
 836call test_signal() $$
 837ERROR 77777: 
 838drop procedure test_signal $$
 839create procedure test_signal()
 840begin
 841DECLARE foo CONDITION FOR SQLSTATE '99999';
 842SIGNAL foo SET
 843MESSAGE_TEXT = "Something bad happened",
 844MYSQL_ERRNO=9999;
 845end $$
 846call test_signal() $$
 847ERROR 99999: Something bad happened
 848drop procedure test_signal $$
 849create procedure test_signal()
 850begin
 851DECLARE warn CONDITION FOR SQLSTATE "01000";
 852SIGNAL warn SET MESSAGE_TEXT = "Something bad happened";
 853end $$
 854call test_signal() $$
 855Warnings:
 856Warning	1642	Something bad happened
 857drop procedure test_signal $$
 858create procedure test_signal()
 859begin
 860DECLARE not_found CONDITION FOR SQLSTATE "02000";
 861SIGNAL not_found SET MESSAGE_TEXT = "Something bad happened";
 862end $$
 863call test_signal() $$
 864ERROR 02000: Something bad happened
 865drop procedure test_signal $$
 866create procedure test_signal()
 867begin
 868DECLARE error CONDITION FOR SQLSTATE "55000";
 869SIGNAL error SET MESSAGE_TEXT = "Something bad happened";
 870end $$
 871call test_signal() $$
 872ERROR 55000: Something bad happened
 873drop procedure test_signal $$
 874create procedure test_signal()
 875begin
 876DECLARE something CONDITION FOR SQLSTATE "01000";
 877SIGNAL something SET MESSAGE_TEXT = _utf8 "This is a UTF8 text";
 878end $$
 879call test_signal() $$
 880Warnings:
 881Warning	1642	This is a UTF8 text
 882drop procedure test_signal $$
 883create procedure test_signal()
 884begin
 885DECLARE something CONDITION FOR SQLSTATE "01000";
 886SIGNAL something SET MESSAGE_TEXT = "";
 887end $$
 888call test_signal() $$
 889Warnings:
 890Warning	1642	
 891drop procedure test_signal $$
 892create procedure test_signal()
 893begin
 894DECLARE warn CONDITION FOR SQLSTATE "01111";
 895SIGNAL warn SET MESSAGE_TEXT = "ĂĄ a";
 896end $$
 897call test_signal() $$
 898Warnings:
 899Warning	1642	ĂĄ a
 900show warnings $$
 901Level	Code	Message
 902Warning	1642	ĂĄ a
 903drop procedure test_signal $$
 904#
 905# Test SET complex expressions
 906#
 907create procedure test_signal()
 908begin
 909DECLARE error CONDITION FOR SQLSTATE '99999';
 910SIGNAL error SET
 911MYSQL_ERRNO = NULL;
 912end $$
 913call test_signal() $$
 914ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of 'NULL'
 915drop procedure test_signal $$
 916create procedure test_signal()
 917begin
 918DECLARE error CONDITION FOR SQLSTATE '99999';
 919SIGNAL error SET
 920CLASS_ORIGIN = NULL;
 921end $$
 922call test_signal() $$
 923ERROR 42000: Variable 'CLASS_ORIGIN' can't be set to the value of 'NULL'
 924drop procedure test_signal $$
 925create procedure test_signal()
 926begin
 927DECLARE error CONDITION FOR SQLSTATE '99999';
 928SIGNAL error SET
 929SUBCLASS_ORIGIN = NULL;
 930end $$
 931call test_signal() $$
 932ERROR 42000: Variable 'SUBCLASS_ORIGIN' can't be set to the value of 'NULL'
 933drop procedure test_signal $$
 934create procedure test_signal()
 935begin
 936DECLARE error CONDITION FOR SQLSTATE '99999';
 937SIGNAL error SET
 938CONSTRAINT_CATALOG = NULL;
 939end $$
 940call test_signal() $$
 941ERROR 42000: Variable 'CONSTRAINT_CATALOG' can't be set to the value of 'NULL'
 942drop procedure test_signal $$
 943create procedure test_signal()
 944begin
 945DECLARE error CONDITION FOR SQLSTATE '99999';
 946SIGNAL error SET
 947CONSTRAINT_SCHEMA = NULL;
 948end $$
 949call test_signal() $$
 950ERROR 42000: Variable 'CONSTRAINT_SCHEMA' can't be set to the value of 'NULL'
 951drop procedure test_signal $$
 952create procedure test_signal()
 953begin
 954DECLARE error CONDITION FOR SQLSTATE '99999';
 955SIGNAL error SET
 956CONSTRAINT_NAME = NULL;
 957end $$
 958call test_signal() $$
 959ERROR 42000: Variable 'CONSTRAINT_NAME' can't be set to the value of 'NULL'
 960drop procedure test_signal $$
 961create procedure test_signal()
 962begin
 963DECLARE error CONDITION FOR SQLSTATE '99999';
 964SIGNAL error SET
 965CATALOG_NAME = NULL;
 966end $$
 967call test_signal() $$
 968ERROR 42000: Variable 'CATALOG_NAME' can't be set to the value of 'NULL'
 969drop procedure test_signal $$
 970create procedure test_signal()
 971begin
 972DECLARE error CONDITION FOR SQLSTATE '99999';
 973SIGNAL error SET
 974SCHEMA_NAME = NULL;
 975end $$
 976call test_signal() $$
 977ERROR 42000: Variable 'SCHEMA_NAME' can't be set to the value of 'NULL'
 978drop procedure test_signal $$
 979create procedure test_signal()
 980begin
 981DECLARE error CONDITION FOR SQLSTATE '99999';
 982SIGNAL error SET
 983TABLE_NAME = NULL;
 984end $$
 985call test_signal() $$
 986ERROR 42000: Variable 'TABLE_NAME' can't be set to the value of 'NULL'
 987drop procedure test_signal $$
 988create procedure test_signal()
 989begin
 990DECLARE error CONDITION FOR SQLSTATE '99999';
 991SIGNAL error SET
 992COLUMN_NAME = NULL;
 993end $$
 994call test_signal() $$
 995ERROR 42000: Variable 'COLUMN_NAME' can't be set to the value of 'NULL'
 996drop procedure test_signal $$
 997create procedure test_signal()
 998begin
 999DECLARE error CONDITION FOR SQLSTATE '99999';
1000SIGNAL error SET
1001CURSOR_NAME = NULL;
1002end $$
1003call test_signal() $$
1004ERROR 42000: Variable 'CURSOR_NAME' can't be set to the value of 'NULL'
1005drop procedure test_signal $$
1006create procedure test_signal()
1007begin
1008DECLARE error CONDITION FOR SQLSTATE '99999';
1009SIGNAL error SET
1010MESSAGE_TEXT = NULL;
1011end $$
1012call test_signal() $$
1013ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'
1014drop procedure test_signal $$
1015create procedure test_signal()
1016begin
1017DECLARE something CONDITION FOR SQLSTATE '99999';
1018DECLARE message_text VARCHAR(64) DEFAULT "Local string variable";
1019DECLARE sqlcode INTEGER DEFAULT 1234;
1020SIGNAL something SET
1021MESSAGE_TEXT = message_text,
1022MYSQL_ERRNO = sqlcode;
1023end $$
1024call test_signal() $$
1025ERROR 99999: Local string variable
1026drop procedure test_signal $$
1027create procedure test_signal(message_text VARCHAR(64), sqlcode INTEGER)
1028begin
1029DECLARE something CONDITION FOR SQLSTATE "12345";
1030SIGNAL something SET
1031MESSAGE_TEXT = message_text,
1032MYSQL_ERRNO = sqlcode;
1033end $$
1034call test_signal("Parameter string", NULL) $$
1035ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of 'NULL'
1036call test_signal(NULL, 1234) $$
1037ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'
1038call test_signal("Parameter string", 5678) $$
1039ERROR 12345: Parameter string
1040drop procedure test_signal $$
1041create procedure test_signal()
1042begin
1043DECLARE something CONDITION FOR SQLSTATE "AABBB";
1044SIGNAL something SET
1045MESSAGE_TEXT = @message_text,
1046MYSQL_ERRNO = @sqlcode;
1047end $$
1048call test_signal() $$
1049ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'
1050set @sqlcode= 12 $$
1051call test_signal() $$
1052ERROR 42000: Variable 'MESSAGE_TEXT' can't be set to the value of 'NULL'
1053set @message_text= "User variable" $$
1054call test_signal() $$
1055ERROR AABBB: User variable
1056drop procedure test_signal $$
1057create procedure test_invalid()
1058begin
1059DECLARE something CONDITION FOR SQLSTATE "AABBB";
1060SIGNAL something SET
1061MESSAGE_TEXT = @message_text := 'illegal',
1062MYSQL_ERRNO = @sqlcode := 1234;
1063end $$
1064ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
1065MYSQL_ERRNO = @sqlcode := 1234;
1066end' at line 5
1067create procedure test_signal()
1068begin
1069DECLARE aaa VARCHAR(64);
1070DECLARE bbb VARCHAR(64);
1071DECLARE ccc VARCHAR(64);
1072DECLARE ddd VARCHAR(64);
1073DECLARE eee VARCHAR(64);
1074DECLARE fff VARCHAR(64);
1075DECLARE ggg VARCHAR(64);
1076DECLARE hhh VARCHAR(64);
1077DECLARE iii VARCHAR(64);
1078DECLARE jjj VARCHAR(64);
1079DECLARE kkk VARCHAR(64);
1080DECLARE warn CONDITION FOR SQLSTATE "01234";
1081set aaa= repeat("A", 64);
1082set bbb= repeat("B", 64);
1083set ccc= repeat("C", 64);
1084set ddd= repeat("D", 64);
1085set eee= repeat("E", 64);
1086set fff= repeat("F", 64);
1087set ggg= repeat("G", 64);
1088set hhh= repeat("H", 64);
1089set iii= repeat("I", 64);
1090set jjj= repeat("J", 64);
1091set kkk= repeat("K", 64);
1092SIGNAL warn SET
1093CLASS_ORIGIN = aaa,
1094SUBCLASS_ORIGIN = bbb,
1095CONSTRAINT_CATALOG = ccc,
1096CONSTRAINT_SCHEMA = ddd,
1097CONSTRAINT_NAME = eee,
1098CATALOG_NAME = fff,
1099SCHEMA_NAME = ggg,
1100TABLE_NAME = hhh,
1101COLUMN_NAME = iii,
1102CURSOR_NAME = jjj,
1103MESSAGE_TEXT = kkk,
1104MYSQL_ERRNO = 65535;
1105end $$
1106call test_signal() $$
1107Warnings:
1108Warning	65535	KKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKKK
1109drop procedure test_signal $$
1110create procedure test_signal()
1111begin
1112DECLARE warn CONDITION FOR SQLSTATE "01234";
1113SIGNAL warn SET
1114MYSQL_ERRNO = 999999999999999999999999999999999999999999999999999;
1115end $$
1116call test_signal() $$
1117ERROR 42000: Variable 'MYSQL_ERRNO' can't be set to the value of '999999999999999999999999999999999999999999999999999'
1118drop procedure test_signal $$
1119create procedure test_signal()
1120begin
1121DECLARE aaax VARCHAR(65);
1122DECLARE bbbx VARCHAR(65);
1123DECLARE cccx VARCHAR(65);
1124DECLARE dddx VARCHAR(65);
1125DECLARE eeex VARCHAR(65);
1126DECLARE fffx VARCHAR(65);
1127DECLARE gggx VARCHAR(65);
1128DECLARE hhhx VARCHAR(65);
1129DECLARE iiix VARCHAR(65);
1130DECLARE jjjx VARCHAR(65);
1131DECLARE kkkx VARCHAR(65);
1132DECLARE lllx VARCHAR(129);
1133DECLARE warn CONDITION FOR SQLSTATE "01234";
1134set aaax= concat(repeat("A", 64), "X");
1135set bbbx= concat(repeat("B", 64), "X");
1136set cccx= concat(repeat("C", 64), "X");
1137set dddx= concat(repeat("D", 64), "X");
1138set eeex= concat(repeat("E", 64), "X");
1139set fffx= concat(repeat("F", 64), "X");
1140set gggx= concat(repeat("G", 64), "X");
1141set hhhx= concat(repeat("H", 64), "X");
1142set iiix= concat(repeat("I", 64), "X");
1143set jjjx= concat(repeat("J", 64), "X");
1144set kkkx= concat(repeat("K", 64), "X");
1145set lllx= concat(repeat("1", 100),
1146repeat("2", 20),
1147repeat("8", 8),
1148"X");
1149SIGNAL warn SET
1150CLASS_ORIGIN = aaax,
1151SUBCLASS_ORIGIN = bbbx,
1152CONSTRAINT_CATALOG = cccx,
1153CONSTRAINT_SCHEMA = dddx,
1154CONSTRAINT_NAME = eeex,
1155CATALOG_NAME = fffx,
1156SCHEMA_NAME = gggx,
1157TABLE_NAME = hhhx,
1158COLUMN_NAME = iiix,
1159CURSOR_NAME = jjjx,
1160MESSAGE_TEXT = lllx,
1161MYSQL_ERRNO = 10000;
1162end $$
1163call test_signal() $$
1164Warnings:
1165Warning	1647	Data truncated for condition item 'CLASS_ORIGIN'
1166Warning	1647	Data truncated for condition item 'SUBCLASS_ORIGIN'
1167Warning	1647	Data truncated for condition item 'CONSTRAINT_CATALOG'
1168Warning	1647	Data truncated for condition item 'CONSTRAINT_SCHEMA'
1169Warning	1647	Data truncated for condition item 'CONSTRAINT_NAME'
1170Warning	1647	Data truncated for condition item 'CATALOG_NAME'
1171Warning	1647	Data truncated for condition item 'SCHEMA_NAME'
1172Warning	1647	Data truncated for condition item 'TABLE_NAME'
1173Warning	1647	Data truncated for condition item 'COLUMN_NAME'
1174Warning	1647	Data truncated for condition item 'CURSOR_NAME'
1175Warning	1647	Data truncated for condition item 'MESSAGE_TEXT'
1176Warning	10000	11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111112222222222222222222288888888
1177drop procedure test_signal $$
1178create procedure test_signal()
1179begin
1180DECLARE warn CONDITION FOR SQLSTATE "01234";
1181DECLARE CONTINUE HANDLER for SQLSTATE "01234"
1182  begin
1183select "Caught by SQLSTATE";
1184end;
1185SIGNAL warn SET
1186MESSAGE_TEXT = "Raising a warning",
1187MYSQL_ERRNO = 1012;
1188end $$
1189call test_signal() $$
1190Caught by SQLSTATE
1191Caught by SQLSTATE
1192Warnings:
1193Warning	1012	Raising a warning
1194drop procedure test_signal $$
1195create procedure test_signal()
1196begin
1197DECLARE warn CONDITION FOR SQLSTATE "01234";
1198DECLARE CONTINUE HANDLER for 1012
1199begin
1200select "Caught by number";
1201end;
1202SIGNAL warn SET
1203MESSAGE_TEXT = "Raising a warning",
1204MYSQL_ERRNO = 1012;
1205end $$
1206call test_signal() $$
1207Caught by number
1208Caught by number
1209Warnings:
1210Warning	1012	Raising a warning
1211drop procedure test_signal $$
1212create procedure test_signal()
1213begin
1214DECLARE warn CONDITION FOR SQLSTATE "01234";
1215DECLARE CONTINUE HANDLER for SQLWARNING
1216begin
1217select "Caught by SQLWARNING";
1218end;
1219SIGNAL warn SET
1220MESSAGE_TEXT = "Raising a warning",
1221MYSQL_ERRNO = 1012;
1222end $$
1223call test_signal() $$
1224Caught by SQLWARNING
1225Caught by SQLWARNING
1226Warnings:
1227Warning	1012	Raising a warning
1228drop procedure test_signal $$
1229create procedure test_signal()
1230begin
1231DECLARE not_found CONDITION FOR SQLSTATE "02ABC";
1232DECLARE CONTINUE HANDLER for SQLSTATE "02ABC"
1233  begin
1234select "Caught by SQLSTATE";
1235end;
1236SIGNAL not_found SET
1237MESSAGE_TEXT = "Raising a not found",
1238MYSQL_ERRNO = 1012;
1239end $$
1240call test_signal() $$
1241Caught by SQLSTATE
1242Caught by SQLSTATE
1243Warnings:
1244Error	1012	Raising a not found
1245drop procedure test_signal $$
1246create procedure test_signal()
1247begin
1248DECLARE not_found CONDITION FOR SQLSTATE "02ABC";
1249DECLARE CONTINUE HANDLER for 1012
1250begin
1251select "Caught by number";
1252end;
1253SIGNAL not_found SET
1254MESSAGE_TEXT = "Raising a not found",
1255MYSQL_ERRNO = 1012;
1256end $$
1257call test_signal() $$
1258Caught by number
1259Caught by number
1260Warnings:
1261Error	1012	Raising a not found
1262drop procedure test_signal $$
1263create procedure test_signal()
1264begin
1265DECLARE not_found CONDITION FOR SQLSTATE "02ABC";
1266DECLARE CONTINUE HANDLER for NOT FOUND
1267begin
1268select "Caught by NOT FOUND";
1269end;
1270SIGNAL not_found SET
1271MESSAGE_TEXT = "Raising a not found",
1272MYSQL_ERRNO = 1012;
1273end $$
1274call test_signal() $$
1275Caught by NOT FOUND
1276Caught by NOT FOUND
1277Warnings:
1278Error	1012	Raising a not found
1279drop procedure test_signal $$
1280create procedure test_signal()
1281begin
1282DECLARE error CONDITION FOR SQLSTATE "55555";
1283DECLARE CONTINUE HANDLER for SQLSTATE "55555"
1284  begin
1285select "Caught by SQLSTATE";
1286end;
1287SIGNAL error SET
1288MESSAGE_TEXT = "Raising an error",
1289MYSQL_ERRNO = 1012;
1290end $$
1291call test_signal() $$
1292Caught by SQLSTATE
1293Caught by SQLSTATE
1294Warnings:
1295Error	1012	Raising an error
1296drop procedure test_signal $$
1297create procedure test_signal()
1298begin
1299DECLARE error CONDITION FOR SQLSTATE "55555";
1300DECLARE CONTINUE HANDLER for 1012
1301begin
1302select "Caught by number";
1303end;
1304SIGNAL error SET
1305MESSAGE_TEXT = "Raising an error",
1306MYSQL_ERRNO = 1012;
1307end $$
1308call test_signal() $$
1309Caught by number
1310Caught by number
1311Warnings:
1312Error	1012	Raising an error
1313drop procedure test_signal $$
1314create procedure test_signal()
1315begin
1316DECLARE error CONDITION FOR SQLSTATE "55555";
1317DECLARE CONTINUE HANDLER for SQLEXCEPTION
1318begin
1319select "Caught by SQLEXCEPTION";
1320end;
1321SIGNAL error SET
1322MESSAGE_TEXT = "Raising an error",
1323MYSQL_ERRNO = 1012;
1324end $$
1325call test_signal() $$
1326Caught by SQLEXCEPTION
1327Caught by SQLEXCEPTION
1328Warnings:
1329Error	1012	Raising an error
1330drop procedure test_signal $$
1331#
1332# Test where SIGNAL can be used
1333#
1334create function test_signal_func() returns integer
1335begin
1336DECLARE warn CONDITION FOR SQLSTATE "01XXX";
1337SIGNAL warn SET
1338MESSAGE_TEXT = "This function SIGNAL a warning",
1339MYSQL_ERRNO = 1012;
1340return 5;
1341end $$
1342select test_signal_func() $$
1343test_signal_func()
13445
1345Warnings:
1346Warning	1012	This function SIGNAL a warning
1347drop function test_signal_func $$
1348create function test_signal_func() returns integer
1349begin
1350DECLARE not_found CONDITION FOR SQLSTATE "02XXX";
1351SIGNAL not_found SET
1352MESSAGE_TEXT = "This function SIGNAL not found",
1353MYSQL_ERRNO = 1012;
1354return 5;
1355end $$
1356select test_signal_func() $$
1357ERROR 02XXX: This function SIGNAL not found
1358drop function test_signal_func $$
1359create function test_signal_func() returns integer
1360begin
1361DECLARE error CONDITION FOR SQLSTATE "50000";
1362SIGNAL error SET
1363MESSAGE_TEXT = "This function SIGNAL an error",
1364MYSQL_ERRNO = 1012;
1365return 5;
1366end $$
1367select test_signal_func() $$
1368ERROR 50000: This function SIGNAL an error
1369drop function test_signal_func $$
1370drop table if exists t1 $$
1371create table t1 (a integer) $$
1372create trigger t1_ai after insert on t1 for each row
1373begin
1374DECLARE msg VARCHAR(128);
1375DECLARE warn CONDITION FOR SQLSTATE "01XXX";
1376set msg= concat("This trigger SIGNAL a warning, a=", NEW.a);
1377SIGNAL warn SET
1378MESSAGE_TEXT = msg,
1379MYSQL_ERRNO = 1012;
1380end $$
1381insert into t1 values (1), (2) $$
1382drop trigger t1_ai $$
1383create trigger t1_ai after insert on t1 for each row
1384begin
1385DECLARE msg VARCHAR(128);
1386DECLARE not_found CONDITION FOR SQLSTATE "02XXX";
1387set msg= concat("This trigger SIGNAL a not found, a=", NEW.a);
1388SIGNAL not_found SET
1389MESSAGE_TEXT = msg,
1390MYSQL_ERRNO = 1012;
1391end $$
1392insert into t1 values (3), (4) $$
1393ERROR 02XXX: This trigger SIGNAL a not found, a=3
1394drop trigger t1_ai $$
1395create trigger t1_ai after insert on t1 for each row
1396begin
1397DECLARE msg VARCHAR(128);
1398DECLARE error CONDITION FOR SQLSTATE "03XXX";
1399set msg= concat("This trigger SIGNAL an error, a=", NEW.a);
1400SIGNAL error SET
1401MESSAGE_TEXT = msg,
1402MYSQL_ERRNO = 1012;
1403end $$
1404insert into t1 values (5), (6) $$
1405ERROR 03XXX: This trigger SIGNAL an error, a=5
1406drop table t1 $$
1407create table t1 (errno integer, msg varchar(128)) $$
1408create trigger t1_ai after insert on t1 for each row
1409begin
1410DECLARE warn CONDITION FOR SQLSTATE "01XXX";
1411SIGNAL warn SET
1412MESSAGE_TEXT = NEW.msg,
1413MYSQL_ERRNO = NEW.errno;
1414end $$
1415insert into t1 set errno=1012, msg='Warning message 1 in trigger' $$
1416insert into t1 set errno=1013, msg='Warning message 2 in trigger' $$
1417drop table t1 $$
1418drop table if exists t1 $$
1419drop procedure if exists p1 $$
1420drop function if exists f1 $$
1421create table t1 (s1 int) $$
1422insert into t1 values (1) $$
1423create procedure p1()
1424begin
1425declare a int;
1426declare c cursor for select f1() from t1;
1427declare continue handler for sqlstate '03000'
1428    select "caught 03000";
1429declare continue handler for 1326
1430select "caught cursor is not open";
1431select "Before open";
1432open c;
1433select "Before fetch";
1434fetch c into a;
1435select "Before close";
1436close c;
1437end $$
1438create function f1() returns int
1439begin
1440signal sqlstate '03000';
1441return 5;
1442end $$
1443drop table t1 $$
1444drop procedure p1 $$
1445drop function f1 $$
1446#
1447# Test the RESIGNAL runtime
1448#
1449create procedure test_resignal()
1450begin
1451DECLARE warn CONDITION FOR SQLSTATE "01234";
1452DECLARE CONTINUE HANDLER for 1012
1453begin
1454select "before RESIGNAL";
1455RESIGNAL;
1456select "after RESIGNAL";
1457end;
1458SIGNAL warn SET
1459MESSAGE_TEXT = "Raising a warning",
1460MYSQL_ERRNO = 1012;
1461end $$
1462call test_resignal() $$
1463before RESIGNAL
1464before RESIGNAL
1465after RESIGNAL
1466after RESIGNAL
1467Warnings:
1468Warning	1012	Raising a warning
1469Warning	1012	Raising a warning
1470drop procedure test_resignal $$
1471create procedure test_resignal()
1472begin
1473DECLARE not_found CONDITION FOR SQLSTATE "02222";
1474DECLARE CONTINUE HANDLER for 1012
1475begin
1476select "before RESIGNAL";
1477RESIGNAL;
1478select "after RESIGNAL";
1479end;
1480SIGNAL not_found SET
1481MESSAGE_TEXT = "Raising a not found",
1482MYSQL_ERRNO = 1012;
1483end $$
1484call test_resignal() $$
1485before RESIGNAL
1486before RESIGNAL
1487ERROR 02222: Raising a not found
1488drop procedure test_resignal $$
1489create procedure test_resignal()
1490begin
1491DECLARE error CONDITION FOR SQLSTATE "55555";
1492DECLARE CONTINUE HANDLER for 1012
1493begin
1494select "before RESIGNAL";
1495RESIGNAL;
1496select "after RESIGNAL";
1497end;
1498SIGNAL error SET
1499MESSAGE_TEXT = "Raising an error",
1500MYSQL_ERRNO = 1012;
1501end $$
1502call test_resignal() $$
1503before RESIGNAL
1504before RESIGNAL
1505ERROR 55555: Raising an error
1506drop procedure test_resignal $$
1507create procedure test_resignal()
1508begin
1509DECLARE CONTINUE HANDLER for sqlwarning
1510begin
1511select "before RESIGNAL";
1512RESIGNAL;
1513select "after RESIGNAL";
1514end;
1515insert into t_warn set a= 9999999999999999;
1516end $$
1517call test_resignal() $$
1518before RESIGNAL
1519before RESIGNAL
1520after RESIGNAL
1521after RESIGNAL
1522Warnings:
1523Warning	1264	Out of range value for column 'a' at row 1
1524Warning	1264	Out of range value for column 'a' at row 1
1525drop procedure test_resignal $$
1526create procedure test_resignal()
1527begin
1528DECLARE x integer;
1529DECLARE c cursor for select * from t_cursor;
1530DECLARE CONTINUE HANDLER for not found
1531begin
1532select "before RESIGNAL";
1533RESIGNAL;
1534select "after RESIGNAL";
1535end;
1536open c;
1537fetch c into x;
1538close c;
1539end $$
1540call test_resignal() $$
1541before RESIGNAL
1542before RESIGNAL
1543ERROR 02000: No data - zero rows fetched, selected, or processed
1544drop procedure test_resignal $$
1545create procedure test_resignal()
1546begin
1547DECLARE CONTINUE HANDLER for sqlexception
1548begin
1549select "before RESIGNAL";
1550RESIGNAL;
1551select "after RESIGNAL";
1552end;
1553drop table no_such_table;
1554end $$
1555call test_resignal() $$
1556before RESIGNAL
1557before RESIGNAL
1558ERROR 42S02: Unknown table 'no_such_table'
1559drop procedure test_resignal $$
1560create procedure test_resignal()
1561begin
1562DECLARE warn CONDITION FOR SQLSTATE "01234";
1563DECLARE CONTINUE HANDLER for 1012
1564begin
1565select "before RESIGNAL";
1566RESIGNAL SET
1567MESSAGE_TEXT = "RESIGNAL of a warning",
1568MYSQL_ERRNO = 5555 ;
1569select "after RESIGNAL";
1570end;
1571SIGNAL warn SET
1572MESSAGE_TEXT = "Raising a warning",
1573MYSQL_ERRNO = 1012;
1574end $$
1575call test_resignal() $$
1576before RESIGNAL
1577before RESIGNAL
1578after RESIGNAL
1579after RESIGNAL
1580Warnings:
1581Warning	1012	Raising a warning
1582Warning	5555	RESIGNAL of a warning
1583drop procedure test_resignal $$
1584create procedure test_resignal()
1585begin
1586DECLARE not_found CONDITION FOR SQLSTATE "02111";
1587DECLARE CONTINUE HANDLER for 1012
1588begin
1589select "before RESIGNAL";
1590RESIGNAL SET
1591MESSAGE_TEXT = "RESIGNAL of a not found",
1592MYSQL_ERRNO = 5555 ;
1593select "after RESIGNAL";
1594end;
1595SIGNAL not_found SET
1596MESSAGE_TEXT = "Raising a not found",
1597MYSQL_ERRNO = 1012;
1598end $$
1599call test_resignal() $$
1600before RESIGNAL
1601before RESIGNAL
1602ERROR 02111: RESIGNAL of a not found
1603drop procedure test_resignal $$
1604create procedure test_resignal()
1605begin
1606DECLARE error CONDITION FOR SQLSTATE "33333";
1607DECLARE CONTINUE HANDLER for 1012
1608begin
1609select "before RESIGNAL";
1610RESIGNAL SET
1611MESSAGE_TEXT = "RESIGNAL of an error",
1612MYSQL_ERRNO = 5555 ;
1613select "after RESIGNAL";
1614end;
1615SIGNAL error SET
1616MESSAGE_TEXT = "Raising an error",
1617MYSQL_ERRNO = 1012;
1618end $$
1619call test_resignal() $$
1620before RESIGNAL
1621before RESIGNAL
1622ERROR 33333: RESIGNAL of an error
1623drop procedure test_resignal $$
1624create procedure test_resignal()
1625begin
1626DECLARE CONTINUE HANDLER for sqlwarning
1627begin
1628select "before RESIGNAL";
1629RESIGNAL SET
1630MESSAGE_TEXT = "RESIGNAL of a warning",
1631MYSQL_ERRNO = 5555 ;
1632select "after RESIGNAL";
1633end;
1634insert into t_warn set a= 9999999999999999;
1635end $$
1636call test_resignal() $$
1637before RESIGNAL
1638before RESIGNAL
1639after RESIGNAL
1640after RESIGNAL
1641Warnings:
1642Warning	1264	Out of range value for column 'a' at row 1
1643Warning	5555	RESIGNAL of a warning
1644drop procedure test_resignal $$
1645create procedure test_resignal()
1646begin
1647DECLARE x integer;
1648DECLARE c cursor for select * from t_cursor;
1649DECLARE CONTINUE HANDLER for not found
1650begin
1651select "before RESIGNAL";
1652RESIGNAL SET
1653MESSAGE_TEXT = "RESIGNAL of not found",
1654MYSQL_ERRNO = 5555 ;
1655select "after RESIGNAL";
1656end;
1657open c;
1658fetch c into x;
1659close c;
1660end $$
1661call test_resignal() $$
1662before RESIGNAL
1663before RESIGNAL
1664ERROR 02000: RESIGNAL of not found
1665drop procedure test_resignal $$
1666create procedure test_resignal()
1667begin
1668DECLARE CONTINUE HANDLER for sqlexception
1669begin
1670select "before RESIGNAL";
1671RESIGNAL SET
1672MESSAGE_TEXT = "RESIGNAL of an error",
1673MYSQL_ERRNO = 5555 ;
1674select "after RESIGNAL";
1675end;
1676drop table no_such_table;
1677end $$
1678call test_resignal() $$
1679before RESIGNAL
1680before RESIGNAL
1681ERROR 42S02: RESIGNAL of an error
1682drop procedure test_resignal $$
1683create procedure test_resignal()
1684begin
1685DECLARE warn CONDITION FOR SQLSTATE "01111";
1686DECLARE CONTINUE HANDLER for 1012
1687begin
1688select "before RESIGNAL";
1689RESIGNAL SQLSTATE "01222" SET
1690MESSAGE_TEXT = "RESIGNAL to warning",
1691MYSQL_ERRNO = 5555 ;
1692select "after RESIGNAL";
1693end;
1694SIGNAL warn SET
1695MESSAGE_TEXT = "Raising a warning",
1696MYSQL_ERRNO = 1012;
1697end $$
1698call test_resignal() $$
1699before RESIGNAL
1700before RESIGNAL
1701after RESIGNAL
1702after RESIGNAL
1703Warnings:
1704Warning	1012	Raising a warning
1705Warning	5555	RESIGNAL to warning
1706drop procedure test_resignal $$
1707create procedure test_resignal()
1708begin
1709DECLARE warn CONDITION FOR SQLSTATE "01111";
1710DECLARE CONTINUE HANDLER for 1012
1711begin
1712select "before RESIGNAL";
1713RESIGNAL SQLSTATE "02222" SET
1714MESSAGE_TEXT = "RESIGNAL to not found",
1715MYSQL_ERRNO = 5555 ;
1716select "after RESIGNAL";
1717end;
1718SIGNAL warn SET
1719MESSAGE_TEXT = "Raising a warning",
1720MYSQL_ERRNO = 1012;
1721end $$
1722call test_resignal() $$
1723before RESIGNAL
1724before RESIGNAL
1725ERROR 02222: RESIGNAL to not found
1726show warnings $$
1727Level	Code	Message
1728Warning	1012	Raising a warning
1729Error	5555	RESIGNAL to not found
1730drop procedure test_resignal $$
1731create procedure test_resignal()
1732begin
1733DECLARE warn CONDITION FOR SQLSTATE "01111";
1734DECLARE CONTINUE HANDLER for 1012
1735begin
1736select "before RESIGNAL";
1737RESIGNAL SQLSTATE "33333" SET
1738MESSAGE_TEXT = "RESIGNAL to error",
1739MYSQL_ERRNO = 5555 ;
1740select "after RESIGNAL";
1741end;
1742SIGNAL warn SET
1743MESSAGE_TEXT = "Raising a warning",
1744MYSQL_ERRNO = 1012;
1745end $$
1746call test_resignal() $$
1747before RESIGNAL
1748before RESIGNAL
1749ERROR 33333: RESIGNAL to error
1750show warnings $$
1751Level	Code	Message
1752Warning	1012	Raising a warning
1753Error	5555	RESIGNAL to error
1754drop procedure test_resignal $$
1755create procedure test_resignal()
1756begin
1757DECLARE not_found CONDITION FOR SQLSTATE "02ABC";
1758DECLARE CONTINUE HANDLER for 1012
1759begin
1760select "before RESIGNAL";
1761RESIGNAL SQLSTATE "01222" SET
1762MESSAGE_TEXT = "RESIGNAL to warning",
1763MYSQL_ERRNO = 5555 ;
1764select "after RESIGNAL";
1765end;
1766SIGNAL not_found SET
1767MESSAGE_TEXT = "Raising a not found",
1768MYSQL_ERRNO = 1012;
1769end $$
1770call test_resignal() $$
1771before RESIGNAL
1772before RESIGNAL
1773after RESIGNAL
1774after RESIGNAL
1775Warnings:
1776Error	1012	Raising a not found
1777Warning	5555	RESIGNAL to warning
1778drop procedure test_resignal $$
1779create procedure test_resignal()
1780begin
1781DECLARE not_found CONDITION FOR SQLSTATE "02ABC";
1782DECLARE CONTINUE HANDLER for 1012
1783begin
1784select "before RESIGNAL";
1785RESIGNAL SQLSTATE "02222" SET
1786MESSAGE_TEXT = "RESIGNAL to not found",
1787MYSQL_ERRNO = 5555 ;
1788select "after RESIGNAL";
1789end;
1790SIGNAL not_found SET
1791MESSAGE_TEXT = "Raising a not found",
1792MYSQL_ERRNO = 1012;
1793end $$
1794call test_resignal() $$
1795before RESIGNAL
1796before RESIGNAL
1797ERROR 02222: RESIGNAL to not found
1798show warnings $$
1799Level	Code	Message
1800Error	1012	Raising a…

Large files files are truncated, but you can click here to view the full file