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

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)