diff options
Diffstat (limited to 'mysql-test')
| -rw-r--r-- | mysql-test/r/opt_tvc.result | 830 | ||||
| -rw-r--r-- | mysql-test/r/table_value_constr.result | 1409 | ||||
| -rw-r--r-- | mysql-test/t/opt_tvc.test | 244 | ||||
| -rw-r--r-- | mysql-test/t/table_value_const.test | 36 | ||||
| -rw-r--r-- | mysql-test/t/table_value_constr.test | 811 |
5 files changed, 2601 insertions, 729 deletions
diff --git a/mysql-test/r/opt_tvc.result b/mysql-test/r/opt_tvc.result index f3a5f182e61..a3c71faff46 100644 --- a/mysql-test/r/opt_tvc.result +++ b/mysql-test/r/opt_tvc.result @@ -1,10 +1,10 @@ create table t1 (a int, b int); -insert into t1 +insert into t1 values (1,2), (4,6), (9,7), (1,1), (2,5), (7,8); create table t2 (a int, b int, c int); -insert into t2 -values (1,2,3), (5,1,2), (4,3,7), +insert into t2 +values (1,2,3), (5,1,2), (4,3,7), (8,9,0), (10,7,1), (5,5,1); # optimization is not used select * from t1 where a in (1,2); @@ -37,484 +37,436 @@ a b 1 2 1 1 2 5 -select * from t1 -where a in +select * from t1 +where a in ( -select * -from (values (1),(2)) as new_tvc +select * +from (values (1),(2)) as tvc_0 ); a b 1 2 1 1 2 5 -explain select * from t1 where a in (1,2); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -explain select * from t1 -where a in -( -select * -from (values (1),(2)) as new_tvc +explain extended select * from t1 where a in (1,2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +explain extended select * from t1 +where a in +( +select * +from (values (1),(2)) as tvc_0 ); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -explain format=json select * from t1 where a in (1,2); -EXPLAIN -{ - "query_block": { - "select_id": 1, - "table": { - "table_name": "<subquery2>", - "access_type": "ALL", - "possible_keys": ["distinct_key"], - "rows": 2, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<unit3>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 3, - "table": { - "message": "No tables used" - } - } - } - ] - } - } - } - } - } - } - }, - "block-nl-join": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 6, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "256Kb", - "join_type": "BNL", - "attached_condition": "t1.a = new_tvc.`1`" - } - } -} +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` # AND-condition with IN-predicates in WHERE-part -select * from t1 -where a in (1,2) and +select * from t1 +where a in (1,2) and b in (1,5); a b 1 1 2 5 -select * from t1 -where a in +select * from t1 +where a in ( -select * -from (values (1),(2)) as new_tvc -) +select * +from (values (1),(2)) as tvc_0 +) and b in ( -select * -from (values (1),(5)) as new_tvc +select * +from (values (1),(5)) as tvc_1 ); a b 1 1 2 5 -explain select * from t1 -where a in (1,2) and +explain extended select * from t1 +where a in (1,2) and b in (1,5); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -5 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -explain select * from t1 -where a in -( -select * -from (values (1),(2)) as new_tvc -) +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `tvc_0`.`1` = `test`.`t1`.`a` and `test`.`t1`.`b` = `tvc_1`.`1` +explain extended select * from t1 +where a in +( +select * +from (values (1),(2)) as tvc_0 +) and b in ( -select * -from (values (1),(5)) as new_tvc +select * +from (values (1),(5)) as tvc_1 +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1` +# subquery with IN-predicate +select * from t1 +where a in +( +select a +from t2 where b in (3,4) +); +a b +4 6 +select * from t1 +where a in +( +select a from t2 +where b in +( +select * +from (values (3),(4)) as tvc_0 +) ); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <subquery4> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -5 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -explain format=json select * from t1 -where a in (1,2) and -b in (1,5); -EXPLAIN -{ - "query_block": { - "select_id": 1, - "table": { - "table_name": "<subquery4>", - "access_type": "ALL", - "possible_keys": ["distinct_key"], - "rows": 2, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 4, - "table": { - "table_name": "<derived5>", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<unit5>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 5, - "table": { - "message": "No tables used" - } - } - } - ] - } - } - } - } - } - } - }, - "block-nl-join": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 6, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "256Kb", - "join_type": "BNL", - "attached_condition": "t1.b = new_tvc.`1`" - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "4", - "used_key_parts": ["1"], - "ref": ["func"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<unit3>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 3, - "table": { - "message": "No tables used" - } - } - } - ] - } - } - } - } - } - } - } - } -} -# OR-condition with IN-predicates in WHERE-part -select * from t1 -where a in (1,2) or -b in (4,5); +a b +4 6 +explain extended select * from t1 +where a in +( +select a +from t2 where b in (3,4) +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3` +explain extended select * from t1 +where a in +( +select a from t2 +where b in +( +select * +from (values (3),(4)) as tvc_0 +) +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3` +# derived table with IN-predicate +select * from +( +select * +from t1 +where a in (1,2) +) as dr_table; a b 1 2 1 1 2 5 -select * from t1 -where a in +select * from ( -select * -from (values (1),(2)) as new_tvc -) -or b in +select * +from t1 +where a in ( -select * -from (values (4),(5)) as new_tvc -); +select * +from (values (1),(2)) +as tvc_0 +) +) as dr_table; a b 1 2 1 1 2 5 -explain select * from t1 -where a in (1,2) or -b in (4,5); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 Using where -4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 -5 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -explain select * from t1 -where a in -( -select * -from (values (1),(2)) as new_tvc -) -or b in -( -select * -from (values (4),(5)) as new_tvc -); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 Using where -4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 -5 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -explain format=json select * from t1 -where a in (1,2) or -b in (4,5); -EXPLAIN -{ - "query_block": { - "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 6, - "filtered": 100, - "attached_condition": "<in_optimizer>(t1.a,t1.a in (subquery#2)) or <in_optimizer>(t1.b,t1.b in (subquery#4))" - }, - "subqueries": [ - { - "query_block": { - "select_id": 4, - "table": { - "table_name": "<derived5>", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<unit5>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 5, - "table": { - "message": "No tables used" - } - } - } - ] - } - } - } - } - } - }, - { - "query_block": { - "select_id": 2, - "table": { - "table_name": "<derived3>", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<unit3>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 3, - "table": { - "message": "No tables used" - } - } - } - ] - } - } - } - } - } - } - ] - } -} -# subquery with IN-predicate -select * from t1 -where a in +explain extended select * from ( -select a -from t2 where b in (3,4) +select * +from t1 +where a in (1,2) +) as dr_table; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`)))) +explain extended select * from +( +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 ) -; +) as dr_table; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`)))) +# non-recursive CTE with IN-predicate +with tvc_0 as +( +select * +from t1 +where a in (1,2) +) +select * from tvc_0; a b -4 6 -select * from t1 -where a in ( -select a from t2 -where b in +1 2 +1 1 +2 5 +select * from +( +select * +from t1 +where a in ( -select * -from (values (3),(4)) as new_tvc) +select * +from (values (1),(2)) +as tvc_0 ) -; +) as dr_table; a b -4 6 -explain select * from t1 -where a in +1 2 +1 1 +2 5 +explain extended with tvc_0 as ( -select a -from t2 where b in (3,4) +select * +from t1 +where a in (1,2) ) -; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -4 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -explain select * from t1 -where a in ( -select a from t2 -where b in -( -select * -from (values (3),(4)) as new_tvc) +select * from tvc_0; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`)))) +explain extended select * from +( +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 ) -; -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 6 -1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 -2 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 -2 MATERIALIZED t2 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (flat, BNL join) -4 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used -explain format=json select * from t1 -where a in -( -select a -from t2 where b in (3,4) +) as dr_table; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`)))) +# VIEW with IN-predicate +create view v1 as +select * +from t1 +where a in (1,2); +create view v2 as +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 ) ; -EXPLAIN -{ - "query_block": { - "select_id": 1, - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 6, - "filtered": 100 - }, - "table": { - "table_name": "<subquery2>", - "access_type": "eq_ref", - "possible_keys": ["distinct_key"], - "key": "distinct_key", - "key_length": "4", - "used_key_parts": ["a"], - "ref": ["func"], - "rows": 1, - "filtered": 100, - "materialized": { - "unique": 1, - "query_block": { - "select_id": 2, - "table": { - "table_name": "<derived4>", - "access_type": "ALL", - "rows": 2, - "filtered": 100, - "materialized": { - "query_block": { - "union_result": { - "table_name": "<unit4>", - "access_type": "ALL", - "query_specifications": [ - { - "query_block": { - "select_id": 4, - "table": { - "message": "No tables used" - } - } - } - ] - } - } - } - }, - "block-nl-join": { - "table": { - "table_name": "t2", - "access_type": "ALL", - "rows": 6, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "256Kb", - "join_type": "BNL", - "attached_condition": "t2.b = new_tvc.`3`" - } - } - } - } - } -} -drop table t1; +select * from v1; +a b +1 2 +1 1 +2 5 +select * from v2; +a b +1 2 +1 1 +2 5 +explain extended select * from v1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`)))) +explain extended select * from v2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`)))) +drop view v1,v2; +# subselect defined by derived table with IN-predicate +select * from t1 +where a in +( +select 1 +from +( +select * +from t1 +where a in (1,2) +) +as dr_table +); +a b +1 2 +1 1 +select * from t1 +where a in +( +select 1 +from +( +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 +) +) +as dr_table +); +a b +1 2 +1 1 +explain extended select * from t1 +where a in +( +select 1 +from +( +select * +from t1 +where a in (1,2) +) +as dr_table +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t1`) where `test`.`t1`.`a` = 1 and <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#4 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery4>`.`1`)))) +explain extended select * from t1 +where a in +( +select 1 +from +( +select * +from t1 +where a in +( +select * +from (values (1),(2)) +as tvc_0 +) +) +as dr_table +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where +4 MATERIALIZED <derived5> ALL NULL NULL NULL NULL 2 100.00 +5 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t1`) where `test`.`t1`.`a` = 1 and <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#4 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery4>`.`1`)))) +# derived table with IN-predicate and group by +select * from +( +select max(a),b +from t1 +where b in (3,5) +group by b +) as dr_table; +max(a) b +2 5 +select * from +( +select max(a),b +from t1 +where b in +( +select * +from (values (3),(5)) +as tvc_0 +) +group by b +) as dr_table; +max(a) b +2 5 +explain extended select * from +( +select max(a),b +from t1 +where b in (3,5) +group by b +) as dr_table; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort +2 DERIVED <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 Using where +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where `tvc_0`.`3` = `test`.`t1`.`b` group by `test`.`t1`.`b`) `dr_table` +explain extended select * from +( +select max(a),b +from t1 +where b in +( +select * +from (values (3),(5)) +as tvc_0 +) +group by b +) as dr_table; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 100.00 +2 DERIVED t1 ALL NULL NULL NULL NULL 6 100.00 Using temporary; Using filesort +2 DERIVED <subquery3> eq_ref distinct_key distinct_key 4 func 1 100.00 +3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2 100.00 +4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where 1 group by `test`.`t1`.`b`) `dr_table` +drop table t1, t2; set @@in_subquery_conversion_threshold= default; diff --git a/mysql-test/r/table_value_constr.result b/mysql-test/r/table_value_constr.result index dac84fc5dcb..411edc53168 100644 --- a/mysql-test/r/table_value_constr.result +++ b/mysql-test/r/table_value_constr.result @@ -12,289 +12,591 @@ values (1,2), (3,4), (5.6,0); values ("abc", "def"); abc def abc def -# UNION using VALUES structure(s) -select 1,2 union values (1,2); +# UNION that uses VALUES structure(s) +select 1,2 +union +values (1,2); 1 2 1 2 -values (1,2) union select 1,2; +values (1,2) +union +select 1,2; 1 2 1 2 -select 1,2 union values (1,2),(3,4),(5,6),(7,8); +select 1,2 +union +values (1,2),(3,4),(5,6),(7,8); 1 2 1 2 3 4 5 6 7 8 -select 3,7 union values (1,2),(3,4),(5,6); +select 3,7 +union +values (1,2),(3,4),(5,6); 3 7 3 7 1 2 3 4 5 6 -select 3,7,4 union values (1,2,5),(4,5,6); +select 3,7,4 +union +values (1,2,5),(4,5,6); 3 7 4 3 7 4 1 2 5 4 5 6 -select 1,2 union values (1,7),(3,6.5); +select 1,2 +union +values (1,7),(3,6.5); 1 2 1 2.0 1 7.0 3 6.5 -select 1,2 union values (1,2.0),(3,6); +select 1,2 +union +values (1,2.0),(3,6); 1 2 1 2.0 3 6.0 -select 1.8,2 union values (1,2),(3,6); +select 1.8,2 +union +values (1,2),(3,6); 1.8 2 1.8 2 1.0 2 3.0 6 -values (1,2.4),(3,6) union select 2.8,9; +values (1,2.4),(3,6) +union +select 2.8,9; 1 2.4 1.0 2.4 3.0 6.0 2.8 9.0 -values (1,2),(3,4),(5,6),(7,8) union select 5,6; +values (1,2),(3,4),(5,6),(7,8) +union +select 5,6; 1 2 1 2 3 4 5 6 7 8 -select "ab","cdf" union values ("al","zl"),("we","q"); +select "ab","cdf" +union +values ("al","zl"),("we","q"); ab cdf ab cdf al zl we q -values ("ab", "cdf") union select "ab","cdf"; +values ("ab", "cdf") +union +select "ab","cdf"; ab cdf ab cdf -values (1,2) union values (1,2),(5,6); +values (1,2) +union +values (1,2),(5,6); 1 2 1 2 5 6 -values (1,2) union values (3,4),(5,6); +values (1,2) +union +values (3,4),(5,6); 1 2 1 2 3 4 5 6 -values (1,2) union values (1,2) union values (4,5); +values (1,2) +union +values (1,2) +union values (4,5); 1 2 1 2 4 5 -# UNION ALL using VALUES structure -values (1,2),(3,4) union all select 5,6; +# UNION ALL that uses VALUES structure +values (1,2),(3,4) +union all +select 5,6; 1 2 1 2 3 4 5 6 -values (1,2),(3,4) union all select 1,2; +values (1,2),(3,4) +union all +select 1,2; 1 2 1 2 3 4 1 2 -select 5,6 union all values (1,2),(3,4); +select 5,6 +union all +values (1,2),(3,4); 5 6 5 6 1 2 3 4 -select 1,2 union all values (1,2),(3,4); +select 1,2 +union all +values (1,2),(3,4); 1 2 1 2 1 2 3 4 -values (1,2) union all values (1,2),(5,6); +values (1,2) +union all +values (1,2),(5,6); 1 2 1 2 1 2 5 6 -values (1,2) union all values (3,4),(5,6); +values (1,2) +union all +values (3,4),(5,6); 1 2 1 2 3 4 5 6 -values (1,2) union all values (1,2) union all values (4,5); +values (1,2) +union all +values (1,2) +union all +values (4,5); 1 2 1 2 1 2 4 5 -values (1,2) union all values (1,2) union values (1,2); +values (1,2) +union all +values (1,2) +union values (1,2); 1 2 1 2 -values (1,2) union values (1,2) union all values (1,2); +values (1,2) +union +values (1,2) +union all +values (1,2); 1 2 1 2 1 2 -# EXCEPT using VALUES structure(s) -select 1,2 except values (3,4),(5,6); +# EXCEPT that uses VALUES structure(s) +select 1,2 +except +values (3,4),(5,6); 1 2 1 2 -select 1,2 except values (1,2),(3,4); +select 1,2 +except +values (1,2),(3,4); 1 2 -values (1,2),(3,4) except select 5,6; +values (1,2),(3,4) +except +select 5,6; 1 2 1 2 3 4 -values (1,2),(3,4) except select 1,2; +values (1,2),(3,4) +except +select 1,2; 1 2 3 4 -values (1,2),(3,4) except values (5,6); +values (1,2),(3,4) +except +values (5,6); 1 2 1 2 3 4 -values (1,2),(3,4) except values (1,2); +values (1,2),(3,4) +except +values (1,2); 1 2 3 4 -# INTERSECT using VALUES structure(s) -select 1,2 intersect values (3,4),(5,6); +# INTERSECT that uses VALUES structure(s) +select 1,2 +intersect +values (3,4),(5,6); 1 2 -select 1,2 intersect values (1,2),(3,4); +select 1,2 +intersect +values (1,2),(3,4); 1 2 1 2 -values (1,2),(3,4) intersect select 5,6; +values (1,2),(3,4) +intersect +select 5,6; 1 2 -values (1,2),(3,4) intersect select 1,2; +values (1,2),(3,4) +intersect +select 1,2; 1 2 1 2 -values (1,2),(3,4) intersect values (5,6); +values (1,2),(3,4) +intersect +values (5,6); 1 2 -values (1,2),(3,4) intersect values (1,2); +values (1,2),(3,4) +intersect +values (1,2); 1 2 1 2 -# combination of different structures using VALUES structures : UNION + EXCEPT -values (1,2),(3,4) except select 1,2 union values (1,2); +# combination of different structures that uses VALUES structures : UNION + EXCEPT +values (1,2),(3,4) +except +select 1,2 +union values (1,2); 1 2 1 2 3 4 -values (1,2),(3,4) except values (1,2) union values (1,2); +values (1,2),(3,4) +except +values (1,2) +union +values (1,2); 1 2 1 2 3 4 -values (1,2),(3,4) except values (1,2) union values (3,4); +values (1,2),(3,4) +except +values (1,2) +union +values (3,4); 1 2 3 4 -values (1,2),(3,4) union values (1,2) except values (1,2); +values (1,2),(3,4) +union +values (1,2) +except +values (1,2); 1 2 3 4 -# combination of different structures using VALUES structures : UNION ALL + EXCEPT -values (1,2),(3,4) except select 1,2 union all values (1,2); +# combination of different structures that uses VALUES structures : UNION ALL + EXCEPT +values (1,2),(3,4) +except +select 1,2 +union all +values (1,2); 1 2 1 2 3 4 -values (1,2),(3,4) except values (1,2) union all values (1,2); +values (1,2),(3,4) +except +values (1,2) +union all +values (1,2); 1 2 1 2 3 4 -values (1,2),(3,4) except values (1,2) union all values (3,4); +values (1,2),(3,4) +except +values (1,2) +union all +values (3,4); 1 2 3 4 3 4 -values (1,2),(3,4) union all values (1,2) except values (1,2); +values (1,2),(3,4) +union all +values (1,2) +except +values (1,2); 1 2 3 4 -# combination of different structures using VALUES structures : UNION + INTERSECT -values (1,2),(3,4) intersect select 1,2 union values (1,2); +# combination of different structures that uses VALUES structures : UNION + INTERSECT +values (1,2),(3,4) +intersect +select 1,2 +union +values (1,2); 1 2 1 2 -values (1,2),(3,4) intersect values (1,2) union values (1,2); +values (1,2),(3,4) +intersect +values (1,2) +union +values (1,2); 1 2 1 2 -values (1,2),(3,4) intersect values (1,2) union values (3,4); +values (1,2),(3,4) +intersect +values (1,2) +union +values (3,4); 1 2 1 2 3 4 -values (1,2),(3,4) union values (1,2) intersect values (1,2); +values (1,2),(3,4) +union +values (1,2) +intersect +values (1,2); 1 2 1 2 3 4 -# combination of different structures using VALUES structures : UNION ALL + INTERSECT -values (1,2),(3,4) intersect select 1,2 union all values (1,2); +# combination of different structures that uses VALUES structures : UNION ALL + INTERSECT +values (1,2),(3,4) +intersect +select 1,2 +union all +values (1,2); 1 2 1 2 1 2 -values (1,2),(3,4) intersect values (1,2) union all values (1,2); +values (1,2),(3,4) +intersect +values (1,2) +union all +values (1,2); 1 2 1 2 1 2 -values (1,2),(3,4) intersect values (1,2) union all values (3,4); +values (1,2),(3,4) +intersect +values (1,2) +union all +values (3,4); 1 2 1 2 3 4 -values (1,2),(3,4) union all values (1,2) intersect values (1,2); +values (1,2),(3,4) +union all +values (1,2) +intersect +values (1,2); 1 2 1 2 3 4 1 2 -# combination of different structures using VALUES structures : UNION + UNION ALL -values (1,2),(3,4) union all select 1,2 union values (1,2); +# combination of different structures that uses VALUES structures : UNION + UNION ALL +values (1,2),(3,4) +union all +select 1,2 +union +values (1,2); 1 2 1 2 3 4 -values (1,2),(3,4) union all values (1,2) union values (1,2); +values (1,2),(3,4) +union all +values (1,2) +union +values (1,2); 1 2 1 2 3 4 -values (1,2),(3,4) union all values (1,2) union values (3,4); +values (1,2),(3,4) +union all +values (1,2) +union +values (3,4); 1 2 1 2 3 4 -values (1,2),(3,4) union values (1,2) union all values (1,2); +values (1,2),(3,4) +union +values (1,2) +union all +values (1,2); 1 2 1 2 3 4 1 2 -values (1,2) union values (1,2) union all values (1,2); +values (1,2) +union +values (1,2) +union all +values (1,2); 1 2 1 2 1 2 -# CTE using VALUES structure(s) -with t2 as (values (1,2),(3,4)) select * from t2; +# CTE that uses VALUES structure(s) : non-recursive CTE +with t2 as +( +values (1,2),(3,4) +) +select * from t2; 1 2 1 2 3 4 -with t2 as (select 1,2 union values (1,2)) select * from t2; +with t2 as +( +select 1,2 +union +values (1,2) +) +select * from t2; 1 2 1 2 -with t2 as (select 1,2 union values (1,2),(3,4)) select * from t2; +with t2 as +( +select 1,2 +union +values (1,2),(3,4) +) +select * from t2; 1 2 1 2 3 4 -with t2 as (values (1,2) union select 1,2) select * from t2; +with t2 as +( +values (1,2) +union +select 1,2 +) +select * from t2; 1 2 1 2 -with t2 as (values (1,2),(3,4) union select 1,2) select * from t2; +with t2 as +( +values (1,2),(3,4) +union +select 1,2 +) +select * from t2; 1 2 1 2 3 4 -with t2 as (values (5,6) union values (1,2),(3,4)) select * from t2; +with t2 as +( +values (5,6) +union +values (1,2),(3,4) +) +select * from t2; 5 6 5 6 1 2 3 4 -with t2 as (values (1,2) union values (1,2),(3,4)) select * from t2; +with t2 as +( +values (1,2) +union +values (1,2),(3,4) +) +select * from t2; 1 2 1 2 3 4 -with t2 as (select 1,2 union all values (1,2),(3,4)) select * from t2; +with t2 as +( +select 1,2 +union all +values (1,2),(3,4) +) +select * from t2; 1 2 1 2 1 2 3 4 -with t2 as (values (1,2),(3,4) union all select 1,2) select * from t2; +with t2 as +( +values (1,2),(3,4) +union all +select 1,2 +) +select * from t2; 1 2 1 2 3 4 1 2 -with t2 as (values (1,2) union all values (1,2),(3,4)) select * from t2; +with t2 as +( +values (1,2) +union all +values (1,2),(3,4) +) +select * from t2; 1 2 1 2 1 2 3 4 -# Derived table using VALUES structure(s) +# recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor +with recursive t2(a,b) as +( +values(1,1) +union +select t1.a, t1.b +from t1,t2 +where t1.a=t2.a +) +select * from t2; +a b +1 1 +1 2 +with recursive t2(a,b) as +( +values(1,1) +union +select t1.a+1, t1.b +from t1,t2 +where t1.a=t2.a +) +select * from t2; +a b +1 1 +2 2 +2 1 +3 5 +# recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors +with recursive t2(a,b) as +( +values(1,1) +union +values (3,4) +union +select t2.a+1, t1.b +from t1,t2 +where t1.a=t2.a +) +select * from t2; +a b +1 1 +3 4 +2 2 +2 1 +3 5 +# recursive CTE that uses VALUES structure(s) : that uses UNION ALL +with recursive t2(a,b,st) as +( +values(1,1,1) +union all +select t2.a, t1.b, t2.st+1 +from t1,t2 +where t1.a=t2.a and st<3 +) +select * from t2; +a b st +1 1 1 +1 2 2 +1 1 2 +1 2 3 +1 2 3 +1 1 3 +1 1 3 +# recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements) +with recursive fact(n,f) as +( +values(1,1) +union +select n+1,f*n from fact where n < 10 +) +select * from fact; +n f +1 1 +2 1 +3 2 +4 6 +5 24 +6 120 +7 720 +8 5040 +9 40320 +10 362880 +# Derived table that uses VALUES structure(s) : singe VALUES structure select * from (values (1,2),(3,4)) as t2; 1 2 1 2 3 4 +# Derived table that uses VALUES structure(s) : UNION with VALUES structure(s) select * from (select 1,2 union values (1,2)) as t2; 1 2 1 2 @@ -318,6 +620,7 @@ select * from (values (1,2) union values (1,2),(3,4)) as t2; 1 2 1 2 3 4 +# Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s) select * from (select 1,2 union all values (1,2),(3,4)) as t2; 1 2 1 2 @@ -333,67 +636,997 @@ select * from (values (1,2) union all values (1,2),(3,4)) as t2; 1 2 1 2 3 4 -# CREATE VIEW using VALUES structure(s) +# CREATE VIEW that uses VALUES structure(s) : singe VALUES structure create view v1 as values (1,2),(3,4); select * from v1; 1 2 1 2 3 4 drop view v1; -create view v1 as select 1,2 union values (1,2); +# CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s) +create view v1 as +select 1,2 +union +values (1,2); select * from v1; 1 2 1 2 drop view v1; -create view v1 as select 1,2 union values (1,2),(3,4); +create view v1 as +select 1,2 +union +values (1,2),(3,4); select * from v1; 1 2 1 2 3 4 drop view v1; -create view v1 as values (1,2) union select 1,2; +create view v1 as +values (1,2) +union +select 1,2; select * from v1; 1 2 1 2 drop view v1; -create view v1 as values (1,2),(3,4) union select 1,2; +create view v1 as +values (1,2),(3,4) +union +select 1,2; select * from v1; 1 2 1 2 3 4 drop view v1; -create view v1 as values (5,6) union values (1,2),(3,4); +create view v1 as +values (5,6) +union +values (1,2),(3,4); select * from v1; 5 6 5 6 1 2 3 4 drop view v1; -create view v1 as values (1,2) union values (1,2),(3,4); +# CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s) +create view v1 as +values (1,2) +union +values (1,2),(3,4); select * from v1; 1 2 1 2 3 4 drop view v1; -create view v1 as select 1,2 union all values (1,2),(3,4); +create view v1 as +select 1,2 +union all +values (1,2),(3,4); select * from v1; 1 2 1 2 1 2 3 4 drop view v1; -create view v1 as values (1,2),(3,4) union all select 1,2; +create view v1 as +values (1,2),(3,4) +union all +select 1,2; select * from v1; 1 2 1 2 3 4 1 2 drop view v1; -create view v1 as values (1,2) union all values (1,2),(3,4); +create view v1 as +values (1,2) +union all +values (1,2),(3,4); select * from v1; 1 2 1 2 1 2 3 4 drop view v1; +# prepare statement that uses VALUES structure(s): single VALUES structure +prepare stmt1 from " +values (1,2); +"; +execute stmt1; +1 2 +1 2 +execute stmt1; +1 2 +1 2 +deallocate prepare stmt1; +# prepare statement that uses VALUES structure(s): UNION with VALUES structure(s) +prepare stmt1 from " + select 1,2 + union + values (1,2),(3,4); +"; +execute stmt1; +1 2 +1 2 +3 4 +execute stmt1; +1 2 +1 2 +3 4 +deallocate prepare stmt1; +prepare stmt1 from " + values (1,2),(3,4) + union + select 1,2; +"; +execute stmt1; +1 2 +1 2 +3 4 +execute stmt1; +1 2 +1 2 +3 4 +deallocate prepare stmt1; +prepare stmt1 from " + select 1,2 + union + values (3,4) + union + values (1,2); +"; +execute stmt1; +1 2 +1 2 +3 4 +execute stmt1; +1 2 +1 2 +3 4 +deallocate prepare stmt1; +prepare stmt1 from " + values (5,6) + union + values (1,2),(3,4); +"; +execute stmt1; +5 6 +5 6 +1 2 +3 4 +execute stmt1; +5 6 +5 6 +1 2 +3 4 +deallocate prepare stmt1; +# prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s) +prepare stmt1 from " + select 1,2 + union + values (1,2),(3,4); +"; +execute stmt1; +1 2 +1 2 +3 4 +execute stmt1; +1 2 +1 2 +3 4 +deallocate prepare stmt1; +prepare stmt1 from " + values (1,2),(3,4) + union all + select 1,2; +"; +execute stmt1; +1 2 +1 2 +3 4 +1 2 +execute stmt1; +1 2 +1 2 +3 4 +1 2 +deallocate prepare stmt1; +prepare stmt1 from " + select 1,2 + union all + values (3,4) + union all + values (1,2); +"; +execute stmt1; +1 2 +1 2 +3 4 +1 2 +execute stmt1; +1 2 +1 2 +3 4 +1 2 +deallocate prepare stmt1; +prepare stmt1 from " + values (1,2) + union all + values (1,2),(3,4); +"; +execute stmt1; +1 2 +1 2 +1 2 +3 4 +execute stmt1; +1 2 +1 2 +1 2 +3 4 +deallocate prepare stmt1; +# explain query that uses VALUES structure(s): single VALUES structure +explain +values (1,2); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used +explain format=json +values (1,2); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<unit1>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +# explain query that uses VALUES structure(s): UNION with VALUES structure(s) +explain +select 1,2 +union +values (1,2),(3,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 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +explain +values (1,2),(3,4) +union +select 1,2; +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 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +explain +values (5,6) +union +values (1,2),(3,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 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +explain format=json +select 1,2 +union +values (1,2),(3,4); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +explain format=json +values (1,2),(3,4) +union +select 1,2; +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +explain format=json +values (5,6) +union +values (1,2),(3,4); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +explain +select 1,2 +union +values (3,4) +union +values (1,2); +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 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL +explain format=json +select 1,2 +union +values (3,4) +union +values (1,2); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +# explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s) +explain +select 1,2 +union +values (1,2),(3,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 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL +explain +values (1,2),(3,4) +union all +select 1,2; +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 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +explain +values (1,2) +union all +values (1,2),(3,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 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +explain format=json +values (1,2),(3,4) +union all +select 1,2; +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +explain format=json +select 1,2 +union +values (1,2),(3,4); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +explain format=json +values (1,2) +union all +values (1,2),(3,4); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +explain +select 1,2 +union all +values (3,4) +union all +values (1,2); +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 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +explain format=json +select 1,2 +union all +values (3,4) +union all +values (1,2); +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2,3>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +# analyze query that uses VALUES structure(s): single VALUES structure +analyze +values (1,2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +analyze format=json +values (1,2); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<unit1>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +# analyze query that uses VALUES structure(s): UNION with VALUES structure(s) +analyze +select 1,2 +union +values (1,2),(3,4); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL +analyze +values (1,2),(3,4) +union +select 1,2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL +analyze +values (5,6) +union +values (1,2),(3,4); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 3.00 NULL NULL +analyze format=json +select 1,2 +union +values (1,2),(3,4); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 2, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +analyze format=json +values (1,2),(3,4) +union +select 1,2; +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 2, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +analyze format=json +values (5,6) +union +values (1,2),(3,4); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 3, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +analyze +select 1,2 +union +values (3,4) +union +values (1,2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2,3> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL +analyze format=json +select 1,2 +union +values (3,4) +union +values (1,2); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,2,3>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 2, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +# analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s) +analyze +select 1,2 +union +values (1,2),(3,4); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 2.00 NULL NULL +analyze +values (1,2),(3,4) +union all +select 1,2; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +analyze +values (1,2) +union all +values (1,2),(3,4); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +analyze format=json +values (1,2),(3,4) +union all +select 1,2; +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +analyze format=json +select 1,2 +union +values (1,2),(3,4); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "r_loops": 1, + "r_rows": 2, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +analyze format=json +values (1,2) +union all +values (1,2),(3,4); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} +analyze +select 1,2 +union all +values (3,4) +union all +values (1,2); +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used +analyze format=json +select 1,2 +union all +values (3,4) +union all +values (1,2); +ANALYZE +{ + "query_block": { + "union_result": { + "table_name": "<union1,2,3>", + "access_type": "ALL", + "r_loops": 0, + "r_rows": null, + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + }, + { + "query_block": { + "select_id": 3, + "operation": "UNION", + "table": { + "message": "No tables used" + } + } + } + ] + } + } +} drop table t1; diff --git a/mysql-test/t/opt_tvc.test b/mysql-test/t/opt_tvc.test index 1529672fc69..fe5110ece51 100644 --- a/mysql-test/t/opt_tvc.test +++ b/mysql-test/t/opt_tvc.test @@ -1,13 +1,13 @@ create table t1 (a int, b int); -insert into t1 +insert into t1 values (1,2), (4,6), (9,7), (1,1), (2,5), (7,8); create table t2 (a int, b int, c int); -insert into t2 -values (1,2,3), (5,1,2), (4,3,7), +insert into t2 +values (1,2,3), (5,1,2), (4,3,7), (8,9,0), (10,7,1), (5,5,1); --echo # optimization is not used @@ -15,7 +15,7 @@ values (1,2,3), (5,1,2), (4,3,7), let $query= select * from t1 where a in (1,2); eval $query; eval explain $query; -eval explain format=json $query; +eval explain format=json $query; --echo # set minimum number of values in VALUEs list when optimization works to 2 @@ -25,100 +25,212 @@ set @@in_subquery_conversion_threshold= 2; let $query= select * from t1 where a in (1,2); -let $optimized_query= -select * from t1 -where a in +let $optimized_query= +select * from t1 +where a in ( - select * - from (values (1),(2)) as new_tvc + select * + from (values (1),(2)) as tvc_0 ); eval $query; eval $optimized_query; -eval explain $query; -eval explain $optimized_query; -eval explain format=json $query; +eval explain extended $query; +eval explain extended $optimized_query; --echo # AND-condition with IN-predicates in WHERE-part -let $query= -select * from t1 -where a in (1,2) and +let $query= +select * from t1 +where a in (1,2) and b in (1,5); -let $optimized_query= -select * from t1 -where a in +let $optimized_query= +select * from t1 +where a in ( - select * - from (values (1),(2)) as new_tvc -) + select * + from (values (1),(2)) as tvc_0 +) and b in ( - select * - from (values (1),(5)) as new_tvc + select * + from (values (1),(5)) as tvc_1 ); - + eval $query; eval $optimized_query; -eval explain $query; -eval explain $optimized_query; -eval explain format=json $query; - ---echo # OR-condition with IN-predicates in WHERE-part +eval explain extended $query; +eval explain extended $optimized_query; -let $query= -select * from t1 -where a in (1,2) or - b in (4,5); +--echo # subquery with IN-predicate -let $optimized_query= -select * from t1 -where a in +let $query= +select * from t1 +where a in ( - select * - from (values (1),(2)) as new_tvc -) -or b in + select a + from t2 where b in (3,4) +); + +let $optimized_query= +select * from t1 +where a in ( - select * - from (values (4),(5)) as new_tvc + select a from t2 + where b in + ( + select * + from (values (3),(4)) as tvc_0 + ) ); - + eval $query; eval $optimized_query; -eval explain $query; -eval explain $optimized_query; -eval explain format=json $query; +eval explain extended $query; +eval explain extended $optimized_query; ---echo # subquery with IN-predicate +--echo # derived table with IN-predicate + +let $query= +select * from +( + select * + from t1 + where a in (1,2) +) as dr_table; -let $query= -select * from t1 -where a in +let $optimized_query= +select * from +( + select * + from t1 + where a in ( - select a - from t2 where b in (3,4) + select * + from (values (1),(2)) + as tvc_0 ) -; +) as dr_table; -let $optimized_query= -select * from t1 -where a in ( - select a from t2 - where b in +eval $query; +eval $optimized_query; +eval explain extended $query; +eval explain extended $optimized_query; + +--echo # non-recursive CTE with IN-predicate + +let $cte_query= +with tvc_0 as +( + select * + from t1 + where a in (1,2) +) +select * from tvc_0; + +eval $cte_query; +eval $optimized_query; +eval explain extended $cte_query; +eval explain extended $optimized_query; + +--echo # VIEW with IN-predicate + +create view v1 as + select * + from t1 + where a in (1,2); + +create view v2 as + select * + from t1 + where a in ( - select * - from (values (3),(4)) as new_tvc) + select * + from (values (1),(2)) + as tvc_0 ) ; - + +let $query= select * from v1; +let $optimized_query= select * from v2; + eval $query; eval $optimized_query; -eval explain $query; -eval explain $optimized_query; -eval explain format=json $query; +eval explain extended $query; +eval explain extended $optimized_query; -drop table t1; -set @@in_subquery_conversion_threshold= default; +drop view v1,v2; + +--echo # subselect defined by derived table with IN-predicate + +let $query= +select * from t1 +where a in +( + select 1 + from + ( + select * + from t1 + where a in (1,2) + ) + as dr_table +); + +let $optimized_query= +select * from t1 +where a in +( + select 1 + from + ( + select * + from t1 + where a in + ( + select * + from (values (1),(2)) + as tvc_0 + ) + ) + as dr_table +); +eval $query; +eval $optimized_query; +eval explain extended $query; +eval explain extended $optimized_query; + +--echo # derived table with IN-predicate and group by + +let $query= +select * from +( + select max(a),b + from t1 + where b in (3,5) + group by b +) as dr_table; + +let $optimized_query= +select * from +( + select max(a),b + from t1 + where b in + ( + select * + from (values (3),(5)) + as tvc_0 + ) + group by b +) as dr_table; + +eval $query; +eval $optimized_query; +eval explain extended $query; +eval explain extended $optimized_query; + +drop table t1, t2; +set @@in_subquery_conversion_threshold= default; diff --git a/mysql-test/t/table_value_const.test b/mysql-test/t/table_value_const.test deleted file mode 100644 index 00cd1baad95..00000000000 --- a/mysql-test/t/table_value_const.test +++ /dev/null @@ -1,36 +0,0 @@ -create table t1 (a int, b int); - -insert into t1 values (1,2),(4,6),(9,7),(1,1),(2,5),(7,8); - -values (1,2); - -select 1,2 union values (1,2); - -values (1,2) union select (1,2); - -values (1,2), (3,4) union select 1,2; - -select * from t1 where (t1.a,t1.b) in (select 5,7 union values (1,2),(2,3)); - -select * from t1 where (t1.a,t1.b) in (values (1,2),(2,3) union select 5,7); - -let $drop_view= drop view v1; - -create view v1 as values (1,2); - -eval $drop_view; - -create view v1 as values (1,2) union select 3,4; - -eval $drop_view; - -create view v1 as select 1,2 union values (3,4); - -eval $drop_view; - -create view v1 as select 1,2 union values (3,4),(5,6); - -eval $drop_view; - -drop table t1; - diff --git a/mysql-test/t/table_value_constr.test b/mysql-test/t/table_value_constr.test index 4aba843bbac..0021c27486c 100644 --- a/mysql-test/t/table_value_constr.test +++ b/mysql-test/t/table_value_constr.test @@ -1,6 +1,7 @@ create table t1 (a int, b int); -insert into t1 values (1,2),(4,6),(9,7),(1,1),(2,5),(7,8); +insert into t1 values (1,2),(4,6),(9,7), + (1,1),(2,5),(7,8); --echo # just VALUES @@ -10,164 +11,443 @@ values (1,2), (3,4), (5.6,0); values ("abc", "def"); ---echo # UNION using VALUES structure(s) +--echo # UNION that uses VALUES structure(s) -select 1,2 union values (1,2); - -values (1,2) union select 1,2; - -select 1,2 union values (1,2),(3,4),(5,6),(7,8); - -select 3,7 union values (1,2),(3,4),(5,6); - -select 3,7,4 union values (1,2,5),(4,5,6); - -select 1,2 union values (1,7),(3,6.5); - -select 1,2 union values (1,2.0),(3,6); - -select 1.8,2 union values (1,2),(3,6); - -values (1,2.4),(3,6) union select 2.8,9; - -values (1,2),(3,4),(5,6),(7,8) union select 5,6; - -select "ab","cdf" union values ("al","zl"),("we","q"); - -values ("ab", "cdf") union select "ab","cdf"; +select 1,2 +union +values (1,2); -values (1,2) union values (1,2),(5,6); +values (1,2) +union +select 1,2; -values (1,2) union values (3,4),(5,6); +select 1,2 +union +values (1,2),(3,4),(5,6),(7,8); -values (1,2) union values (1,2) union values (4,5); +select 3,7 +union +values (1,2),(3,4),(5,6); ---echo # UNION ALL using VALUES structure +select 3,7,4 +union +values (1,2,5),(4,5,6); -values (1,2),(3,4) union all select 5,6; +select 1,2 +union +values (1,7),(3,6.5); -values (1,2),(3,4) union all select 1,2; +select 1,2 +union +values (1,2.0),(3,6); -select 5,6 union all values (1,2),(3,4); +select 1.8,2 +union +values (1,2),(3,6); -select 1,2 union all values (1,2),(3,4); +values (1,2.4),(3,6) +union +select 2.8,9; -values (1,2) union all values (1,2),(5,6); +values (1,2),(3,4),(5,6),(7,8) +union +select 5,6; -values (1,2) union all values (3,4),(5,6); +select "ab","cdf" +union +values ("al","zl"),("we","q"); -values (1,2) union all values (1,2) union all values (4,5); +values ("ab", "cdf") +union +select "ab","cdf"; -values (1,2) union all values (1,2) union values (1,2); +values (1,2) +union +values (1,2),(5,6); -values (1,2) union values (1,2) union all values (1,2); +values (1,2) +union +values (3,4),(5,6); ---echo # EXCEPT using VALUES structure(s) +values (1,2) +union +values (1,2) +union values (4,5); -select 1,2 except values (3,4),(5,6); +--echo # UNION ALL that uses VALUES structure -select 1,2 except values (1,2),(3,4); +values (1,2),(3,4) +union all +select 5,6; -values (1,2),(3,4) except select 5,6; +values (1,2),(3,4) +union all +select 1,2; -values (1,2),(3,4) except select 1,2; +select 5,6 +union all +values (1,2),(3,4); -values (1,2),(3,4) except values (5,6); +select 1,2 +union all +values (1,2),(3,4); -values (1,2),(3,4) except values (1,2); +values (1,2) +union all +values (1,2),(5,6); ---echo # INTERSECT using VALUES structure(s) +values (1,2) +union all +values (3,4),(5,6); -select 1,2 intersect values (3,4),(5,6); +values (1,2) +union all +values (1,2) +union all +values (4,5); -select 1,2 intersect values (1,2),(3,4); +values (1,2) +union all +values (1,2) +union values (1,2); -values (1,2),(3,4) intersect select 5,6; +values (1,2) +union +values (1,2) +union all +values (1,2); -values (1,2),(3,4) intersect select 1,2; +--echo # EXCEPT that uses VALUES structure(s) -values (1,2),(3,4) intersect values (5,6); +select 1,2 +except +values (3,4),(5,6); -values (1,2),(3,4) intersect values (1,2); +select 1,2 +except +values (1,2),(3,4); ---echo # combination of different structures using VALUES structures : UNION + EXCEPT +values (1,2),(3,4) +except +select 5,6; -values (1,2),(3,4) except select 1,2 union values (1,2); +values (1,2),(3,4) +except +select 1,2; -values (1,2),(3,4) except values (1,2) union values (1,2); +values (1,2),(3,4) +except +values (5,6); -values (1,2),(3,4) except values (1,2) union values (3,4); +values (1,2),(3,4) +except +values (1,2); -values (1,2),(3,4) union values (1,2) except values (1,2); +--echo # INTERSECT that uses VALUES structure(s) ---echo # combination of different structures using VALUES structures : UNION ALL + EXCEPT +select 1,2 +intersect +values (3,4),(5,6); -values (1,2),(3,4) except select 1,2 union all values (1,2); +select 1,2 +intersect +values (1,2),(3,4); -values (1,2),(3,4) except values (1,2) union all values (1,2); +values (1,2),(3,4) +intersect +select 5,6; -values (1,2),(3,4) except values (1,2) union all values (3,4); +values (1,2),(3,4) +intersect +select 1,2; -values (1,2),(3,4) union all values (1,2) except values (1,2); +values (1,2),(3,4) +intersect +values (5,6); ---echo # combination of different structures using VALUES structures : UNION + INTERSECT +values (1,2),(3,4) +intersect +values (1,2); -values (1,2),(3,4) intersect select 1,2 union values (1,2); +--echo # combination of different structures that uses VALUES structures : UNION + EXCEPT -values (1,2),(3,4) intersect values (1,2) union values (1,2); +values (1,2),(3,4) +except +select 1,2 +union values (1,2); -values (1,2),(3,4) intersect values (1,2) union values (3,4); +values (1,2),(3,4) +except +values (1,2) +union +values (1,2); -values (1,2),(3,4) union values (1,2) intersect values (1,2); +values (1,2),(3,4) +except +values (1,2) +union +values (3,4); ---echo # combination of different structures using VALUES structures : UNION ALL + INTERSECT +values (1,2),(3,4) +union +values (1,2) +except +values (1,2); -values (1,2),(3,4) intersect select 1,2 union all values (1,2); +--echo # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT -values (1,2),(3,4) intersect values (1,2) union all values (1,2); +values (1,2),(3,4) +except +select 1,2 +union all +values (1,2); -values (1,2),(3,4) intersect values (1,2) union all values (3,4); +values (1,2),(3,4) +except +values (1,2) +union all +values (1,2); -values (1,2),(3,4) union all values (1,2) intersect values (1,2); +values (1,2),(3,4) +except +values (1,2) +union all +values (3,4); ---echo # combination of different structures using VALUES structures : UNION + UNION ALL +values (1,2),(3,4) +union all +values (1,2) +except +values (1,2); -values (1,2),(3,4) union all select 1,2 union values (1,2); +--echo # combination of different structures that uses VALUES structures : UNION + INTERSECT -values (1,2),(3,4) union all values (1,2) union values (1,2); +values (1,2),(3,4) +intersect +select 1,2 +union +values (1,2); -values (1,2),(3,4) union all values (1,2) union values (3,4); +values (1,2),(3,4) +intersect +values (1,2) +union +values (1,2); -values (1,2),(3,4) union values (1,2) union all values (1,2); +values (1,2),(3,4) +intersect +values (1,2) +union +values (3,4); -values (1,2) union values (1,2) union all values (1,2); +values (1,2),(3,4) +union +values (1,2) +intersect +values (1,2); ---echo # CTE using VALUES structure(s) +--echo # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT -with t2 as (values (1,2),(3,4)) select * from t2; +values (1,2),(3,4) +intersect +select 1,2 +union all +values (1,2); -with t2 as (select 1,2 union values (1,2)) select * from t2; +values (1,2),(3,4) +intersect +values (1,2) +union all +values (1,2); -with t2 as (select 1,2 union values (1,2),(3,4)) select * from t2; +values (1,2),(3,4) +intersect +values (1,2) +union all +values (3,4); -with t2 as (values (1,2) union select 1,2) select * from t2; +values (1,2),(3,4) +union all +values (1,2) +intersect +values (1,2); -with t2 as (values (1,2),(3,4) union select 1,2) select * from t2; +--echo # combination of different structures that uses VALUES structures : UNION + UNION ALL -with t2 as (values (5,6) union values (1,2),(3,4)) select * from t2; +values (1,2),(3,4) +union all +select 1,2 +union +values (1,2); -with t2 as (values (1,2) union values (1,2),(3,4)) select * from t2; +values (1,2),(3,4) +union all +values (1,2) +union +values (1,2); -with t2 as (select 1,2 union all values (1,2),(3,4)) select * from t2; +values (1,2),(3,4) +union all +values (1,2) +union +values (3,4); -with t2 as (values (1,2),(3,4) union all select 1,2) select * from t2; +values (1,2),(3,4) +union +values (1,2) +union all +values (1,2); -with t2 as (values (1,2) union all values (1,2),(3,4)) select * from t2; +values (1,2) +union +values (1,2) +union all +values (1,2); ---echo # Derived table using VALUES structure(s) +--echo # CTE that uses VALUES structure(s) : non-recursive CTE + +with t2 as +( + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + select 1,2 + union + values (1,2) +) +select * from t2; + +with t2 as +( + select 1,2 + union + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + values (1,2) + union + select 1,2 +) +select * from t2; + +with t2 as +( + values (1,2),(3,4) + union + select 1,2 +) +select * from t2; + +with t2 as +( + values (5,6) + union + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + values (1,2) + union + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + select 1,2 + union all + values (1,2),(3,4) +) +select * from t2; + +with t2 as +( + values (1,2),(3,4) + union all + select 1,2 +) +select * from t2; + +with t2 as +( + values (1,2) + union all + values (1,2),(3,4) +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor + +with recursive t2(a,b) as +( + values(1,1) + union + select t1.a, t1.b + from t1,t2 + where t1.a=t2.a +) +select * from t2; + +with recursive t2(a,b) as +( + values(1,1) + union + select t1.a+1, t1.b + from t1,t2 + where t1.a=t2.a +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors + +with recursive t2(a,b) as +( + values(1,1) + union + values (3,4) + union + select t2.a+1, t1.b + from t1,t2 + where t1.a=t2.a +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : that uses UNION ALL + +with recursive t2(a,b,st) as +( + values(1,1,1) + union all + select t2.a, t1.b, t2.st+1 + from t1,t2 + where t1.a=t2.a and st<3 +) +select * from t2; + +--echo # recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements) + +with recursive fact(n,f) as +( + values(1,1) + union + select n+1,f*n from fact where n < 10 +) +select * from fact; + +--echo # Derived table that uses VALUES structure(s) : singe VALUES structure select * from (values (1,2),(3,4)) as t2; +--echo # Derived table that uses VALUES structure(s) : UNION with VALUES structure(s) + select * from (select 1,2 union values (1,2)) as t2; select * from (select 1,2 union values (1,2),(3,4)) as t2; @@ -180,13 +460,15 @@ select * from (values (5,6) union values (1,2),(3,4)) as t2; select * from (values (1,2) union values (1,2),(3,4)) as t2; +--echo # Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s) + select * from (select 1,2 union all values (1,2),(3,4)) as t2; select * from (values (1,2),(3,4) union all select 1,2) as t2; select * from (values (1,2) union all values (1,2),(3,4)) as t2; ---echo # CREATE VIEW using VALUES structure(s) +--echo # CREATE VIEW that uses VALUES structure(s) : singe VALUES structure let $drop_view= drop view v1; let $select_view= select * from v1; @@ -196,49 +478,378 @@ create view v1 as values (1,2),(3,4); eval $select_view; eval $drop_view; -create view v1 as select 1,2 union values (1,2); +--echo # CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s) + +create view v1 as + select 1,2 + union + values (1,2); eval $select_view; eval $drop_view; -create view v1 as select 1,2 union values (1,2),(3,4); +create view v1 as + select 1,2 + union + values (1,2),(3,4); eval $select_view; eval $drop_view; -create view v1 as values (1,2) union select 1,2; +create view v1 as + values (1,2) + union + select 1,2; eval $select_view; eval $drop_view; -create view v1 as values (1,2),(3,4) union select 1,2; +create view v1 as + values (1,2),(3,4) + union + select 1,2; eval $select_view; eval $drop_view; -create view v1 as values (5,6) union values (1,2),(3,4); +create view v1 as + values (5,6) + union + values (1,2),(3,4); eval $select_view; eval $drop_view; -create view v1 as values (1,2) union values (1,2),(3,4); +--echo # CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s) + +create view v1 as + values (1,2) + union + values (1,2),(3,4); eval $select_view; eval $drop_view; -create view v1 as select 1,2 union all values (1,2),(3,4); +create view v1 as + select 1,2 + union all + values (1,2),(3,4); eval $select_view; eval $drop_view; -create view v1 as values (1,2),(3,4) union all select 1,2; +create view v1 as + values (1,2),(3,4) + union all + select 1,2; eval $select_view; eval $drop_view; -create view v1 as values (1,2) union all values (1,2),(3,4); +create view v1 as + values (1,2) + union all + values (1,2),(3,4); eval $select_view; eval $drop_view; +--echo # prepare statement that uses VALUES structure(s): single VALUES structure + +prepare stmt1 from " +values (1,2); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +--echo # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s) + +prepare stmt1 from " + select 1,2 + union + values (1,2),(3,4); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + values (1,2),(3,4) + union + select 1,2; +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + select 1,2 + union + values (3,4) + union + values (1,2); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + values (5,6) + union + values (1,2),(3,4); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +--echo # prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s) + +prepare stmt1 from " + select 1,2 + union + values (1,2),(3,4); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + values (1,2),(3,4) + union all + select 1,2; +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + select 1,2 + union all + values (3,4) + union all + values (1,2); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +prepare stmt1 from " + values (1,2) + union all + values (1,2),(3,4); +"; + +execute stmt1; +execute stmt1; +deallocate prepare stmt1; + +--echo # explain query that uses VALUES structure(s): single VALUES structure + +explain +values (1,2); + +explain format=json +values (1,2); + +--echo # explain query that uses VALUES structure(s): UNION with VALUES structure(s) + +explain +select 1,2 +union +values (1,2),(3,4); + +explain +values (1,2),(3,4) +union +select 1,2; + +explain +values (5,6) +union +values (1,2),(3,4); + +explain format=json +select 1,2 +union +values (1,2),(3,4); + +explain format=json +values (1,2),(3,4) +union +select 1,2; + +explain format=json +values (5,6) +union +values (1,2),(3,4); + +explain +select 1,2 +union +values (3,4) +union +values (1,2); + +explain format=json +select 1,2 +union +values (3,4) +union +values (1,2); + +--echo # explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s) + +explain +select 1,2 +union +values (1,2),(3,4); + +explain +values (1,2),(3,4) +union all +select 1,2; + +explain +values (1,2) +union all +values (1,2),(3,4); + +explain format=json +values (1,2),(3,4) +union all +select 1,2; + +explain format=json +select 1,2 +union +values (1,2),(3,4); + +explain format=json +values (1,2) +union all +values (1,2),(3,4); + +explain +select 1,2 +union all +values (3,4) +union all +values (1,2); + +explain format=json +select 1,2 +union all +values (3,4) +union all +values (1,2); + +--echo # analyze query that uses VALUES structure(s): single VALUES structure + +analyze +values (1,2); + +analyze format=json +values (1,2); + +--echo # analyze query that uses VALUES structure(s): UNION with VALUES structure(s) + +analyze +select 1,2 +union +values (1,2),(3,4); + +analyze +values (1,2),(3,4) +union +select 1,2; + +analyze +values (5,6) +union +values (1,2),(3,4); + +analyze format=json +select 1,2 +union +values (1,2),(3,4); + +analyze format=json +values (1,2),(3,4) +union +select 1,2; + +analyze format=json +values (5,6) +union +values (1,2),(3,4); + +analyze +select 1,2 +union +values (3,4) +union +values (1,2); + +analyze format=json +select 1,2 +union +values (3,4) +union +values (1,2); + +--echo # analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s) + +analyze +select 1,2 +union +values (1,2),(3,4); + +analyze +values (1,2),(3,4) +union all +select 1,2; + +analyze +values (1,2) +union all +values (1,2),(3,4); + +analyze format=json +values (1,2),(3,4) +union all +select 1,2; + +analyze format=json +select 1,2 +union +values (1,2),(3,4); + +analyze format=json +values (1,2) +union all +values (1,2),(3,4); + +analyze +select 1,2 +union all +values (3,4) +union all +values (1,2); + +analyze format=json +select 1,2 +union all +values (3,4) +union all +values (1,2); + drop table t1;
\ No newline at end of file |