~drizzle-trunk/drizzle/development

1 by brian
clean slate
1
# Initialise
2
--disable_warnings
3
drop table if exists t1, t2;
4
--enable_warnings
5
6
#
7
# Testing of NULL in a lot of different places
8
#
9
10
select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null;
11
explain extended select null,\N,isnull(null),isnull(1/0),isnull(1/0 = null),ifnull(null,1),ifnull(null,"TRUE"),ifnull("TRUE","ERROR"),1/0 is null,1 is not null;
12
select 1 | NULL,1 & NULL,1+NULL,1-NULL;
13
select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,IFNULL(NULL,1) | 0;
14
select strcmp("a",NULL),(1<NULL)+0.0,NULL regexp "a",null like "a%","a%" like null;
15
select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1);
16
select repeat("a",0),repeat("ab",5+5),repeat("ab",-1),reverse(NULL);
17
select field(NULL,"a","b","c");
18
select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null;
19
explain extended select 2 between null and 1,2 between 3 AND NULL,NULL between 1 and 2,2 between NULL and 3, 2 between 1 AND null;
20
SELECT NULL AND NULL, 1 AND NULL, NULL AND 1, NULL OR NULL, 0 OR NULL, NULL OR 0;
21
SELECT (NULL OR NULL) IS NULL;
22
select NULL AND 0, 0 and NULL;
23
select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton("");
24
explain extended select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton("");
25
26
create table t1 (x int);
27
insert into t1 values (null);
28
select * from t1 where x != 0;
29
drop table t1;
30
31
#
32
# Test problem med index on NULL columns and testing with =NULL;
33
#
34
35
CREATE TABLE t1 (
36
  indexed_field int default NULL,
37
  KEY indexed_field (indexed_field)
38
);
39
INSERT INTO t1 VALUES (NULL),(NULL);
40
SELECT * FROM t1 WHERE indexed_field=NULL;
41
SELECT * FROM t1 WHERE indexed_field IS NULL;
42
SELECT * FROM t1 WHERE indexed_field<=>NULL;
43
DROP TABLE t1;
44
45
#
46
# Testing of IFNULL
47
#
48
create table t1 (a int, b int) engine=myisam;
49
insert into t1 values(20,null);
50
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
51
t2.b=t3.a;
52
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
53
t2.b=t3.a order by 1;
54
insert into t1 values(10,null);
55
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on
56
t2.b=t3.a order by 1;
57
drop table t1;
58
59
#
60
# Test inserting and updating with NULL
61
#
62
CREATE TABLE t1 (a varchar(16) NOT NULL default '', b smallint(6) NOT NULL default 0, c datetime NOT NULL default '0000-00-00 00:00:00', d smallint(6) NOT NULL default 0);
63
INSERT INTO t1 SET a = "", d= "2003-01-14 03:54:55";
64
--error 1048
65
UPDATE t1 SET d=1/NULL;
66
--error 1048
67
UPDATE t1 SET d=NULL;
68
--error 1048
69
INSERT INTO t1 (a) values (null);
70
--error 1048
71
INSERT INTO t1 (a) values (1/null);
72
INSERT INTO t1 (a) values (null),(null);
73
--error 1048
74
INSERT INTO t1 (b) values (null);
75
--error 1048
76
INSERT INTO t1 (b) values (1/null);
77
INSERT INTO t1 (b) values (null),(null);
78
--error 1048
79
INSERT INTO t1 (c) values (null);
80
--error 1048
81
INSERT INTO t1 (c) values (1/null);
82
INSERT INTO t1 (c) values (null),(null);
83
--error 1048
84
INSERT INTO t1 (d) values (null);
85
--error 1048
86
INSERT INTO t1 (d) values (1/null);
87
INSERT INTO t1 (d) values (null),(null);
88
select * from t1;
89
drop table t1;
90
91
#
92
# Test to check elimination of IS NULL predicate for a non-nullable attribute
93
# (bug #1990)  
94
#
95
create table t1 (a int not null, b int not null, index idx(a));
96
insert into t1 values
97
  (1,1), (2,2), (3,3), (4,4), (5,5), (6,6),
98
  (7,7), (8,8), (9,9), (10,10), (11,11), (12,12);
99
explain select * from t1 where a between 2 and 3;
100
explain select * from t1 where a between 2 and 3 or b is null;
101
drop table t1;
102
select cast(NULL as signed);
103
104
#
105
# IS NULL is unable to use index in range if column is declared not null
106
# (Bug #4256)
107
#
108
create table t1(i int, key(i));
109
insert into t1 values(1);
110
insert into t1 select i*2 from t1;
111
insert into t1 select i*2 from t1;
112
insert into t1 select i*2 from t1;
113
insert into t1 select i*2 from t1;
114
insert into t1 select i*2 from t1;
115
insert into t1 select i*2 from t1;
116
insert into t1 select i*2 from t1;
117
insert into t1 select i*2 from t1;
118
insert into t1 select i*2 from t1;
119
insert into t1 values(null);
120
explain select * from t1 where i=2 or i is null;
121
select count(*) from t1 where i=2 or i is null;
122
alter table t1 change i i int not null;
123
explain select * from t1 where i=2 or i is null;
124
select count(*) from t1 where i=2 or i is null;
125
drop table t1;
126
127
#
128
# NULL has its own type BINARY(0) by default.
129
# But NULL should be weaker than a constant
130
# when mixing charsets/collations
131
#
132
set names latin2;
133
# Check that result type is taken from a non-null string
134
create table t1 select
135
  null as c00,
