diff options
| -rw-r--r-- | mysql-test/r/subselect3.result | 47 | ||||
| -rw-r--r-- | mysql-test/r/subselect3_jcl6.result | 47 | ||||
| -rw-r--r-- | mysql-test/t/subselect3.test | 47 | ||||
| -rw-r--r-- | sql/item.h | 1 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 9 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 2 | ||||
| -rw-r--r-- | sql/item_subselect.h | 1 |
7 files changed, 153 insertions, 1 deletions
diff --git a/mysql-test/r/subselect3.result b/mysql-test/r/subselect3.result index 1279fc6b8f4..108fab8bbc9 100644 --- a/mysql-test/r/subselect3.result +++ b/mysql-test/r/subselect3.result @@ -1424,4 +1424,51 @@ CALL p1; ERROR 42S22: Unknown column 'f1' in 'where clause' DROP PROCEDURE p1; DROP TABLE t1, t2; +# +# fix of LP BUG#824425 (prohibiting subqueries in row in +# left part of IN/ALL/ANY) +# +CREATE TABLE t1 ( a int) ; +INSERT INTO t1 VALUES (20),(30); +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (3),(9); +CREATE TABLE t3 ( a int, b int) ; +INSERT INTO t3 VALUES (20,5),(30,6); +set @optimizer_switch_save=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF'; +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( +SELECT b +FROM t3 +); +a +20 +30 +explain extended +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( +SELECT b +FROM t3 +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`)))))))) +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ), +( SELECT a FROM t2 WHERE a = 3 ) +) NOT IN ( +SELECT b , a +FROM t3 +); +ERROR 42000: This version of MySQL doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' +set optimizer_switch=@optimizer_switch_save; +drop table t1,t2,t3; set @@optimizer_switch=@subselect3_tmp; diff --git a/mysql-test/r/subselect3_jcl6.result b/mysql-test/r/subselect3_jcl6.result index 15dd920d95d..6d1e9ea4500 100644 --- a/mysql-test/r/subselect3_jcl6.result +++ b/mysql-test/r/subselect3_jcl6.result @@ -1433,6 +1433,53 @@ CALL p1; ERROR 42S22: Unknown column 'f1' in 'where clause' DROP PROCEDURE p1; DROP TABLE t1, t2; +# +# fix of LP BUG#824425 (prohibiting subqueries in row in +# left part of IN/ALL/ANY) +# +CREATE TABLE t1 ( a int) ; +INSERT INTO t1 VALUES (20),(30); +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (3),(9); +CREATE TABLE t3 ( a int, b int) ; +INSERT INTO t3 VALUES (20,5),(30,6); +set @optimizer_switch_save=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF'; +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( +SELECT b +FROM t3 +); +a +20 +30 +explain extended +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( +SELECT b +FROM t3 +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 +3 SUBQUERY t3 ALL NULL NULL NULL NULL 2 100.00 +2 SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (not(<expr_cache><(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9))>(<in_optimizer>((select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)),(select `test`.`t2`.`a` from `test`.`t2` where (`test`.`t2`.`a` = 9)) in ( <materialize> (select `test`.`t3`.`b` from `test`.`t3` ), <primary_index_lookup>(9 in <temporary table> on distinct_key where ((9 = `<subquery3>`.`b`)))))))) +SELECT * FROM t1 +WHERE ( +( SELECT a FROM t2 WHERE a = 9 ), +( SELECT a FROM t2 WHERE a = 3 ) +) NOT IN ( +SELECT b , a +FROM t3 +); +ERROR 42000: This version of MySQL doesn't yet support 'SUBQUERY in ROW in left expression of IN/ALL/ANY' +set optimizer_switch=@optimizer_switch_save; +drop table t1,t2,t3; set @@optimizer_switch=@subselect3_tmp; set join_cache_level=default; show variables like 'join_cache_level'; diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index f3faf44289e..52acaad9d83 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -1187,5 +1187,52 @@ CALL p1; DROP PROCEDURE p1; DROP TABLE t1, t2; + +--echo # +--echo # fix of LP BUG#824425 (prohibiting subqueries in row in +--echo # left part of IN/ALL/ANY) +--echo # + +CREATE TABLE t1 ( a int) ; +INSERT INTO t1 VALUES (20),(30); + +CREATE TABLE t2 (a int) ; +INSERT INTO t2 VALUES (3),(9); + +CREATE TABLE t3 ( a int, b int) ; +INSERT INTO t3 VALUES (20,5),(30,6); + +set @optimizer_switch_save=@@optimizer_switch; +SET SESSION optimizer_switch='semijoin=OFF,in_to_exists=OFF,materialization=ON,partial_match_rowid_merge=ON,partial_match_table_scan=OFF'; + +SELECT * FROM t1 +WHERE ( + ( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( + SELECT b + FROM t3 +); +explain extended +SELECT * FROM t1 +WHERE ( + ( SELECT a FROM t2 WHERE a = 9 ) +) NOT IN ( + SELECT b + FROM t3 +); + +--error ER_NOT_SUPPORTED_YET +SELECT * FROM t1 +WHERE ( + ( SELECT a FROM t2 WHERE a = 9 ), + ( SELECT a FROM t2 WHERE a = 3 ) +) NOT IN ( + SELECT b , a + FROM t3 +); +set optimizer_switch=@optimizer_switch_save; + +drop table t1,t2,t3; + # The following command must be the last one the file set @@optimizer_switch=@subselect3_tmp; diff --git a/sql/item.h b/sql/item.h index fa6918d8484..b858d8ce587 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1023,6 +1023,7 @@ public: virtual bool view_used_tables_processor(uchar *arg) { return 0; } virtual bool eval_not_null_tables(uchar *opt_arg) { return 0; } virtual bool clear_sum_processor(uchar *opt_arg) { return 0; } + virtual bool is_subquery_processor (uchar *opt_arg) { return 0; } /* To call bool function for all arguments */ struct bool_func_call_args diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index a33dd090f14..5b2c862e9eb 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1436,6 +1436,7 @@ bool Item_in_optimizer::fix_left(THD *thd, Item **ref) cache->setup(args[0]); if (cache->cols() == 1) { + DBUG_ASSERT(args[0]->type() != ROW_ITEM); if ((used_tables_cache= args[0]->used_tables())) cache->set_used_tables(OUTER_REF_TABLE_BIT); else @@ -1446,6 +1447,14 @@ bool Item_in_optimizer::fix_left(THD *thd, Item **ref) uint n= cache->cols(); for (uint i= 0; i < n; i++) { + /* Check that the expression (part of row) do not contain a subquery */ + if (args[0]->element_index(i)->walk(&Item::is_subquery_processor, + FALSE, NULL)) + { + my_error(ER_NOT_SUPPORTED_YET, MYF(0), + "SUBQUERY in ROW in left expression of IN/ALL/ANY"); + return 1; + } if (args[0]->element_index(i)->used_tables()) ((Item_cache *)cache->element_index(i))->set_used_tables(OUTER_REF_TABLE_BIT); else diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 6134903fee6..71408528903 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -528,7 +528,7 @@ bool Item_subselect::walk(Item_processor processor, bool walk_subquery, invalidated by irreversible cleanups (those happen after an uncorrelated subquery has been executed). */ - return FALSE; + return (this->*processor)(argument); } if (walk_subquery) diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 0ec0969e0ae..2012306c0f7 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -226,6 +226,7 @@ public: const char *func_name() const { DBUG_ASSERT(0); return "subselect"; } virtual bool expr_cache_is_needed(THD *); virtual void get_cache_parameters(List<Item> ¶meters); + virtual bool is_subquery_processor (uchar *opt_arg) { return 1; } friend class select_result_interceptor; friend class Item_in_optimizer; |