Mistakes corrected.

TVC can be used in IN subquery and in PARTITION BY struct now.
Special variable to control working of optimization added.

34 files changed, 826 insertions, 80 deletions

diff --git a/mysql-test/r/errors.result b/mysql-test/r/errors.result
index a909366a89b..0b434864323 100644
--- a/mysql-test/r/errors.result
+++ b/mysql-test/r/errors.result
@@ -150,17 +150,17 @@ ERROR 22003: BIGINT value is out of range in '-73 * -2465717823867977728'
 #
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2(a INT PRIMARY KEY, b INT);
-SELECT '' AS b FROM t1 GROUP BY VALUES(b);
+SELECT '' AS b FROM t1 GROUP BY VALUE(b);
 ERROR 42S22: Unknown column '' in 'VALUES() function'
-REPLACE t2(b) SELECT '' AS b FROM t1 GROUP BY VALUES(b);
+REPLACE t2(b) SELECT '' AS b FROM t1 GROUP BY VALUE(b);
 ERROR 42S22: Unknown column '' in 'VALUES() function'
-UPDATE t2 SET a=(SELECT '' AS b FROM t1 GROUP BY VALUES(b));
+UPDATE t2 SET a=(SELECT '' AS b FROM t1 GROUP BY VALUE(b));
 ERROR 42S22: Unknown column '' in 'VALUES() function'
 INSERT INTO t2 VALUES (1,0) ON DUPLICATE KEY UPDATE
-b=(SELECT '' AS b FROM t1 GROUP BY VALUES(b));
+b=(SELECT '' AS b FROM t1 GROUP BY VALUE(b));
 ERROR 42S22: Unknown column '' in 'VALUES() function'
 INSERT INTO t2(a,b) VALUES (1,0) ON DUPLICATE KEY UPDATE
-b=(SELECT VALUES(a)+2 FROM t1);
+b=(SELECT VALUE(a)+2 FROM t1);
 DROP TABLE t1, t2;
 #
 # MDEV-492: incorrect error check before sending OK in mysql_update 
diff --git a/mysql-test/r/func_group_innodb.result b/mysql-test/r/func_group_innodb.result
index 52d5922df95..e340c04107d 100644
--- a/mysql-test/r/func_group_innodb.result
+++ b/mysql-test/r/func_group_innodb.result
@@ -226,7 +226,7 @@ create table y select 1 b;
 select 1 from y group by b;
 1
 1
-select 1 from y group by values(b);
+select 1 from y group by value(b);
 1
 1
 drop table y;
diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index 39f8f41d1e7..1006cdcea5c 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -286,11 +286,11 @@ NAME_CONST('a', -(1)) OR 1
 CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (1,10);
 CREATE VIEW v1 AS SELECT * FROM t1;
-EXPLAIN EXTENDED SELECT VALUES(b) FROM v1;
+EXPLAIN EXTENDED SELECT VALUE(b) FROM v1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	system	NULL	NULL	NULL	NULL	1	100.00	
 Warnings:
-Note	1003	select values(10) AS `VALUES(b)` from dual
+Note	1003	select values(10) AS `VALUE(b)` from dual
 drop view v1;
 drop table t1;
 End of 5.3 tests
diff --git a/mysql-test/r/group_by_null.result b/mysql-test/r/group_by_null.result
index 01053514cb0..1ae090fdc63 100644
--- a/mysql-test/r/group_by_null.result
+++ b/mysql-test/r/group_by_null.result
@@ -1,6 +1,6 @@
 create table t1 (a int);
 insert into t1 values (1),(2);
-select max('foo') from t1 group by values(a), extractvalue('bar','qux') order by "v";
+select max('foo') from t1 group by value(a), extractvalue('bar','qux') order by "v";
 max('foo')
 foo
 drop table t1;
diff --git a/mysql-test/r/insert_update.result b/mysql-test/r/insert_update.result
index e8e6e16fe5a..ec87eeb85a6 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -49,19 +49,19 @@ a	b	c
 5	0	30
 8	9	60
 INSERT t1 VALUES (2,1,11), (7,4,40) ON DUPLICATE KEY UPDATE c=c+VALUES(a);
-SELECT *, VALUES(a) FROM t1;
-a	b	c	VALUES(a)
+SELECT *, VALUE(a) FROM t1;
+a	b	c	VALUE(a)
 1	2	10	NULL
 3	4	127	NULL
 5	0	30	NULL
 8	9	60	NULL
 2	1	11	NULL
-explain extended SELECT *, VALUES(a) FROM t1;
+explain extended SELECT *, VALUE(a) FROM t1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,values(`test`.`t1`.`a`) AS `VALUES(a)` from `test`.`t1`
-explain extended select * from t1 where values(a);
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,values(`test`.`t1`.`a`) AS `VALUE(a)` from `test`.`t1`
+explain extended select * from t1 where value(a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
 Warnings:
@@ -160,8 +160,8 @@ a	b	c
 INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a);
 ERROR 23000: Column 'c' in field list is ambiguous
 INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=t1.c+VALUES(t1.a);
-SELECT *, VALUES(a) FROM t1;
-a	b	c	VALUES(a)
+SELECT *, VALUE(a) FROM t1;
+a	b	c	VALUE(a)
 1	2	10	NULL
 3	4	127	NULL
 5	0	30	NULL
diff --git a/mysql-test/r/mysqld--help.result b/mysql-test/r/mysqld--help.result
index 564269319cb..b3d8f413fea 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -286,6 +286,10 @@ The following options may be given as the first argument:
  Specifies a directory to add to the ignore list when
  collecting database names from the datadir. Put a blank
  argument to reset the list accumulated so far.
