Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5, 10.0, 10.1
-
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)
|