[Commits] 2261387: MDEV-16930 Crash when VALUES in derived table contains expressions

revision-id: 226138774a1befa622e5e1dcaf9749b5cda43db4 (mariadb-10.3.7-109-g2261387) parent(s): d453374fc480112266996a1026b97654cc174c09 author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-11 16:21:08 -0700 message: MDEV-16930 Crash when VALUES in derived table contains expressions This patch provides columns of the temporary table used for materialization of a table value constructors with comprehensive names. Before this patch these names were borrowed from the items of the first row of the table value constructor. It caused different problems when a TVC was used as the specification of a derived table: some columns of the derived table has the same name, some columns did not get any name and this triggered a crash. --- mysql-test/main/opt_tvc.result | 46 +++---- mysql-test/main/sp-bugs.result | 2 +- mysql-test/main/table_value_constr.result | 216 ++++++++++++++++-------------- mysql-test/main/table_value_constr.test | 8 ++ sql/sql_tvc.cc | 33 ++++- 5 files changed, 175 insertions(+), 130 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 0ecae5b..37c76d1 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -51,7 +51,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` +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`.`col_1` explain extended select * from t1 where a in ( @@ -64,7 +64,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` +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`.`col_1` # AND-condition with IN-predicates in WHERE-part select * from t1 where a in (1,2) and @@ -98,7 +98,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` +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`.`col_1` explain extended select * from t1 where a in ( @@ -119,7 +119,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` +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`.`col_1` # subquery with IN-predicate select * from t1 where a in @@ -154,7 +154,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` +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`.`col_1` explain extended select * from t1 where a in ( @@ -172,7 +172,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` +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`.`col_1` # derived table with IN-predicate select * from ( @@ -211,7 +211,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +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`.`col_1` explain extended select * from ( select * @@ -229,7 +229,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +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`.`col_1` # non-recursive CTE with IN-predicate with tvc_0 as ( @@ -270,7 +270,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` in (1,2))/* 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` +Note 1003 with tvc_0 as (/* select#2 */ 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` in (1,2))/* 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`.`col_1` explain extended select * from ( select * @@ -288,7 +288,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +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`.`col_1` # VIEW with IN-predicate create view v1 as select * @@ -321,7 +321,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +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`.`col_1` explain extended select * from v2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2 100.00 @@ -329,7 +329,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +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`.`col_1` drop view v1,v2; # subselect defined by derived table with IN-predicate select * from t1 @@ -386,7 +386,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) 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 ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`1` +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` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`col_1` explain extended select * from t1 where a in ( @@ -411,7 +411,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 MATERIALIZED t1 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) 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 ((values (1),(2)) `tvc_0` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`1` +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` join `test`.`t1`) where `test`.`t1`.`a` = 1 and `test`.`t1`.`a` = `tvc_0`.`col_1` # derived table with IN-predicate and group by select * from ( @@ -509,11 +509,11 @@ a b explain extended select * from t3 where a in (1,4,10); 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 t3 ref idx idx 5 tvc_0.1 3 100.00 +1 PRIMARY t3 ref idx idx 5 tvc_0.col_1 3 100.00 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`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`col_1` # use vectors in IN predeicate set @@in_predicate_conversion_threshold= 4; select * from t1 where (a,b) in ((1,2),(3,4)); @@ -526,7 +526,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` and `test`.`t1`.`b` = `tvc_0`.`2` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1,2),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` and `test`.`t1`.`b` = `tvc_0`.`col_2` set @@in_predicate_conversion_threshold= 2; # trasformation works for the one IN predicate and doesn't work for the other set @@in_predicate_conversion_threshold= 5; @@ -545,7 +545,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`1` and `test`.`t2`.`c` = `tvc_0`.`3` and (`tvc_0`.`1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`col_1` and `test`.`t2`.`c` = `tvc_0`.`col_2` and (`tvc_0`.`col_1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) set @@in_predicate_conversion_threshold= 2; # # mdev-14281: conversion of NOT IN predicate into subquery predicate @@ -573,7 +573,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`col_1`,`tvc_0`.`col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`col_1` and `test`.`t1`.`b` = `<subquery2>`.`col_2`)))) explain extended select * from t1 where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -581,7 +581,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`col_1`,`tvc_0`.`col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`col_1` and `test`.`t1`.`b` = `<subquery2>`.`col_2`)))) select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); a b @@ -595,7 +595,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`col_1`,`tvc_0`.`col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`col_1` and `test`.`t1`.`b` = `<subquery2>`.`col_2`)))) select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); a b c @@ -611,7 +611,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`1` and `test`.`t2`.`c` = `<subquery2>`.`2`)))) +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`col_1`,`tvc_0`.`col_2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`col_1` and `test`.`t2`.`c` = `<subquery2>`.`col_2`)))) drop table t1, t2, t3; set @@in_predicate_conversion_threshold= default; # @@ -635,7 +635,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`NULL` +Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`col_1` SET in_predicate_conversion_threshold= default; DROP TABLE t1; # diff --git a/mysql-test/main/sp-bugs.result b/mysql-test/main/sp-bugs.result index a699cd1..aaaf4f6 100644 --- a/mysql-test/main/sp-bugs.result +++ b/mysql-test/main/sp-bugs.result @@ -322,7 +322,7 @@ DROP PROCEDURE p1; CREATE PROCEDURE p1() VALUES (1); $$ CALL p1; -1 +col_1 1 SELECT body FROM mysql.proc WHERE db='test' AND specific_name='p1'; body diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 9e0a096..4023c27 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -3,15 +3,15 @@ insert into t1 values (1,2),(4,6),(9,7), (1,1),(2,5),(7,8); # just VALUES values (1,2); -1 2 +col_1 col_2 1 2 values (1,2), (3,4), (5.6,0); -1 2 +col_1 col_2 1.0 2 3.0 4 5.6 0 values ("abc", "def"); -abc def +col_1 col_2 abc def # UNION that uses VALUES structure(s) select 1,2 @@ -22,7 +22,7 @@ values (1,2); values (1,2) union select 1,2; -1 2 +col_1 col_2 1 2 select 1,2 union @@ -70,14 +70,14 @@ values (1,2),(3,6); values (1,2.4),(3,6) union select 2.8,9; -1 2.4 +col_1 col_2 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; -1 2 +col_1 col_2 1 2 3 4 5 6 @@ -92,18 +92,18 @@ we q values ("ab", "cdf") union select "ab","cdf"; -ab cdf +col_1 col_2 ab cdf values (1,2) union values (1,2),(5,6); -1 2 +col_1 col_2 1 2 5 6 values (1,2) union values (3,4),(5,6); -1 2 +col_1 col_2 1 2 3 4 5 6 @@ -111,21 +111,21 @@ values (1,2) union values (1,2) union values (4,5); -1 2 +col_1 col_2 1 2 4 5 # UNION ALL that uses VALUES structure values (1,2),(3,4) union all select 5,6; -1 2 +col_1 col_2 1 2 3 4 5 6 values (1,2),(3,4) union all select 1,2; -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -146,14 +146,14 @@ values (1,2),(3,4); values (1,2) union all values (1,2),(5,6); -1 2 +col_1 col_2 1 2 1 2 5 6 values (1,2) union all values (3,4),(5,6); -1 2 +col_1 col_2 1 2 3 4 5 6 @@ -162,7 +162,7 @@ union all values (1,2) union all values (4,5); -1 2 +col_1 col_2 1 2 1 2 4 5 @@ -170,14 +170,14 @@ values (1,2) union all values (1,2) union values (1,2); -1 2 +col_1 col_2 1 2 values (1,2) union values (1,2) union all values (1,2); -1 2 +col_1 col_2 1 2 1 2 # EXCEPT that uses VALUES structure(s) @@ -193,24 +193,24 @@ values (1,2),(3,4); values (1,2),(3,4) except select 5,6; -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) except select 1,2; -1 2 +col_1 col_2 3 4 values (1,2),(3,4) except values (5,6); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) except values (1,2); -1 2 +col_1 col_2 3 4 # INTERSECT that uses VALUES structure(s) select 1,2 @@ -225,27 +225,27 @@ values (1,2),(3,4); values (1,2),(3,4) intersect select 5,6; -1 2 +col_1 col_2 values (1,2),(3,4) intersect select 1,2; -1 2 +col_1 col_2 1 2 values (1,2),(3,4) intersect values (5,6); -1 2 +col_1 col_2 values (1,2),(3,4) intersect values (1,2); -1 2 +col_1 col_2 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 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -253,7 +253,7 @@ except values (1,2) union values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -261,14 +261,14 @@ except values (1,2) union values (3,4); -1 2 +col_1 col_2 3 4 values (1,2),(3,4) union values (1,2) except values (1,2); -1 2 +col_1 col_2 3 4 # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT values (1,2),(3,4) @@ -276,7 +276,7 @@ except select 1,2 union all values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -284,7 +284,7 @@ except values (1,2) union all values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -292,7 +292,7 @@ except values (1,2) union all values (3,4); -1 2 +col_1 col_2 3 4 3 4 values (1,2),(3,4) @@ -300,7 +300,7 @@ union all values (1,2) except values (1,2); -1 2 +col_1 col_2 3 4 # combination of different structures that uses VALUES structures : UNION + INTERSECT values (1,2),(3,4) @@ -308,21 +308,21 @@ intersect select 1,2 union values (1,2); -1 2 +col_1 col_2 1 2 values (1,2),(3,4) intersect values (1,2) union values (1,2); -1 2 +col_1 col_2 1 2 values (1,2),(3,4) intersect values (1,2) union values (3,4); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -330,7 +330,7 @@ union values (1,2) intersect values (1,2); -1 2 +col_1 col_2 1 2 3 4 # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT @@ -339,7 +339,7 @@ intersect select 1,2 union all values (1,2); -1 2 +col_1 col_2 1 2 1 2 values (1,2),(3,4) @@ -347,7 +347,7 @@ intersect values (1,2) union all values (1,2); -1 2 +col_1 col_2 1 2 1 2 values (1,2),(3,4) @@ -355,7 +355,7 @@ intersect values (1,2) union all values (3,4); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -363,7 +363,7 @@ union all values (1,2) intersect values (1,2); -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -373,7 +373,7 @@ union all select 1,2 union values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -381,7 +381,7 @@ union all values (1,2) union values (1,2); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -389,7 +389,7 @@ union all values (1,2) union values (3,4); -1 2 +col_1 col_2 1 2 3 4 values (1,2),(3,4) @@ -397,7 +397,7 @@ union values (1,2) union all values (1,2); -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -406,7 +406,7 @@ union values (1,2) union all values (1,2); -1 2 +col_1 col_2 1 2 1 2 # CTE that uses VALUES structure(s) : non-recursive CTE @@ -415,7 +415,7 @@ with t2 as values (1,2),(3,4) ) select * from t2; -1 2 +col_1 col_2 1 2 3 4 with t2 as @@ -444,7 +444,7 @@ union select 1,2 ) select * from t2; -1 2 +col_1 col_2 1 2 with t2 as ( @@ -453,7 +453,7 @@ union select 1,2 ) select * from t2; -1 2 +col_1 col_2 1 2 3 4 with t2 as @@ -463,7 +463,7 @@ union values (1,2),(3,4) ) select * from t2; -5 6 +col_1 col_2 5 6 1 2 3 4 @@ -474,7 +474,7 @@ union values (1,2),(3,4) ) select * from t2; -1 2 +col_1 col_2 1 2 3 4 with t2 as @@ -495,7 +495,7 @@ union all select 1,2 ) select * from t2; -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -506,7 +506,7 @@ union all values (1,2),(3,4) ) select * from t2; -1 2 +col_1 col_2 1 2 1 2 3 4 @@ -594,7 +594,7 @@ n f 10 362880 # Derived table that uses VALUES structure(s) : singe VALUES structure select * from (values (1,2),(3,4)) as t2; -1 2 +col_1 col_2 1 2 3 4 # Derived table that uses VALUES structure(s) : UNION with VALUES structure(s) @@ -606,19 +606,19 @@ select * from (select 1,2 union values (1,2),(3,4)) as t2; 1 2 3 4 select * from (values (1,2) union select 1,2) as t2; -1 2 +col_1 col_2 1 2 select * from (values (1,2),(3,4) union select 1,2) as t2; -1 2 +col_1 col_2 1 2 3 4 select * from (values (5,6) union values (1,2),(3,4)) as t2; -5 6 +col_1 col_2 5 6 1 2 3 4 select * from (values (1,2) union values (1,2),(3,4)) as t2; -1 2 +col_1 col_2 1 2 3 4 # Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s) @@ -628,19 +628,19 @@ select * from (select 1,2 union all values (1,2),(3,4)) as t2; 1 2 3 4 select * from (values (1,2),(3,4) union all select 1,2) as t2; -1 2 +col_1 col_2 1 2 3 4 1 2 select * from (values (1,2) union all values (1,2),(3,4)) as t2; -1 2 +col_1 col_2 1 2 1 2 3 4 # CREATE VIEW that uses VALUES structure(s) : singe VALUES structure create view v1 as values (1,2),(3,4); select * from v1; -1 2 +col_1 col_2 1 2 3 4 drop view v1; @@ -667,7 +667,7 @@ values (1,2) union select 1,2; select * from v1; -1 2 +col_1 col_2 1 2 drop view v1; create view v1 as @@ -675,7 +675,7 @@ values (1,2),(3,4) union select 1,2; select * from v1; -1 2 +col_1 col_2 1 2 3 4 drop view v1; @@ -684,7 +684,7 @@ values (5,6) union values (1,2),(3,4); select * from v1; -5 6 +col_1 col_2 5 6 1 2 3 4 @@ -695,7 +695,7 @@ values (1,2) union values (1,2),(3,4); select * from v1; -1 2 +col_1 col_2 1 2 3 4 drop view v1; @@ -714,7 +714,7 @@ values (1,2),(3,4) union all select 1,2; select * from v1; -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -724,7 +724,7 @@ values (1,2) union all values (1,2),(3,4); select * from v1; -1 2 +col_1 col_2 1 2 1 2 3 4 @@ -748,7 +748,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` explain extended select * from t1 where a in (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -757,7 +757,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`col_1` # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a in (values (1) union select 2); @@ -781,7 +781,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) +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`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) explain extended select * from t1 where a in (select * from (values (1)) as tvc_0 union select 2); @@ -792,7 +792,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) +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`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) # IN-subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a in (select 2 union values (1)); @@ -816,7 +816,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +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`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) explain extended select * from t1 where a in (select 2 union select * from (values (1)) tvc_0); @@ -827,7 +827,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +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`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) # IN-subquery with VALUES structure(s) : UNION ALL select * from t1 where a in (values (1) union all select b from t1); @@ -852,7 +852,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 6 100.00 Using where 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`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union all /* select#3 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`))) +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`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union all /* select#3 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`))) explain extended select * from t1 where a in (select * from (values (1)) as tvc_0 union all select b from t1); @@ -862,7 +862,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used 4 DEPENDENT UNION t1 ALL NULL NULL NULL NULL 6 100.00 Using where 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`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union all /* select#4 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`))) +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`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union all /* select#4 */ select `test`.`t1`.`b` from `test`.`t1` where <cache>(`test`.`t1`.`a`) = `test`.`t1`.`b`))) # NOT IN subquery with VALUES structure(s) : simple case select * from t1 where a not in (values (1),(2)); @@ -883,7 +883,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 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`)))) +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`.`col_1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`col_1`)))) explain extended select * from t1 where a not in (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -891,7 +891,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#2 */ 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` = `<subquery2>`.`1`)))) +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#2 */ select `tvc_0`.`col_1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`col_1`)))) # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a not in (values (1) union select 2); @@ -915,7 +915,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) union /* select#3 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))) +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`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`) union /* select#3 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))) explain extended select * from t1 where a not in (select * from (values (1)) as tvc_0 union select 2); @@ -926,7 +926,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`) union /* select#4 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))) +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`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`) union /* select#4 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2))))) # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a not in (select 2 union values (1)); @@ -950,7 +950,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))) +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`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`)))) explain extended select * from t1 where a not in (select 2 union select * from (values (1)) as tvc_0); @@ -961,7 +961,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`1`)))) +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`,<exists>(/* select#2 */ select 2 having trigcond(<cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`)))) # ANY-subquery with VALUES structure(s) : simple case select * from t1 where a = any (values (1),(2)); @@ -983,7 +983,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 MATERIALIZED <derived2> ALL NULL NULL NULL NULL 2 100.00 2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 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` +Note 1003 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`.`col_1` explain extended select * from t1 where a = any (select * from (values (1),(2)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -992,7 +992,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 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` +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`.`col_1` # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = any (values (1) union select 2); @@ -1016,7 +1016,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) +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`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#3 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) explain extended select * from t1 where a = any (select * from (values (1)) as tvc_0 union select 2); @@ -1027,7 +1027,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) +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`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1` union /* select#4 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2)))) # ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a = any (select 2 union values (1)); @@ -1051,7 +1051,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +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`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) explain extended select * from t1 where a = any (select 2 union select * from (values (1)) as tvc_0); @@ -1062,7 +1062,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +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`,<exists>(/* select#2 */ select 2 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(2) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) # ALL-subquery with VALUES structure(s) : simple case select * from t1 where a = all (values (1)); @@ -1081,7 +1081,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 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 Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`))))) explain extended select * from t1 where a = all (select * from (values (1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra @@ -1089,7 +1089,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where 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` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`))))) # ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place select * from t1 where a = all (values (1) union select 1); @@ -1111,7 +1111,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union4,3> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`) union /* select#3 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1)))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`) union /* select#3 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1)))))) explain extended select * from t1 where a = all (select * from (values (1)) as tvc_0 union select 1); @@ -1122,7 +1122,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DEPENDENT UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`1`) union /* select#4 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1)))))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <not>(<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#2 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) <> `tvc_0`.`col_1`) union /* select#4 */ select 1 having trigcond(<cache>(`test`.`t1`.`a`) <> <ref_null_helper>(1)))))) # ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place select * from t1 where a = any (select 1 union values (1)); @@ -1144,7 +1144,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,4> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#4 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +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`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#4 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) explain extended select * from t1 where a = any (select 1 union select * from (values (1)) as tvc_0); @@ -1155,16 +1155,16 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 4 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL 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`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#3 */ select `tvc_0`.`1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`1`))) +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`,<exists>(/* select#2 */ select 1 having <cache>(`test`.`t1`.`a`) = <ref_null_helper>(1) union /* select#3 */ select `tvc_0`.`col_1` from (values (1)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`col_1`))) # prepare statement that uses VALUES structure(s): single VALUES structure prepare stmt1 from " values (1,2); "; execute stmt1; -1 2 +col_1 col_2 1 2 execute stmt1; -1 2 +col_1 col_2 1 2 deallocate prepare stmt1; # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s) @@ -1188,11 +1188,11 @@ prepare stmt1 from " select 1,2; "; execute stmt1; -1 2 +col_1 col_2 1 2 3 4 execute stmt1; -1 2 +col_1 col_2 1 2 3 4 deallocate prepare stmt1; @@ -1218,12 +1218,12 @@ prepare stmt1 from " values (1,2),(3,4); "; execute stmt1; -5 6 +col_1 col_2 5 6 1 2 3 4 execute stmt1; -5 6 +col_1 col_2 5 6 1 2 3 4 @@ -1249,12 +1249,12 @@ prepare stmt1 from " select 1,2; "; execute stmt1; -1 2 +col_1 col_2 1 2 3 4 1 2 execute stmt1; -1 2 +col_1 col_2 1 2 3 4 1 2 @@ -1283,12 +1283,12 @@ prepare stmt1 from " values (1,2),(3,4); "; execute stmt1; -1 2 +col_1 col_2 1 2 1 2 3 4 execute stmt1; -1 2 +col_1 col_2 1 2 1 2 3 4 @@ -2097,3 +2097,13 @@ v # with t as (values (),()) select 1 from t; ERROR HY000: Row with no elements is not allowed in table value constructor in this context +# +# MDEV-16930: expression in the first row of TVC specifying derived table +# the same constant/expressions in the first row +# +SELECT * FROM (VALUES(1+1,2)) t; +col_1 col_2 +2 2 +SELECT * FROM (VALUES(2,2)) t; +col_1 col_2 +2 2 diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb5ea59..73d0195 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1075,3 +1075,11 @@ DELIMITER ;| --error ER_EMPTY_ROW_IN_TVC with t as (values (),()) select 1 from t; + +--echo # +--echo # MDEV-16930: expression in the first row of TVC specifying derived table +--echo # the same constant/expressions in the first row +--echo # + +SELECT * FROM (VALUES(1+1,2)) t; +SELECT * FROM (VALUES(2,2)) t; diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index 188ba8c..5c81228 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -237,10 +237,25 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl, get_type_attributes_for_tvc(thd, li, holders, lists_of_values.elements, cnt)) DBUG_RETURN(true); - + List_iterator_fast<Item> it(*first_elem); Item *item; - + + /* + Temporarily set comprehensive names for the items in the first_elem list + These names will be used as the column names of the temporary table + that is created for the table value constructor. + */ + size_t name_len; + char buff[NAME_LEN]; + for (uint column_no= 1; (item= it++); column_no++) + { + name_len= my_snprintf(buff, NAME_LEN, "col_%u", column_no); + item->orig_name= item->name.str; + item->set_name(thd, buff, name_len, system_charset_info); + } + + it.rewind(); sl->item_list.empty(); for (uint pos= 0; (item= it++); pos++) { @@ -254,7 +269,19 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl, new_holder->fix_fields(thd, 0); sl->item_list.push_back(new_holder); } - + + /* + Restore the original names of the items in the first_elem list. This is + needed for the proper print of the table value constructor. + */ + it.rewind(); + while((item= it++)) + { + item->set_name(thd, item->orig_name, + item->orig_name ? strlen(item->orig_name) : 0, + system_charset_info); + } + if (unlikely(thd->is_fatal_error)) DBUG_RETURN(true); // out of memory