+ --in-subquery-conversion-threshold[=#] 
+ The minimum number of scalar elements in the value list
+ ofIN predicate that triggers its conversion to IN
+ subquery
  --init-connect=name Command(s) that are executed for each new connection
  (unless the user has SUPER privilege)
  --init-file=name    Read SQL commands from this file at startup
@@ -1269,6 +1273,7 @@ idle-readwrite-transaction-timeout 0
 idle-transaction-timeout 0
 ignore-builtin-innodb FALSE
 ignore-db-dirs 
+in-subquery-conversion-threshold 10000
 init-connect 
 init-file (No default value)
 init-rpl-role MASTER
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 567be0d0d3f..dbe70490327 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -4649,7 +4649,7 @@ EXECUTE IMMEDIATE MAX('SELECT 1 AS c');
 ERROR HY000: Invalid use of group function
 EXECUTE IMMEDIATE DEFAULT(a);
 ERROR 42S22: Unknown column 'a' in 'field list'
-EXECUTE IMMEDIATE VALUES(a);
+EXECUTE IMMEDIATE VALUE(a);
 ERROR 42S22: Unknown column 'a' in 'field list'
 CREATE FUNCTION f1() RETURNS VARCHAR(64) RETURN 't1';
 EXECUTE IMMEDIATE f1();
diff --git a/mysql-test/r/selectivity.result b/mysql-test/r/selectivity.result
index 8b447f85013..ba9ac04b807 100644
--- a/mysql-test/r/selectivity.result
+++ b/mysql-test/r/selectivity.result
@@ -400,7 +400,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	SUBQUERY	customer	ALL	NULL	NULL	NULL	NULL	150	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'dbt3_s001.customer.c_custkey' of SELECT #4 was resolved in SELECT #2
-Note	1003	/* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey`)) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2)
+Note	1003	/* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,<expr_cache><`dbt3_s001`.`customer`.`c_custkey`>(exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey`))) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2)
 select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal
 from  (
 select substr(c_phone, 1, 2) as cntrycode, c_acctbal
@@ -441,7 +441,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	SUBQUERY	customer	ALL	NULL	NULL	NULL	NULL	150	91.00	Using where
 Warnings:
 Note	1276	Field or reference 'dbt3_s001.customer.c_custkey' of SELECT #4 was resolved in SELECT #2
-Note	1003	/* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey`)) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2)
+Note	1003	/* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,<expr_cache><`dbt3_s001`.`customer`.`c_custkey`>(exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey`))) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2)
 select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal
 from  (
 select substr(c_phone, 1, 2) as cntrycode, c_acctbal
diff --git a/mysql-test/r/selectivity_innodb.result b/mysql-test/r/selectivity_innodb.result
index 8128edb2901..e35512290b5 100644
--- a/mysql-test/r/selectivity_innodb.result
+++ b/mysql-test/r/selectivity_innodb.result
@@ -403,7 +403,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	SUBQUERY	customer	ALL	NULL	NULL	NULL	NULL	150	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'dbt3_s001.customer.c_custkey' of SELECT #4 was resolved in SELECT #2
-Note	1003	/* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey`)) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2)
+Note	1003	/* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,<expr_cache><`dbt3_s001`.`customer`.`c_custkey`>(exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey`))) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2)
 select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal
 from  (
 select substr(c_phone, 1, 2) as cntrycode, c_acctbal
@@ -444,7 +444,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	SUBQUERY	customer	ALL	NULL	NULL	NULL	NULL	150	91.00	Using where
 Warnings:
 Note	1276	Field or reference 'dbt3_s001.customer.c_custkey' of SELECT #4 was resolved in SELECT #2
-Note	1003	/* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey`)) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2)
+Note	1003	/* select#1 */ select substr(`dbt3_s001`.`customer`.`c_phone`,1,2) AS `cntrycode`,count(0) AS `numcust`,sum(`dbt3_s001`.`customer`.`c_acctbal`) AS `totacctbal` from `dbt3_s001`.`customer` where substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25') and `dbt3_s001`.`customer`.`c_acctbal` > (/* select#3 */ select avg(`dbt3_s001`.`customer`.`c_acctbal`) from `dbt3_s001`.`customer` where `dbt3_s001`.`customer`.`c_acctbal` > 0.00 and substr(`dbt3_s001`.`customer`.`c_phone`,1,2) in ('10','20','14','19','11','28','25')) and !<in_optimizer>(1,<expr_cache><`dbt3_s001`.`customer`.`c_custkey`>(exists(/* select#4 */ select 1 from `dbt3_s001`.`orders` where `dbt3_s001`.`orders`.`o_custkey` = `dbt3_s001`.`customer`.`c_custkey`))) group by substr(`dbt3_s001`.`customer`.`c_phone`,1,2) order by substr(`dbt3_s001`.`customer`.`c_phone`,1,2)
 select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal
 from  (
 select substr(c_phone, 1, 2) as cntrycode, c_acctbal
diff --git a/mysql-test/r/table_elim.result b/mysql-test/r/table_elim.result
index 04a9b47b6c9..cf9a4a38779 100644
--- a/mysql-test/r/table_elim.result
+++ b/mysql-test/r/table_elim.result
@@ -143,7 +143,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	DEPENDENT SUBQUERY	t2	ref	PRIMARY	PRIMARY	4	test.a2.id	2	100.00	Using index
 Warnings:
 Note	1276	Field or reference 'test.a2.id' of SELECT #3 was resolved in SELECT #2
-Note	1003	/* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where `f`.`id` = `a2`.`id` and `a2`.`attr2` between 12 and 14 and `a2`.`fromdate` = (/* select#3 */ select max(`test`.`t2`.`fromdate`) from `test`.`t2` where `test`.`t2`.`id` = `a2`.`id`)
+Note	1003	/* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where `f`.`id` = `a2`.`id` and `a2`.`attr2` between 12 and 14 and `a2`.`fromdate` = <expr_cache><`a2`.`id`>((/* select#3 */ select max(`test`.`t2`.`fromdate`) from `test`.`t2` where `test`.`t2`.`id` = `a2`.`id`))
 This should use one table:
 explain select id from v2 where id=2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
@@ -171,7 +171,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	DEPENDENT SUBQUERY	t2	ref	PRIMARY	PRIMARY	4	test.f.id	2	100.00	Using index
 Warnings:
 Note	1276	Field or reference 'test.f.id' of SELECT #3 was resolved in SELECT #2
