Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Description
Possibly the test case can be reduced further, I kept the structures close to the original dbt3, just removed unnecessary columns and rows.
CREATE DATABASE dbt3; |
USE dbt3; |
|
|
CREATE TABLE `customer` ( |
`c_nationkey` int(11) DEFAULT NULL, |
KEY `i_c_nationkey` (`c_nationkey`) |
) ENGINE=MyISAM;
|
|
|
INSERT INTO `customer` VALUES (18),(17),(8); |
|
|
CREATE TABLE `lineitem` ( |
`l_orderkey` int(11) NOT NULL DEFAULT 0, |
`l_partkey` int(11) DEFAULT NULL, |
`l_linenumber` int(11) NOT NULL DEFAULT 0, |
PRIMARY KEY (`l_orderkey`,`l_linenumber`) |
) ENGINE=MyISAM;
|
|
|
INSERT INTO `lineitem` VALUES (1,1,1),(1,1,4),(3,1,1); |
|
|
CREATE TABLE `nation` ( |
`n_nationkey` int(11) NOT NULL, |
`n_regionkey` int(11) DEFAULT NULL, |
PRIMARY KEY (`n_nationkey`), |
KEY `i_n_regionkey` (`n_regionkey`) |
) ENGINE=MyISAM;
|
|
|
INSERT INTO `nation` VALUES (17,1),(24,1); |
|
|
CREATE TABLE `orders` ( |
`o_orderkey` int(11) NOT NULL, |
`o_totalprice` double DEFAULT NULL, |
PRIMARY KEY (`o_orderkey`) |
) ENGINE=MyISAM;
|
|
|
INSERT INTO `orders` VALUES |
(1,125405.67),(2,36349.29),(3,150931.2),(4,28568.05),(5,80601.73);
|
|
|
CREATE TABLE `partsupp` ( |
`ps_partkey` int(11) NOT NULL DEFAULT 0, |
`ps_suppkey` int(11) NOT NULL DEFAULT 0, |
PRIMARY KEY (`ps_partkey`,`ps_suppkey`) |
) ENGINE=MyISAM;
|
|
|
INSERT INTO `partsupp` VALUES (1,1),(17,1); |
|
|
CREATE TABLE `region` ( |
`r_regionkey` int(11) NOT NULL, |
PRIMARY KEY (`r_regionkey`) |
) ENGINE=MyISAM;
|
|
|
INSERT INTO `region` VALUES (0),(1),(2),(3),(4); |
|
|
CREATE TABLE `supplier` ( |
`s_suppkey` int(11) NOT NULL, |
`s_nationkey` int(11) DEFAULT NULL, |
`s_acctbal` double DEFAULT NULL, |
PRIMARY KEY (`s_suppkey`) |
) ENGINE=MyISAM;
|
|
|
INSERT INTO `supplier` VALUES (1,17,5755.94); |
|
|
SELECT n_nationkey FROM nation LEFT JOIN region ON ( r_regionkey = n_regionkey ) JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) JOIN lineitem ON ( ps_partkey = l_partkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer WHERE o_totalprice = 151 OR c_nationkey = s_nationkey AND s_acctbal > 187; |
|
|
CREATE TABLE t (a INT); |
INSERT INTO t VALUES (1),(17),(2),(17); |
|
|
SELECT * FROM t WHERE a IN ( |
SELECT n_nationkey FROM nation LEFT JOIN region ON ( r_regionkey = n_regionkey ) JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) JOIN lineitem ON ( ps_partkey = l_partkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer WHERE o_totalprice = 151 OR c_nationkey = s_nationkey AND s_acctbal > 187 |
);
|
|
|
# Cleanup
|
DROP DATABASE dbt3; |
SELECT n_nationkey FROM nation LEFT JOIN region ON ( r_regionkey = n_regionkey ) JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) JOIN lineitem ON ( ps_partkey = l_partkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer WHERE o_totalprice = 151 OR c_nationkey = s_nationkey AND s_acctbal > 187; |
n_nationkey
|
17
|
17
|
17
|
CREATE TABLE t (a INT); |
INSERT INTO t VALUES (1),(17),(2),(17); |
SELECT * FROM t WHERE a IN ( |
SELECT n_nationkey FROM nation LEFT JOIN region ON ( r_regionkey = n_regionkey ) JOIN supplier ON ( s_nationkey = n_nationkey ) JOIN partsupp ON ( s_suppkey = ps_suppkey ) JOIN lineitem ON ( ps_partkey = l_partkey ) JOIN orders ON ( l_orderkey = o_orderkey ) JOIN customer WHERE o_totalprice = 151 OR c_nationkey = s_nationkey AND s_acctbal > 187 |
);
|
a
|
So, the first SELECT above returns rows with value 17.
The table t contains values 17.
However, when the same SELECT is used as IN subquery, the resulting query doesn't return anything.
Plan:
id select_type table type possible_keys key key_len ref rows filtered Extra |
1 PRIMARY supplier system PRIMARY NULL NULL NULL 1 100.00 |
1 PRIMARY nation const PRIMARY PRIMARY 4 const 1 100.00 |
1 PRIMARY region const PRIMARY PRIMARY 4 const 1 100.00 Using index |
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 1 100.00 Using where |
1 PRIMARY t ALL NULL NULL NULL NULL 4 100.00 Using where; Using join buffer (flat, BNL join) |
2 MATERIALIZED lineitem ALL PRIMARY NULL NULL NULL 3 100.00 Using where |
2 MATERIALIZED orders eq_ref PRIMARY PRIMARY 4 dbt3.lineitem.l_orderkey 1 100.00 |
2 MATERIALIZED customer index i_c_nationkey i_c_nationkey 5 NULL 3 100.00 Using where; Using index; Using join buffer (flat, BNL join) |
2 MATERIALIZED partsupp eq_ref PRIMARY PRIMARY 8 dbt3.lineitem.l_partkey,const 1 100.00 Using index |
Warnings:
|
Note 1003 select `dbt3`.`t`.`a` AS `a` from `dbt3`.`t` semi join (`dbt3`.`nation` left join `dbt3`.`region` on(1 = 1) join `dbt3`.`partsupp` join `dbt3`.`lineitem` join `dbt3`.`orders` join `dbt3`.`customer`) where `dbt3`.`t`.`a` = 17 and `dbt3`.`orders`.`o_orderkey` = `dbt3`.`lineitem`.`l_orderkey` and `dbt3`.`partsupp`.`ps_partkey` = `dbt3`.`lineitem`.`l_partkey` and `dbt3`.`partsupp`.`ps_suppkey` = 1 and (`dbt3`.`orders`.`o_totalprice` = 151 or `dbt3`.`customer`.`c_nationkey` = 17 and `dbt3`.`t`.`a` = 17) |