diff options
Diffstat (limited to 'mysql-test/r/subselect.result')
| -rw-r--r-- | mysql-test/r/subselect.result | 114 |
1 files changed, 112 insertions, 2 deletions
diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index a4bad836d1f..14347e9b899 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -6060,6 +6060,116 @@ WHERE (col_varchar_nokey, 'x') IN col_int_nokey 1 DROP TABLE ot,it1,it2; +# +# MDEV-746 +# Bug#13651009 WRONG RESULT FROM DERIVED TABLE IF THE SUBQUERY +# HAS AN EMPTY RESULT +# +CREATE TABLE t1 ( +pk int NOT NULL, +col_int_nokey int NOT NULL, +col_int_key int NOT NULL, +col_time_key time NOT NULL, +col_varchar_key varchar(1) NOT NULL, +col_varchar_nokey varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY col_int_key (col_int_key), +KEY col_time_key (col_time_key), +KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=MyISAM; +CREATE TABLE t2 ( +pk int NOT NULL AUTO_INCREMENT, +col_int_nokey int NOT NULL, +col_int_key int NOT NULL, +col_time_key time NOT NULL, +col_varchar_key varchar(1) NOT NULL, +col_varchar_nokey varchar(1) NOT NULL, +PRIMARY KEY (pk), +KEY col_int_key (col_int_key), +KEY col_time_key (col_time_key), +KEY col_varchar_key (col_varchar_key,col_int_key) +) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,4,4,'00:00:00','b','b'); +SET @var2:=4, @var3:=8; + +Testcase without inner subquery +EXPLAIN SELECT @var3:=12, sq4_alias1.* +FROM t1 AS sq4_alias1 +WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR +sq4_alias1.col_varchar_key = @var3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +SELECT @var3:=12, sq4_alias1.* +FROM t1 AS sq4_alias1 +WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR +sq4_alias1.col_varchar_key = @var3; +@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey +SELECT @var3; +@var3 +8 +EXPLAIN SELECT * FROM ( SELECT @var3:=12, sq4_alias1.* +FROM t1 AS sq4_alias1 +WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR +sq4_alias1.col_varchar_key = @var3 ) AS alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE <derived2> system NULL NULL NULL NULL 0 const row not found +2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +SELECT * FROM ( SELECT @var3:=12, sq4_alias1.* +FROM t1 AS sq4_alias1 +WHERE (sq4_alias1.col_varchar_key + NULL) IS NULL OR +sq4_alias1.col_varchar_key = @var3 ) AS alias3; +@var3:=12 pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey +SELECT @var3; +@var3 +8 + +Testcase with inner subquery; crashed WL#6095 +SET @var3=8; +EXPLAIN SELECT sq4_alias1.* +FROM t1 AS sq4_alias1 +WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey) +NOT IN +(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1, +c_sq1_alias1.col_varchar_nokey AS c_sq1_field2 +FROM t2 AS c_sq1_alias1 +WHERE (c_sq1_alias1.col_int_nokey != @var2 +OR c_sq1_alias1.pk != @var3)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1 +SELECT sq4_alias1.* +FROM t1 AS sq4_alias1 +WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey) +NOT IN +(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1, +c_sq1_alias1.col_varchar_nokey AS c_sq1_field2 +FROM t2 AS c_sq1_alias1 +WHERE (c_sq1_alias1.col_int_nokey != @var2 +OR c_sq1_alias1.pk != @var3)); +pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey +EXPLAIN SELECT * FROM ( SELECT sq4_alias1.* +FROM t1 AS sq4_alias1 +WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey) +NOT IN +(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1, +c_sq1_alias1.col_varchar_nokey AS c_sq1_field2 +FROM t2 AS c_sq1_alias1 +WHERE (c_sq1_alias1.col_int_nokey != @var2 +OR c_sq1_alias1.pk != @var3)) ) AS alias3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +3 DEPENDENT SUBQUERY c_sq1_alias1 system PRIMARY NULL NULL NULL 1 +SELECT * FROM ( SELECT sq4_alias1.* +FROM t1 AS sq4_alias1 +WHERE (sq4_alias1.col_varchar_key , sq4_alias1.col_varchar_nokey) +NOT IN +(SELECT c_sq1_alias1.col_varchar_key AS c_sq1_field1, +c_sq1_alias1.col_varchar_nokey AS c_sq1_field2 +FROM t2 AS c_sq1_alias1 +WHERE (c_sq1_alias1.col_int_nokey != @var2 +OR c_sq1_alias1.pk != @var3)) ) AS alias3; +pk col_int_nokey col_int_key col_time_key col_varchar_key col_varchar_nokey +DROP TABLE t1,t2; End of 5.2 tests # # BUG#779885: Crash in eliminate_item_equal with materialization=on in @@ -6860,7 +6970,7 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 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 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; # @@ -6894,6 +7004,6 @@ INSERT INTO t2 VALUES (45),(17),(20); EXPLAIN SELECT * FROM t1 WHERE EXISTS ( SELECT a FROM t1, t2 WHERE b = a GROUP BY a HAVING a <> 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 SUBQUERY t1 index a a 5 NULL 1 Using where; Using index +2 SUBQUERY t1 index a a 5 NULL 2 Using where; Using index 2 SUBQUERY t2 ref b b 5 test.t1.a 2 Using index DROP TABLE t1,t2; |