diff options
Diffstat (limited to 'mysql-test/main/table_value_constr.result')
| -rw-r--r-- | mysql-test/main/table_value_constr.result | 101 |
1 files changed, 101 insertions, 0 deletions
diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index c2269f0862b..7ab9666c373 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2675,6 +2675,107 @@ 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; +# +# MDEV-24934:EXPLAIN for queries based on TVC using subqueries +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +values (8), ((select * from t1 where a between 2 and 4)); +8 +8 +3 +explain values (8), ((select * from t1 where a between 2 and 4)); +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 t1 ALL NULL NULL NULL NULL 3 Using where +values ((select * from t1 where a between 2 and 4)), +((select * from t1 where a > 10)); +(select * from t1 where a between 2 and 4) +3 +NULL +explain values ((select * from t1 where a between 2 and 4)), +((select * from t1 where a > 10)); +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 +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +values (10,11), ((select * from t1 where a = 7) + 1, 21); +10 11 +10 11 +8 21 +explain values (10,11), ((select * from t1 where a = 7) + 1, 21); +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 t1 ALL NULL NULL NULL NULL 3 Using where +drop table t1; +# # End of 10.3 tests # # |