136
  if(1, null, 'string') as c01,
137
  if(0, null, 'string') as c02,
138
  ifnull(null, 'string') as c03,
139
  ifnull('string', null) as c04,
140
  case when 0 then null else 'string' end as c05,
141
  case when 1 then null else 'string' end as c06,
142
  coalesce(null, 'string') as c07,
143
  coalesce('string', null) as c08,
144
  least('string',null) as c09,
145
  least(null, 'string') as c10,
146
  greatest('string',null) as c11,
147
  greatest(null, 'string') as c12,
148
  nullif('string', null) as c13,
149
  nullif(null, 'string') as c14,
150
  trim('string' from null) as c15,
151
  trim(null from 'string') as c16,
152
  substring_index('string', null, 1) as c17,
153
  substring_index(null, 'string', 1) as c18,
154
  elt(1, null, 'string') as c19,
155
  elt(1, 'string', null) as c20,
156
  concat('string', null) as c21,
157
  concat(null, 'string') as c22,
158
  concat_ws('sep', 'string', null) as c23,
159
  concat_ws('sep', null, 'string') as c24,
160
  concat_ws(null, 'string', 'string') as c25,
161
  make_set(3, 'string', null) as c26,
162
  make_set(3, null, 'string') as c27,
163
  export_set(3, null, 'off', 'sep') as c29,
164
  export_set(3, 'on', null, 'sep') as c30,
165
  export_set(3, 'on', 'off', null) as c31,
166
  replace(null, 'from', 'to') as c32,
167
  replace('str', null, 'to') as c33,
168
  replace('str', 'from', null) as c34,
169
  insert('str', 1, 2, null) as c35,
170
  insert(null, 1, 2, 'str') as c36,
171
  lpad('str', 10, null) as c37,
172
  rpad(null, 10, 'str') as c38;
173
  
174
show create table t1;
175
drop table t1;
176
177
#
178
# Check that comparison is done according to
179
# non-null string collation, i.e. case insensitively,
180
# rather than according to NULL's collation, i.e. case sensitively
181
#
182
# in field
183
select 
184
  case 'str' when 'STR' then 'str' when null then 'null' end as c01,
185
  case 'str' when null then 'null' when 'STR' then 'str' end as c02,
186
  field(null, 'str1', 'str2') as c03,
187
  field('str1','STR1', null) as c04,
188
  field('str1', null, 'STR1') as c05,
189
  'string' in ('STRING', null) as c08,
190
  'string' in (null, 'STRING') as c09;
191
192
# Restore charset to the default value.
193
set names latin1;
194
195
#
196
# Bug#19145: mysqld crashes if you set the default value of an enum field to NULL
197
#
198
create table bug19145a (e enum('a','b','c')          default 'b' , s set('x', 'y', 'z')          default 'y' ) engine=MyISAM;
199
create table bug19145b (e enum('a','b','c')          default null, s set('x', 'y', 'z')          default null) engine=MyISAM;
200
201
create table bug19145c (e enum('a','b','c') not null default 'b' , s set('x', 'y', 'z') not null default 'y' ) engine=MyISAM;
202
203
# Invalid default value for 's'
204
--error 1067
205
create table bug19145setnotnulldefaultnull (e enum('a','b','c')          default null, s set('x', 'y', 'z') not null default null) engine=MyISAM;
206
207
# Invalid default value for 'e'
208
--error 1067
209
create table bug19145enumnotnulldefaultnull (e enum('a','b','c') not null default null, s set('x', 'y', 'z')          default null) engine=MyISAM;
210
211
alter table bug19145a alter column e set default null;
212
alter table bug19145a alter column s set default null;
213
alter table bug19145a add column (i int);
214
215
alter table bug19145b alter column e set default null;
216
alter table bug19145b alter column s set default null;
217
alter table bug19145b add column (i int);
218
219
# Invalid default value for 'e'
220
--error 1067
221
alter table bug19145c alter column e set default null;
222
223
# Invalid default value for 's'
224
--error 1067
225
alter table bug19145c alter column s set default null;
226
alter table bug19145c add column (i int);
227
228
show create table bug19145a;
229
show create table bug19145b;
230
show create table bug19145c;
231
232
drop table bug19145a;
233
drop table bug19145b;
234
drop table bug19145c;
235
236
--echo # End of 4.1 tests
237
238
--echo #
239
--echo # Bug #31471: decimal_bin_size: Assertion `scale >= 0 &&
240
--echo #             precision > 0 && scale <= precision'
241
--echo #
242
243
CREATE TABLE t1 (a DECIMAL (1, 0) ZEROFILL, b DECIMAL (1, 0) ZEROFILL);
244
INSERT INTO t1 (a, b) VALUES (0, 0);
245
246
CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1;
247
DESCRIBE t2;
248
DROP TABLE t2;
249
250
CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1;
251
DESCRIBE t2;
252
DROP TABLE t2;
253
254
CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1;
255
DESCRIBE t2;
256
257
DROP TABLE t1, t2;
258
259
--echo # End of 5.0 tests