-Note	1003	/* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where `f`.`id` = `a2`.`id` and `a2`.`attr2` between 12 and 14 and `a2`.`fromdate` = (/* select#3 */ select max(`test`.`t2`.`fromdate`) from `test`.`t2` where `test`.`t2`.`id` = `f`.`id`)
+Note	1003	/* select#1 */ select `f`.`id` AS `id` from `test`.`t0` `f` join `test`.`t2` `a2` where `f`.`id` = `a2`.`id` and `a2`.`attr2` between 12 and 14 and `a2`.`fromdate` = <expr_cache><`f`.`id`>((/* select#3 */ select max(`test`.`t2`.`fromdate`) from `test`.`t2` where `test`.`t2`.`id` = `f`.`id`))
 drop view v1, v2;
 drop table t0, t1, t2;
 create table t1 (a int);
diff --git a/mysql-test/r/table_value_constr.result b/mysql-test/r/table_value_constr.result
index 5c64085138c..39caba331ef 100644
--- a/mysql-test/r/table_value_constr.result
+++ b/mysql-test/r/table_value_constr.result
@@ -729,6 +729,433 @@ select * from v1;
 1	2
 3	4
 drop view v1;
+# IN-subquery with VALUES structure(s) : simple case
+select * from t1
+where a in (values (1));
+a	b
+1	2
+1	1
+select * from t1
+where a in (select * from (values (1)) as tvc_0);
+a	b
+1	2
+1	1
+explain extended select * from t1
+where a in (values (1));
+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	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+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`
+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
+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)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`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);
+a	b
+1	2
+1	1
+2	5
+select * from t1
+where a in (select * from (values (1)) as tvc_0 union 
+select 2);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1
+where a in (values (1) union select 2);
+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
+4	DEPENDENT SUBQUERY	<derived2>	ref	key0	key0	4	func	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+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))))
+explain extended select * from t1
+where a in (select * from (values (1)) as tvc_0 union 
+select 2);
+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
+2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+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))))
+# IN-subquery with VALUES structure(s) : UNION with VALUES on the second place
+select * from t1
+where a in (select 2 union values (1));
+a	b
+1	2
+1	1
+2	5
+select * from t1
+where a in (select 2 union 
+select * from (values (1)) tvc_0);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1
+where a in (select 2 union values (1));
+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
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<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`)))
+explain extended select * from t1
+where a in (select 2 union 
+select * from (values (1)) 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
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	2	100.00	
+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`)))
+# IN-subquery with VALUES structure(s) : UNION ALL
+select * from t1
+where a in (values (1) union all select b from t1);
+a	b
+1	2
+1	1
+2	5
+7	8
+select * from t1
+where a in (select * from (values (1)) as tvc_0 union all 
+select b from t1);
+a	b
+1	2
+1	1
+2	5
+7	8
+explain extended select * from t1
+where a in (values (1) union all select b from t1);
+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
+4	DEPENDENT SUBQUERY	<derived2>	ref	key0	key0	4	func	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+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`)))
+explain extended select * from t1
+where a in (select * from (values (1)) as tvc_0 union all 
+select b from t1);
+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
+2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	4	func	2	100.00	
+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`)))
+# NOT IN subquery with VALUES structure(s) : simple case
+select * from t1 
+where a not in (values (1),(2));
+a	b
+4	6
+9	7
+7	8
+select * from t1 
+where a not in (select * from (values (1),(2)) as tvc_0);
+a	b
+4	6
+9	7
+7	8
+explain extended select * from t1 
+where a not in (values (1),(2));
+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	<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`))))
+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
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+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`))))
+# 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);
+a	b
+4	6
+9	7
+7	8
+select * from t1
+where a not in (select * from (values (1)) as tvc_0 union 
+select 2);
+a	b
+4	6
+9	7
+7	8
+explain extended select * from t1
+where a not in (values (1) union select 2);
+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
+4	DEPENDENT SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+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)))))
+explain extended select * from t1
+where a not in (select * from (values (1)) as tvc_0 union 
+select 2);
+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
+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
+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)))))
+# 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));
+a	b
+4	6
+9	7
+7	8
+select * from t1
+where a not in (select 2 union 
+select * from (values (1)) as tvc_0);
+a	b
+4	6
+9	7
+7	8
+explain extended select * from t1
+where a not in (select 2 union values (1));
+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
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<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`))))
+explain extended select * from t1
+where a not in (select 2 union 
+select * from (values (1)) 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	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+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`))))
+# ANY-subquery with VALUES structure(s) : simple case
+select * from t1 
+where a = any (values (1),(2));
+a	b
+1	2
+1	1
+2	5
+select * from t1 
+where a = any (select * from (values (1),(2)) as tvc_0);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1 
+where a = any (values (1),(2));
+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	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+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`
+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
+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`
+# ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place
+select * from t1 
+where a = any (values (1) union select 2);
+a	b
+1	2
+1	1
+2	5
+select * from t1
+where a = any (select * from (values (1)) as tvc_0 union 
+select 2);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1 
+where a = any (values (1) union select 2);
+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
+4	DEPENDENT SUBQUERY	<derived2>	ref	key0	key0	4	func	2	100.00	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+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))))
+explain extended select * from t1
+where a = any (select * from (values (1)) as tvc_0 union 
+select 2);
+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
+2	DEPENDENT SUBQUERY	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+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))))
+# ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place
+select * from t1
+where a = any (select 2 union values (1));
+a	b
+1	2
+1	1
+2	5
+select * from t1
+where a = any (select 2 union 
+select * from (values (1)) as tvc_0);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1
+where a = any (select 2 union values (1));
+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
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<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`)))
+explain extended select * from t1
+where a = any (select 2 union 
+select * from (values (1)) 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	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	2	100.00	
+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`)))
+# ALL-subquery with VALUES structure(s) : simple case
+select * from t1 
+where a = all (values (1));
+a	b
+1	2
+1	1
+select * from t1 
+where a = all (select * from (values (1)) as tvc_0);
+a	b
+1	2
+1	1
+explain extended select * from t1 
+where a = all (values (1));
+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	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`)))))
+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
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+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`)))))
+# ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place
+select * from t1 
+where a = all (values (1) union select 1);
+a	b
+1	2
+1	1
+select * from t1
+where a = all (select * from (values (1)) as tvc_0 union 
+select 1);
+a	b
+1	2
+1	1
+explain extended select * from t1 
+where a = all (values (1) union select 1);
+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
+4	DEPENDENT SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+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))))))
+explain extended select * from t1
+where a = all (select * from (values (1)) as tvc_0 union 
+select 1);
+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
+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
+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))))))
+# ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place
+select * from t1
+where a = any (select 1 union values (1));
+a	b
+1	2
+1	1
+select * from t1
+where a = any (select 1 union 
+select * from (values (1)) as tvc_0);
+a	b
+1	2
+1	1
+explain extended select * from t1
+where a = any (select 1 union values (1));
+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
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+4	DEPENDENT UNION	<derived3>	ref	key0	key0	4	func	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<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`)))
+explain extended select * from t1
+where a = any (select 1 union 
+select * from (values (1)) 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	Using where
+2	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT UNION	<derived4>	ref	key0	key0	4	func	2	100.00	
+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`)))
 # prepare statement that uses VALUES structure(s): single VALUES structure
 prepare stmt1 from "
 values (1,2);
