Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
-
None
Description
CREATE TABLE t1 (a int, b time, KEY(b), KEY(a)) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (0,'00:00:00'), (4,'00:00:00'), (8,'00:00:08'); |
|
CREATE TABLE t2 (c int); |
|
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 ); |
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 ) ORDER BY b DESC; |
ALTER TABLE t1 DROP INDEX a; |
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 ); |
|
# Cleanup
|
DROP TABLE t1, t2; |
The first query, without ORDER BY, returns one row:
10.2 114476f2 |
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 ); |
b
|
00:00:08
|
The second query, with ORDER BY DESC, returns two rows:
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 ) ORDER BY b DESC; |
b
|
00:00:00
|
00:00:00
|
And the third query, which is the same as the first one but after an index was dropped, returns 3 rows:
ALTER TABLE t1 DROP INDEX a; |
SELECT b FROM t1 HAVING b NOT IN ( SELECT MAX(a) FROM t1 JOIN t2 ); |
b
|
00:00:00
|
00:00:00
|
00:00:08
|
Because of an aggregate function upon an empty result set in subquery and a datatype difference (time vs int), I wouldn't dare claim which of the results is correct. But they can't all be correct at once.