|
|
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.
|