@@ -1633,19 +2060,14 @@ ANALYZE
 # different number of values in TVC
 values (1,2),(3,4,5);
 ERROR HY000: The used table value constructor has a different number of values
-# subquery that uses VALUES structure(s)
-select * from t1
-where a in (values (1));
-ERROR HY000: Table value constructor can't be used as specification of subquery isn't implemented yet
-select * from t1
-where a in (select 2 union values (1));
-ERROR HY000: Table value constructor can't be used as specification of subquery isn't implemented yet
-select * from t1
-where a in (values (1) union select 2);
-ERROR HY000: Table value constructor can't be used as specification of subquery isn't implemented yet
 # illegal parameter data types in TVC
 values (1,point(1,1)),(1,1);
 ERROR HY000: Illegal parameter data types geometry and int for operation 'TABLE VALUE CONSTRUCTOR'
 values (1,point(1,1)+1);
 ERROR HY000: Illegal parameter data types geometry and int for operation '+'
+# field reference in TVC
+select * from (values (1), (b), (2)) as new_tvc;
+ERROR HY000: Field reference 'b' can't be used in table value constructor
+select * from (values (1), (t1.b), (2)) as new_tvc;
+ERROR HY000: Field reference 't1.b' can't be used in table value constructor
 drop table t1;
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index 676432690b4..ce977bffdc0 100644
--- a/mysql-test/r/variables.result
+++ b/mysql-test/r/variables.result
@@ -1789,7 +1789,7 @@ set session collation_database=2048;
 ERROR HY000: Unknown collation: '2048'
 set session rand_seed1=DEFAULT;
 ERROR 42000: Variable 'rand_seed1' doesn't have a default value
-set autocommit = values(v);
+set autocommit = value(v);
 ERROR 42S22: Unknown column 'v' in 'field list'
 set session sql_mode=ansi_quotes;
 select * from information_schema.session_variables where variable_name='sql_mode';
diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test
index a3b349bb384..d310f4b85af 100644
--- a/mysql-test/t/default.test
+++ b/mysql-test/t/default.test
@@ -180,7 +180,7 @@ CREATE TABLE t1 (a INT DEFAULT 10);
 INSERT INTO t1 VALUES (11);
 CREATE VIEW v1 AS SELECT a AS a FROM t1;
 CREATE VIEW v2 AS SELECT DEFAULT(a) AS a FROM t1;
-CREATE VIEW v3 AS SELECT VALUES(a) AS a FROM t1;
+CREATE VIEW v3 AS SELECT VALUE(a) AS a FROM t1;
 SELECT * FROM v1;
 SELECT * FROM v2;
 SELECT * FROM v3;
@@ -447,7 +447,7 @@ CALL p1;
 DROP PROCEDURE p1;
 
 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
-CREATE TABLE t1 (a INT DEFAULT VALUES(a));
+CREATE TABLE t1 (a INT DEFAULT VALUE(a));
 
 
 CREATE TABLE t1 (a INT);
