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
|
set @save_storage_engine= @@storage_engine;
set storage_engine=InnoDB;
#
# Bug#42580 - Innodb's ORDER BY ..LIMIT returns no rows for
# null-safe operator <=> NULL
#
CREATE TABLE t1(
c1 DATE NOT NULL,
c2 DATE NULL,
c3 DATETIME,
c4 TIMESTAMP,
PRIMARY KEY(c1),
UNIQUE(c2)
);
INSERT INTO t1 VALUES('0000-00-00', '0000-00-00', '2008-01-04', '2008-01-05');
INSERT INTO t1 VALUES('2007-05-25', '2007-05-25', '2007-05-26', '2007-05-26');
INSERT INTO t1 VALUES('2008-01-01', NULL , '2008-01-02', '2008-01-03');
INSERT INTO t1 VALUES('2008-01-17', NULL , NULL , '2009-01-29');
INSERT INTO t1 VALUES('2009-01-29', '2009-01-29', '2009-01-29', '2009-01-29');
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2;
c1 c2 c3 c4
2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00
2008-01-17 NULL NULL 2009-01-29 00:00:00
SELECT * FROM t1 WHERE c2 <=> NULL ORDER BY c1,c2 LIMIT 2;
c1 c2 c3 c4
2008-01-01 NULL 2008-01-02 00:00:00 2008-01-03 00:00:00
2008-01-17 NULL NULL 2009-01-29 00:00:00
DROP TABLE t1;
set storage_engine= @save_storage_engine;
|
