Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
Jessie x86_64, gcc (Debian 4.9.2-10) 4.9.2, cmake version 3.0.2
-
10.2.2-3
Description
|
Test case |
CREATE TABLE t1 (i1 INT, i2 INT NOT NULL) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1,4),(2,6); |
|
SELECT * FROM t1 AS alias1 |
WHERE alias1.i1 IN ( |
SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 ) |
);
|
DROP TABLE t1; |
|
10.0 bf2e31500c debug build |
MariaDB [test]> SELECT * FROM t1 AS alias1 |
-> WHERE alias1.i1 IN ( |
-> SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 ) |
-> );
|
Empty set (0.01 sec) |
MariaDB [test]> EXPLAIN EXTENDED
|
-> SELECT * FROM t1 AS alias1
|
-> WHERE alias1.i1 IN (
|
-> SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 )
|
-> );
|
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
|
| 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | test.alias1.i2 | 1 | 100.00 | |
|
| 1 | PRIMARY | alias1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using join buffer (flat, BNL join) |
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; FirstMatch(alias1); Using join buffer (incremental, BNL join) |
|
| 3 | MATERIALIZED | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
|
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
|
4 rows in set, 2 warnings (0.00 sec)
|
|
|
MariaDB [test]> select @@version;
|
+-----------------------+
|
| @@version |
|
+-----------------------+
|
| 10.0.27-MariaDB-debug |
|
+-----------------------+
|
1 row in set (0.00 sec)
|
|
10.0 bf2e31500c relwithdebinfo build |
MariaDB [test]> SELECT * FROM t1 AS alias1 |
-> WHERE alias1.i1 IN ( |
-> SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 ) |
-> );
|
+------+----+ |
| i1 | i2 |
|
+------+----+ |
| 1 | 4 |
|
| 2 | 6 |
|
+------+----+ |
2 rows in set (0.00 sec) |
MariaDB [test]> EXPLAIN EXTENDED
|
-> SELECT * FROM t1 AS alias1
|
-> WHERE alias1.i1 IN (
|
-> SELECT i1 FROM t1 WHERE alias1.i2 IN ( SELECT i2 FROM t1 HAVING i2 <> 7 )
|
-> );
|
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
|
| 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | test.alias1.i2 | 1 | 100.00 | |
|
| 1 | PRIMARY | alias1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using join buffer (flat, BNL join) |
|
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where; FirstMatch(alias1); Using join buffer (incremental, BNL join) |
|
| 3 | MATERIALIZED | t1 | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | |
|
+------+--------------+-------------+--------+---------------+--------------+---------+----------------+------+----------+----------------------------------------------------------------------------+
|
4 rows in set, 2 warnings (0.00 sec)
|
|
|
MariaDB [test]> select @@version;
|
+-----------------+
|
| @@version |
|
+-----------------+
|
| 10.0.27-MariaDB |
|
+-----------------+
|
1 row in set (0.00 sec)
|
Attachments
Activity
Debugging...
- convert_subq_to_jtbm converts subquery#3 into a non-merged semi-join nest
(aka JTBM) - Note that IN-equality of "alias1.i2=t1.i2" doesn't specifically exist for
JTBMs. - convert_subq_to_sj converts subquery#2 to a merged semi-join nest.
The problem is that I don't see where "alias1.i2" had its fix_after_pullout()
call to adjust its attributes.
Indeed, proceeding further to join optimization in the top query, we see:
|
|
(gdb) wher 1
|
#0 best_access_path (join=0x7fff5400be00, s=0x7fff54030cd8, remaining_tables=6, idx=1, disable_jbuf=false, record_count=2, pos=0x7fff54031360, loose_scan_pos=0x7ffff427c950) at /home/psergey/dev-git/10.1-dbg6/sql/sql_select.cc:5961
|
(More stack frames follow...)
|
(gdb) p s->table->alias.Ptr
|
$125 = 0x7fff5402b378 "<subquery3>"
|
(gdb) p dbug_print_item(s->keyuse[0].val)
|
$126 = 0x555556d53040 "alias1.i2"
|
(gdb) p/x s->keyuse[0].val->used_tables()
|
$127 = 0x4000000000000000
|
|
alias1.i2 has table_map()=OUTER_REF_TABLE_BIT...
how come the first table
| 1 | PRIMARY | <subquery3> | eq_ref | distinct_key | distinct_key | 4 | test.alias1.i2referes to alias1.i2, if table alias1 is accessed after table <subquery3> ?