Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
5.1.67, 5.2.14, 5.3.12, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
5.5.50, 10.1.29
Description
Test case |
DROP TABLE IF EXISTS t; |
|
CREATE TABLE t (pk INT PRIMARY KEY, i INT) ENGINE=MyISAM; |
INSERT INTO t VALUES (1,8),(2,0),(3,9),(4,3),(5,4); |
|
( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 ) |
UNION |
( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 ) |
;
|
Each part of the query is supposed to choose the same 2 rows with minimal i values, and then UNION should remove duplicates and return 2 rows. But the result is different:
MariaDB [test]> ( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 ) |
-> UNION |
-> ( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 ) |
-> ;
|
+------+ |
| i |
|
+------+ |
| 0 |
|
| 3 |
|
| 4 |
|
| 8 |
|
+------+ |
4 rows in set (0.00 sec) |
EXPLAIN on MariaDB 5.5 |
MariaDB [test]> EXPLAIN EXTENDED ( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 ) UNION ( SELECT t1.i FROM t AS t1, t AS t2 WHERE t1.pk = t2.pk ORDER BY i LIMIT 2 ); |
+------+--------------+------------+--------+---------------+---------+---------+------------+------+----------+----------------+ |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | |
+------+--------------+------------+--------+---------------+---------+---------+------------+------+----------+----------------+ |
| 1 | PRIMARY | t1 | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | Using filesort | |
| 1 | PRIMARY | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.pk | 1 | 100.00 | Using index | |
| 2 | UNION | t1 | ALL | PRIMARY | NULL | NULL | NULL | 5 | 100.00 | Using filesort | |
| 2 | UNION | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.pk | 1 | 100.00 | Using index | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | | |
+------+--------------+------------+--------+---------------+---------+---------+------------+------+----------+----------------+ |
5 rows in set, 1 warning (0.00 sec) |
|
| Note | 1003 | (select `test`.`t1`.`i` AS `i` from `test`.`t` `t1` join `test`.`t` `t2` where (`test`.`t2`.`pk` = `test`.`t1`.`pk`) order by `test`.`t1`.`i` limit 2) union (select `test`.`t1`.`i` AS `i` from `test`.`t` `t1` join `test`.`t` `t2` where (`test`.`t2`.`pk` = `test`.`t1`.`pk`) order by `test`.`t1`.`i` limit 2) | |
Reproducible on MariaDB 5.1 - 10.1, on old releases as well.
Not reproducible on MySQL 5.5, 5.6.
Attachments
Issue Links
- relates to
-
MDEV-10172 UNION query returns incorrect rows outside conditional evaluation
- Closed