Details
Minor
Description
CREATE TABLE t1 (a INT, KEY(a)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT, c INT) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,6),(2,4); SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ; SET SESSION optimizer_switch = "materialization=off"; SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ; EXPLAIN EXTENDED SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;
MariaDB [test]> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;
Empty set (0.00 sec)
MariaDB [test]> EXPLAIN EXTENDED
-> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;
+------+--------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
| 1 | PRIMARY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using where; Using index |
| 2 | MATERIALIZED | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using index |
| 2 | MATERIALIZED | t2x | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using join buffer (flat, BNL join) |
| 2 | MATERIALIZED | t2y | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (incremental, BNL join) |
+------+--------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
MariaDB [test]> SHOW WARNINGS;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` `t2x` join `test`.`t2` `t2y` where (`test`.`t2y`.`c` = `test`.`t2x`.`b`) ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where ((`test`.`t1`.`a` = `<subquery2>`.`MIN(a)`)))))))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [test]> SET SESSION optimizer_switch = "materialization=off";
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;
+------+
| a |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
MariaDB [test]> EXPLAIN EXTENDED
-> SELECT * FROM t1 WHERE a != ALL ( SELECT MIN(a) FROM t1, t2 t2x, t2 t2y WHERE t2y.c = t2x.b ) ;
+------+--------------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
| 1 | PRIMARY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t1 | index | NULL | a | 5 | NULL | 2 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | t2x | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using join buffer (flat, BNL join) |
| 2 | DEPENDENT SUBQUERY | t2y | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (incremental, BNL join) |
+------+--------------------+-------+-------+---------------+------+---------+------+------+----------+--------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
MariaDB [test]> SHOW WARNINGS;
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(select min(`test`.`t1`.`a`) from `test`.`t1` join `test`.`t2` `t2x` join `test`.`t2` `t2y` where (`test`.`t2y`.`c` = `test`.`t2x`.`b`) having trigcond((<cache>(`test`.`t1`.`a`) = <cache>(<ref_null_helper>(min(`test`.`t1`.`a`)))))))))) |
+-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
5.5 commit fdd6c111c254c5044cd9b6c2f7e4d0c74f427a79.
Also reproducible on previous 5.5 releases, 10.0, MySQL 5.6, 5.7 (only MySQL returns 2 rows where MariaDB returns 1).
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
After fixing, also check test case from
MDEV-7602. It is not simplified and hence ugly, but it should be obvious whether it's fixed or not. If not, please re-openMDEV-7602and assign it to me.