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 |
||
1812.4.2
by Brian Aker
Fix issue with divide by zero not being an error. |
10 |
--error ER_DIVISION_BY_ZERO
|
1
by brian
clean slate |
11 |
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; |
1812.4.2
by Brian Aker
Fix issue with divide by zero not being an error. |
12 |
--error ER_DIVISION_BY_ZERO
|
1
by brian
clean slate |
13 |
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; |
685.4.1
by Jay Pipes
Enabled the null.test. |
14 |
select CONCAT(1, NULL),1+NULL,1-NULL; |
15 |
select NULL=NULL,NULL<>NULL,IFNULL(NULL,1.1)+0,CONCAT(IFNULL(NULL,1), 0); |
|
16 |
select strcmp("a",NULL),(1<NULL)+0.0,null like "a%","a%" like null; |
|
1
by brian
clean slate |
17 |
select concat("a",NULL),replace(NULL,"a","b"),replace("string","i",NULL),replace("string",NULL,"i"),insert("abc",1,1,NULL),left(NULL,1); |
18 |
select repeat("a",0),repeat("ab",5+5),repeat("ab",-1),reverse(NULL); |
|
19 |
select field(NULL,"a","b","c"); |
|
20 |
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; |
|
21 |
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; |
|
22 |
SELECT NULL AND NULL, 1 AND NULL, NULL AND 1, NULL OR NULL, 0 OR NULL, NULL OR 0; |
|
23 |
SELECT (NULL OR NULL) IS NULL; |
|
24 |
select NULL AND 0, 0 and NULL; |
|
685.4.1
by Jay Pipes
Enabled the null.test. |
25 |
# @TODO Move to functions/inet_ntoa.test when INET_NTOA function is |
26 |
# implemented |
|
27 |
# select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton(""); |
|
28 |
# explain extended select inet_ntoa(null),inet_aton(null),inet_aton("122.256"),inet_aton("122.226."),inet_aton(""); |
|
1
by brian
clean slate |
29 |
|
30 |
create table t1 (x int); |
|
31 |
insert into t1 values (null); |
|
32 |
select * from t1 where x != 0; |
|
33 |
drop table t1; |
|
34 |
||
35 |
#
|
|
36 |
# Test problem med index on NULL columns and testing with =NULL; |
|
37 |
#
|
|
38 |
||
39 |
CREATE TABLE t1 ( |
|
40 |
indexed_field int default NULL, |
|
41 |
KEY indexed_field (indexed_field) |
|
42 |
);
|
|
43 |
INSERT INTO t1 VALUES (NULL),(NULL); |
|
44 |
SELECT * FROM t1 WHERE indexed_field=NULL; |
|
45 |
SELECT * FROM t1 WHERE indexed_field IS NULL; |
|
46 |
SELECT * FROM t1 WHERE indexed_field<=>NULL; |
|
47 |
DROP TABLE t1; |
|
48 |
||
49 |
#
|
|
50 |
# Testing of IFNULL |
|
51 |
#
|
|
685.4.1
by Jay Pipes
Enabled the null.test. |
52 |
create table t1 (a int, b int); |
1
by brian
clean slate |
53 |
insert into t1 values(20,null); |
54 |
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on |
|
55 |
t2.b=t3.a; |
|
56 |
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on |
|
57 |
t2.b=t3.a order by 1; |
|
58 |
insert into t1 values(10,null); |
|
59 |
select t2.b, ifnull(t2.b,"this is null") from t1 as t2 left join t1 as t3 on |
|
60 |
t2.b=t3.a order by 1; |
|
61 |
drop table t1; |
|
62 |
||
63 |
#
|
|
64 |
# Test inserting and updating with NULL |
|
65 |
#
|
|
873.1.2
by Jay Pipes
Fixed Field_datetime to never accept any bad datetimes as a string. This broke |
66 |
CREATE TABLE t1 (a varchar(16) NOT NULL default '', b int NOT NULL default 0, c datetime NOT NULL default '2009-02-10 00:00:00', d int NOT NULL default 0); |
685.4.1
by Jay Pipes
Enabled the null.test. |
67 |
|
68 |
# Test INSERT with NULL |
|
69 |
||
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
70 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
71 |
INSERT INTO t1 (a) values (null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
72 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
73 |
INSERT INTO t1 (a) values (1/null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
74 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
75 |
INSERT INTO t1 (a) values (null),(null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
76 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
77 |
INSERT INTO t1 (b) values (null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
78 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
79 |
INSERT INTO t1 (b) values (1/null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
80 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
81 |
INSERT INTO t1 (b) values (null),(null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
82 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
83 |
INSERT INTO t1 (c) values (null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
84 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
85 |
INSERT INTO t1 (c) values (1/null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
86 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
87 |
INSERT INTO t1 (c) values (null),(null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
88 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
89 |
INSERT INTO t1 (d) values (null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
90 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
91 |
INSERT INTO t1 (d) values (1/null); |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
92 |
--error ER_BAD_NULL_ERROR
|
1
by brian
clean slate |
93 |
INSERT INTO t1 (d) values (null),(null); |
685.4.1
by Jay Pipes
Enabled the null.test. |
94 |
|
95 |
# Test UPDATE with NULLs |
|
96 |
||
97 |
# The following should not error since there |
|
98 |
# are no rows in the table. |
|
99 |
UPDATE t1 SET d= NULL; |
|
100 |
||
101 |
# Insert a default row in order to test UPDATE to NULL error |
|
102 |
INSERT INTO t1 VALUES (); |
|
103 |
||
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
104 |
--error ER_BAD_NULL_ERROR
|
685.4.1
by Jay Pipes
Enabled the null.test. |
105 |
UPDATE t1 SET a=1/NULL; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
106 |
--error ER_BAD_NULL_ERROR
|
685.4.1
by Jay Pipes
Enabled the null.test. |
107 |
UPDATE t1 SET a=NULL; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
108 |
--error ER_BAD_NULL_ERROR
|
685.4.1
by Jay Pipes
Enabled the null.test. |
109 |
UPDATE t1 SET b=NULL; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
110 |
--error ER_BAD_NULL_ERROR
|
685.4.1
by Jay Pipes
Enabled the null.test. |
111 |
UPDATE t1 SET c=NULL; |
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
112 |
--error ER_BAD_NULL_ERROR
|
685.4.1
by Jay Pipes
Enabled the null.test. |
113 |
UPDATE t1 SET d=NULL; |
114 |
||
115 |
truncate table t1; |
|
116 |
||
117 |
# Test for LOAD DATA INFILE and check for NULL handling |
|
118 |
# Error produced should be 1263, which is almost the same |
|
119 |
# as 1048, only gives a "row" number. |
|
120 |
# @TODO Is there really a reason for a separate error |
|
121 |
# just for LOAD DATA INFILE? |
|
1878.7.1
by patrick crews
Updated tests to use variable vardir rather than a hard-coded one |
122 |
--replace_result $DRIZZLETEST_VARDIR DRIZZLETEST_VARDIR
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
123 |
--error ER_WARN_NULL_TO_NOTNULL
|
1878.7.1
by patrick crews
Updated tests to use variable vardir rather than a hard-coded one |
124 |
eval LOAD DATA INFILE '$DRIZZLETEST_VARDIR/std_data_ln/null_test.txt' INTO TABLE t1 FIELDS ENCLOSED BY '"'; |
685.4.1
by Jay Pipes
Enabled the null.test. |
125 |
|
1
by brian
clean slate |
126 |
drop table t1; |
127 |
||
685.4.1
by Jay Pipes
Enabled the null.test. |
128 |
|
1
by brian
clean slate |
129 |
#
|
130 |
# Test to check elimination of IS NULL predicate for a non-nullable attribute |
|
131 |
# (bug #1990) |
|
132 |
#
|
|
133 |
create table t1 (a int not null, b int not null, index idx(a)); |
|
134 |
insert into t1 values |
|
135 |
(1,1), (2,2), (3,3), (4,4), (5,5), (6,6), |
|
136 |
(7,7), (8,8), (9,9), (10,10), (11,11), (12,12); |
|
137 |
explain select * from t1 where a between 2 and 3; |
|
138 |
explain select * from t1 where a between 2 and 3 or b is null; |
|
139 |
drop table t1; |
|
140 |
||
141 |
#
|
|
142 |
# IS NULL is unable to use index in range if column is declared not null |
|
143 |
# (Bug #4256) |
|
144 |
#
|
|
685.4.1
by Jay Pipes
Enabled the null.test. |
145 |
#create table t1(i int, key(i)); |
146 |
#insert into t1 values(1); |
|
147 |
#insert into t1 select i*2 from t1; |
|
148 |
#insert into t1 select i*2 from t1; |
|
149 |
#insert into t1 select i*2 from t1; |
|
150 |
#insert into t1 select i*2 from t1; |
|
151 |
#insert into t1 select i*2 from t1; |
|
152 |
#insert into t1 select i*2 from t1; |
|
153 |
#insert into t1 select i*2 from t1; |
|
154 |
#insert into t1 select i*2 from t1; |
|
155 |
#insert into t1 select i*2 from t1; |
|
156 |
#insert into t1 values(null); |
|
157 |
#explain select * from t1 where i=2 or i is null; |
|
158 |
#select count(*) from t1 where i=2 or i is null; |
|
159 |
# @TODO Fails with error 1265 on line 128. Bug? |
|
160 |
#alter table t1 change i i int not null; |
|
161 |
#explain select * from t1 where i=2 or i is null; |
|
162 |
#select count(*) from t1 where i=2 or i is null; |
|
163 |
#drop table t1; |
|
1
by brian
clean slate |
164 |
|
165 |
#
|
|
166 |
# NULL has its own type BINARY(0) by default. |
|
167 |
# But NULL should be weaker than a constant |
|
168 |
# when mixing charsets/collations |
|
169 |
#
|
|
170 |
# Check that result type is taken from a non-null string |
|
171 |
create table t1 select |
|
172 |
null as c00, |
|
173 |
if(1, null, 'string') as c01, |
|
174 |
if(0, null, 'string') as c02, |
|
175 |
ifnull(null, 'string') as c03, |
|
176 |
ifnull('string', null) as c04, |
|
177 |
case when 0 then null else 'string' end as c05, |
|
178 |
case when 1 then null else 'string' end as c06, |
|
179 |
coalesce(null, 'string') as c07, |
|
180 |
coalesce('string', null) as c08, |
|
181 |
least('string',null) as c09, |
|
182 |
least(null, 'string') as c10, |
|
183 |
greatest('string',null) as c11, |
|
184 |
greatest(null, 'string') as c12, |
|
185 |
nullif('string', null) as c13, |
|
186 |
nullif(null, 'string') as c14, |
|
187 |
trim('string' from null) as c15, |
|
188 |
trim(null from 'string') as c16, |
|
189 |
substring_index('string', null, 1) as c17, |
|
190 |
substring_index(null, 'string', 1) as c18, |
|
191 |
elt(1, null, 'string') as c19, |
|
192 |
elt(1, 'string', null) as c20, |
|
193 |
concat('string', null) as c21, |
|
194 |
concat(null, 'string') as c22, |
|
195 |
concat_ws('sep', 'string', null) as c23, |
|
196 |
concat_ws('sep', null, 'string') as c24, |
|
197 |
concat_ws(null, 'string', 'string') as c25, |
|
198 |
make_set(3, 'string', null) as c26, |
|
199 |
make_set(3, null, 'string') as c27, |
|
200 |
export_set(3, null, 'off', 'sep') as c29, |
|
201 |
export_set(3, 'on', null, 'sep') as c30, |
|
202 |
export_set(3, 'on', 'off', null) as c31, |
|
203 |
replace(null, 'from', 'to') as c32, |
|
204 |
replace('str', null, 'to') as c33, |
|
205 |
replace('str', 'from', null) as c34, |
|
206 |
insert('str', 1, 2, null) as c35, |
|
207 |
insert(null, 1, 2, 'str') as c36, |
|
208 |
lpad('str', 10, null) as c37, |
|
209 |
rpad(null, 10, 'str') as c38; |
|
210 |
||
942.3.1
by Vladimir Kolesnikov
test generalizations |
211 |
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
|
1
by brian
clean slate |
212 |
show create table t1; |
213 |
drop table t1; |
|
214 |
||
215 |
#
|
|
216 |
# Check that comparison is done according to |
|
217 |
# non-null string collation, i.e. case insensitively, |
|
218 |
# rather than according to NULL's collation, i.e. case sensitively |
|
219 |
#
|
|
220 |
# in field
|
|
221 |
select
|
|
222 |
case 'str' when 'STR' then 'str' when null then 'null' end as c01, |
|
223 |
case 'str' when null then 'null' when 'STR' then 'str' end as c02, |
|
224 |
field(null, 'str1', 'str2') as c03, |
|
225 |
field('str1','STR1', null) as c04, |
|
226 |
field('str1', null, 'STR1') as c05, |
|
227 |
'string' in ('STRING', null) as c08, |
|
228 |
'string' in (null, 'STRING') as c09; |
|
229 |
||
230 |
#
|
|
231 |
# Bug#19145: mysqld crashes if you set the default value of an enum field to NULL
|
|
232 |
#
|
|
685.4.1
by Jay Pipes
Enabled the null.test. |
233 |
#create table bug19145a (e enum('a','b','c') default 'b' , s set('x', 'y', 'z') default 'y' ) engine=MyISAM; |
234 |
#create table bug19145b (e enum('a','b','c') default null, s set('x', 'y', 'z') default null) engine=MyISAM; |
|
235 |
||
236 |
#create table bug19145c (e enum('a','b','c') not null default 'b' , s set('x', 'y', 'z') not null default 'y' ) engine=MyISAM; |
|
237 |
||
238 |
# Invalid default value for 's' |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
239 |
#--error ER_INVALID_DEFAULT
|
685.4.1
by Jay Pipes
Enabled the null.test. |
240 |
#create table bug19145setnotnulldefaultnull (e enum('a','b','c') default null, s set('x', 'y', 'z') not null default null) engine=MyISAM; |
241 |
||
242 |
# Invalid default value for 'e' |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
243 |
#--error ER_INVALID_DEFAULT
|
685.4.1
by Jay Pipes
Enabled the null.test. |
244 |
#create table bug19145enumnotnulldefaultnull (e enum('a','b','c') not null default null, s set('x', 'y', 'z') default null) engine=MyISAM; |
245 |
||
246 |
#alter table bug19145a alter column e set default null;
|
|
247 |
#alter table bug19145a alter column s set default null;
|
|
248 |
#alter table bug19145a add column (i int);
|
|
249 |
||
250 |
#alter table bug19145b alter column e set default null;
|
|
251 |
#alter table bug19145b alter column s set default null;
|
|
252 |
#alter table bug19145b add column (i int);
|
|
253 |
||
254 |
# Invalid default value for 'e' |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
255 |
#--error ER_INVALID_DEFAULT
|
685.4.1
by Jay Pipes
Enabled the null.test. |
256 |
#alter table bug19145c alter column e set default null;
|
257 |
||
258 |
# Invalid default value for 's' |
|
1731.3.1
by Lee Bieber
change tests to use enum values instead of error numbers |
259 |
#--error ER_INVALID_DEFAULT
|
685.4.1
by Jay Pipes
Enabled the null.test. |
260 |
#alter table bug19145c alter column s set default null;
|
261 |
#alter table bug19145c add column (i int);
|
|
262 |
||
263 |
#show create table bug19145a;
|
|
264 |
#show create table bug19145b;
|
|
265 |
#show create table bug19145c;
|
|
266 |
||
267 |
#drop table bug19145a;
|
|
268 |
#drop table bug19145b;
|
|
269 |
#drop table bug19145c;
|
|
1
by brian
clean slate |
270 |
|
271 |
--echo # End of 4.1 tests
|
|
272 |
||
273 |
--echo #
|
|
274 |
--echo # Bug #31471: decimal_bin_size: Assertion `scale >= 0 &&
|
|
275 |
--echo # precision > 0 && scale <= precision'
|
|
276 |
--echo #
|
|
277 |
||
685.4.1
by Jay Pipes
Enabled the null.test. |
278 |
CREATE TABLE t1 (a DECIMAL (1, 0) , b DECIMAL (1, 0) ); |
1
by brian
clean slate |
279 |
INSERT INTO t1 (a, b) VALUES (0, 0); |
280 |
||
281 |
CREATE TABLE t2 SELECT IFNULL(a, b) FROM t1; |
|
282 |
DESCRIBE t2; |
|
283 |
DROP TABLE t2; |
|
284 |
||
285 |
CREATE TABLE t2 SELECT IFNULL(a, NULL) FROM t1; |
|
286 |
DESCRIBE t2; |
|
287 |
DROP TABLE t2; |
|
288 |
||
289 |
CREATE TABLE t2 SELECT IFNULL(NULL, b) FROM t1; |
|
290 |
DESCRIBE t2; |
|
291 |
||
292 |
DROP TABLE t1, t2; |
|
293 |
||
294 |
--echo # End of 5.0 tests
|