bool Item_in_subselect::init_left_expr_cache()
{
...
/*
If we use end_[send | write]_group to handle complete rows of the outer
query, make the cache of the left IN operand use Item_field::result_field
instead of Item_field::field. We need this because normally
Cached_item_field uses Item::field to fetch field data, while
copy_ref_key() that copies the left IN operand into a lookup key uses
Item::result_field. In the case end_[send | write]_group result_field is
one row behind field.
*/
end_select= outer_join->join_tab[outer_join->tables-1].next_select;
if (end_select == end_send_group || end_select == end_write_group)
use_result_field= TRUE;
which is not yet set by the time we invoke the subquery. It is not yet set, because we're using the following query plan:
MariaDB [test]> explain SELECT t1.f2 FROM t1
-> STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
-> HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
-> ORDER BY f2;
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------------------+
| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-------------+-------+--------+---------------+------+---------+------+------+---------------------------------+
4 rows in set (0.03 sec)
we first do filesort() over table t1, and then join it with t2. The subquery is evaluated during filesort(), and we don't yet have (*) set at that point.
The problem is with this piece of code:
bool Item_in_ subselect: :init_left_ expr_cache( ) :result_ field item_field uses Item::field to fetch field data, while :result_ field. In the case end_[send | write]_group result_field is >join_tab[ outer_join- >tables- 1].next_ select; result_ field= TRUE;
{
...
/*
If we use end_[send | write]_group to handle complete rows of the outer
query, make the cache of the left IN operand use Item_field:
instead of Item_field::field. We need this because normally
Cached_
copy_ref_key() that copies the left IN operand into a lookup key uses
Item:
one row behind field.
*/
end_select= outer_join-
if (end_select == end_send_group || end_select == end_write_group)
use_
It tries to access
outer_ join->join_ tab[outer_ join->tables- 1].next_ select (*)
which is not yet set by the time we invoke the subquery. It is not yet set, because we're using the following query plan:
MariaDB [test]> explain SELECT t1.f2 FROM t1 ------- -----+- ------+ ------- -+----- ------- ---+--- ---+--- ------+ ------+ ------+ ------- ------- ------- ------- -----+ ------- -----+- ------+ ------- -+----- ------- ---+--- ---+--- ------+ ------+ ------+ ------- ------- ------- ------- -----+ ------- -----+- ------+ ------- -+----- ------- ---+--- ---+--- ------+ ------+ ------+ ------- ------- ------- ------- -----+
-> STRAIGHT_JOIN (t2 JOIN t3 ON t3.f2 = t2.f2 ) ON t3 .f2 = t2 .f2
-> HAVING ('v', 'i') NOT IN (SELECT f2, MIN(f2) FROM t1)
-> ORDER BY f2;
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | PRIMARY | t3 | system | NULL | NULL | NULL | NULL | 1 | Using temporary; Using filesort |
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using join buffer |
| 2 | SUBQUERY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | |
+----+-
4 rows in set (0.03 sec)
we first do filesort() over table t1, and then join it with t2. The subquery is evaluated during filesort(), and we don't yet have (*) set at that point.