1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
--disable_warnings
drop table if exists t1;
--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;
#
# 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;
SET @@SQL_MODE=@OLD_SQL_MODE;
|
