2 drop table if exists t1,t2,v1,v2;
3 drop view if exists t1,t2,v1,v2;
7 a int not null auto_increment,
8 `pseudo` varchar(35) character set latin2 NOT NULL default '',
9 `email` varchar(60) character set latin2 NOT NULL default '',
11 UNIQUE KEY `email` USING BTREE (`email`)
12 ) ENGINE=HEAP CHARSET=latin1 ROW_FORMAT DYNAMIC;
14 show variables like 'sql_mode';
16 set @@sql_mode="ansi_quotes";
17 show variables like 'sql_mode';
19 set @@sql_mode="no_table_options";
20 show variables like 'sql_mode';
22 set @@sql_mode="no_key_options";
23 show variables like 'sql_mode';
25 set @@sql_mode="no_field_options,mysql323,mysql40";
26 show variables like 'sql_mode';
28 set sql_mode="postgresql,oracle,mssql,db2,maxdb";
34 # Check that a binary collation adds 'binary'
35 # suffix into a char() column definition in
36 # mysql40 and mysql2323 modes. This allows
37 # not to lose the column's case sensitivity
38 # when loading the dump in pre-4.1 servers.
40 # Thus, in 4.0 and 3.23 modes we dump:
42 # 'char(10) collate xxx_bin' as 'char(10) binary'
43 # 'binary(10)' as 'binary(10)'
45 # In mysql-4.1 these types are different, and they will
46 # be recreated differently.
48 # In mysqld-4.0 the the above two types were the same,
49 # so it will create a 'char(10) binary' column for both definitions.
53 b char(10) collate latin1_bin,
55 ) character set latin1;
58 set @@sql_mode="mysql323";
60 set @@sql_mode="mysql40";
65 # BUG#5318 - failure: 'IGNORE_SPACE' affects numeric values after DEFAULT
67 # Force the usage of the default
68 set session sql_mode = '';
69 # statement for comparison, value starts with '.'
70 create table t1 ( min_num dec(6,6) default .000001);
74 set session sql_mode = 'IGNORE_SPACE';
75 # statement for comparison, value starts with '0'
76 create table t1 ( min_num dec(6,6) default 0.000001);
79 # This statement fails, value starts with '.'
80 create table t1 ( min_num dec(6,6) default .000001);
85 # Bug #10732: Set SQL_MODE to NULL gives garbled error message
91 # Bug #797: in sql_mode=ANSI, show create table ignores auto_increment
93 set session sql_mode=ansi;
95 (f1 integer auto_increment primary key,
96 f2 timestamp default current_timestamp on update current_timestamp);
98 set session sql_mode=no_field_options;
106 # WL 1941 "NO_C_ESCAPES sql_mode"
108 # an sql_mode to disable \n, \r, \b, etc escapes in string literals. actually, to
109 # disable special meaning of backslash completely. It's not in the SQL standard
110 # and it causes some R/3 tests to fail.
113 SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE='';
114 show local variables like 'SQL_MODE';
116 CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p));
140 SELECT p, hex(a) FROM t1;
142 delete from t1 where a in ('\n','\r','\t', '\b');
147 examples.a as example
150 left join t1 as examples on examples.a LIKE masks.a
151 order by masks.p, example;
155 SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
156 show local variables like 'SQL_MODE';
158 CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p));
182 SELECT p, hex(a) FROM t1;
184 delete from t1 where a in ('\n','\r','\t', '\b');
189 examples.a as example
192 left join t1 as examples on examples.a LIKE masks.a
193 order by masks.p, example;
197 # Bug #6368: Make sure backslashes mixed with doubled quotes are handled
198 # correctly in NO_BACKSLASH_ESCAPES mode
199 SET @@SQL_MODE='NO_BACKSLASH_ESCAPES';
200 SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b';
201 SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b";
204 SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b';
205 SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b";
208 # Bug#6877: MySQL should give an error if the requested table type
212 #set session sql_mode = 'NO_ENGINE_SUBSTITUTION';
214 #create table t1 (a int) engine=isam;
216 #show create table t1;
217 #drop table if exists t1;
219 ## for comparison, lets see the warnings...
220 #set session sql_mode = '';
221 #create table t1 (a int) engine=isam;
222 #show create table t1;
226 # Bug #6903: ANSI_QUOTES does not come into play with SHOW CREATE FUNCTION
227 # or PROCEDURE because it displays the SQL_MODE used to create the routine.
230 create function `foo` () returns int return 5;
231 show create function `foo`;
232 SET @@SQL_MODE='ANSI_QUOTES';
233 show create function `foo`;
236 create function `foo` () returns int return 5;
237 show create function `foo`;
239 show create function `foo`;
243 # Bug #6903: ANSI_QUOTES should have effect for SHOW CREATE VIEW (Bug #6903)
246 create table t1 (a int);
247 create table t2 (a int);
248 create view v1 as select a from t1;
250 SET @@SQL_MODE='ANSI_QUOTES';
252 # Test a view with a subselect, which will get shown incorrectly without
253 # thd->lex->view_prepare_mode set properly.
254 create view v2 as select a from t2 where a in (select a from v1);
259 set sql_mode=2097152;
262 set sql_mode=4194304;
264 set sql_mode=16384+(65536*4);
267 set sql_mode=2147483648*2; # that mode does not exist
271 # Test WL921: Retain spaces when retrieving CHAR column values
273 set sql_mode=PAD_CHAR_TO_FULL_LENGTH;
274 create table t1 (a int auto_increment primary key, b char(5));
275 insert into t1 (b) values('a'),('b\t'),('c ');
276 select concat('x',b,'x') from t1;
278 select concat('x',b,'x') from t1;
281 SET @@SQL_MODE=@OLD_SQL_MODE;
285 # Bug #32753: PAD_CHAR_TO_FULL_LENGTH is not documented and interferes
289 create user mysqltest_32753@localhost;
291 # try to make the user-table space-padded
293 set @OLD_SQL_MODE=@@SESSION.SQL_MODE;
294 set session sql_mode='PAD_CHAR_TO_FULL_LENGTH';
297 # if user-table is affected by PAD_CHAR_TO_FULL_LENGTH, our connect will fail
299 connect (user_32753,localhost,mysqltest_32753,,test,$MASTER_MYPORT,$MASTER_MYSOCK);
300 select current_user();
304 set session sql_mode=@OLD_SQL_MODE;
307 --disconnect user_32753
310 drop user mysqltest_32753@localhost;
313 # Bug#45100: Incomplete DROP USER in case of SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH'
317 DROP TABLE IF EXISTS t1,t2;
320 # Generate some prerequisites
321 CREATE USER 'user_PCTFL'@'localhost' identified by 'PWD';
322 CREATE USER 'user_no_PCTFL'@'localhost' identified by 'PWD';
324 CREATE TABLE t1 (f1 BIGINT);
325 CREATE TABLE t2 (f1 CHAR(3) NOT NULL, f2 CHAR(20));
327 # Grant privilege on a TABLE
328 GRANT ALL ON t1 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost';
329 # Grant privilege on some COLUMN of a table
330 GRANT SELECT(f1) ON t2 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost';
332 SET @OLD_SQL_MODE = @@SESSION.SQL_MODE;
333 SET SESSION SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH';
334 DROP USER 'user_PCTFL'@'localhost';
335 SET SESSION SQL_MODE = @OLD_SQL_MODE;
336 DROP USER 'user_no_PCTFL'@'localhost';
340 SELECT * FROM mysql.db WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
341 SELECT * FROM mysql.tables_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
342 SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL';
351 --echo # Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES
352 --echo # IGNORED AND BREAKS REPLICATION
356 DROP TABLE IF EXISTS test_table;
357 DROP FUNCTION IF EXISTS test_function;
360 CREATE TABLE test_table (c1 CHAR(50));
362 SET @org_mode=@@sql_mode;
366 PREPARE insert_stmt FROM 'INSERT INTO test_table VALUES (?)';
367 PREPARE update_stmt FROM 'UPDATE test_table SET c1= ? WHERE c1= ?';
369 CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50)
371 DECLARE char_val CHAR(50);
372 SELECT c1 INTO char_val FROM test_table WHERE c1=var;
378 SET @var1='abcd\'ef';
379 SET @var2='abcd\"ef';
380 SET @var3='abcd\bef';
381 SET @var4='abcd\nef';
382 SET @var5='abcd\ref';
383 SET @var6='abcd\tef';
384 SET @var7='abcd\\ef';
385 SET @var8='abcd\%ef';
386 SET @var9='abcd\_ef';
388 SET @to_var1='wxyz\'ef';
389 SET @to_var2='wxyz\"ef';
390 SET @to_var3='wxyz\bef';
391 SET @to_var4='wxyz\nef';
392 SET @to_var5='wxyz\ref';
393 SET @to_var6='wxyz\tef';
394 SET @to_var7='wxyz\\ef';
395 SET @to_var8='wxyz\%ef';
396 SET @to_var9='wxyz\_ef';
398 --echo # STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT
399 EXECUTE insert_stmt USING @var1;
400 EXECUTE insert_stmt USING @var2;
401 EXECUTE insert_stmt USING @var3;
402 EXECUTE insert_stmt USING @var4;
403 EXECUTE insert_stmt USING @var5;
404 EXECUTE insert_stmt USING @var6;
405 EXECUTE insert_stmt USING @var7;
406 EXECUTE insert_stmt USING @var8;
407 EXECUTE insert_stmt USING @var9;
409 SELECT * FROM test_table;
411 EXECUTE update_stmt USING @to_var1, @var1;
412 EXECUTE update_stmt USING @to_var2, @var2;
413 EXECUTE update_stmt USING @to_var3, @var3;
414 EXECUTE update_stmt USING @to_var4, @var4;
415 EXECUTE update_stmt USING @to_var5, @var5;
416 EXECUTE update_stmt USING @to_var6, @var6;
417 EXECUTE update_stmt USING @to_var7, @var7;
418 EXECUTE update_stmt USING @to_var8, @var8;
419 EXECUTE update_stmt USING @to_var9, @var9;
421 SELECT * FROM test_table;
424 --echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT
426 --echo # STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING
427 select test_function(@to_var1);
428 SELECT test_function(@to_var2);
429 SELECT test_function(@to_var3);
430 SELECT test_function(@to_var4);
431 SELECT test_function(@to_var5);
432 SELECT test_function(@to_var6);
433 SELECT test_function(@to_var7);
434 SELECT test_function(@to_var8);
435 SELECT test_function(@to_var9);
438 --echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING
439 DELETE FROM test_table;
440 DROP FUNCTION test_function;
442 SET @@sql_mode='NO_BACKSLASH_ESCAPES';
444 CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50)
446 DECLARE char_val CHAR(50);
447 SELECT c1 INTO char_val FROM test_table WHERE c1=var;
453 --echo # STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT
454 EXECUTE insert_stmt USING @var1;
455 EXECUTE insert_stmt USING @var2;
456 EXECUTE insert_stmt USING @var3;
457 EXECUTE insert_stmt USING @var4;
458 EXECUTE insert_stmt USING @var5;
459 EXECUTE insert_stmt USING @var6;
460 EXECUTE insert_stmt USING @var7;
461 EXECUTE insert_stmt USING @var8;
462 EXECUTE insert_stmt USING @var9;
464 SELECT * FROM test_table;
466 EXECUTE update_stmt USING @to_var1, @var1;
467 EXECUTE update_stmt USING @to_var2, @var2;
468 EXECUTE update_stmt USING @to_var3, @var3;
469 EXECUTE update_stmt USING @to_var4, @var4;
470 EXECUTE update_stmt USING @to_var5, @var5;
471 EXECUTE update_stmt USING @to_var6, @var6;
472 EXECUTE update_stmt USING @to_var7, @var7;
473 EXECUTE update_stmt USING @to_var8, @var8;
474 EXECUTE update_stmt USING @to_var9, @var9;
476 SELECT * FROM test_table;
479 --echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT
481 --echo # STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING
482 select test_function(@to_var1);
483 SELECT test_function(@to_var2);
484 SELECT test_function(@to_var3);
485 SELECT test_function(@to_var4);
486 SELECT test_function(@to_var5);
487 SELECT test_function(@to_var6);
488 SELECT test_function(@to_var7);
489 SELECT test_function(@to_var8);
490 SELECT test_function(@to_var9);
493 --echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING
495 DROP TABLE test_table;
496 DROP FUNCTION test_function;
497 SET @@sql_mode= @org_mode;
500 --echo #End of Test for Bug#12601974