1drop table if exists t1,t2,v1,v2; 2drop view if exists t1,t2,v1,v2; 3CREATE TABLE `t1` ( 4a int not null auto_increment, 5`pseudo` varchar(35) character set latin2 NOT NULL default '', 6`email` varchar(60) character set latin2 NOT NULL default '', 7PRIMARY KEY (a), 8UNIQUE KEY `email` USING BTREE (`email`) 9) ENGINE=HEAP CHARSET=latin1 ROW_FORMAT DYNAMIC; 10set @@sql_mode=""; 11show variables like 'sql_mode'; 12Variable_name Value 13sql_mode 14show create table t1; 15Table Create Table 16t1 CREATE TABLE `t1` ( 17 `a` int(11) NOT NULL AUTO_INCREMENT, 18 `pseudo` varchar(35) CHARACTER SET latin2 NOT NULL DEFAULT '', 19 `email` varchar(60) CHARACTER SET latin2 NOT NULL DEFAULT '', 20 PRIMARY KEY (`a`), 21 UNIQUE KEY `email` (`email`) USING BTREE 22) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC 23set @@sql_mode="ansi_quotes"; 24show variables like 'sql_mode'; 25Variable_name Value 26sql_mode ANSI_QUOTES 27show create table t1; 28Table Create Table 29t1 CREATE TABLE "t1" ( 30 "a" int(11) NOT NULL AUTO_INCREMENT, 31 "pseudo" varchar(35) CHARACTER SET latin2 NOT NULL DEFAULT '', 32 "email" varchar(60) CHARACTER SET latin2 NOT NULL DEFAULT '', 33 PRIMARY KEY ("a"), 34 UNIQUE KEY "email" ("email") USING BTREE 35) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC 36set @@sql_mode="no_table_options"; 37show variables like 'sql_mode'; 38Variable_name Value 39sql_mode NO_TABLE_OPTIONS 40show create table t1; 41Table Create Table 42t1 CREATE TABLE `t1` ( 43 `a` int(11) NOT NULL AUTO_INCREMENT, 44 `pseudo` varchar(35) CHARACTER SET latin2 NOT NULL DEFAULT '', 45 `email` varchar(60) CHARACTER SET latin2 NOT NULL DEFAULT '', 46 PRIMARY KEY (`a`), 47 UNIQUE KEY `email` (`email`) USING BTREE 48) 49set @@sql_mode="no_key_options"; 50show variables like 'sql_mode'; 51Variable_name Value 52sql_mode NO_KEY_OPTIONS 53show create table t1; 54Table Create Table 55t1 CREATE TABLE `t1` ( 56 `a` int(11) NOT NULL AUTO_INCREMENT, 57 `pseudo` varchar(35) CHARACTER SET latin2 NOT NULL DEFAULT '', 58 `email` varchar(60) CHARACTER SET latin2 NOT NULL DEFAULT '', 59 PRIMARY KEY (`a`), 60 UNIQUE KEY `email` (`email`) 61) ENGINE=MEMORY DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC 62set @@sql_mode="no_field_options,mysql323,mysql40"; 63show variables like 'sql_mode'; 64Variable_name Value 65sql_mode NO_FIELD_OPTIONS,MYSQL323,MYSQL40,HIGH_NOT_PRECEDENCE 66show create table t1; 67Table Create Table 68t1 CREATE TABLE `t1` ( 69 `a` int(11) NOT NULL, 70 `pseudo` varchar(35) NOT NULL DEFAULT '', 71 `email` varchar(60) NOT NULL DEFAULT '', 72 PRIMARY KEY (`a`), 73 UNIQUE KEY `email` (`email`) 74) TYPE=MEMORY ROW_FORMAT=DYNAMIC 75set sql_mode="postgresql,oracle,mssql,db2,maxdb"; 76select @@sql_mode; 77@@sql_mode 78PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,POSTGRESQL,ORACLE,MSSQL,DB2,MAXDB,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT 79show create table t1; 80Table Create Table 81t1 CREATE TABLE "t1" ( 82 "a" int(11) NOT NULL, 83 "pseudo" varchar(35) CHARACTER SET latin2 NOT NULL DEFAULT '', 84 "email" varchar(60) CHARACTER SET latin2 NOT NULL DEFAULT '', 85 PRIMARY KEY ("a"), 86 UNIQUE KEY "email" ("email") 87) 88drop table t1; 89CREATE TABLE t1 ( 90a char(10), 91b char(10) collate latin1_bin, 92c binary(10) 93) character set latin1; 94set @@sql_mode=""; 95show create table t1; 96Table Create Table 97t1 CREATE TABLE `t1` ( 98 `a` char(10) DEFAULT NULL, 99 `b` char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, 100 `c` binary(10) DEFAULT NULL 101) ENGINE=MyISAM DEFAULT CHARSET=latin1 102set @@sql_mode="mysql323"; 103show create table t1; 104Table Create Table 105t1 CREATE TABLE `t1` ( 106 `a` char(10) DEFAULT NULL, 107 `b` char(10) binary DEFAULT NULL, 108 `c` binary(10) DEFAULT NULL 109) TYPE=MyISAM 110set @@sql_mode="mysql40"; 111show create table t1; 112Table Create Table 113t1 CREATE TABLE `t1` ( 114 `a` char(10) DEFAULT NULL, 115 `b` char(10) binary DEFAULT NULL, 116 `c` binary(10) DEFAULT NULL 117) TYPE=MyISAM 118drop table t1; 119set session sql_mode = ''; 120create table t1 ( min_num dec(6,6) default .000001); 121show create table t1; 122Table Create Table 123t1 CREATE TABLE `t1` ( 124 `min_num` decimal(6,6) DEFAULT 0.000001 125) ENGINE=MyISAM DEFAULT CHARSET=latin1 126drop table t1 ; 127set session sql_mode = 'IGNORE_SPACE'; 128create table t1 ( min_num dec(6,6) default 0.000001); 129show create table t1; 130Table Create Table 131t1 CREATE TABLE `t1` ( 132 `min_num` decimal(6,6) DEFAULT 0.000001 133) ENGINE=MyISAM DEFAULT CHARSET=latin1 134drop table t1 ; 135create table t1 ( min_num dec(6,6) default .000001); 136show create table t1; 137Table Create Table 138t1 CREATE TABLE `t1` ( 139 `min_num` decimal(6,6) DEFAULT 0.000001 140) ENGINE=MyISAM DEFAULT CHARSET=latin1 141drop table t1 ; 142set @@SQL_MODE=NULL; 143ERROR 42000: Variable 'sql_mode' can't be set to the value of 'NULL' 144set session sql_mode=ansi; 145create table t1 146(f1 integer auto_increment primary key, 147f2 timestamp not null default current_timestamp on update current_timestamp); 148show create table t1; 149Table Create Table 150t1 CREATE TABLE "t1" ( 151 "f1" int(11) NOT NULL AUTO_INCREMENT, 152 "f2" timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), 153 PRIMARY KEY ("f1") 154) 155set session sql_mode=no_field_options; 156show create table t1; 157Table Create Table 158t1 CREATE TABLE `t1` ( 159 `f1` int(11) NOT NULL, 160 `f2` timestamp NOT NULL DEFAULT current_timestamp(), 161 PRIMARY KEY (`f1`) 162) ENGINE=MyISAM DEFAULT CHARSET=latin1 163drop table t1; 164SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=''; 165show local variables like 'SQL_MODE'; 166Variable_name Value 167sql_mode 168CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); 169INSERT t1 (a) VALUES 170('\\'), 171('\n'), 172('\b'), 173('\r'), 174('\t'), 175('\x'), 176('\a'), 177('\aa'), 178('\\a'), 179('\\aa'), 180('_'), 181('\_'), 182('\\_'), 183('\\\_'), 184('\\\\_'), 185('%'), 186('\%'), 187('\\%'), 188('\\\%'), 189('\\\\%') 190; 191SELECT p, hex(a) FROM t1; 192p hex(a) 1931 5C 1942 0A 1953 08 1964 0D 1975 09 1986 78 1997 61 2008 6161 2019 5C61 20210 5C6161 20311 5F 20412 5C5F 20513 5C5F 20614 5C5C5F 20715 5C5C5F 20816 25 20917 5C25 21018 5C25 21119 5C5C25 21220 5C5C25 213delete from t1 where a in ('\n','\r','\t', '\b'); 214select 215masks.p, 216masks.a as mask, 217examples.a as example 218from 219t1 as masks 220left join t1 as examples on examples.a LIKE masks.a 221order by masks.p, example; 222p mask example 2231 \ \ 2246 x x 2257 a a 2268 aa aa 2279 \a a 22810 \aa aa 22911 _ % 23011 _ a 23111 _ x 23211 _ \ 23311 _ _ 23412 \_ _ 23513 \_ _ 23614 \\_ \% 23714 \\_ \% 23814 \\_ \a 23914 \\_ \_ 24014 \\_ \_ 24115 \\_ \% 24215 \\_ \% 24315 \\_ \a 24415 \\_ \_ 24515 \\_ \_ 24616 % % 24716 % a 24816 % aa 24916 % x 25016 % \ 25116 % \% 25216 % \% 25316 % \a 25416 % \aa 25516 % \\% 25616 % \\% 25716 % \\_ 25816 % \\_ 25916 % \_ 26016 % \_ 26116 % _ 26217 \% % 26318 \% % 26419 \\% \ 26519 \\% \% 26619 \\% \% 26719 \\% \a 26819 \\% \aa 26919 \\% \\% 27019 \\% \\% 27119 \\% \\_ 27219 \\% \\_ 27319 \\% \_ 27419 \\% \_ 27520 \\% \ 27620 \\% \% 27720 \\% \% 27820 \\% \a 27920 \\% \aa 28020 \\% \\% 28120 \\% \\% 28220 \\% \\_ 28320 \\% \\_ 28420 \\% \_ 28520 \\% \_ 286DROP TABLE t1; 287SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 288show local variables like 'SQL_MODE'; 289Variable_name Value 290sql_mode NO_BACKSLASH_ESCAPES 291CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); 292INSERT t1 (a) VALUES 293('\\'), 294('\n'), 295('\b'), 296('\r'), 297('\t'), 298('\x'), 299('\a'), 300('\aa'), 301('\\a'), 302('\\aa'), 303('_'), 304('\_'), 305('\\_'), 306('\\\_'), 307('\\\\_'), 308('%'), 309('\%'), 310('\\%'), 311('\\\%'), 312('\\\\%') 313; 314SELECT p, hex(a) FROM t1; 315p hex(a) 3161 5C5C 3172 5C6E 3183 5C62 3194 5C72 3205 5C74 3216 5C78 3227 5C61 3238 5C6161 3249 5C5C61 32510 5C5C6161 32611 5F 32712 5C5F 32813 5C5C5F 32914 5C5C5C5F 33015 5C5C5C5C5F 33116 25 33217 5C25 33318 5C5C25 33419 5C5C5C25 33520 5C5C5C5C25 336delete from t1 where a in ('\n','\r','\t', '\b'); 337select 338masks.p, 339masks.a as mask, 340examples.a as example 341from 342t1 as masks 343left join t1 as examples on examples.a LIKE masks.a 344order by masks.p, example; 345p mask example 3461 \\ \\ 3476 \x \x 3487 \a \a 3498 \aa \aa 3509 \\a \\a 35110 \\aa \\aa 35211 _ % 35311 _ _ 35412 \_ \% 35512 \_ \a 35612 \_ \x 35712 \_ \\ 35812 \_ \_ 35913 \\_ \\% 36013 \\_ \\a 36113 \\_ \\_ 36214 \\\_ \\\% 36314 \\\_ \\\_ 36415 \\\\_ \\\\% 36515 \\\\_ \\\\_ 36616 % % 36716 % \% 36816 % \a 36916 % \aa 37016 % \x 37116 % \\ 37216 % \\% 37316 % \\a 37416 % \\aa 37516 % \\\% 37616 % \\\\% 37716 % \\\\_ 37816 % \\\_ 37916 % \\_ 38016 % \_ 38116 % _ 38217 \% \% 38317 \% \a 38417 \% \aa 38517 \% \x 38617 \% \\ 38717 \% \\% 38817 \% \\a 38917 \% \\aa 39017 \% \\\% 39117 \% \\\\% 39217 \% \\\\_ 39317 \% \\\_ 39417 \% \\_ 39517 \% \_ 39618 \\% \\ 39718 \\% \\% 39818 \\% \\a 39918 \\% \\aa 40018 \\% \\\% 40118 \\% \\\\% 40218 \\% \\\\_ 40318 \\% \\\_ 40418 \\% \\_ 40519 \\\% \\\% 40619 \\\% \\\\% 40719 \\\% \\\\_ 40819 \\\% \\\_ 40920 \\\\% \\\\% 41020 \\\\% \\\\_ 411DROP TABLE t1; 412SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 413SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; 414a\\b a\\\"b a'\\b a'\\\"b 415a\\b a\\\"b a'\\b a'\\\"b 416SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; 417a\\b a\\\'b a"\\b a"\\\'b 418a\\b a\\\'b a"\\b a"\\\'b 419SET @@SQL_MODE=''; 420SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; 421a\b a\"b a'\b a'\"b 422a\b a\"b a'\b a'\"b 423SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; 424a\b a\'b a"\b a"\'b 425a\b a\'b a"\b a"\'b 426SET @@SQL_MODE=''; 427create function `foo` () returns int return 5; 428show create function `foo`; 429Function sql_mode Create Function character_set_client collation_connection Database Collation 430foo CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11) 431return 5 latin1 latin1_swedish_ci latin1_swedish_ci 432SET @@SQL_MODE='ANSI_QUOTES'; 433show create function `foo`; 434Function sql_mode Create Function character_set_client collation_connection Database Collation 435foo CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11) 436return 5 latin1 latin1_swedish_ci latin1_swedish_ci 437drop function `foo`; 438create function `foo` () returns int return 5; 439show create function `foo`; 440Function sql_mode Create Function character_set_client collation_connection Database Collation 441foo ANSI_QUOTES CREATE DEFINER="root"@"localhost" FUNCTION "foo"() RETURNS int(11) 442return 5 latin1 latin1_swedish_ci latin1_swedish_ci 443SET @@SQL_MODE=''; 444show create function `foo`; 445Function sql_mode Create Function character_set_client collation_connection Database Collation 446foo ANSI_QUOTES CREATE DEFINER="root"@"localhost" FUNCTION "foo"() RETURNS int(11) 447return 5 latin1 latin1_swedish_ci latin1_swedish_ci 448drop function `foo`; 449SET @@SQL_MODE=''; 450create table t1 (a int); 451create table t2 (a int); 452create view v1 as select a from t1; 453show create view v1; 454View Create View character_set_client collation_connection 455v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci 456SET @@SQL_MODE='ANSI_QUOTES'; 457show create view v1; 458View Create View character_set_client collation_connection 459v1 CREATE ALGORITHM=UNDEFINED DEFINER="root"@"localhost" SQL SECURITY DEFINER VIEW "v1" AS select "t1"."a" AS "a" from "t1" latin1 latin1_swedish_ci 460create view v2 as select a from t2 where a in (select a from v1); 461drop view v2, v1; 462drop table t1, t2; 463select @@sql_mode; 464@@sql_mode 465ANSI_QUOTES 466set sql_mode=2097152; 467select @@sql_mode; 468@@sql_mode 469STRICT_TRANS_TABLES 470set sql_mode=4194304; 471select @@sql_mode; 472@@sql_mode 473STRICT_ALL_TABLES 474set sql_mode=16384+(65536*4); 475select @@sql_mode; 476@@sql_mode 477REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_TABLE_OPTIONS,ANSI 478set sql_mode=2147483648*2*2*2; 479select @@sql_mode; 480@@sql_mode 481TIME_ROUND_FRACTIONAL 482set sql_mode=2147483648*2*2*2*2; 483ERROR 42000: Variable 'sql_mode' can't be set to the value of '34359738368' 484select @@sql_mode; 485@@sql_mode 486TIME_ROUND_FRACTIONAL 487set sql_mode=PAD_CHAR_TO_FULL_LENGTH; 488create table t1 (a int auto_increment primary key, b char(5)); 489insert into t1 (b) values('a'),('b\t'),('c '); 490select concat('x',b,'x') from t1; 491concat('x',b,'x') 492xa x 493xb x 494xc x 495set sql_mode=0; 496select concat('x',b,'x') from t1; 497concat('x',b,'x') 498xax 499xb x 500xcx 501drop table t1; 502SET @@SQL_MODE=@OLD_SQL_MODE; 503create user mysqltest_32753@localhost; 504connection default; 505set @OLD_SQL_MODE=@@SESSION.SQL_MODE; 506set session sql_mode='PAD_CHAR_TO_FULL_LENGTH'; 507flush privileges; 508connect user_32753,localhost,mysqltest_32753,,test,$MASTER_MYPORT,$MASTER_MYSOCK; 509select current_user(); 510current_user() 511mysqltest_32753@localhost 512connection default; 513set session sql_mode=@OLD_SQL_MODE; 514flush privileges; 515disconnect user_32753; 516connection default; 517drop user mysqltest_32753@localhost; 518SET @org_mode=@@sql_mode; 519SET @@sql_mode='traditional'; 520SELECT @@sql_mode LIKE '%NO_ENGINE_SUBSTITUTION%'; 521@@sql_mode LIKE '%NO_ENGINE_SUBSTITUTION%' 5221 523SET sql_mode=@org_mode; 524DROP TABLE IF EXISTS t1,t2; 525CREATE USER 'user_PCTFL'@'localhost' identified by 'PWD'; 526CREATE USER 'user_no_PCTFL'@'localhost' identified by 'PWD'; 527CREATE TABLE t1 (f1 BIGINT); 528CREATE TABLE t2 (f1 CHAR(3) NOT NULL, f2 CHAR(20)); 529GRANT ALL ON t1 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost'; 530GRANT SELECT(f1) ON t2 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost'; 531SET @OLD_SQL_MODE = @@SESSION.SQL_MODE; 532SET SESSION SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH'; 533DROP USER 'user_PCTFL'@'localhost'; 534SET SESSION SQL_MODE = @OLD_SQL_MODE; 535DROP USER 'user_no_PCTFL'@'localhost'; 536FLUSH PRIVILEGES; 537SELECT * FROM mysql.db WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; 538Host Db User Select_priv Insert_priv Update_priv Delete_priv Create_priv Drop_priv Grant_priv References_priv Index_priv Alter_priv Create_tmp_table_priv Lock_tables_priv Create_view_priv Show_view_priv Create_routine_priv Alter_routine_priv Execute_priv Event_priv Trigger_priv Delete_history_priv 539SELECT * FROM mysql.tables_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; 540Host Db User Table_name Grantor Timestamp Table_priv Column_priv 541SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; 542Host Db User Table_name Column_name Timestamp Column_priv 543DROP TABLE t1; 544DROP TABLE t2; 545 546# 547# Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES 548# IGNORED AND BREAKS REPLICATION 549# 550DROP TABLE IF EXISTS test_table; 551DROP FUNCTION IF EXISTS test_function; 552CREATE TABLE test_table (c1 CHAR(50)); 553SET @org_mode=@@sql_mode; 554SET @@sql_mode=''; 555PREPARE insert_stmt FROM 'INSERT INTO test_table VALUES (?)'; 556PREPARE update_stmt FROM 'UPDATE test_table SET c1= ? WHERE c1= ?'; 557CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) 558BEGIN 559DECLARE char_val CHAR(50); 560SELECT c1 INTO char_val FROM test_table WHERE c1=var; 561RETURN char_val; 562END 563$ 564SET @var1='abcd\'ef'; 565SET @var2='abcd\"ef'; 566SET @var3='abcd\bef'; 567SET @var4='abcd\nef'; 568SET @var5='abcd\ref'; 569SET @var6='abcd\tef'; 570SET @var7='abcd\\ef'; 571SET @var8='abcd\%ef'; 572SET @var9='abcd\_ef'; 573SET @to_var1='wxyz\'ef'; 574SET @to_var2='wxyz\"ef'; 575SET @to_var3='wxyz\bef'; 576SET @to_var4='wxyz\nef'; 577SET @to_var5='wxyz\ref'; 578SET @to_var6='wxyz\tef'; 579SET @to_var7='wxyz\\ef'; 580SET @to_var8='wxyz\%ef'; 581SET @to_var9='wxyz\_ef'; 582# STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT 583EXECUTE insert_stmt USING @var1; 584EXECUTE insert_stmt USING @var2; 585EXECUTE insert_stmt USING @var3; 586EXECUTE insert_stmt USING @var4; 587EXECUTE insert_stmt USING @var5; 588EXECUTE insert_stmt USING @var6; 589EXECUTE insert_stmt USING @var7; 590EXECUTE insert_stmt USING @var8; 591EXECUTE insert_stmt USING @var9; 592SELECT * FROM test_table; 593c1 594abcd'ef 595abcd"ef 596abcdef 597abcd 598ef 599abcd 600ef 601abcd ef 602abcd\ef 603abcd\%ef 604abcd\_ef 605EXECUTE update_stmt USING @to_var1, @var1; 606EXECUTE update_stmt USING @to_var2, @var2; 607EXECUTE update_stmt USING @to_var3, @var3; 608EXECUTE update_stmt USING @to_var4, @var4; 609EXECUTE update_stmt USING @to_var5, @var5; 610EXECUTE update_stmt USING @to_var6, @var6; 611EXECUTE update_stmt USING @to_var7, @var7; 612EXECUTE update_stmt USING @to_var8, @var8; 613EXECUTE update_stmt USING @to_var9, @var9; 614SELECT * FROM test_table; 615c1 616wxyz'ef 617wxyz"ef 618wxyzef 619wxyz 620ef 621wxyz 622ef 623wxyz ef 624wxyz\ef 625wxyz\%ef 626wxyz\_ef 627 628# END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT 629# STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING 630select test_function(@to_var1); 631test_function(@to_var1) 632wxyz'ef 633SELECT test_function(@to_var2); 634test_function(@to_var2) 635wxyz"ef 636SELECT test_function(@to_var3); 637test_function(@to_var3) 638wxyzef 639SELECT test_function(@to_var4); 640test_function(@to_var4) 641wxyz 642ef 643SELECT test_function(@to_var5); 644test_function(@to_var5) 645wxyz 646ef 647SELECT test_function(@to_var6); 648test_function(@to_var6) 649wxyz ef 650SELECT test_function(@to_var7); 651test_function(@to_var7) 652wxyz\ef 653SELECT test_function(@to_var8); 654test_function(@to_var8) 655wxyz\%ef 656SELECT test_function(@to_var9); 657test_function(@to_var9) 658wxyz\_ef 659 660# END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING 661DELETE FROM test_table; 662DROP FUNCTION test_function; 663SET @@sql_mode='NO_BACKSLASH_ESCAPES'; 664CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) 665BEGIN 666DECLARE char_val CHAR(50); 667SELECT c1 INTO char_val FROM test_table WHERE c1=var; 668RETURN char_val; 669END 670$ 671# STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT 672EXECUTE insert_stmt USING @var1; 673EXECUTE insert_stmt USING @var2; 674EXECUTE insert_stmt USING @var3; 675EXECUTE insert_stmt USING @var4; 676EXECUTE insert_stmt USING @var5; 677EXECUTE insert_stmt USING @var6; 678EXECUTE insert_stmt USING @var7; 679EXECUTE insert_stmt USING @var8; 680EXECUTE insert_stmt USING @var9; 681SELECT * FROM test_table; 682c1 683abcd'ef 684abcd"ef 685abcdef 686abcd 687ef 688abcd 689ef 690abcd ef 691abcd\ef 692abcd\%ef 693abcd\_ef 694EXECUTE update_stmt USING @to_var1, @var1; 695EXECUTE update_stmt USING @to_var2, @var2; 696EXECUTE update_stmt USING @to_var3, @var3; 697EXECUTE update_stmt USING @to_var4, @var4; 698EXECUTE update_stmt USING @to_var5, @var5; 699EXECUTE update_stmt USING @to_var6, @var6; 700EXECUTE update_stmt USING @to_var7, @var7; 701EXECUTE update_stmt USING @to_var8, @var8; 702EXECUTE update_stmt USING @to_var9, @var9; 703SELECT * FROM test_table; 704c1 705wxyz'ef 706wxyz"ef 707wxyzef 708wxyz 709ef 710wxyz 711ef 712wxyz ef 713wxyz\ef 714wxyz\%ef 715wxyz\_ef 716 717# END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT 718# STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING 719select test_function(@to_var1); 720test_function(@to_var1) 721wxyz'ef 722SELECT test_function(@to_var2); 723test_function(@to_var2) 724wxyz"ef 725SELECT test_function(@to_var3); 726test_function(@to_var3) 727wxyzef 728SELECT test_function(@to_var4); 729test_function(@to_var4) 730wxyz 731ef 732SELECT test_function(@to_var5); 733test_function(@to_var5) 734wxyz 735ef 736SELECT test_function(@to_var6); 737test_function(@to_var6) 738wxyz ef 739SELECT test_function(@to_var7); 740test_function(@to_var7) 741wxyz\ef 742SELECT test_function(@to_var8); 743test_function(@to_var8) 744wxyz\%ef 745SELECT test_function(@to_var9); 746test_function(@to_var9) 747wxyz\_ef 748 749# END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING 750DROP TABLE test_table; 751DROP FUNCTION test_function; 752SET @@sql_mode= @org_mode; 753 754#End of Test for Bug#12601974 755# 756# MDEV-11848 Automatic statement repreparation changes query semantics 757# 758SET sql_mode=DEFAULT; 759CREATE OR REPLACE TABLE t1 (a TEXT); 760PREPARE stmt FROM 'INSERT INTO t1 (a) VALUES (2||3)'; 761EXECUTE stmt; 762SET sql_mode=ORACLE; 763EXECUTE stmt; 764ALTER TABLE t1 ADD b INT; 765EXECUTE stmt; 766SELECT * FROM t1; 767a b 7681 NULL 7691 NULL 7701 NULL 771DROP TABLE t1; 772# 773# MDEV-12390 Wrong error line numbers reported with sql_mode=IGNORE_SPACE 774# 775SET sql_mode=IGNORE_SPACE; 776CREATE PROCEDURE p1() 777BEGIN 778SELECT 1+1; 779syntax error; 780END; 781$$ 782ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'error; 783END' at line 4 784SET sql_mode=DEFAULT; 785CREATE PROCEDURE p1() 786BEGIN 787SELECT 1+1; 788syntax error; 789END; 790$$ 791ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'error; 792END' at line 4 793# 794# End of 10.2 tests 795# 796# 797# Start of 10.3 tests 798# 799# 800# MDEV-16471 mysqldump throws "Variable 'sql_mode' can't be set to the value of 'NULL' (1231)" 801# 802SET sql_mode='ORACLE,EMPTY_STRING_IS_NULL'; 803SELECT @@sql_mode; 804@@sql_mode 805PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,EMPTY_STRING_IS_NULL,SIMULTANEOUS_ASSIGNMENT 806SELECT '' AS empty; 807empty 808NULL 809SET sql_mode=''; 810SELECT @@sql_mode; 811@@sql_mode 812 813SET sql_mode=DEFAULT; 814# 815# End of 10.3 tests 816# 817