diff options
Diffstat (limited to 'mysql-test/main/sql_mode.result')
| -rw-r--r-- | mysql-test/main/sql_mode.result | 810 |
1 files changed, 810 insertions, 0 deletions
diff --git a/mysql-test/main/sql_mode.result b/mysql-test/main/sql_mode.result new file mode 100644 index 00000000..31308cd6 --- /dev/null +++ b/mysql-test/main/sql_mode.result @@ -0,0 +1,810 @@ +drop table if exists t1,t2,v1,v2; +drop view if exists t1,t2,v1,v2; +CREATE TABLE `t1` ( +a int not null auto_increment, +`pseudo` varchar(35) character set latin2 NOT NULL default '', +`email` varchar(60) character set latin2 NOT NULL default '', +PRIMARY KEY (a), +UNIQUE KEY `email` USING BTREE (`email`) +) ENGINE=HEAP CHARSET=latin1 ROW_FORMAT DYNAMIC; +set @@sql_mode=""; +show variables like 'sql_mode'; +Variable_name Value +sql_mode +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + `pseudo` varchar(35) CHARACTER SET latin2 COLLATE latin2_general_ci NOT NULL DEFAULT '', + `email` varchar(60) CHARACTER SET latin2 COLLATE latin2_general_ci NOT NULL DEFAULT '', + PRIMARY KEY (`a`), + UNIQUE KEY `email` (`email`) USING BTREE +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC +set @@sql_mode="ansi_quotes"; +show variables like 'sql_mode'; +Variable_name Value +sql_mode ANSI_QUOTES +show create table t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" int(11) NOT NULL AUTO_INCREMENT, + "pseudo" varchar(35) CHARACTER SET latin2 COLLATE latin2_general_ci NOT NULL DEFAULT '', + "email" varchar(60) CHARACTER SET latin2 COLLATE latin2_general_ci NOT NULL DEFAULT '', + PRIMARY KEY ("a"), + UNIQUE KEY "email" ("email") USING BTREE +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC +set @@sql_mode="no_table_options"; +show variables like 'sql_mode'; +Variable_name Value +sql_mode NO_TABLE_OPTIONS +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + `pseudo` varchar(35) CHARACTER SET latin2 COLLATE latin2_general_ci NOT NULL DEFAULT '', + `email` varchar(60) CHARACTER SET latin2 COLLATE latin2_general_ci NOT NULL DEFAULT '', + PRIMARY KEY (`a`), + UNIQUE KEY `email` (`email`) USING BTREE +) +set @@sql_mode="no_key_options"; +show variables like 'sql_mode'; +Variable_name Value +sql_mode NO_KEY_OPTIONS +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL AUTO_INCREMENT, + `pseudo` varchar(35) CHARACTER SET latin2 COLLATE latin2_general_ci NOT NULL DEFAULT '', + `email` varchar(60) CHARACTER SET latin2 COLLATE latin2_general_ci NOT NULL DEFAULT '', + PRIMARY KEY (`a`), + UNIQUE KEY `email` (`email`) +) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ROW_FORMAT=DYNAMIC +set @@sql_mode="no_field_options,mysql323,mysql40"; +show variables like 'sql_mode'; +Variable_name Value +sql_mode NO_FIELD_OPTIONS,MYSQL323,MYSQL40,HIGH_NOT_PRECEDENCE +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int(11) NOT NULL, + `pseudo` varchar(35) NOT NULL DEFAULT '', + `email` varchar(60) NOT NULL DEFAULT '', + PRIMARY KEY (`a`), + UNIQUE KEY `email` (`email`) +) TYPE=MEMORY ROW_FORMAT=DYNAMIC +set sql_mode="postgresql,oracle,mssql,db2,maxdb"; +select @@sql_mode; +@@sql_mode +PIPES_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 +show create table t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "a" int(11) NOT NULL, + "pseudo" varchar(35) CHARACTER SET latin2 COLLATE latin2_general_ci NOT NULL DEFAULT '', + "email" varchar(60) CHARACTER SET latin2 COLLATE latin2_general_ci NOT NULL DEFAULT '', + PRIMARY KEY ("a"), + UNIQUE KEY "email" ("email") +) +drop table t1; +CREATE TABLE t1 ( +a char(10), +b char(10) collate latin1_bin, +c binary(10) +) character set latin1; +set @@sql_mode=""; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(10) DEFAULT NULL, + `b` char(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, + `c` binary(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +set @@sql_mode="mysql323"; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(10) DEFAULT NULL, + `b` char(10) binary DEFAULT NULL, + `c` binary(10) DEFAULT NULL +) TYPE=MyISAM +set @@sql_mode="mysql40"; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` char(10) DEFAULT NULL, + `b` char(10) binary DEFAULT NULL, + `c` binary(10) DEFAULT NULL +) TYPE=MyISAM +drop table t1; +set session sql_mode = ''; +create table t1 ( min_num dec(6,6) default .000001); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `min_num` decimal(6,6) DEFAULT 0.000001 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table t1 ; +set session sql_mode = 'IGNORE_SPACE'; +create table t1 ( min_num dec(6,6) default 0.000001); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `min_num` decimal(6,6) DEFAULT 0.000001 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table t1 ; +create table t1 ( min_num dec(6,6) default .000001); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `min_num` decimal(6,6) DEFAULT 0.000001 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table t1 ; +set @@SQL_MODE=NULL; +ERROR 42000: Variable 'sql_mode' can't be set to the value of 'NULL' +set session sql_mode=ansi; +create table t1 +(f1 integer auto_increment primary key, +f2 timestamp not null default current_timestamp on update current_timestamp); +show create table t1; +Table Create Table +t1 CREATE TABLE "t1" ( + "f1" int(11) NOT NULL AUTO_INCREMENT, + "f2" timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + PRIMARY KEY ("f1") +) +set session sql_mode=no_field_options; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) NOT NULL, + `f2` timestamp NOT NULL DEFAULT current_timestamp(), + PRIMARY KEY (`f1`) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci +drop table t1; +SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=''; +show local variables like 'SQL_MODE'; +Variable_name Value +sql_mode +CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); +INSERT t1 (a) VALUES +('\\'), +('\n'), +('\b'), +('\r'), +('\t'), +('\x'), +('\a'), +('\aa'), +('\\a'), +('\\aa'), +('_'), +('\_'), +('\\_'), +('\\\_'), +('\\\\_'), +('%'), +('\%'), +('\\%'), +('\\\%'), +('\\\\%') +; +SELECT p, hex(a) FROM t1; +p hex(a) +1 5C +2 0A +3 08 +4 0D +5 09 +6 78 +7 61 +8 6161 +9 5C61 +10 5C6161 +11 5F +12 5C5F +13 5C5F +14 5C5C5F +15 5C5C5F +16 25 +17 5C25 +18 5C25 +19 5C5C25 +20 5C5C25 +delete from t1 where a in ('\n','\r','\t', '\b'); +select +masks.p, +masks.a as mask, +examples.a as example +from +t1 as masks +left join t1 as examples on examples.a LIKE masks.a +order by masks.p, example; +p mask example +1 \ \ +6 x x +7 a a +8 aa aa +9 \a a +10 \aa aa +11 _ % +11 _ a +11 _ x +11 _ \ +11 _ _ +12 \_ _ +13 \_ _ +14 \\_ \% +14 \\_ \% +14 \\_ \a +14 \\_ \_ +14 \\_ \_ +15 \\_ \% +15 \\_ \% +15 \\_ \a +15 \\_ \_ +15 \\_ \_ +16 % % +16 % a +16 % aa +16 % x +16 % \ +16 % \% +16 % \% +16 % \a +16 % \aa +16 % \\% +16 % \\% +16 % \\_ +16 % \\_ +16 % \_ +16 % \_ +16 % _ +17 \% % +18 \% % +19 \\% \ +19 \\% \% +19 \\% \% +19 \\% \a +19 \\% \aa +19 \\% \\% +19 \\% \\% +19 \\% \\_ +19 \\% \\_ +19 \\% \_ +19 \\% \_ +20 \\% \ +20 \\% \% +20 \\% \% +20 \\% \a +20 \\% \aa +20 \\% \\% +20 \\% \\% +20 \\% \\_ +20 \\% \\_ +20 \\% \_ +20 \\% \_ +DROP TABLE t1; +SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; +show local variables like 'SQL_MODE'; +Variable_name Value +sql_mode NO_BACKSLASH_ESCAPES +CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); +INSERT t1 (a) VALUES +('\\'), +('\n'), +('\b'), +('\r'), +('\t'), +('\x'), +('\a'), +('\aa'), +('\\a'), +('\\aa'), +('_'), +('\_'), +('\\_'), +('\\\_'), +('\\\\_'), +('%'), +('\%'), +('\\%'), +('\\\%'), +('\\\\%') +; +SELECT p, hex(a) FROM t1; +p hex(a) +1 5C5C +2 5C6E +3 5C62 +4 5C72 +5 5C74 +6 5C78 +7 5C61 +8 5C6161 +9 5C5C61 +10 5C5C6161 +11 5F +12 5C5F +13 5C5C5F +14 5C5C5C5F +15 5C5C5C5C5F +16 25 +17 5C25 +18 5C5C25 +19 5C5C5C25 +20 5C5C5C5C25 +delete from t1 where a in ('\n','\r','\t', '\b'); +select +masks.p, +masks.a as mask, +examples.a as example +from +t1 as masks +left join t1 as examples on examples.a LIKE masks.a +order by masks.p, example; +p mask example +1 \\ \\ +6 \x \x +7 \a \a +8 \aa \aa +9 \\a \\a +10 \\aa \\aa +11 _ % +11 _ _ +12 \_ \% +12 \_ \a +12 \_ \x +12 \_ \\ +12 \_ \_ +13 \\_ \\% +13 \\_ \\a +13 \\_ \\_ +14 \\\_ \\\% +14 \\\_ \\\_ +15 \\\\_ \\\\% +15 \\\\_ \\\\_ +16 % % +16 % \% +16 % \a +16 % \aa +16 % \x +16 % \\ +16 % \\% +16 % \\a +16 % \\aa +16 % \\\% +16 % \\\\% +16 % \\\\_ +16 % \\\_ +16 % \\_ +16 % \_ +16 % _ +17 \% \% +17 \% \a +17 \% \aa +17 \% \x +17 \% \\ +17 \% \\% +17 \% \\a +17 \% \\aa +17 \% \\\% +17 \% \\\\% +17 \% \\\\_ +17 \% \\\_ +17 \% \\_ +17 \% \_ +18 \\% \\ +18 \\% \\% +18 \\% \\a +18 \\% \\aa +18 \\% \\\% +18 \\% \\\\% +18 \\% \\\\_ +18 \\% \\\_ +18 \\% \\_ +19 \\\% \\\% +19 \\\% \\\\% +19 \\\% \\\\_ +19 \\\% \\\_ +20 \\\\% \\\\% +20 \\\\% \\\\_ +DROP TABLE t1; +SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; +SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; +a\\b a\\\"b a'\\b a'\\\"b +a\\b a\\\"b a'\\b a'\\\"b +SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; +a\\b a\\\'b a"\\b a"\\\'b +a\\b a\\\'b a"\\b a"\\\'b +SET @@SQL_MODE=''; +SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; +a\b a\"b a'\b a'\"b +a\b a\"b a'\b a'\"b +SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; +a\b a\'b a"\b a"\'b +a\b a\'b a"\b a"\'b +SET @@SQL_MODE=''; +create function `foo` () returns int return 5; +show create function `foo`; +Function sql_mode Create Function character_set_client collation_connection Database Collation +foo CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11) +return 5 latin1 latin1_swedish_ci latin1_swedish_ci +SET @@SQL_MODE='ANSI_QUOTES'; +show create function `foo`; +Function sql_mode Create Function character_set_client collation_connection Database Collation +foo CREATE DEFINER=`root`@`localhost` FUNCTION `foo`() RETURNS int(11) +return 5 latin1 latin1_swedish_ci latin1_swedish_ci +drop function `foo`; +create function `foo` () returns int return 5; +show create function `foo`; +Function sql_mode Create Function character_set_client collation_connection Database Collation +foo ANSI_QUOTES CREATE DEFINER="root"@"localhost" FUNCTION "foo"() RETURNS int(11) +return 5 latin1 latin1_swedish_ci latin1_swedish_ci +SET @@SQL_MODE=''; +show create function `foo`; +Function sql_mode Create Function character_set_client collation_connection Database Collation +foo ANSI_QUOTES CREATE DEFINER="root"@"localhost" FUNCTION "foo"() RETURNS int(11) +return 5 latin1 latin1_swedish_ci latin1_swedish_ci +drop function `foo`; +SET @@SQL_MODE=''; +create table t1 (a int); +create table t2 (a int); +create view v1 as select a from t1; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a` from `t1` latin1 latin1_swedish_ci +SET @@SQL_MODE='ANSI_QUOTES'; +show create view v1; +View Create View character_set_client collation_connection +v1 CREATE ALGORITHM=UNDEFINED DEFINER="root"@"localhost" SQL SECURITY DEFINER VIEW "v1" AS select "t1"."a" AS "a" from "t1" latin1 latin1_swedish_ci +create view v2 as select a from t2 where a in (select a from v1); +drop view v2, v1; +drop table t1, t2; +select @@sql_mode; +@@sql_mode +ANSI_QUOTES +set sql_mode=2097152; +select @@sql_mode; +@@sql_mode +STRICT_TRANS_TABLES +set sql_mode=4194304; +select @@sql_mode; +@@sql_mode +STRICT_ALL_TABLES +set sql_mode=16384+(65536*4); +select @@sql_mode; +@@sql_mode +REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_TABLE_OPTIONS,ANSI +set sql_mode=2147483648*2*2*2; +select @@sql_mode; +@@sql_mode +TIME_ROUND_FRACTIONAL +set sql_mode=2147483648*2*2*2*2; +ERROR 42000: Variable 'sql_mode' can't be set to the value of '34359738368' +select @@sql_mode; +@@sql_mode +TIME_ROUND_FRACTIONAL +set sql_mode=PAD_CHAR_TO_FULL_LENGTH; +create table t1 (a int auto_increment primary key, b char(5)); +insert into t1 (b) values('a'),('b\t'),('c '); +select concat('x',b,'x') from t1; +concat('x',b,'x') +xa x +xb x +xc x +set sql_mode=0; +select concat('x',b,'x') from t1; +concat('x',b,'x') +xax +xb x +xcx +drop table t1; +SET @@SQL_MODE=@OLD_SQL_MODE; +create user mysqltest_32753@localhost; +connection default; +set @OLD_SQL_MODE=@@SESSION.SQL_MODE; +set session sql_mode='PAD_CHAR_TO_FULL_LENGTH'; +flush privileges; +connect user_32753,localhost,mysqltest_32753,,"*NO-ONE*",$MASTER_MYPORT,$MASTER_MYSOCK; +select current_user(); +current_user() +mysqltest_32753@localhost +connection default; +set session sql_mode=@OLD_SQL_MODE; +flush privileges; +disconnect user_32753; +connection default; +drop user mysqltest_32753@localhost; +SET @org_mode=@@sql_mode; +SET @@sql_mode='traditional'; +SELECT @@sql_mode LIKE '%NO_ENGINE_SUBSTITUTION%'; +@@sql_mode LIKE '%NO_ENGINE_SUBSTITUTION%' +1 +SET sql_mode=@org_mode; +DROP TABLE IF EXISTS t1,t2; +CREATE USER 'user_PCTFL'@'localhost' identified by 'PWD'; +CREATE USER 'user_no_PCTFL'@'localhost' identified by 'PWD'; +CREATE TABLE t1 (f1 BIGINT); +CREATE TABLE t2 (f1 CHAR(3) NOT NULL, f2 CHAR(20)); +GRANT ALL ON t1 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost'; +GRANT SELECT(f1) ON t2 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost'; +SET @OLD_SQL_MODE = @@SESSION.SQL_MODE; +SET SESSION SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH'; +DROP USER 'user_PCTFL'@'localhost'; +SET SESSION SQL_MODE = @OLD_SQL_MODE; +DROP USER 'user_no_PCTFL'@'localhost'; +FLUSH PRIVILEGES; +SELECT * FROM mysql.db WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; +Host 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 +SELECT * FROM mysql.tables_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; +Host Db User Table_name Grantor Timestamp Table_priv Column_priv +SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; +Host Db User Table_name Column_name Timestamp Column_priv +DROP TABLE t1; +DROP TABLE t2; + +# +# Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES +# IGNORED AND BREAKS REPLICATION +# +DROP TABLE IF EXISTS test_table; +DROP FUNCTION IF EXISTS test_function; +CREATE TABLE test_table (c1 CHAR(50)); +SET @org_mode=@@sql_mode; +SET @@sql_mode=''; +PREPARE insert_stmt FROM 'INSERT INTO test_table VALUES (?)'; +PREPARE update_stmt FROM 'UPDATE test_table SET c1= ? WHERE c1= ?'; +CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) +BEGIN +DECLARE char_val CHAR(50); +SELECT c1 INTO char_val FROM test_table WHERE c1=var; +RETURN char_val; +END +$ +SET @var1='abcd\'ef'; +SET @var2='abcd\"ef'; +SET @var3='abcd\bef'; +SET @var4='abcd\nef'; +SET @var5='abcd\ref'; +SET @var6='abcd\tef'; +SET @var7='abcd\\ef'; +SET @var8='abcd\%ef'; +SET @var9='abcd\_ef'; +SET @to_var1='wxyz\'ef'; +SET @to_var2='wxyz\"ef'; +SET @to_var3='wxyz\bef'; +SET @to_var4='wxyz\nef'; +SET @to_var5='wxyz\ref'; +SET @to_var6='wxyz\tef'; +SET @to_var7='wxyz\\ef'; +SET @to_var8='wxyz\%ef'; +SET @to_var9='wxyz\_ef'; +# STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +EXECUTE insert_stmt USING @var1; +EXECUTE insert_stmt USING @var2; +EXECUTE insert_stmt USING @var3; +EXECUTE insert_stmt USING @var4; +EXECUTE insert_stmt USING @var5; +EXECUTE insert_stmt USING @var6; +EXECUTE insert_stmt USING @var7; +EXECUTE insert_stmt USING @var8; +EXECUTE insert_stmt USING @var9; +SELECT * FROM test_table; +c1 +abcd'ef +abcd"ef +abcdef +abcd +ef +abcd
ef +abcd ef +abcd\ef +abcd\%ef +abcd\_ef +EXECUTE update_stmt USING @to_var1, @var1; +EXECUTE update_stmt USING @to_var2, @var2; +EXECUTE update_stmt USING @to_var3, @var3; +EXECUTE update_stmt USING @to_var4, @var4; +EXECUTE update_stmt USING @to_var5, @var5; +EXECUTE update_stmt USING @to_var6, @var6; +EXECUTE update_stmt USING @to_var7, @var7; +EXECUTE update_stmt USING @to_var8, @var8; +EXECUTE update_stmt USING @to_var9, @var9; +SELECT * FROM test_table; +c1 +wxyz'ef +wxyz"ef +wxyzef +wxyz +ef +wxyz
ef +wxyz ef +wxyz\ef +wxyz\%ef +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +# STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +select test_function(@to_var1); +test_function(@to_var1) +wxyz'ef +SELECT test_function(@to_var2); +test_function(@to_var2) +wxyz"ef +SELECT test_function(@to_var3); +test_function(@to_var3) +wxyzef +SELECT test_function(@to_var4); +test_function(@to_var4) +wxyz +ef +SELECT test_function(@to_var5); +test_function(@to_var5) +wxyz
ef +SELECT test_function(@to_var6); +test_function(@to_var6) +wxyz ef +SELECT test_function(@to_var7); +test_function(@to_var7) +wxyz\ef +SELECT test_function(@to_var8); +test_function(@to_var8) +wxyz\%ef +SELECT test_function(@to_var9); +test_function(@to_var9) +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +DELETE FROM test_table; +DROP FUNCTION test_function; +SET @@sql_mode='NO_BACKSLASH_ESCAPES'; +CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) +BEGIN +DECLARE char_val CHAR(50); +SELECT c1 INTO char_val FROM test_table WHERE c1=var; +RETURN char_val; +END +$ +# STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +EXECUTE insert_stmt USING @var1; +EXECUTE insert_stmt USING @var2; +EXECUTE insert_stmt USING @var3; +EXECUTE insert_stmt USING @var4; +EXECUTE insert_stmt USING @var5; +EXECUTE insert_stmt USING @var6; +EXECUTE insert_stmt USING @var7; +EXECUTE insert_stmt USING @var8; +EXECUTE insert_stmt USING @var9; +SELECT * FROM test_table; +c1 +abcd'ef +abcd"ef +abcdef +abcd +ef +abcd
ef +abcd ef +abcd\ef +abcd\%ef +abcd\_ef +EXECUTE update_stmt USING @to_var1, @var1; +EXECUTE update_stmt USING @to_var2, @var2; +EXECUTE update_stmt USING @to_var3, @var3; +EXECUTE update_stmt USING @to_var4, @var4; +EXECUTE update_stmt USING @to_var5, @var5; +EXECUTE update_stmt USING @to_var6, @var6; +EXECUTE update_stmt USING @to_var7, @var7; +EXECUTE update_stmt USING @to_var8, @var8; +EXECUTE update_stmt USING @to_var9, @var9; +SELECT * FROM test_table; +c1 +wxyz'ef +wxyz"ef +wxyzef +wxyz +ef +wxyz
ef +wxyz ef +wxyz\ef +wxyz\%ef +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT +# STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +select test_function(@to_var1); +test_function(@to_var1) +wxyz'ef +SELECT test_function(@to_var2); +test_function(@to_var2) +wxyz"ef +SELECT test_function(@to_var3); +test_function(@to_var3) +wxyzef +SELECT test_function(@to_var4); +test_function(@to_var4) +wxyz +ef +SELECT test_function(@to_var5); +test_function(@to_var5) +wxyz
ef +SELECT test_function(@to_var6); +test_function(@to_var6) +wxyz ef +SELECT test_function(@to_var7); +test_function(@to_var7) +wxyz\ef +SELECT test_function(@to_var8); +test_function(@to_var8) +wxyz\%ef +SELECT test_function(@to_var9); +test_function(@to_var9) +wxyz\_ef + +# END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING +DROP TABLE test_table; +DROP FUNCTION test_function; +SET @@sql_mode= @org_mode; + +#End of Test for Bug#12601974 +# +# MDEV-11848 Automatic statement repreparation changes query semantics +# +SET sql_mode=DEFAULT; +CREATE OR REPLACE TABLE t1 (a TEXT); +PREPARE stmt FROM 'INSERT INTO t1 (a) VALUES (2||3)'; +EXECUTE stmt; +SET sql_mode=ORACLE; +EXECUTE stmt; +ALTER TABLE t1 ADD b INT; +EXECUTE stmt; +SELECT * FROM t1; +a b +1 NULL +1 NULL +1 NULL +DROP TABLE t1; +# +# MDEV-12390 Wrong error line numbers reported with sql_mode=IGNORE_SPACE +# +SET sql_mode=IGNORE_SPACE; +CREATE PROCEDURE p1() +BEGIN +SELECT 1+1; +syntax error; +END; +$$ +ERROR 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; +END' at line 4 +SET sql_mode=DEFAULT; +CREATE PROCEDURE p1() +BEGIN +SELECT 1+1; +syntax error; +END; +$$ +ERROR 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; +END' at line 4 +# +# End of 10.2 tests +# +# +# Start of 10.3 tests +# +# +# MDEV-16471 mysqldump throws "Variable 'sql_mode' can't be set to the value of 'NULL' (1231)" +# +SET sql_mode='ORACLE,EMPTY_STRING_IS_NULL'; +SELECT @@sql_mode; +@@sql_mode +PIPES_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 +SELECT '' AS empty; +empty +NULL +SET sql_mode=''; +SELECT @@sql_mode; +@@sql_mode + +SET sql_mode=DEFAULT; +# +# End of 10.3 tests +# |