# # Bug #46791: Assertion failed:(table->key_read==0),function unknown # function,file sql_base.cc # CREATE TABLE t1 (a INT, b INT, KEY(a)); INSERT INTO t1 VALUES (1,1),(2,2); CREATE TABLE t2 LIKE t1; INSERT INTO t2 VALUES (1,1),(2,2); CREATE TABLE t3 LIKE t1; # should have 1 impossible where and 2 dependent subqueries EXPLAIN SELECT 1 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY count(*); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index NULL a 5 NULL 2 Using where; Using index; Using temporary 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where 3 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found # should not crash the next statement SELECT 1 FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3)) ORDER BY count(*); 1 1 # should not crash: the crash is caused by the previous statement SELECT 1; 1 1 DROP TABLE t1,t2,t3; # # Bug #47106: Crash / segfault on adding EXPLAIN to a non-crashing # query # CREATE TABLE t1 ( a INT, b INT, PRIMARY KEY (a), KEY b (b) ); INSERT INTO t1 VALUES (1, 1), (2, 1); CREATE TABLE t2 LIKE t1; INSERT INTO t2 SELECT * FROM t1; CREATE TABLE t3 LIKE t1; INSERT INTO t3 SELECT * FROM t1; # Should not crash. # Should have 1 impossible where and 2 dependent subqs. EXPLAIN SELECT (SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) FROM t3 WHERE 1 = 0 GROUP BY 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE 2 DEPENDENT SUBQUERY t1 index NULL PRIMARY 4 NULL 2 Using index 2 DEPENDENT SUBQUERY t2 index b b 5 NULL 2 Using where; Using index; Using join buffer (flat, BNL join) # should return 0 rows SELECT (SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) FROM t3 WHERE 1 = 0 GROUP BY 1; (SELECT 1 FROM t1,t2 WHERE t2.b > t3.b) DROP TABLE t1,t2,t3; End of 5.0 tests. CREATE TABLE t1 (col_int_nokey int(11) NOT NULL, col_varchar_nokey varchar(1) NOT NULL) engine=myisam; INSERT INTO t1 VALUES (2,'s'),(0,'v'),(2,'s'); CREATE TABLE t2 ( pk int(11) NOT NULL AUTO_INCREMENT, `col_int_key` int(11) NOT NULL, col_varchar_key varchar(1) NOT NULL, PRIMARY KEY (pk), KEY `col_int_key` (`col_int_key`), KEY `col_varchar_key` (`col_varchar_key`) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (4,10,'g'), (5,20,'v'); SELECT t1.col_int_nokey,(SELECT MIN( t2_a.col_int_key ) FROM t2 t2_a, t2 t2_b, t1 t1_a WHERE t1_a.col_varchar_nokey = t2_b.col_varchar_key and t1.col_int_nokey ) as sub FROM t1; col_int_nokey sub 2 10 0 NULL 2 10 SELECT t1.col_int_nokey,(SELECT MIN( t2_a.col_int_key ) +1 FROM t2 t2_a, t2 t2_b, t1 t1_a WHERE t1_a.col_varchar_nokey = t2_b.col_varchar_key and t1.col_int_nokey ) as sub FROM t1; col_int_nokey sub 2 11 0 NULL 2 11 DROP TABLE t1,t2; # # Bug#54568: create view cause Assertion failed: 0, # file .\item_subselect.cc, line 836 # EXPLAIN SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization DESCRIBE SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1249 Select 2 was reduced during optimization # None of the below should crash CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( SELECT 1 ) ); CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) ); DROP VIEW v1, v2; # # Bug#51070: Query with a NOT IN subquery predicate returns a wrong # result set # CREATE TABLE t1 ( a INT, b INT ); INSERT INTO t1 VALUES ( 1, NULL ), ( 2, NULL ); CREATE TABLE t2 ( c INT, d INT ); INSERT INTO t2 VALUES ( NULL, 3 ), ( NULL, 4 ); CREATE TABLE t3 ( e INT, f INT ); INSERT INTO t3 VALUES ( NULL, NULL ), ( NULL, NULL ); CREATE TABLE t4 ( a INT ); INSERT INTO t4 VALUES (1), (2), (3); CREATE TABLE t5 ( a INT ); INSERT INTO t5 VALUES (NULL), (2); EXPLAIN SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra x PRIMARY x x x x x x x x x SUBQUERY x x x x x x x x SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ); a b EXPLAIN SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where 2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS NULL; a b 1 NULL 2 NULL SELECT * FROM t1 WHERE ( a, b ) IN ( SELECT c, d FROM t2 ) IS NULL; a b 1 NULL 2 NULL SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 ) IS UNKNOWN; a b 1 NULL 2 NULL SELECT * FROM t1 WHERE (( a, b ) NOT IN ( SELECT c, d FROM t2 )) IS UNKNOWN; a b 1 NULL 2 NULL SELECT * FROM t1 WHERE 1 = 1 AND ( a, b ) NOT IN ( SELECT c, d FROM t2 ); a b EXPLAIN SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 ); id select_type table type possible_keys key key_len ref rows Extra x PRIMARY x x x x x x x x x SUBQUERY x x x x x x x x SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT e, f FROM t3 ); a b EXPLAIN SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 ); id select_type table type possible_keys key key_len ref rows Extra x PRIMARY x x x x x x x x x SUBQUERY x x x x x x x x SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT a, b FROM t1 ); c d EXPLAIN SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 ); id select_type table type possible_keys key key_len ref rows Extra x PRIMARY x x x x x x x x x SUBQUERY x x x x x x x x SELECT * FROM t3 WHERE ( e, f ) NOT IN ( SELECT c, d FROM t2 ); e f EXPLAIN SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 ); id select_type table type possible_keys key key_len ref rows Extra x PRIMARY x x x x x x x x x SUBQUERY x x x x x x x x SELECT * FROM t2 WHERE ( c, d ) NOT IN ( SELECT e, f FROM t3 ); c d SELECT * FROM t1 WHERE ( a, b ) NOT IN ( SELECT c, d FROM t2 WHERE c = 1 AND c <> 1 ); a b 1 NULL 2 NULL SELECT * FROM t1 WHERE b NOT IN ( SELECT c FROM t2 WHERE c = 1 ); a b 1 NULL 2 NULL SELECT * FROM t1 WHERE NULL NOT IN ( SELECT c FROM t2 WHERE c = 1 AND c <> 1 ); a b 1 NULL 2 NULL DROP TABLE t1, t2, t3, t4, t5; # # End of 5.1 tests. # # # BUG#46743 "Azalea processing correlated, aggregate SELECT # subqueries incorrectly" # CREATE TABLE t1 (c int); INSERT INTO t1 VALUES (NULL); CREATE TABLE t2 (d int , KEY (d)); INSERT INTO t2 VALUES (NULL),(NULL); 0 rows in subquery SELECT 1 AS RESULT FROM t2,t1 WHERE d = c; RESULT base query SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ; RESULT NULL EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 Note 1003 select <expr_cache><NULL>((select 1 from `test`.`t2` where 0)) AS `RESULT` from `test`.`t1` first equivalent variant SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c ; RESULT NULL EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = IFNULL(c,NULL)) AS RESULT FROM t1 GROUP BY c; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 Note 1003 select <expr_cache><NULL>((select 1 from `test`.`t2` where 0)) AS `RESULT` from `test`.`t1` group by NULL second equivalent variant SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; RESULT NULL EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) AS RESULT FROM t1 GROUP BY c ; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00 2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 Note 1003 select <expr_cache><NULL>((select 1 from `test`.`t2` where 0)) AS `RESULT` from `test`.`t1` group by NULL DROP TABLE t1,t2; # # BUG#45928 "Differing query results depending on MRR and # engine_condition_pushdown settings" # CREATE TABLE `t1` ( `pk` int(11) NOT NULL AUTO_INCREMENT, `time_nokey` time NOT NULL, `varchar_key` varchar(1) NOT NULL, `varchar_nokey` varchar(1) NOT NULL, PRIMARY KEY (`pk`), KEY `varchar_key` (`varchar_key`) ) AUTO_INCREMENT=12 DEFAULT CHARSET=latin1; INSERT INTO `t1` VALUES (10,'00:00:00','i','i'),(11,'00:00:00','',''); set @old_optimizer_switch = @@session.optimizer_switch, @old_optimizer_use_mrr = @@session.optimizer_use_mrr, @old_engine_condition_pushdown = @@session.engine_condition_pushdown; SET SESSION OPTIMIZER_SWITCH = 'materialization=off,semijoin=off,loosescan=off,firstmatch=off'; SET SESSION optimizer_use_mrr = 'force'; SET SESSION engine_condition_pushdown = 1; SELECT `time_nokey` G1 FROM t1 WHERE ( `varchar_nokey` , `varchar_key` ) IN ( SELECT `varchar_nokey` , `varchar_nokey` ) AND `varchar_key` >= 'c' HAVING G1 ORDER BY `pk` ; G1 set @@session.optimizer_switch = @old_optimizer_switch, @@session.optimizer_use_mrr = @old_optimizer_use_mrr, @@session.engine_condition_pushdown = @old_engine_condition_pushdown; DROP TABLE t1; # # BUG#45863 "Assertion failed: (fixed == 0), function fix_fields(), # file item.cc, line 4448" # DROP TABLE IF EXISTS C, BB; CREATE TABLE C ( varchar_nokey varchar(1) NOT NULL ); INSERT INTO C VALUES ('k'),('a'),(''),('u'),('e'),('v'),('i'), ('t'),('u'),('f'),('u'),('m'),('j'),('f'), ('v'),('j'),('g'),('e'),('h'),('z'); CREATE TABLE BB ( varchar_nokey varchar(1) NOT NULL ); INSERT INTO BB VALUES ('i'),('t'); SELECT varchar_nokey FROM C WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey FROM BB); ERROR 21000: Operand should contain 2 column(s) SELECT varchar_nokey FROM C WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey, varchar_nokey FROM BB); ERROR 42S22: Unknown column 'OUTR' in 'IN/ALL/ANY subquery' DROP TABLE C,BB; # # During work with BUG#45863 I had problems with a query that was # optimized differently in regular and prepared mode. # Because there was a bug in one of the selected strategies, I became # aware of the problem. Adding an EXPLAIN query to catch this. DROP TABLE IF EXISTS t1, t2, t3; CREATE TABLE t1 (EMPNUM CHAR(3) NOT NULL, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); CREATE TABLE t2 (PNUM CHAR(3) NOT NULL, PNAME CHAR(20), PTYPE CHAR(6), BUDGET DECIMAL(9), CITY CHAR(15)); CREATE TABLE t3 (EMPNUM CHAR(3) NOT NULL, PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5)); INSERT INTO t1 VALUES ('E1','Alice',12,'Deale'); INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna'); INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna'); INSERT INTO t1 VALUES ('E4','Don',12,'Deale'); INSERT INTO t1 VALUES ('E5','Ed',13,'Akron'); INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale'); INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna'); INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa'); INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale'); INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna'); INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale'); INSERT INTO t3 VALUES ('E1','P1',40); INSERT INTO t3 VALUES ('E1','P2',20); INSERT INTO t3 VALUES ('E1','P3',80); INSERT INTO t3 VALUES ('E1','P4',20); INSERT INTO t3 VALUES ('E1','P5',12); INSERT INTO t3 VALUES ('E1','P6',12); INSERT INTO t3 VALUES ('E2','P1',40); INSERT INTO t3 VALUES ('E2','P2',80); INSERT INTO t3 VALUES ('E3','P2',20); INSERT INTO t3 VALUES ('E4','P2',20); INSERT INTO t3 VALUES ('E4','P4',40); INSERT INTO t3 VALUES ('E4','P5',80); SET @old_optimizer_switch = @@session.optimizer_switch; SET @old_join_cache_level = @@session.join_cache_level; SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on'; SET SESSION join_cache_level = 1; CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM); EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design'))"; EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; DROP INDEX t1_IDX ON t1; CREATE INDEX t1_IDX ON t1(EMPNUM); EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design'))"; EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; DROP INDEX t1_IDX ON t1; EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design'))"; EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; SET SESSION optimizer_switch = @old_optimizer_switch; SET SESSION join_cache_level = @old_join_cache_level; DROP TABLE t1, t2, t3; # # BUG#45221 Query SELECT pk FROM C WHERE pk IN (SELECT int_key) failing # CREATE TABLE t1 ( i1_key INT, i2 INT, i3 INT, KEY i1_index (i1_key) ); INSERT INTO t1 VALUES (9,1,2), (9,2,1); CREATE TABLE t2 ( pk INT NOT NULL, i1 INT, PRIMARY KEY (pk) ); INSERT INTO t2 VALUES (9,1); # Enable Index condition pushdown SELECT @old_icp:=@@engine_condition_pushdown; @old_icp:=@@engine_condition_pushdown # SET SESSION engine_condition_pushdown = 'ON'; SELECT pk FROM t2 WHERE pk IN ( SELECT i1_key FROM t1 WHERE t1.i2 < t1.i3 XOR t2.i1 > 1 ORDER BY t1.i2 desc); pk 9 # Restore old value for Index condition pushdown SET SESSION engine_condition_pushdown=@old_icp; DROP TABLE t1,t2; # # BUG#45863 "Assertion failed: (fixed == 0), function fix_fields(), # file item.cc, line 4448" # DROP TABLE IF EXISTS C, BB; CREATE TABLE C ( varchar_nokey varchar(1) NOT NULL ); INSERT INTO C VALUES ('k'),('a'),(''),('u'),('e'),('v'),('i'), ('t'),('u'),('f'),('u'),('m'),('j'),('f'), ('v'),('j'),('g'),('e'),('h'),('z'); CREATE TABLE BB ( varchar_nokey varchar(1) NOT NULL ); INSERT INTO BB VALUES ('i'),('t'); SELECT varchar_nokey FROM C WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey FROM BB); ERROR 21000: Operand should contain 2 column(s) SELECT varchar_nokey FROM C WHERE (varchar_nokey, OUTR) IN (SELECT varchar_nokey, varchar_nokey FROM BB); ERROR 42S22: Unknown column 'OUTR' in 'IN/ALL/ANY subquery' DROP TABLE C,BB; # # During work with BUG#45863 I had problems with a query that was # optimized differently in regular and prepared mode. # Because there was a bug in one of the selected strategies, I became # aware of the problem. Adding an EXPLAIN query to catch this. DROP TABLE IF EXISTS t1, t2, t3; CREATE TABLE t1 (EMPNUM CHAR(3) NOT NULL, EMPNAME CHAR(20), GRADE DECIMAL(4), CITY CHAR(15)); CREATE TABLE t2 (PNUM CHAR(3) NOT NULL, PNAME CHAR(20), PTYPE CHAR(6), BUDGET DECIMAL(9), CITY CHAR(15)); CREATE TABLE t3 (EMPNUM CHAR(3) NOT NULL, PNUM CHAR(3) NOT NULL, HOURS DECIMAL(5)); INSERT INTO t1 VALUES ('E1','Alice',12,'Deale'); INSERT INTO t1 VALUES ('E2','Betty',10,'Vienna'); INSERT INTO t1 VALUES ('E3','Carmen',13,'Vienna'); INSERT INTO t1 VALUES ('E4','Don',12,'Deale'); INSERT INTO t1 VALUES ('E5','Ed',13,'Akron'); INSERT INTO t2 VALUES ('P1','MXSS','Design',10000,'Deale'); INSERT INTO t2 VALUES ('P2','CALM','Code',30000,'Vienna'); INSERT INTO t2 VALUES ('P3','SDP','Test',30000,'Tampa'); INSERT INTO t2 VALUES ('P4','SDP','Design',20000,'Deale'); INSERT INTO t2 VALUES ('P5','IRM','Test',10000,'Vienna'); INSERT INTO t2 VALUES ('P6','PAYR','Design',50000,'Deale'); INSERT INTO t3 VALUES ('E1','P1',40); INSERT INTO t3 VALUES ('E1','P2',20); INSERT INTO t3 VALUES ('E1','P3',80); INSERT INTO t3 VALUES ('E1','P4',20); INSERT INTO t3 VALUES ('E1','P5',12); INSERT INTO t3 VALUES ('E1','P6',12); INSERT INTO t3 VALUES ('E2','P1',40); INSERT INTO t3 VALUES ('E2','P2',80); INSERT INTO t3 VALUES ('E3','P2',20); INSERT INTO t3 VALUES ('E4','P2',20); INSERT INTO t3 VALUES ('E4','P4',40); INSERT INTO t3 VALUES ('E4','P5',80); SET @old_optimizer_switch = @@session.optimizer_switch; SET @old_join_cache_level = @@session.join_cache_level; SET SESSION optimizer_switch = 'firstmatch=on,loosescan=on,materialization=on,semijoin=on'; SET SESSION join_cache_level = 1; CREATE UNIQUE INDEX t1_IDX ON t1(EMPNUM); EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design'))"; EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; DROP INDEX t1_IDX ON t1; CREATE INDEX t1_IDX ON t1(EMPNUM); EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design'))"; EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL t1_IDX NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL t1_IDX NULL NULL NULL 5 1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; DROP INDEX t1_IDX ON t1; EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design')); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) PREPARE stmt FROM "EXPLAIN SELECT EMPNAME FROM t1 WHERE EMPNUM IN (SELECT EMPNUM FROM t3 WHERE PNUM IN (SELECT PNUM FROM t2 WHERE PTYPE = 'Design'))"; EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 5 1 SIMPLE <subquery2> eq_ref distinct_key distinct_key 3 func 1 2 SUBQUERY t2 ALL NULL NULL NULL NULL 6 Using where 2 SUBQUERY t3 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) DEALLOCATE PREPARE stmt; SET SESSION optimizer_switch = @old_optimizer_switch; SET SESSION join_cache_level = @old_join_cache_level; DROP TABLE t1, t2, t3;