diff options
| -rw-r--r-- | mysql-test/main/table_value_constr.result | 68 | ||||
| -rw-r--r-- | mysql-test/main/table_value_constr.test | 33 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 6 | ||||
| -rw-r--r-- | sql/item_subselect.h | 2 | ||||
| -rw-r--r-- | sql/sql_tvc.cc | 11 |
5 files changed, 113 insertions, 7 deletions
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 69c75ddab75..d7b32865db5 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2674,4 +2674,72 @@ values (1,2); values ((select min(a), max(b) from t1)); ERROR 21000: Operand should contain 1 column(s) drop table t1; +# +# MDEV-24840: union of TVCs in IN subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); +select a from t1 where a in (values (7) union values (8)); +a +7 +explain extended select a from t1 where a in (values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +4 DEPENDENT SUBQUERY <derived2> ref key0 key0 4 func 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 DEPENDENT UNION <derived3> ref key0 key0 4 func 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`7` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`8`))) +prepare stmt from "select a from t1 where a in (values (7) union values (8))"; +execute stmt; +a +7 +execute stmt; +a +7 +deallocate prepare stmt; +select a from t1 where a not in (values (7) union values (8)); +a +3 +1 +explain extended select a from t1 where a not in (values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 DEPENDENT UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`7`) union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`8`)))) +select a from t1 where a < all(values (7) union values (8)); +a +3 +1 +explain extended select a from t1 where a < all(values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 where a >= any(values (7) union values (8)); +a +7 +explain extended select a from t1 where a >= any(values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`))) +drop table t1; End of 10.3 tests diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index e8697bef589..bac85ff75cc 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1401,4 +1401,37 @@ values ((select min(a), max(b) from t1)); drop table t1; +--echo # +--echo # MDEV-24840: union of TVCs in IN subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); + +let $q= +select a from t1 where a in (values (7) union values (8)); +eval $q; +eval explain extended $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q= +select a from t1 where a not in (values (7) union values (8)); +eval $q; +eval explain extended $q; + +let $q= +select a from t1 where a < all(values (7) union values (8)); +eval $q; +eval explain extended $q; + +let $q= +select a from t1 where a >= any(values (7) union values (8)); +eval $q; +eval explain extended $q; + +drop table t1; + --echo End of 10.3 tests diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index d882918de5c..6a30012cda4 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -269,7 +269,11 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref) { if (sl->tvc) { - wrap_tvc_into_select(thd, sl); + if (!(sl= wrap_tvc_into_select(thd, sl))) + { + res= TRUE; + goto end; + } } } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index fdc39f1f05e..4e0a9639187 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -271,7 +271,7 @@ public: Item* build_clone(THD *thd) { return 0; } Item* get_copy(THD *thd) { return 0; } - bool wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl); + st_select_lex *wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl); friend class select_result_interceptor; friend class Item_in_optimizer; diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 1f91539ff45..78607b61e6d 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -779,11 +779,12 @@ st_select_lex *wrap_tvc_with_tail(THD *thd, st_select_lex *tvc_sl) SELECT * FROM (VALUES (v1), ... (vn)) tvc_x and replaces the subselect with the result of the transformation. - @retval false if successfull - true otherwise + @retval wrapping select if successful + 0 otherwise */ -bool Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl) +st_select_lex * +Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl) { LEX *lex= thd->lex; /* SELECT_LEX object where the transformation is performed */ @@ -794,12 +795,12 @@ bool Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl) if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE) ((subselect_single_select_engine *) engine)->change_select(wrapper_sl); lex->current_select= wrapper_sl; - return false; + return wrapper_sl; } else { lex->current_select= parent_select; - return true; + return 0; } } |