diff options
Diffstat (limited to '')
| -rw-r--r-- | mysql-test/main/subselect4.result | 95 |
1 files changed, 95 insertions, 0 deletions
diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index a041d76e..6d1e0dac 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -1892,6 +1892,8 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY SUBQUERY2_t1 index NULL col_int_key 5 NULL 2 Using index 2 SUBQUERY SUBQUERY2_t2 ALL col_varchar_key NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 MATERIALIZED t1 ALL NULL NULL NULL NULL 2 +Warnings: +Note 1105 Cannot use key `col_varchar_key` part[0] for lookup: `test`.`t1`.`col_varchar_key` of type `varchar` < "0" of type `bigint` SELECT col_int_key FROM t2 WHERE (SELECT SUBQUERY2_t1.col_int_key @@ -1917,6 +1919,8 @@ id select_type table type possible_keys key key_len ref rows Extra 2 SUBQUERY SUBQUERY2_t1 index NULL col_int_key 5 NULL 2 Using index 2 SUBQUERY SUBQUERY2_t2 ALL col_varchar_key NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where +Warnings: +Note 1105 Cannot use key `col_varchar_key` part[0] for lookup: `test`.`t1`.`col_varchar_key` of type `varchar` < "0" of type `bigint` SELECT col_int_key FROM t2 WHERE (SELECT SUBQUERY2_t1.col_int_key @@ -3235,4 +3239,95 @@ FROM x ) ); ERROR 21000: Operand should contain 2 column(s) +# +# MDEV-29362: Constant subquery used as left part of IN subquery +# +CREATE TABLE t1 (a int) ENGINE=MyISAM; +INSERT INTO t1 VALUES (15), (1), (2); +CREATE TABLE t2 (b int) ENGINE=MyISAM; +INSERT INTO t2 VALUES (15), (1); +CREATE TABLE t3 (c int) ENGINE=MyISAM; +INSERT INTO t3 VALUES (15), (1); +SET optimizer_switch='condition_pushdown_from_having=off'; +SELECT a FROM t1 GROUP BY a +HAVING a = ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3) ) + 1; +a +2 +SELECT a FROM t1 GROUP BY a +HAVING a IN ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3) ); +a +1 +SET optimizer_switch='condition_pushdown_from_having=on'; +SELECT a FROM t1 GROUP BY a +HAVING a = ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3) ) + 1; +a +2 +SELECT a FROM t1 GROUP BY a +HAVING a IN ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3) ); +a +1 +EXPLAIN FORMAT=JSON SELECT a FROM t1 GROUP BY a +HAVING a = ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3) ) + 1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "nested_loop": [ + { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t1.a = <cache>((<in_optimizer>((subquery#2),<exists>(subquery#3))) + 1)" + } + } + ], + "subqueries": [ + { + "query_block": { + "select_id": 3, + "having_condition": "trigcond(t3.c is null)", + "nested_loop": [ + { + "table": { + "table_name": "t3", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "trigcond(1 = t3.c or t3.c is null)" + } + } + ] + } + }, + { + "query_block": { + "select_id": 2, + "nested_loop": [ + { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t2.b = 1" + } + } + ] + } + } + ] + } +} +PREPARE stmt FROM "SELECT a FROM t1 GROUP BY a +HAVING a = ( (SELECT b FROM t2 where b=1) IN (SELECT c FROM t3) ) + 1"; +EXECUTE stmt; +a +2 +EXECUTE stmt; +a +2 +DEALLOCATE PREPARE stmt; +DROP TABLE t1,t2,t3; # End of 10.4 tests |