diff options
| author | Varun Gupta <varunraiko1803@gmail.com> | 2018-03-21 01:34:45 +0530 |
|---|---|---|
| committer | Varun Gupta <varunraiko1803@gmail.com> | 2018-03-21 09:38:56 +0200 |
| commit | 2dd4e50d5f74451a5f6bf56d1a36bafffcca878c (patch) | |
| tree | bdebcc6aa52bca19915fa547b1906bd8856a8262 | |
| parent | 69bc3c1976ebcbe116890d9d26305fd2887ed47c (diff) | |
| download | mariadb-git-2dd4e50d5f74451a5f6bf56d1a36bafffcca878c.tar.gz | |
MDEV-15555: select from DUAL where false yielding wrong result when in a IN
For the query having an IN subquery with no tables, we were converting the subquery with an expression between
the left part and the select list of the subquery . This can give incorrect results when we have a condition
in the subquery with a dual table (as this is treated as a no table).
The fix is that we don't do this conversion when we have conds in the subquery with a dual table.
| -rw-r--r-- | mysql-test/r/subselect4.result | 11 | ||||
| -rw-r--r-- | mysql-test/t/subselect4.test | 8 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 2 |
3 files changed, 20 insertions, 1 deletions
diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index d3c63ff9a2f..c20c048b919 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -2498,5 +2498,16 @@ FROM t2 WHERE b <= 'quux' GROUP BY field; field COUNT(DISTINCT c) 0 1 drop table t1,t2; +# +# MDEV-15555: select from DUAL where false yielding wrong result when in a IN +# +explain +SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 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 +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1); +2 IN (SELECT 2 from DUAL WHERE 1 != 1) +0 SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index f051c8eaaf2..673dc9be0b4 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -2035,5 +2035,13 @@ SELECT ( SELECT COUNT(*) FROM t1 WHERE a = c ) AS field, COUNT(DISTINCT c) FROM t2 WHERE b <= 'quux' GROUP BY field; drop table t1,t2; +--echo # +--echo # MDEV-15555: select from DUAL where false yielding wrong result when in a IN +--echo # + +explain +SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1); +SELECT 2 IN (SELECT 2 from DUAL WHERE 1 != 1); + SET optimizer_switch= @@global.optimizer_switch; set @@tmp_table_size= @@global.tmp_table_size; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 46d41fd61e3..57dcbd4f540 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1746,7 +1746,7 @@ Item_in_subselect::single_value_transformer(JOIN *join) Item* join_having= join->having ? join->having : join->tmp_having; if (!(join_having || select_lex->with_sum_func || select_lex->group_list.elements) && - select_lex->table_list.elements == 0 && + select_lex->table_list.elements == 0 && !join->conds && !select_lex->master_unit()->is_union()) { Item *where_item= (Item*) select_lex->item_list.head(); |