MariaDB [test]> EXPLAIN EXTENDED
|
-> SELECT sq1.f2 FROM t1 AS sq1
|
-> WHERE EXISTS ( SELECT * FROM t1 AS sq2
|
-> WHERE sq1.`pk` IN ( SELECT f1 FROM t1 ) AND sq2.f1 = sq1.f1 );
|
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+
|
| 1 | PRIMARY | sq1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
|
| 2 | DEPENDENT SUBQUERY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | 100.00 | |
|
| 2 | DEPENDENT SUBQUERY | sq2 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; Using join buffer (flat, BNL join) |
|
| 3 | MATERIALIZED | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
|
+------+--------------------+-------------+--------+---------------+--------------+---------+------+------+----------+-------------------------------------------------+
|
4 rows in set, 3 warnings (0.00 sec)
|
|
MariaDB [test]> SHOW WARNINGS;
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1276 | Field or reference 'test.sq1.pk' of SELECT #2 was resolved in SELECT #1 |
|
| Note | 1276 | Field or reference 'test.sq1.f1' of SELECT #2 was resolved in SELECT #1 |
|
| Note | 1003 | select `test`.`sq1`.`f2` AS `f2` from `test`.`t1` `sq1` where <expr_cache><`test`.`sq1`.`f1`,`test`.`sq1`.`pk`>(exists(select 1 from `test`.`t1` `sq2` semi join (`test`.`t1`) where ((`test`.`sq2`.`f1` = `test`.`sq1`.`f1`) and (`test`.`sq1`.`pk` = `test`.`t1`.`f1`)))) |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|