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
126
127
128
129
130
|
drop table if exists t1;
create table t1 (id int auto_increment primary key not null, mydate date not null);
insert into t1 values (0,"2002-05-01"),(0,"2002-05-01"),(0,"2002-05-01");
flush tables;
select * from t1 where isnull(to_days(mydate));
id mydate
drop table t1;
#
# Bug#53933 crash when using uncacheable subquery in the having clause of outer query
#
CREATE TABLE t1 (f1 INT);
INSERT INTO t1 VALUES (0),(0);
SELECT ISNULL((SELECT GET_LOCK('Bug#53933', 0) FROM t1 GROUP BY f1)) AS f2
FROM t1 GROUP BY f1 HAVING f2 = f2;
f2
0
SELECT RELEASE_LOCK('Bug#53933');
RELEASE_LOCK('Bug#53933')
1
DROP TABLE t1;
End of 5.0 tests
CREATE TABLE t1 (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(id));
INSERT INTO t1( id ) VALUES ( NULL );
SELECT t1.id FROM t1 WHERE (id is not null and id is null );
id
DROP TABLE t1;
# End of 5.1 tests
#
# MDEV-14911: IS NULL for field from mergeable view
#
CREATE TABLE t1 (d1 datetime NOT NULL);
INSERT INTO t1 VALUES
('0000-00-00 00:00:00'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');
SELECT * FROM t1;
d1
0000-00-00 00:00:00
0000-00-00 00:00:00
1979-09-03 20:49:36
SELECT * FROM t1 WHERE d1 IS NULL;
d1
0000-00-00 00:00:00
0000-00-00 00:00:00
EXPLAIN EXTENDED SELECT * FROM t1 WHERE d1 IS NULL;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` = 0
SELECT count(*) FROM t1 WHERE d1 IS NULL;
count(*)
2
CREATE VIEW v1 AS (SELECT * FROM t1);
SELECT * FROM v1;
d1
0000-00-00 00:00:00
0000-00-00 00:00:00
1979-09-03 20:49:36
SELECT * FROM v1 WHERE d1 IS NULL;
d1
0000-00-00 00:00:00
0000-00-00 00:00:00
EXPLAIN EXTENDED SELECT * FROM v1 WHERE d1 IS NULL;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where
Warnings:
Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` = 0
SELECT count(*) FROM v1 WHERE d1 IS NULL;
count(*)
2
SET @save_optimizer_switch=@@optimizer_switch;
SET SESSION optimizer_switch='derived_merge=off';
SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL;
count(*)
2
SET SESSION optimizer_switch='derived_merge=on';
SELECT count(*) FROM ( SELECT * FROM t1 ) AS a1 WHERE d1 IS NULL;
count(*)
2
SET optimizer_switch=@save_optimizer_switch;
CREATE TABLE t2 (d1 datetime NOT NULL);
INSERT INTO t2 VALUES
('1980-09-03 20:49:36'), ('0000-00-00 00:00:00'), ('1979-09-03 20:49:36');
SELECT * FROM t2 LEFT JOIN t1 ON t2.d1=t1.d1 WHERE t1.d1 IS NULL;
d1 d1
0000-00-00 00:00:00 0000-00-00 00:00:00
0000-00-00 00:00:00 0000-00-00 00:00:00
1980-09-03 20:49:36 NULL
EXPLAIN EXTENDED
SELECT * FROM t2 LEFT JOIN t1 ON t2.d1=t1.d1 WHERE t1.d1 IS NULL;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`d1` AS `d1`,`test`.`t1`.`d1` AS `d1` from `test`.`t2` left join `test`.`t1` on(`test`.`t1`.`d1` = `test`.`t2`.`d1`) where `test`.`t1`.`d1` = 0 or `test`.`t1`.`d1` is null
SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL;
d1 d1
0000-00-00 00:00:00 0000-00-00 00:00:00
0000-00-00 00:00:00 0000-00-00 00:00:00
1980-09-03 20:49:36 NULL
EXPLAIN EXTENDED
SELECT * FROM t2 LEFT JOIN v1 ON t2.d1=v1.d1 WHERE v1.d1 IS NULL;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 3 100.00
1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
Warnings:
Note 1003 select `test`.`t2`.`d1` AS `d1`,`test`.`t1`.`d1` AS `d1` from `test`.`t2` left join (`test`.`t1`) on(`test`.`t1`.`d1` = `test`.`t2`.`d1`) where `test`.`t1`.`d1` = 0 or `test`.`t1`.`d1` is null
DROP VIEW v1;
DROP TABLE t1,t2;
#
# MDEV-15475: Assertion `!table || (!table->read_set ||
# bitmap_is_set(table->read_set, field_index))'
# failed on EXPLAIN EXTENDED with constant table and view
#
CREATE TABLE t1 (pk INT PRIMARY KEY) ENGINE=MyISAM;
CREATE VIEW v1 AS SELECT * FROM t1;
INSERT INTO t1 VALUES (1);
EXPLAIN EXTENDED SELECT ISNULL(pk) FROM v1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select /*always not null*/ 1 is null AS `ISNULL(pk)` from dual
EXPLAIN EXTENDED SELECT IFNULL(pk,0) FROM v1;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 SIMPLE t1 system NULL NULL NULL NULL 1 100.00
Warnings:
Note 1003 select ifnull(1,0) AS `IFNULL(pk,0)` from dual
DROP VIEW v1;
DROP TABLE t1;
#
# End of 5.5 tests
#
|
