Create 'main' test directory and move 't' and 'r' there

1 files changed, 556 insertions, 0 deletions

diff --git a/mysql-test/main/sql_mode.test b/mysql-test/main/sql_mode.test
new file mode 100644
index 00000000000..9f38dc935e7
--- /dev/null
+++ b/mysql-test/main/sql_mode.test
@@ -0,0 +1,556 @@
+--disable_warnings
+drop table if exists t1,t2,v1,v2;
+drop view if exists t1,t2,v1,v2;
+--enable_warnings
+
+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';
+show create table t1;
+set @@sql_mode="ansi_quotes";
+show variables like 'sql_mode';
+show create table t1;
+set @@sql_mode="no_table_options";
+show variables like 'sql_mode';
+show create table t1;
+set @@sql_mode="no_key_options";
+show variables like 'sql_mode';
+show create table t1;
+set @@sql_mode="no_field_options,mysql323,mysql40";
+show variables like 'sql_mode';
+show create table t1;
+set sql_mode="postgresql,oracle,mssql,db2,maxdb";
+select @@sql_mode;
+show create table t1;
+drop table t1;
+
+#
+# Check that a binary collation adds 'binary'
+# suffix into a char() column definition in
+# mysql40 and mysql2323 modes. This allows
+# not to lose the column's case sensitivity
+# when loading the dump in pre-4.1 servers. 
+#
+# Thus, in 4.0 and 3.23 modes we dump:
+#
+#   'char(10) collate xxx_bin' as 'char(10) binary' 
+#   'binary(10)' as 'binary(10)'
+#
+# In mysql-4.1 these types are different, and they will
+# be recreated differently.
+#
+# In mysqld-4.0 the the above two types were the same,
+# so it will create a 'char(10) binary' column for both definitions.
+# 
+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;
+set @@sql_mode="mysql323";
+show create table t1;
+set @@sql_mode="mysql40";
+show create table t1;
+drop table t1;
+
+#
+# BUG#5318 - failure: 'IGNORE_SPACE' affects numeric values after DEFAULT
+#
+# Force the usage of the default
+set session sql_mode = '';
+# statement for comparison, value starts with '.'
+create table t1 ( min_num   dec(6,6)     default .000001);
+show create table t1;
+drop table t1 ;
+#
+set session sql_mode = 'IGNORE_SPACE';
+# statement for comparison, value starts with '0'
+create table t1 ( min_num   dec(6,6)     default 0.000001);
+show create table t1;
+drop table t1 ;
+# This statement fails, value starts with '.'
+create table t1 ( min_num   dec(6,6)     default .000001);
+show create table t1;
+drop table t1 ;
+
+#
+# Bug #10732: Set SQL_MODE to NULL gives garbled error message
+#
+--error 1231
+set @@SQL_MODE=NULL;
+
+#
+# Bug #797: in sql_mode=ANSI, show create table ignores auto_increment
+#
+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;
+set session sql_mode=no_field_options;
+show create table t1;
+drop table t1;
+
+# End of 4.1 tests
+
+#
+# test for 
+#  WL 1941 "NO_C_ESCAPES sql_mode"
+#
+# an sql_mode to disable \n, \r, \b, etc escapes in string literals. actually, to
+# disable special meaning of backslash completely. It's not in the SQL standard
+# and it causes some R/3 tests to fail.
+#
+
+SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE='';
+show local variables like '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;
+
+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;
+
+DROP TABLE t1;
+
+SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
+show local variables like '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;
+
+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;
+
+DROP TABLE t1;
+
+# Bug #6368: Make sure backslashes mixed with doubled quotes are handled
+# correctly in NO_BACKSLASH_ESCAPES mode
+SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
+SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b';
+SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b";
+
+SET @@SQL_MODE='';
+SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b';
+SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b";
+
+#
+# Bug#6877: MySQL should give an error if the requested table type 
+#           is not available
+#
+
+#set session sql_mode = 'NO_ENGINE_SUBSTITUTION';
+#--error 1289
+#create table t1 (a int) engine=isam;
+#--error 1146
+#show create table t1;
+#drop table if exists t1;
+#
+## for comparison, lets see the warnings...
+#set session sql_mode = '';
+#create table t1 (a int) engine=isam;
+#show create table t1;
+#drop table t1;
+
+#
+# Bug #6903: ANSI_QUOTES does not come into play with SHOW CREATE FUNCTION
+# or PROCEDURE because it displays the SQL_MODE used to create the routine.
+#
+SET @@SQL_MODE='';
+create function `foo` () returns int return 5;
+show create function `foo`;
+SET @@SQL_MODE='ANSI_QUOTES';
+show create function `foo`;
+drop function `foo`;
+
+create function `foo` () returns int return 5;
+show create function `foo`;
+SET @@SQL_MODE='';
+show create function `foo`;
+drop function `foo`;
+
+# 
+# Bug #6903: ANSI_QUOTES should have effect for SHOW CREATE VIEW (Bug #6903)
+#
+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;
+SET @@SQL_MODE='ANSI_QUOTES';
+show create view v1;
+# Test a view with a subselect, which will get shown incorrectly without
+# thd->lex->view_prepare_mode set properly.
+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;
+set sql_mode=2097152;
+select @@sql_mode;
+# BUG#14675
+set sql_mode=4194304;
+select @@sql_mode;
+set sql_mode=16384+(65536*4);
+select @@sql_mode;
+--error 1231
+set sql_mode=2147483648*2*2*2; # that mode does not exist
+select @@sql_mode;
+
+#
+# Test WL921: Retain spaces when retrieving CHAR column values
+
+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;
+set sql_mode=0;
+select concat('x',b,'x') from t1;
+drop table t1;
+
+SET @@SQL_MODE=@OLD_SQL_MODE;
+
+
+#
+# Bug #32753: PAD_CHAR_TO_FULL_LENGTH is not documented and interferes
+#             with grant tables
+#
+
+create user mysqltest_32753@localhost;
+
+# try to make the user-table space-padded
+--connection default
+set @OLD_SQL_MODE=@@SESSION.SQL_MODE;
+set session sql_mode='PAD_CHAR_TO_FULL_LENGTH';
+flush privileges;
+
+# if user-table is affected by PAD_CHAR_TO_FULL_LENGTH, our connect will fail
+# --error 1045
+connect (user_32753,localhost,mysqltest_32753,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
+select current_user();
+
+# clean up
+--connection default
+set session sql_mode=@OLD_SQL_MODE;
+flush privileges;
+
+--disconnect user_32753
+
+--connection default
+drop user mysqltest_32753@localhost;
+
+
+#
+# Bug#21099 MySQL 5.0.22 silently creates MyISAM tables even though 
+#           InnoDB specified.
+#
+
+SET @org_mode=@@sql_mode;
+SET @@sql_mode='traditional';
+
+# Agreed change was to add NO_ENGINE_SUBSTITUTION to TRADITIONAL sql mode.
+SELECT @@sql_mode LIKE '%NO_ENGINE_SUBSTITUTION%';
+
+SET sql_mode=@org_mode;
+
+
+#
+# Bug#45100: Incomplete DROP USER in case of SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH'
+#
+
+--disable_warnings
+DROP TABLE IF EXISTS t1,t2;
+--enable_warnings
+
+# Generate some prerequisites
+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 privilege on a TABLE
+GRANT ALL ON t1 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost';
+# Grant privilege on some COLUMN of a table
+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';
+SELECT * FROM mysql.tables_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
+SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
+
+# Cleanup
+DROP TABLE t1;
+DROP TABLE t2;
+
+
+--echo 
+--echo #
+--echo # Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES
+--echo # IGNORED AND BREAKS REPLICATION
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS test_table;
+DROP FUNCTION IF EXISTS test_function;
+--enable_warnings
+
+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= ?';
+DELIMITER $;
+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
+$
+DELIMITER ;$
+
+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';
+
+--echo # 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;
+
+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;
+
+--echo 
+--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT
+
+--echo # STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING
+select test_function(@to_var1);
+SELECT test_function(@to_var2);
+SELECT test_function(@to_var3);
+SELECT test_function(@to_var4);
+SELECT test_function(@to_var5);
+SELECT test_function(@to_var6);
+SELECT test_function(@to_var7);
+SELECT test_function(@to_var8);
+SELECT test_function(@to_var9);
+
+--echo 
+--echo # 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';
+DELIMITER $;
+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
+$
+DELIMITER ;$
+
+--echo # 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;
+
+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;
+
+--echo 
+--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT
+
+--echo # STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING
+select test_function(@to_var1);
+SELECT test_function(@to_var2);
+SELECT test_function(@to_var3);
+SELECT test_function(@to_var4);
+SELECT test_function(@to_var5);
+SELECT test_function(@to_var6);
+SELECT test_function(@to_var7);
+SELECT test_function(@to_var8);
+SELECT test_function(@to_var9);
+
+--echo 
+--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING
+
+DROP TABLE test_table;
+DROP FUNCTION test_function;
+SET @@sql_mode= @org_mode;
+
+--echo 
+--echo #End of Test for Bug#12601974 
+
+--echo #
+--echo # MDEV-11848 Automatic statement repreparation changes query semantics
+--echo #
+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;
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-12390 Wrong error line numbers reported with sql_mode=IGNORE_SPACE
+--echo #
+
+SET sql_mode=IGNORE_SPACE;
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1()
+BEGIN
+  SELECT 1+1;
+  syntax error;
+END;
+$$
+DELIMITER ;$$
+
+
+SET sql_mode=DEFAULT;
+DELIMITER $$;
+--error ER_PARSE_ERROR
+CREATE PROCEDURE p1()
+BEGIN
+  SELECT 1+1;
+  syntax error;
+END;
+$$
+DELIMITER ;$$