1--disable_warnings 2drop table if exists t1,t2,v1,v2; 3drop view if exists t1,t2,v1,v2; 4--enable_warnings 5 6CREATE TABLE `t1` ( 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 '', 10 PRIMARY KEY (a), 11 UNIQUE KEY `email` USING BTREE (`email`) 12) ENGINE=HEAP CHARSET=latin1 ROW_FORMAT DYNAMIC; 13set @@sql_mode=""; 14show variables like 'sql_mode'; 15show create table t1; 16set @@sql_mode="ansi_quotes"; 17show variables like 'sql_mode'; 18show create table t1; 19set @@sql_mode="no_table_options"; 20show variables like 'sql_mode'; 21show create table t1; 22set @@sql_mode="no_key_options"; 23show variables like 'sql_mode'; 24show create table t1; 25set @@sql_mode="no_field_options,mysql323,mysql40"; 26show variables like 'sql_mode'; 27show create table t1; 28set sql_mode="postgresql,oracle,mssql,db2,maxdb"; 29select @@sql_mode; 30show create table t1; 31drop table t1; 32 33# 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. 39# 40# Thus, in 4.0 and 3.23 modes we dump: 41# 42# 'char(10) collate xxx_bin' as 'char(10) binary' 43# 'binary(10)' as 'binary(10)' 44# 45# In mysql-4.1 these types are different, and they will 46# be recreated differently. 47# 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. 50# 51CREATE TABLE t1 ( 52 a char(10), 53 b char(10) collate latin1_bin, 54 c binary(10) 55) character set latin1; 56set @@sql_mode=""; 57show create table t1; 58set @@sql_mode="mysql323"; 59show create table t1; 60set @@sql_mode="mysql40"; 61show create table t1; 62drop table t1; 63 64# 65# BUG#5318 - failure: 'IGNORE_SPACE' affects numeric values after DEFAULT 66# 67# Force the usage of the default 68set session sql_mode = ''; 69# statement for comparison, value starts with '.' 70create table t1 ( min_num dec(6,6) default .000001); 71show create table t1; 72drop table t1 ; 73# 74set session sql_mode = 'IGNORE_SPACE'; 75# statement for comparison, value starts with '0' 76create table t1 ( min_num dec(6,6) default 0.000001); 77show create table t1; 78drop table t1 ; 79# This statement fails, value starts with '.' 80create table t1 ( min_num dec(6,6) default .000001); 81show create table t1; 82drop table t1 ; 83 84# 85# Bug #10732: Set SQL_MODE to NULL gives garbled error message 86# 87--error 1231 88set @@SQL_MODE=NULL; 89 90# 91# Bug #797: in sql_mode=ANSI, show create table ignores auto_increment 92# 93set session sql_mode=ansi; 94create table t1 95(f1 integer auto_increment primary key, 96 f2 timestamp not null default current_timestamp on update current_timestamp); 97show create table t1; 98set session sql_mode=no_field_options; 99show create table t1; 100drop table t1; 101 102# End of 4.1 tests 103 104# 105# test for 106# WL 1941 "NO_C_ESCAPES sql_mode" 107# 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. 111# 112 113SET @OLD_SQL_MODE=@@SQL_MODE, @@SQL_MODE=''; 114show local variables like 'SQL_MODE'; 115 116CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); 117INSERT t1 (a) VALUES 118('\\'), 119('\n'), 120('\b'), 121('\r'), 122('\t'), 123('\x'), 124('\a'), 125('\aa'), 126('\\a'), 127('\\aa'), 128('_'), 129('\_'), 130('\\_'), 131('\\\_'), 132('\\\\_'), 133('%'), 134('\%'), 135('\\%'), 136('\\\%'), 137('\\\\%') 138; 139 140SELECT p, hex(a) FROM t1; 141 142delete from t1 where a in ('\n','\r','\t', '\b'); 143 144select 145 masks.p, 146 masks.a as mask, 147 examples.a as example 148from 149 t1 as masks 150 left join t1 as examples on examples.a LIKE masks.a 151order by masks.p, example; 152 153DROP TABLE t1; 154 155SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 156show local variables like 'SQL_MODE'; 157 158CREATE TABLE t1 (p int not null auto_increment, a varchar(20), primary key(p)); 159INSERT t1 (a) VALUES 160('\\'), 161('\n'), 162('\b'), 163('\r'), 164('\t'), 165('\x'), 166('\a'), 167('\aa'), 168('\\a'), 169('\\aa'), 170('_'), 171('\_'), 172('\\_'), 173('\\\_'), 174('\\\\_'), 175('%'), 176('\%'), 177('\\%'), 178('\\\%'), 179('\\\\%') 180; 181 182SELECT p, hex(a) FROM t1; 183 184delete from t1 where a in ('\n','\r','\t', '\b'); 185 186select 187 masks.p, 188 masks.a as mask, 189 examples.a as example 190from 191 t1 as masks 192 left join t1 as examples on examples.a LIKE masks.a 193order by masks.p, example; 194 195DROP TABLE t1; 196 197# Bug #6368: Make sure backslashes mixed with doubled quotes are handled 198# correctly in NO_BACKSLASH_ESCAPES mode 199SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 200SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; 201SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; 202 203SET @@SQL_MODE=''; 204SELECT 'a\\b', 'a\\\"b', 'a''\\b', 'a''\\\"b'; 205SELECT "a\\b", "a\\\'b", "a""\\b", "a""\\\'b"; 206 207# 208# Bug#6877: MySQL should give an error if the requested table type 209# is not available 210# 211 212#set session sql_mode = 'NO_ENGINE_SUBSTITUTION'; 213#--error 1289 214#create table t1 (a int) engine=isam; 215#--error 1146 216#show create table t1; 217#drop table if exists t1; 218# 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; 223#drop table t1; 224 225# 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. 228# 229SET @@SQL_MODE=''; 230create function `foo` () returns int return 5; 231show create function `foo`; 232SET @@SQL_MODE='ANSI_QUOTES'; 233show create function `foo`; 234drop function `foo`; 235 236create function `foo` () returns int return 5; 237show create function `foo`; 238SET @@SQL_MODE=''; 239show create function `foo`; 240drop function `foo`; 241 242# 243# Bug #6903: ANSI_QUOTES should have effect for SHOW CREATE VIEW (Bug #6903) 244# 245SET @@SQL_MODE=''; 246create table t1 (a int); 247create table t2 (a int); 248create view v1 as select a from t1; 249show create view v1; 250SET @@SQL_MODE='ANSI_QUOTES'; 251show create view v1; 252# Test a view with a subselect, which will get shown incorrectly without 253# thd->lex->view_prepare_mode set properly. 254create view v2 as select a from t2 where a in (select a from v1); 255drop view v2, v1; 256drop table t1, t2; 257 258select @@sql_mode; 259set sql_mode=2097152; 260select @@sql_mode; 261# BUG#14675 262set sql_mode=4194304; 263select @@sql_mode; 264set sql_mode=16384+(65536*4); 265select @@sql_mode; 266set sql_mode=2147483648*2*2*2; 267select @@sql_mode; 268--error 1231 269set sql_mode=2147483648*2*2*2*2; # that mode does not exist 270select @@sql_mode; 271 272# 273# Test WL921: Retain spaces when retrieving CHAR column values 274 275set sql_mode=PAD_CHAR_TO_FULL_LENGTH; 276create table t1 (a int auto_increment primary key, b char(5)); 277insert into t1 (b) values('a'),('b\t'),('c '); 278select concat('x',b,'x') from t1; 279set sql_mode=0; 280select concat('x',b,'x') from t1; 281drop table t1; 282 283SET @@SQL_MODE=@OLD_SQL_MODE; 284 285 286# 287# Bug #32753: PAD_CHAR_TO_FULL_LENGTH is not documented and interferes 288# with grant tables 289# 290 291create user mysqltest_32753@localhost; 292 293# try to make the user-table space-padded 294--connection default 295set @OLD_SQL_MODE=@@SESSION.SQL_MODE; 296set session sql_mode='PAD_CHAR_TO_FULL_LENGTH'; 297flush privileges; 298 299# if user-table is affected by PAD_CHAR_TO_FULL_LENGTH, our connect will fail 300# --error 1045 301connect (user_32753,localhost,mysqltest_32753,,test,$MASTER_MYPORT,$MASTER_MYSOCK); 302select current_user(); 303 304# clean up 305--connection default 306set session sql_mode=@OLD_SQL_MODE; 307flush privileges; 308 309--disconnect user_32753 310 311--connection default 312drop user mysqltest_32753@localhost; 313 314 315# 316# Bug#21099 MySQL 5.0.22 silently creates MyISAM tables even though 317# InnoDB specified. 318# 319 320SET @org_mode=@@sql_mode; 321SET @@sql_mode='traditional'; 322 323# Agreed change was to add NO_ENGINE_SUBSTITUTION to TRADITIONAL sql mode. 324SELECT @@sql_mode LIKE '%NO_ENGINE_SUBSTITUTION%'; 325 326SET sql_mode=@org_mode; 327 328 329# 330# Bug#45100: Incomplete DROP USER in case of SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH' 331# 332 333--disable_warnings 334DROP TABLE IF EXISTS t1,t2; 335--enable_warnings 336 337# Generate some prerequisites 338CREATE USER 'user_PCTFL'@'localhost' identified by 'PWD'; 339CREATE USER 'user_no_PCTFL'@'localhost' identified by 'PWD'; 340 341CREATE TABLE t1 (f1 BIGINT); 342CREATE TABLE t2 (f1 CHAR(3) NOT NULL, f2 CHAR(20)); 343 344# Grant privilege on a TABLE 345GRANT ALL ON t1 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost'; 346# Grant privilege on some COLUMN of a table 347GRANT SELECT(f1) ON t2 TO 'user_PCTFL'@'localhost','user_no_PCTFL'@'localhost'; 348 349SET @OLD_SQL_MODE = @@SESSION.SQL_MODE; 350SET SESSION SQL_MODE = 'PAD_CHAR_TO_FULL_LENGTH'; 351DROP USER 'user_PCTFL'@'localhost'; 352SET SESSION SQL_MODE = @OLD_SQL_MODE; 353DROP USER 'user_no_PCTFL'@'localhost'; 354 355FLUSH PRIVILEGES; 356 357SELECT * FROM mysql.db WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; 358SELECT * FROM mysql.tables_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; 359SELECT * FROM mysql.columns_priv WHERE Host = 'localhost' AND User LIKE 'user_%PCTFL'; 360 361# Cleanup 362DROP TABLE t1; 363DROP TABLE t2; 364 365 366--echo 367--echo # 368--echo # Test for Bug#12601974 - STORED PROCEDURE SQL_MODE=NO_BACKSLASH_ESCAPES 369--echo # IGNORED AND BREAKS REPLICATION 370--echo # 371 372--disable_warnings 373DROP TABLE IF EXISTS test_table; 374DROP FUNCTION IF EXISTS test_function; 375--enable_warnings 376 377CREATE TABLE test_table (c1 CHAR(50)); 378 379SET @org_mode=@@sql_mode; 380 381SET @@sql_mode=''; 382 383PREPARE insert_stmt FROM 'INSERT INTO test_table VALUES (?)'; 384PREPARE update_stmt FROM 'UPDATE test_table SET c1= ? WHERE c1= ?'; 385DELIMITER $; 386CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) 387BEGIN 388 DECLARE char_val CHAR(50); 389 SELECT c1 INTO char_val FROM test_table WHERE c1=var; 390 RETURN char_val; 391END 392$ 393DELIMITER ;$ 394 395SET @var1='abcd\'ef'; 396SET @var2='abcd\"ef'; 397SET @var3='abcd\bef'; 398SET @var4='abcd\nef'; 399SET @var5='abcd\ref'; 400SET @var6='abcd\tef'; 401SET @var7='abcd\\ef'; 402SET @var8='abcd\%ef'; 403SET @var9='abcd\_ef'; 404 405SET @to_var1='wxyz\'ef'; 406SET @to_var2='wxyz\"ef'; 407SET @to_var3='wxyz\bef'; 408SET @to_var4='wxyz\nef'; 409SET @to_var5='wxyz\ref'; 410SET @to_var6='wxyz\tef'; 411SET @to_var7='wxyz\\ef'; 412SET @to_var8='wxyz\%ef'; 413SET @to_var9='wxyz\_ef'; 414 415--echo # STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT 416EXECUTE insert_stmt USING @var1; 417EXECUTE insert_stmt USING @var2; 418EXECUTE insert_stmt USING @var3; 419EXECUTE insert_stmt USING @var4; 420EXECUTE insert_stmt USING @var5; 421EXECUTE insert_stmt USING @var6; 422EXECUTE insert_stmt USING @var7; 423EXECUTE insert_stmt USING @var8; 424EXECUTE insert_stmt USING @var9; 425 426SELECT * FROM test_table; 427 428EXECUTE update_stmt USING @to_var1, @var1; 429EXECUTE update_stmt USING @to_var2, @var2; 430EXECUTE update_stmt USING @to_var3, @var3; 431EXECUTE update_stmt USING @to_var4, @var4; 432EXECUTE update_stmt USING @to_var5, @var5; 433EXECUTE update_stmt USING @to_var6, @var6; 434EXECUTE update_stmt USING @to_var7, @var7; 435EXECUTE update_stmt USING @to_var8, @var8; 436EXECUTE update_stmt USING @to_var9, @var9; 437 438SELECT * FROM test_table; 439 440--echo 441--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT 442 443--echo # STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING 444select test_function(@to_var1); 445SELECT test_function(@to_var2); 446SELECT test_function(@to_var3); 447SELECT test_function(@to_var4); 448SELECT test_function(@to_var5); 449SELECT test_function(@to_var6); 450SELECT test_function(@to_var7); 451SELECT test_function(@to_var8); 452SELECT test_function(@to_var9); 453 454--echo 455--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING 456DELETE FROM test_table; 457DROP FUNCTION test_function; 458 459SET @@sql_mode='NO_BACKSLASH_ESCAPES'; 460DELIMITER $; 461CREATE FUNCTION test_function(var CHAR(50)) RETURNS CHAR(50) 462BEGIN 463 DECLARE char_val CHAR(50); 464 SELECT c1 INTO char_val FROM test_table WHERE c1=var; 465 RETURN char_val; 466END 467$ 468DELIMITER ;$ 469 470--echo # STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT 471EXECUTE insert_stmt USING @var1; 472EXECUTE insert_stmt USING @var2; 473EXECUTE insert_stmt USING @var3; 474EXECUTE insert_stmt USING @var4; 475EXECUTE insert_stmt USING @var5; 476EXECUTE insert_stmt USING @var6; 477EXECUTE insert_stmt USING @var7; 478EXECUTE insert_stmt USING @var8; 479EXECUTE insert_stmt USING @var9; 480 481SELECT * FROM test_table; 482 483EXECUTE update_stmt USING @to_var1, @var1; 484EXECUTE update_stmt USING @to_var2, @var2; 485EXECUTE update_stmt USING @to_var3, @var3; 486EXECUTE update_stmt USING @to_var4, @var4; 487EXECUTE update_stmt USING @to_var5, @var5; 488EXECUTE update_stmt USING @to_var6, @var6; 489EXECUTE update_stmt USING @to_var7, @var7; 490EXECUTE update_stmt USING @to_var8, @var8; 491EXECUTE update_stmt USING @to_var9, @var9; 492 493SELECT * FROM test_table; 494 495--echo 496--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN PREPARE STATEMENT 497 498--echo # STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING 499select test_function(@to_var1); 500SELECT test_function(@to_var2); 501SELECT test_function(@to_var3); 502SELECT test_function(@to_var4); 503SELECT test_function(@to_var5); 504SELECT test_function(@to_var6); 505SELECT test_function(@to_var7); 506SELECT test_function(@to_var8); 507SELECT test_function(@to_var9); 508 509--echo 510--echo # END OF CASE - STRING LILTERAL WITH BACKSLASH IN FUNCTION RETURNING STRING 511 512DROP TABLE test_table; 513DROP FUNCTION test_function; 514SET @@sql_mode= @org_mode; 515 516--echo 517--echo #End of Test for Bug#12601974 518 519--echo # 520--echo # MDEV-11848 Automatic statement repreparation changes query semantics 521--echo # 522SET sql_mode=DEFAULT; 523CREATE OR REPLACE TABLE t1 (a TEXT); 524PREPARE stmt FROM 'INSERT INTO t1 (a) VALUES (2||3)'; 525EXECUTE stmt; 526SET sql_mode=ORACLE; 527EXECUTE stmt; 528ALTER TABLE t1 ADD b INT; 529EXECUTE stmt; 530SELECT * FROM t1; 531DROP TABLE t1; 532 533--echo # 534--echo # MDEV-12390 Wrong error line numbers reported with sql_mode=IGNORE_SPACE 535--echo # 536 537SET sql_mode=IGNORE_SPACE; 538DELIMITER $$; 539--error ER_PARSE_ERROR 540CREATE PROCEDURE p1() 541BEGIN 542 SELECT 1+1; 543 syntax error; 544END; 545$$ 546DELIMITER ;$$ 547 548 549SET sql_mode=DEFAULT; 550DELIMITER $$; 551--error ER_PARSE_ERROR 552CREATE PROCEDURE p1() 553BEGIN 554 SELECT 1+1; 555 syntax error; 556END; 557$$ 558DELIMITER ;$$ 559 560--echo # 561--echo # End of 10.2 tests 562--echo # 563 564--echo # 565--echo # Start of 10.3 tests 566--echo # 567 568--echo # 569--echo # MDEV-16471 mysqldump throws "Variable 'sql_mode' can't be set to the value of 'NULL' (1231)" 570--echo # 571 572SET sql_mode='ORACLE,EMPTY_STRING_IS_NULL'; 573SELECT @@sql_mode; 574SELECT '' AS empty; 575SET sql_mode=''; 576SELECT @@sql_mode; 577SET sql_mode=DEFAULT; 578 579--echo # 580--echo # End of 10.3 tests 581--echo # 582