diff --git a/mysql-test/t/errors.test b/mysql-test/t/errors.test
index 6ce6e439919..6b559d60eb4 100644
--- a/mysql-test/t/errors.test
+++ b/mysql-test/t/errors.test
@@ -178,16 +178,16 @@ SELECT UPDATEXML(-73 * -2465717823867977728,@@global.auto_increment_increment,nu
 CREATE TABLE t1 (a INT);
 CREATE TABLE t2(a INT PRIMARY KEY, b INT);
 --error ER_BAD_FIELD_ERROR
-SELECT '' AS b FROM t1 GROUP BY VALUES(b);
+SELECT '' AS b FROM t1 GROUP BY VALUE(b);
 --error ER_BAD_FIELD_ERROR
-REPLACE t2(b) SELECT '' AS b FROM t1 GROUP BY VALUES(b);
+REPLACE t2(b) SELECT '' AS b FROM t1 GROUP BY VALUE(b);
 --error ER_BAD_FIELD_ERROR
-UPDATE t2 SET a=(SELECT '' AS b FROM t1 GROUP BY VALUES(b));
+UPDATE t2 SET a=(SELECT '' AS b FROM t1 GROUP BY VALUE(b));
 --error ER_BAD_FIELD_ERROR
 INSERT INTO t2 VALUES (1,0) ON DUPLICATE KEY UPDATE
-  b=(SELECT '' AS b FROM t1 GROUP BY VALUES(b));
+  b=(SELECT '' AS b FROM t1 GROUP BY VALUE(b));
 INSERT INTO t2(a,b) VALUES (1,0) ON DUPLICATE KEY UPDATE
-  b=(SELECT VALUES(a)+2 FROM t1);
+  b=(SELECT VALUE(a)+2 FROM t1);
 DROP TABLE t1, t2;
 
 --echo #
diff --git a/mysql-test/t/func_group_innodb.test b/mysql-test/t/func_group_innodb.test
index c62d3d08496..1d175f85ed9 100644
--- a/mysql-test/t/func_group_innodb.test
+++ b/mysql-test/t/func_group_innodb.test
@@ -168,7 +168,7 @@ set storage_engine=innodb;
 
 create table y select 1 b;
 select 1 from y group by b;
-select 1 from y group by values(b);
+select 1 from y group by value(b);
 drop table y;
 SET storage_engine=@old_engine;
 
diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test
index c661819424a..137b75e9983 100644
--- a/mysql-test/t/func_misc.test
+++ b/mysql-test/t/func_misc.test
@@ -316,7 +316,7 @@ CREATE TABLE t1 (a INT, b INT) ENGINE=MyISAM;
 INSERT INTO t1 VALUES (1,10);
 CREATE VIEW v1 AS SELECT * FROM t1;
 
-EXPLAIN EXTENDED SELECT VALUES(b) FROM v1;
+EXPLAIN EXTENDED SELECT VALUE(b) FROM v1;
 
 drop view v1;
 drop table t1;
diff --git a/mysql-test/t/group_by_null.test b/mysql-test/t/group_by_null.test
index b3fa2a003ec..93e965671dd 100644
--- a/mysql-test/t/group_by_null.test
+++ b/mysql-test/t/group_by_null.test
@@ -3,5 +3,5 @@
 #
 create table t1 (a int);
 insert into t1 values (1),(2);
-select max('foo') from t1 group by values(a), extractvalue('bar','qux') order by "v";
+select max('foo') from t1 group by value(a), extractvalue('bar','qux') order by "v";
 drop table t1;
diff --git a/mysql-test/t/insert_update.test b/mysql-test/t/insert_update.test
index 7234973eeb8..06e16be84d7 100644
--- a/mysql-test/t/insert_update.test
+++ b/mysql-test/t/insert_update.test
@@ -22,9 +22,9 @@ SELECT * FROM t1;
 INSERT t1 SET a=5 ON DUPLICATE KEY UPDATE b=0;
 SELECT * FROM t1;
 INSERT t1 VALUES (2,1,11), (7,4,40) ON DUPLICATE KEY UPDATE c=c+VALUES(a);
-SELECT *, VALUES(a) FROM t1;
-explain extended SELECT *, VALUES(a) FROM t1;
-explain extended select * from t1 where values(a);
+SELECT *, VALUE(a) FROM t1;
+explain extended SELECT *, VALUE(a) FROM t1;
+explain extended select * from t1 where value(a);
 DROP TABLE t1;
 
 #
@@ -79,7 +79,7 @@ SELECT * FROM t1;
 --error ER_NON_UNIQ_ERROR
 INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=c+VALUES(a);
 INSERT t1 SELECT a,b,c FROM t2 WHERE d=2 ON DUPLICATE KEY UPDATE c=t1.c+VALUES(t1.a);
-SELECT *, VALUES(a) FROM t1;
+SELECT *, VALUE(a) FROM t1;
 DROP TABLE t1;
 DROP TABLE t2;
 
diff --git a/mysql-test/t/opt_tvc.test b/mysql-test/t/opt_tvc.test
index d04c101e87a..6fcb1c5d1aa 100644
--- a/mysql-test/t/opt_tvc.test
+++ b/mysql-test/t/opt_tvc.test
@@ -283,4 +283,4 @@ eval explain extended $query;
 set @@in_subquery_conversion_threshold= 2;
 
 drop table t1, t2, t3;
-set @@in_subquery_conversion_threshold= default;
+set @@in_subquery_conversion_threshold= default;
\ No newline at end of file
diff --git a/mysql-test/t/ps.test b/mysql-test/t/ps.test
index b6d1b4862a6..ce2d26f0dca 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -4154,7 +4154,7 @@ EXECUTE IMMEDIATE MAX('SELECT 1 AS c');
 EXECUTE IMMEDIATE DEFAULT(a);
 
 --error ER_BAD_FIELD_ERROR
-EXECUTE IMMEDIATE VALUES(a);
+EXECUTE IMMEDIATE VALUE(a);
 
 
 CREATE FUNCTION f1() RETURNS VARCHAR(64) RETURN 't1';
diff --git a/mysql-test/t/table_value_constr.test b/mysql-test/t/table_value_constr.test
index c24cbc40137..57a9817f0c2 100644
--- a/mysql-test/t/table_value_constr.test
+++ b/mysql-test/t/table_value_constr.test
@@ -554,6 +554,181 @@ create view v1 as
 eval $select_view;
 eval $drop_view;
 
+--echo # IN-subquery with VALUES structure(s) : simple case
+let $query= 
+select * from t1
+where a in (values (1));
+let $subst_query= 
+select * from t1
+where a in (select * from (values (1)) as tvc_0);
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # IN-subquery with VALUES structure(s) : UNION with VALUES on the first place
+let $query=
+select * from t1
+where a in (values (1) union select 2);
+let $subst_query=
+select * from t1
+where a in (select * from (values (1)) as tvc_0 union 
+	    select 2);
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # IN-subquery with VALUES structure(s) : UNION with VALUES on the second place
+let $query= 
+select * from t1
+where a in (select 2 union values (1));
+let $subst_query= 
+select * from t1
+where a in (select 2 union 
+            select * from (values (1)) tvc_0);
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # IN-subquery with VALUES structure(s) : UNION ALL
+let $query=
+select * from t1
+where a in (values (1) union all select b from t1);
+let $subst_query=
+select * from t1
+where a in (select * from (values (1)) as tvc_0 union all 
+            select b from t1);
+
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # NOT IN subquery with VALUES structure(s) : simple case
+let $query=
+select * from t1 
+where a not in (values (1),(2));
+let $subst_query=
+select * from t1 
+where a not in (select * from (values (1),(2)) as tvc_0);
+
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the first place
+let $query=
+select * from t1
+where a not in (values (1) union select 2);
+let $subst_query=
+select * from t1
+where a not in (select * from (values (1)) as tvc_0 union 
+                select 2);
+                
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # NOT IN subquery with VALUES structure(s) : UNION with VALUES on the second place
+let $query=
+select * from t1
+where a not in (select 2 union values (1));
+let $subst_query=
+select * from t1
+where a not in (select 2 union 
+		select * from (values (1)) as tvc_0);
+		
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # ANY-subquery with VALUES structure(s) : simple case
+let $query=
+select * from t1 
+where a = any (values (1),(2));
+let $subst_query=
+select * from t1 
+where a = any (select * from (values (1),(2)) as tvc_0);
+
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # ANY-subquery with VALUES structure(s) : UNION with VALUES on the first place
+let $query=
+select * from t1 
+where a = any (values (1) union select 2);
+let $subst_query=
+select * from t1
+where a = any (select * from (values (1)) as tvc_0 union 
+               select 2);
+                
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # ANY-subquery with VALUES structure(s) : UNION with VALUES on the second place
+let $query=
+select * from t1
+where a = any (select 2 union values (1));
+let $subst_query=
+select * from t1
+where a = any (select 2 union 
+	       select * from (values (1)) as tvc_0);
+		
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # ALL-subquery with VALUES structure(s) : simple case
+let $query=
+select * from t1 
+where a = all (values (1));
+let $subst_query=
+select * from t1 
+where a = all (select * from (values (1)) as tvc_0);
+
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # ALL-subquery with VALUES structure(s) : UNION with VALUES on the first place
+let $query=
+select * from t1 
+where a = all (values (1) union select 1);
+let $subst_query=
+select * from t1
+where a = all (select * from (values (1)) as tvc_0 union 
+               select 1);
+                
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
+--echo # ALL-subquery with VALUES structure(s) : UNION with VALUES on the second place
+let $query=
+select * from t1
+where a = any (select 1 union values (1));
+let $subst_query=
+select * from t1
+where a = any (select 1 union 
+	       select * from (values (1)) as tvc_0);
+		
+eval $query;
+eval $subst_query;
+eval explain extended $query;
+eval explain extended $subst_query;
+
 --echo # prepare statement that uses VALUES structure(s): single VALUES structure
 
 prepare stmt1 from "
@@ -856,21 +1031,16 @@ values (1,2);
 --error ER_WRONG_NUMBER_OF_VALUES_IN_TVC
 values (1,2),(3,4,5);
 
---echo # subquery that uses VALUES structure(s)
---error ER_TVC_IN_SUBQUERY
-select * from t1
-where a in (values (1));
---error ER_TVC_IN_SUBQUERY
-select * from t1
-where a in (select 2 union values (1));
---error ER_TVC_IN_SUBQUERY
-select * from t1
-where a in (values (1) union select 2);
-
 --echo # illegal parameter data types in TVC
 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
 values (1,point(1,1)),(1,1);
 --error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION
 values (1,point(1,1)+1);
 
+--echo # field reference in TVC
+--error ER_FIELD_REFERENCE_IN_TVC
+select * from (values (1), (b), (2)) as new_tvc;
+--error ER_FIELD_REFERENCE_IN_TVC
+select * from (values (1), (t1.b), (2)) as new_tvc;
+
 drop table t1;
\ No newline at end of file
diff --git a/mysql-test/t/variables.test b/mysql-test/t/variables.test
index 1ba20f0ac9e..8d831567252 100644
--- a/mysql-test/t/variables.test
+++ b/mysql-test/t/variables.test
@@ -1521,7 +1521,7 @@ set session rand_seed1=DEFAULT;
 # Bug #14211565 CRASH WHEN ATTEMPTING TO SET SYSTEM VARIABLE TO RESULT OF VALUES()
 #
 --error ER_BAD_FIELD_ERROR
-set autocommit = values(v);
+set autocommit = value(v);
 
 #
 # MDEV-6673 I_S.SESSION_VARIABLES shows global values
diff --git a/sql/gen_lex_token.cc b/sql/gen_lex_token.cc
index eefe9163819..a6ed8124867 100644
--- a/sql/gen_lex_token.cc
+++ b/sql/gen_lex_token.cc
@@ -132,6 +132,8 @@ void compute_tokens()
 
   set_token(WITH_CUBE_SYM, "WITH CUBE");
   set_token(WITH_ROLLUP_SYM, "WITH ROLLUP");
+  set_token(VALUES_IN_SYM, "VALUES IN");
+  set_token(VALUES_LESS_SYM, "VALUES LESS");
   set_token(NOT2_SYM, "!");
   set_token(OR2_SYM, "|");
   set_token(PARAM_MARKER, "?");
diff --git a/sql/item.cc b/sql/item.cc
index f4236eee013..0719e72cd8f 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5683,6 +5683,13 @@ bool Item_field::fix_fields(THD *thd, Item **reference)
   DBUG_ASSERT(fixed == 0);
   Field *from_field= (Field *)not_found_field;
   bool outer_fixed= false;
+  
+  if (thd->lex->current_select->in_tvc)
+  {
+    my_error(ER_FIELD_REFERENCE_IN_TVC, MYF(0),
+             full_name(), thd->where);
+    return(1);
+  }
 
   if (!field)					// If field is not checked
   {
@@ -9010,7 +9017,7 @@ bool Item_insert_value::fix_fields(THD *thd, Item **items)
 
 void Item_insert_value::print(String *str, enum_query_type query_type)
 {
-  str->append(STRING_WITH_LEN("values("));
+  str->append(STRING_WITH_LEN("value("));
   arg->print(str, query_type);
   str->append(')');
 }
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index efa71a0e8af..eb7d1a97030 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -119,7 +119,7 @@ void Item_subselect::init(st_select_lex *select_lex,
     parsing_place= (outer_select->in_sum_expr ?
                     NO_MATTER :
                     outer_select->parsing_place);
-    if (unit->is_unit_op())
+    if (unit->is_unit_op() && unit->first_select()->next_select())
       engine= new subselect_union_engine(unit, result, this);
     else
       engine= new subselect_single_select_engine(select_lex, result, this);
@@ -269,9 +269,7 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
   {
     if (sl->tvc)
     {
-      my_error(ER_TVC_IN_SUBQUERY, MYF(0));
-      res= 1;
-      goto end;
+      wrap_tvc_in_derived_table(thd, sl);
     }
   }
   
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index cb60b646979..cdba3cd737b 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -266,6 +266,7 @@ public:
   Item* build_clone(THD *thd, MEM_ROOT *mem_root) { return 0; }
   Item* get_copy(THD *thd, MEM_ROOT *mem_root) { return 0; }
 
+  bool wrap_tvc_in_derived_table(THD *thd, st_select_lex *tvc_sl);
 
   friend class select_result_interceptor;
   friend class Item_in_optimizer;
@@ -874,6 +875,7 @@ public:
   virtual enum_engine_type engine_type() { return SINGLE_SELECT_ENGINE; }
   int get_identifier();
   void force_reexecution();
+  void change_select(st_select_lex *new_select) { select_lex= new_select; }
 
   friend class subselect_hash_sj_engine;
   friend class Item_in_subselect;
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 47a5478d635..27a70a7fe6d 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7492,5 +7492,5 @@ ER_SP_STACK_TRACE
         eng "At line %u in %s"
 ER_WRONG_NUMBER_OF_VALUES_IN_TVC
         eng "The used table value constructor has a different number of values"
-ER_TVC_IN_SUBQUERY
-	eng "Table value constructor can't be used as specification of subquery isn't implemented yet"
\ No newline at end of file
+ER_FIELD_REFERENCE_IN_TVC
+	eng "Field reference '%-.192s' can't be used in table value constructor"
\ No newline at end of file
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 08ad0245fb4..58bce754773 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1306,7 +1306,7 @@ int MYSQLlex(YYSTYPE *yylval, THD *thd)
 {
   Lex_input_stream *lip= & thd->m_parser_state->m_lip;
   int token;
-
+  
   if (lip->lookahead_token >= 0)
   {
     /*
@@ -1349,6 +1349,25 @@ int MYSQLlex(YYSTYPE *yylval, THD *thd)
       return WITH;
     }
     break;
+  case VALUES:
+    if (thd->lex->current_select->parsing_place == IN_UPDATE_ON_DUP_KEY
+       // || thd->lex->current_select->parsing_place == IN_PARTITIONING
+    )
+      return VALUE_SYM;
+    token= lex_one_token(yylval, thd);
+    lip->add_digest_token(token, yylval);
+    switch(token) {
+    case LESS_SYM:
+      return VALUES_LESS_SYM;
+    case IN_SYM:
+      return VALUES_IN_SYM;
+    default:
+      lip->lookahead_yylval= lip->yylval;
+      lip->yylval= NULL;
+      lip->lookahead_token= token;
+      return VALUES;
+    }
+    break;
   default:
     break;
   }
@@ -2225,6 +2244,7 @@ void st_select_lex::init_query()
   window_specs.empty();
   window_funcs.empty();
   tvc= 0;
+  in_tvc= false;
 }
 
 void st_select_lex::init_select()
@@ -2266,6 +2286,7 @@ void st_select_lex::init_select()
   tvc= 0;
   in_funcs.empty();
   curr_tvc_name= 0;
+  in_tvc= false;
 }
 
 /*
@@ -2810,10 +2831,7 @@ void st_select_lex_unit::print(String *str, enum_query_type query_type)
     }
     if (sl->braces)
       str->append('(');
-    if (sl->tvc)
-      sl->tvc->print(thd, str, query_type);
-    else
-      sl->print(thd, str, query_type);
+    sl->print(thd, str, query_type);
     if (sl->braces)
       str->append(')');
   }
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index ef5b6108044..d781e14a10a 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -1012,6 +1012,7 @@ public:
   thr_lock_type lock_type;
   
   table_value_constr *tvc;
+  bool in_tvc;
 
   void init_query();
   void init_select();
diff --git a/sql/sql_priv.h b/sql/sql_priv.h
index 67547f7a030..245eb7fbe3d 100644
--- a/sql/sql_priv.h
+++ b/sql/sql_priv.h
@@ -349,6 +349,8 @@ enum enum_parsing_place
   IN_ON,
   IN_GROUP_BY,
   IN_ORDER_BY,
+  IN_UPDATE_ON_DUP_KEY,
+  IN_PARTITIONING,
   PARSING_PLACE_SIZE /* always should be the last */
 };
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 870c40d5b16..cf4fbce7e5a 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -25182,6 +25182,12 @@ void TABLE_LIST::print(THD *thd, table_map eliminated_tables, String *str,
 void st_select_lex::print(THD *thd, String *str, enum_query_type query_type)
 {
   DBUG_ASSERT(thd);
+  
+  if (tvc)
+  {
+    tvc->print(thd, str, query_type);
+    return;
+  }
 
   if ((query_type & QT_SHOW_SELECT_NUMBER) &&
       thd->lex->all_selects_list &&
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 0b1dd2f4408..94462e6ad01 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -195,6 +195,7 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl,
 				 st_select_lex_unit *unit_arg)
 {
   DBUG_ENTER("table_value_constr::prepare");
+  select_lex->in_tvc= true;
   List_iterator_fast<List_item> li(lists_of_values);
   
   List_item *first_elem= li++;
@@ -237,6 +238,7 @@ bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl,
   if (result && result->prepare(sl->item_list, unit_arg))
     DBUG_RETURN(true);
 
+  select_lex->in_tvc= false;
   DBUG_RETURN(false);
 }
 
@@ -496,6 +498,94 @@ static bool create_tvc_name(THD *thd, st_select_lex *parent_select,
 }
 
 
+bool Item_subselect::wrap_tvc_in_derived_table(THD *thd,
+					       st_select_lex *tvc_sl)
+{
+  LEX *lex= thd->lex;
+  /* SELECT_LEX object where the transformation is performed */
+  SELECT_LEX *parent_select= lex->current_select;
+  uint8 save_derived_tables= lex->derived_tables;
+
+  Query_arena backup;
+  Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup);
+
+  /*
+    Create SELECT_LEX of the subquery SQ used in the result of transformation
+  */
+  lex->current_select= tvc_sl;
+  if (mysql_new_select(lex, 0, NULL))
+    goto err;
+  mysql_init_select(lex);
+  /* Create item list as '*' for the subquery SQ */
+  Item *item;
+  SELECT_LEX *sq_select; // select for IN subquery;
+  sq_select= lex->current_select;
+  sq_select->linkage= tvc_sl->linkage;
+  sq_select->parsing_place= SELECT_LIST;
+  item= new (thd->mem_root) Item_field(thd, &sq_select->context,
+                                       NULL, NULL, &star_clex_str);
+  if (item == NULL || add_item_to_list(thd, item))
+    goto err;
+  (sq_select->with_wild)++;
+  
+  /* Exclude SELECT with TVC */
+  tvc_sl->exclude();
+  /*
+    Create derived table DT that will wrap TVC in the result of transformation
+  */
+  SELECT_LEX *tvc_select; // select for tvc
+  SELECT_LEX_UNIT *derived_unit; // unit for tvc_select
+  if (mysql_new_select(lex, 1, tvc_sl))
+    goto err;
+  tvc_select= lex->current_select;
+  derived_unit= tvc_select->master_unit();
+  tvc_select->linkage= DERIVED_TABLE_TYPE;
+
+  lex->current_select= sq_select;
+
+  /*
+    Create the name of the wrapping derived table and
+    add it to the FROM list of the subquery SQ
+   */
+  Table_ident *ti;
+  LEX_CSTRING alias;
+  TABLE_LIST *derived_tab;
+  if (!(ti= new (thd->mem_root) Table_ident(derived_unit)) ||
+      create_tvc_name(thd, parent_select, &alias))
+    goto err;
+  if (!(derived_tab=
+          sq_select->add_table_to_list(thd,
+				       ti, &alias, 0,
+                                       TL_READ, MDL_SHARED_READ)))
+    goto err;
+  sq_select->add_joined_table(derived_tab);
+  sq_select->add_where_field(derived_unit->first_select());
+  sq_select->context.table_list= sq_select->table_list.first;
+  sq_select->context.first_name_resolution_table= sq_select->table_list.first;
+  sq_select->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE;
+  lex->derived_tables|= DERIVED_SUBQUERY;
+
+  sq_select->where= 0;
+  sq_select->set_braces(false);
+  derived_unit->set_with_clause(0);
+
+  if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE)
+    ((subselect_single_select_engine *) engine)->change_select(sq_select);
+
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
+  lex->current_select= sq_select;
+  return false;
+
+err:
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
+  lex->derived_tables= save_derived_tables;
+  lex->current_select= parent_select;
+  return true;
+}
+
+
 /**
   @brief
     Transform IN predicate into IN subquery
@@ -532,6 +622,7 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd,
   if (!transform_into_subq)
     return this;
 
+  
   transform_into_subq= false;
 
   List<List_item> values;
@@ -540,6 +631,12 @@ Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd,
   /* SELECT_LEX object where the transformation is performed */
   SELECT_LEX *parent_select= lex->current_select;
   uint8 save_derived_tables= lex->derived_tables;
+  
+  for (uint i=1; i < arg_count; i++)
+  {
+    if (!args[i]->const_item())
+      return this;
+  }
 
   Query_arena backup;
   Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index e0e09b0b3c4..13c7b38de2b 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1553,6 +1553,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 %token  UTC_TIMESTAMP_SYM
 %token  UTC_TIME_SYM
 %token  VALUES                        /* SQL-2003-R */
+%token  VALUES_IN_SYM
+%token  VALUES_LESS_SYM
 %token  VALUE_SYM                     /* SQL-2003-R */
 %token  VARBINARY
 %token  VARCHAR                       /* SQL-2003-R */
@@ -4938,8 +4940,15 @@ part_type_def:
           { Lex->part_info->part_type= RANGE_PARTITION; }
         | RANGE_SYM part_column_list
           { Lex->part_info->part_type= RANGE_PARTITION; }
-        | LIST_SYM part_func
-          { Lex->part_info->part_type= LIST_PARTITION; }
+        | LIST_SYM 
+	  {
+	    Select->parsing_place= IN_PART_FUNC;
+          }
+          part_func
+          { 
+	    Lex->part_info->part_type= LIST_PARTITION; 
+	    Select->parsing_place= NO_MATTER;
+	  }
         | LIST_SYM part_column_list
           { Lex->part_info->part_type= LIST_PARTITION; }
         ;
@@ -5189,7 +5198,7 @@ opt_part_values:
             else
               part_info->part_type= HASH_PARTITION;
           }
-        | VALUES LESS_SYM THAN_SYM
+        | VALUES_LESS_SYM THAN_SYM
           {
             LEX *lex= Lex;
             partition_info *part_info= lex->part_info;
@@ -5203,7 +5212,7 @@ opt_part_values:
               part_info->part_type= RANGE_PARTITION;
           }
           part_func_max {}
-        | VALUES IN_SYM
+        | VALUES_IN_SYM
           {
             LEX *lex= Lex;
             partition_info *part_info= lex->part_info;
@@ -12675,7 +12684,14 @@ expr_or_default:
 opt_insert_update:
           /* empty */
         | ON DUPLICATE_SYM { Lex->duplicates= DUP_UPDATE; }
-          KEY_SYM UPDATE_SYM insert_update_list
+          KEY_SYM UPDATE_SYM 
+          {
+	    Select->parsing_place= IN_UPDATE_ON_DUP_KEY;
+          }
+          insert_update_list
+          {
+	    Select->parsing_place= NO_MATTER;
+          }
         ;
 
 /* Update rows in a table */
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index aadf47235c4..324dc79f2ce 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -5555,6 +5555,6 @@ static Sys_var_ulong Sys_in_subquery_conversion_threshold(
        "The minimum number of scalar elements in the value list of"
        "IN predicate that triggers its conversion to IN subquery",
        SESSION_VAR(in_subquery_conversion_threshold), CMD_LINE(OPT_ARG),
-       VALID_RANGE(0, ULONG_MAX), DEFAULT(1000), BLOCK_SIZE(1));
+       VALID_RANGE(0, ULONG_MAX), DEFAULT(10000), BLOCK_SIZE(1));
 
 #endif //EMBEDDED_LIBRARY