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';