diff options
Diffstat (limited to 'mysql-test/r/subselect4.result')
| -rw-r--r-- | mysql-test/r/subselect4.result | 347 |
1 files changed, 347 insertions, 0 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result new file mode 100644 index 00000000000..27c3a37f8da --- /dev/null +++ b/mysql-test/r/subselect4.result @@ -0,0 +1,347 @@ +# +# 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 index; Using temporary +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where +3 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table +# 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 +# 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. +# +# 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 (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = '0')) AS `RESULT` from dual +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 (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = ifnull('0',NULL))) AS `RESULT` from dual group by '0' +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 (select 1 AS `1` from `test`.`t2` where (`test`.`t2`.`d` = '0')) AS `RESULT` from dual group by '0' +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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize +1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize +1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize +1 PRIMARY t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +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 t2 ALL NULL NULL NULL NULL 6 Using where; Start materialize +1 SIMPLE t3 ALL NULL NULL NULL NULL 12 Using where; End materialize; Using join buffer +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; |