Merge remote-tracking branch 'shagalla/10.3-mdev12172' into 10.3

As a result of this merge the code for the following tasks appears in 10.3:
- MDEV-12172 Implement tables specified by table value constructors
- MDEV-12176 Transform [NOT] IN predicate with long list of values INTO
             [NOT] IN subquery.

51 files changed, 5311 insertions, 156 deletions

diff --git a/libmysqld/CMakeLists.txt b/libmysqld/CMakeLists.txt
index cdf1abcd89c..abb19fd9932 100644
--- a/libmysqld/CMakeLists.txt
+++ b/libmysqld/CMakeLists.txt
@@ -118,6 +118,7 @@ SET(SQL_EMBEDDED_SOURCES emb_qcache.cc libmysqld.c lib_sql.cc
            ../sql/temporary_tables.cc
            ../sql/session_tracker.cc
            ../sql/proxy_protocol.cc
+           ../sql/sql_tvc.cc ../sql/sql_tvc.h
            ${GEN_SOURCES}
            ${MYSYS_LIBWRAP_SOURCE}
 )
diff --git a/mysql-test/r/default.result b/mysql-test/r/default.result
index a03da4f8df1..c0e786d9876 100644
--- a/mysql-test/r/default.result
+++ b/mysql-test/r/default.result
@@ -232,7 +232,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;
 a
 11
@@ -546,7 +546,7 @@ CREATE PROCEDURE p1() CREATE TABLE t1 (a INT DEFAULT par);
 CALL p1;
 ERROR 42S22: Unknown column 'par' in 'DEFAULT'
 DROP PROCEDURE p1;
-CREATE TABLE t1 (a INT DEFAULT VALUES(a));
+CREATE TABLE t1 (a INT DEFAULT VALUE(a));
 ERROR HY000: Function or expression 'values()' cannot be used in the DEFAULT clause of `a`
 CREATE TABLE t1 (a INT);
 CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW CREATE TABLE t2 (a INT DEFAULT NEW.a);
diff --git a/mysql-test/r/errors.result b/mysql-test/r/errors.result
index bcc171e404d..a139d34fa86 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 299b6344192..6c7c6b018ad 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 value(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..68a1003ad85 100644
--- a/mysql-test/r/insert_update.result
+++ b/mysql-test/r/insert_update.result
@@ -49,23 +49,23 @@ 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`,value(`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:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where values(`test`.`t1`.`a`)
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c` from `test`.`t1` where value(`test`.`t1`.`a`)
 DROP TABLE t1;
 create table t1(a int primary key, b int);
 insert into t1 values(1,1),(2,2),(3,3),(4,4),(5,5);
@@ -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 0037511b741..f5ef190b501 100644
--- a/mysql-test/r/mysqld--help.result
+++ b/mysql-test/r/mysqld--help.result
@@ -334,6 +334,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
+ of IN 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
@@ -1354,6 +1358,7 @@ idle-transaction-timeout 0
 idle-write-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/opt_tvc.result b/mysql-test/r/opt_tvc.result
new file mode 100644
index 00000000000..9d76d2b31b1
--- /dev/null
+++ b/mysql-test/r/opt_tvc.result
@@ -0,0 +1,551 @@
+create table t1 (a int, b int);
+insert into t1
+values (1,2), (4,6), (9,7),
+(1,1), (2,5), (7,8);
+create table t2 (a int, b int, c int);
+insert into t2
+values (1,2,3), (5,1,2), (4,3,7),
+(8,9,0), (10,7,1), (5,5,1);
+create table t3 (a int, b varchar(16), index idx(a));
+insert into t3 values
+(1, "abc"), (3, "egh"), (8, "axxx"), (10, "abc"),
+(2, "ccw"), (8, "wqqe"), (7, "au"), (9, "waa"),
+(3, "rass"), (9, "ert"), (9, "lok"), (8, "aww"),
+(1, "todd"), (3, "rew"), (8, "aww"), (3, "sw"),
+(11, "llk"), (7, "rbw"), (1, "sm"), (2, "jyp"),
+(4, "yq"), (5, "pled"), (12, "ligin"), (12, "toww"),
+(6, "mxm"), (15, "wanone"), (9, "sunqq"), (2, "abe");
+# optimization is not used
+select * from t1 where a in (1,2);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1 where a in (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (1,2)
+# set minimum number of values in VALUEs list when optimization works to 2
+set @@in_subquery_conversion_threshold= 2;
+# single IN-predicate in WHERE-part
+select * from t1 where a in (1,2);
+a	b
+1	2
+1	1
+2	5
+select * from t1
+where a in
+(
+select *
+from (values (1),(2)) as tvc_0
+);
+a	b
+1	2
+1	1
+2	5
+explain extended select * from t1 where a in (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+explain extended select * from t1
+where a in
+(
+select *
+from (values (1),(2)) as tvc_0
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+# AND-condition with IN-predicates in WHERE-part
+select * from t1
+where a in (1,2) and
+b in (1,5);
+a	b
+1	1
+2	5
+select * from t1
+where a in
+(
+select *
+from (values (1),(2)) as tvc_0
+)
+and b in
+(
+select *
+from (values (1),(5)) as tvc_1
+);
+a	b
+1	1
+2	5
+explain extended select * from t1
+where a in (1,2) and
+b in (1,5);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1`
+explain extended select * from t1
+where a in
+(
+select *
+from (values (1),(2)) as tvc_0
+)
+and b in
+(
+select *
+from (values (1),(5)) as tvc_1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1`
+# subquery with IN-predicate
+select * from t1
+where a in
+(
+select a
+from t2 where b in (3,4)
+);
+a	b
+4	6
+select * from t1
+where a in
+(
+select a from t2
+where b in
+(
+select *
+from (values (3),(4)) as tvc_0
+)
+);
+a	b
+4	6
+explain extended select * from t1
+where a in
+(
+select a
+from t2 where b in (3,4)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3`
+explain extended select * from t1
+where a in
+(
+select a from t2
+where b in
+(
+select *
+from (values (3),(4)) as tvc_0
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3`
+# derived table with IN-predicate
+select * from
+(
+select *
+from t1
+where a in (1,2)
+) as dr_table;
+a	b
+1	2
+1	1
+2	5
+select * from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+) as dr_table;
+a	b
+1	2
+1	1
+2	5
+explain extended select * from
+(
+select *
+from t1
+where a in (1,2)
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<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	<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`
+explain extended select * from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<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	<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`
+# non-recursive CTE with IN-predicate
+with tvc_0 as
+(
+select *
+from t1
+where a in (1,2)
+)
+select * from tvc_0;
+a	b
+1	2
+1	1
+2	5
+select * from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+) as dr_table;
+a	b
+1	2
+1	1
+2	5
+explain extended with tvc_0 as
+(
+select *
+from t1
+where a in (1,2)
+)
+select * from tvc_0;
+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	<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`
+explain extended select * from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<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	<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`
+# VIEW with IN-predicate
+create view v1 as
+select *
+from t1
+where a in (1,2);
+create view v2 as
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+;
+select * from v1;
+a	b
+1	2
+1	1
+2	5
+select * from v2;
+a	b
+1	2
+1	1
+2	5
+explain extended select * from v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<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	<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`
+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	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+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`
+drop view v1,v2;
+# subselect defined by derived table with IN-predicate
+select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in (1,2)
+)
+as dr_table
+);
+a	b
+1	2
+1	1
+select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+)
+as dr_table
+);
+a	b
+1	2
+1	1
+explain extended select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in (1,2)
+)
+as dr_table
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+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`
+explain extended select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+)
+as dr_table
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+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`
+# derived table with IN-predicate and group by
+select * from
+(
+select max(a),b
+from t1
+where b in (3,5)
+group by b
+) as dr_table;
+max(a)	b
+2	5
+select * from
+(
+select max(a),b
+from t1
+where b in
+(
+select *
+from (values (3),(5))
+as tvc_0
+)
+group by b
+) as dr_table;
+max(a)	b
+2	5
+explain extended select * from
+(
+select max(a),b
+from t1
+where b in (3,5)
+group by b
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	12	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using temporary; Using filesort
+2	DERIVED	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where 1 group by `test`.`t1`.`b`) `dr_table`
+explain extended select * from
+(
+select max(a),b
+from t1
+where b in
+(
+select *
+from (values (3),(5))
+as tvc_0
+)
+group by b
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	12	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using temporary; Using filesort
+2	DERIVED	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where 1 group by `test`.`t1`.`b`) `dr_table`
+# prepare statement
+prepare stmt from "select * from t1 where a in (1,2)";
+execute stmt;
+a	b
+1	2
+1	1
+2	5
+execute stmt;
+a	b
+1	2
+1	1
+2	5
+deallocate prepare stmt;
+# use inside out access from tvc rows
+set @@in_subquery_conversion_threshold= default;
+select * from t3 where a in (1,4,10);
+a	b
+1	abc
+1	todd
+1	sm
+4	yq
+10	abc
+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	SIMPLE	t3	range	idx	idx	5	NULL	5	100.00	Using index condition
+Warnings:
+Note	1003	select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4,10)
+set @@in_subquery_conversion_threshold= 2;
+select * from t3 where a in (1,4,10);
+a	b
+1	abc
+1	todd
+1	sm
+4	yq
+10	abc
+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	
+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`
+# use vectors in IN predeicate
+set @@in_subquery_conversion_threshold= 4;
+select * from t1 where (a,b) in ((1,2),(3,4));
+a	b
+1	2
+explain extended select * from t1 where (a,b) in ((1,2),(3,4));
+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),(3,4)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1` and `test`.`t1`.`b` = `tvc_0`.`2`
+set @@in_subquery_conversion_threshold= 2;
+# trasformation works for the one IN predicate and doesn't work for the other
+set @@in_subquery_conversion_threshold= 5;
+select * from t2
+where (a,b) in ((1,2),(8,9)) and
+(a,c) in ((1,3),(8,0),(5,1));
+a	b	c
+1	2	3
+8	9	0
+explain extended select * from t2
+where (a,b) in ((1,2),(8,9)) and
+(a,c) in ((1,3),(8,0),(5,1));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t2	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`.`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)))
+set @@in_subquery_conversion_threshold= 2;
+drop table t1, t2, t3;
+set @@in_subquery_conversion_threshold= default;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 4cb5bb79982..ba52176e3bd 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -4792,7 +4792,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 79b46183b60..7400dff3958 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 71657288c66..960a873c854 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/sp.result b/mysql-test/r/sp.result
index e46f9b840ed..b66faec260f 100644
--- a/mysql-test/r/sp.result
+++ b/mysql-test/r/sp.result
@@ -4440,7 +4440,7 @@ create table t3 (id int not null primary key, county varchar(25))|
 insert into t3 (id, county) values (1, 'York')|
 create procedure bug15441(c varchar(25))
 begin
-update t3 set id=2, county=values(c);
+update t3 set id=2, county=value(c);
 end|
 call bug15441('county')|
 ERROR 42S22: Unknown column 'c' in 'field list'
@@ -4451,7 +4451,7 @@ declare c varchar(25) default "hello";
 insert into t3 (id, county) values (1, county)
 on duplicate key update county= values(county);
 select * from t3;
-update t3 set id=2, county=values(id);
+update t3 set id=2, county=value(id);
 select * from t3;
 end|
 call bug15441('Yale')|
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
new file mode 100644
index 00000000000..39caba331ef
--- /dev/null
+++ b/mysql-test/r/table_value_constr.result
@@ -0,0 +1,2073 @@
+create table t1 (a int, b int);
+insert into t1 values (1,2),(4,6),(9,7),
+(1,1),(2,5),(7,8);
+# just VALUES
+values (1,2);
+1	2
+1	2
+values (1,2), (3,4), (5.6,0);
+1	2
+1.0	2
+3.0	4
+5.6	0
+values ("abc", "def");
+abc	def
+abc	def
+# UNION that uses VALUES structure(s)
+select 1,2
+union
+values (1,2);
+1	2
+1	2
+values (1,2)
+union
+select 1,2;
+1	2
+1	2
+select 1,2
+union
+values (1,2),(3,4),(5,6),(7,8);
+1	2
+1	2
+3	4
+5	6
+7	8
+select 3,7
+union
+values (1,2),(3,4),(5,6);
+3	7
+3	7
+1	2
+3	4
+5	6
+select 3,7,4
+union
+values (1,2,5),(4,5,6);
+3	7	4
+3	7	4
+1	2	5
+4	5	6
+select 1,2
+union
+values (1,7),(3,6.5);
+1	2
+1	2.0
+1	7.0
+3	6.5
+select 1,2
+union
+values (1,2.0),(3,6);
+1	2
+1	2.0
+3	6.0
+select 1.8,2
+union
+values (1,2),(3,6);
+1.8	2
+1.8	2
+1.0	2
+3.0	6
+values (1,2.4),(3,6)
+union
+select 2.8,9;
+1	2.4
+1.0	2.4
+3.0	6.0
+2.8	9.0
+values (1,2),(3,4),(5,6),(7,8)
+union
+select 5,6;
+1	2
+1	2
+3	4
+5	6
+7	8
+select "ab","cdf"
+union
+values ("al","zl"),("we","q");
+ab	cdf
+ab	cdf
+al	zl
+we	q
+values ("ab", "cdf")
+union
+select "ab","cdf";
+ab	cdf
+ab	cdf
+values (1,2)
+union
+values (1,2),(5,6);
+1	2
+1	2
+5	6
+values (1,2)
+union
+values (3,4),(5,6);
+1	2
+1	2
+3	4
+5	6
+values (1,2)
+union
+values (1,2)
+union values (4,5);
+1	2
+1	2
+4	5
+# UNION ALL that uses VALUES structure
+values (1,2),(3,4)
+union all
+select 5,6;
+1	2
+1	2
+3	4
+5	6
+values (1,2),(3,4)
+union all
+select 1,2;
+1	2
+1	2
+3	4
+1	2
+select 5,6
+union all
+values (1,2),(3,4);
+5	6
+5	6
+1	2
+3	4
+select 1,2
+union all
+values (1,2),(3,4);
+1	2
+1	2
+1	2
+3	4
+values (1,2)
+union all
+values (1,2),(5,6);
+1	2
+1	2
+1	2
+5	6
+values (1,2)
+union all
+values (3,4),(5,6);
+1	2
+1	2
+3	4
+5	6
+values (1,2)
+union all
+values (1,2)
+union all
+values (4,5);
+1	2
+1	2
+1	2
+4	5
+values (1,2)
+union all
+values (1,2)
+union values (1,2);
+1	2
+1	2
+values (1,2)
+union
+values (1,2)
+union all
+values (1,2);
+1	2
+1	2
+1	2
+# EXCEPT that uses VALUES structure(s)
+select 1,2
+except
+values (3,4),(5,6);
+1	2
+1	2
+select 1,2
+except
+values (1,2),(3,4);
+1	2
+values (1,2),(3,4)
+except
+select 5,6;
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+select 1,2;
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (5,6);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (1,2);
+1	2
+3	4
+# INTERSECT that uses VALUES structure(s)
+select 1,2
+intersect
+values (3,4),(5,6);
+1	2
+select 1,2
+intersect
+values (1,2),(3,4);
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+select 5,6;
+1	2
+values (1,2),(3,4)
+intersect
+select 1,2;
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+values (5,6);
+1	2
+values (1,2),(3,4)
+intersect
+values (1,2);
+1	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
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (1,2)
+union
+values (1,2);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (1,2)
+union
+values (3,4);
+1	2
+3	4
+values (1,2),(3,4)
+union
+values (1,2)
+except
+values (1,2);
+1	2
+3	4
+# combination of different structures that uses VALUES structures : UNION ALL + EXCEPT
+values (1,2),(3,4)
+except
+select 1,2
+union all
+values (1,2);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (1,2)
+union all
+values (1,2);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+except
+values (1,2)
+union all
+values (3,4);
+1	2
+3	4
+3	4
+values (1,2),(3,4)
+union all
+values (1,2)
+except
+values (1,2);
+1	2
+3	4
+# combination of different structures that uses VALUES structures : UNION + INTERSECT
+values (1,2),(3,4)
+intersect
+select 1,2
+union
+values (1,2);
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+values (1,2)
+union
+values (1,2);
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+values (1,2)
+union
+values (3,4);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+union
+values (1,2)
+intersect
+values (1,2);
+1	2
+1	2
+3	4
+# combination of different structures that uses VALUES structures : UNION ALL + INTERSECT
+values (1,2),(3,4)
+intersect
+select 1,2
+union all
+values (1,2);
+1	2
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+values (1,2)
+union all
+values (1,2);
+1	2
+1	2
+1	2
+values (1,2),(3,4)
+intersect
+values (1,2)
+union all
+values (3,4);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+union all
+values (1,2)
+intersect
+values (1,2);
+1	2
+1	2
+3	4
+1	2
+# combination of different structures that uses VALUES structures : UNION + UNION ALL
+values (1,2),(3,4)
+union all
+select 1,2
+union
+values (1,2);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+union all
+values (1,2)
+union
+values (1,2);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+union all
+values (1,2)
+union
+values (3,4);
+1	2
+1	2
+3	4
+values (1,2),(3,4)
+union
+values (1,2)
+union all
+values (1,2);
+1	2
+1	2
+3	4
+1	2
+values (1,2)
+union
+values (1,2)
+union all
+values (1,2);
+1	2
+1	2
+1	2
+# CTE that uses VALUES structure(s) : non-recursive CTE
+with t2 as
+(
+values (1,2),(3,4)
+)
+select * from t2;
+1	2
+1	2
+3	4
+with t2 as
+(
+select 1,2
+union
+values (1,2)
+)
+select * from t2;
+1	2
+1	2
+with t2 as
+(
+select 1,2
+union
+values (1,2),(3,4)
+)
+select * from t2;
+1	2
+1	2
+3	4
+with t2 as
+(
+values (1,2)
+union
+select 1,2
+)
+select * from t2;
+1	2
+1	2
+with t2 as
+(
+values (1,2),(3,4)
+union
+select 1,2
+)
+select * from t2;
+1	2
+1	2
+3	4
+with t2 as
+(
+values (5,6)
+union
+values (1,2),(3,4)
+)
+select * from t2;
+5	6
+5	6
+1	2
+3	4
+with t2 as
+(
+values (1,2)
+union
+values (1,2),(3,4)
+)
+select * from t2;
+1	2
+1	2
+3	4
+with t2 as
+(
+select 1,2
+union all
+values (1,2),(3,4)
+)
+select * from t2;
+1	2
+1	2
+1	2
+3	4
+with t2 as
+(
+values (1,2),(3,4)
+union all
+select 1,2
+)
+select * from t2;
+1	2
+1	2
+3	4
+1	2
+with t2 as
+(
+values (1,2)
+union all
+values (1,2),(3,4)
+)
+select * from t2;
+1	2
+1	2
+1	2
+3	4
+# recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor
+with recursive t2(a,b) as
+(
+values(1,1)
+union
+select t1.a, t1.b
+from t1,t2
+where t1.a=t2.a
+)
+select * from t2;
+a	b
+1	1
+1	2
+with recursive t2(a,b) as
+(
+values(1,1)
+union
+select t1.a+1, t1.b
+from t1,t2
+where t1.a=t2.a
+)
+select * from t2;
+a	b
+1	1
+2	2
+2	1
+3	5
+# recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors
+with recursive t2(a,b) as
+(
+values(1,1)
+union
+values (3,4)
+union
+select t2.a+1, t1.b
+from t1,t2
+where t1.a=t2.a
+)
+select * from t2;
+a	b
+1	1
+3	4
+2	2
+2	1
+3	5
+# recursive CTE that uses VALUES structure(s) : that uses UNION ALL
+with recursive t2(a,b,st) as
+(
+values(1,1,1)
+union all
+select t2.a, t1.b, t2.st+1
+from t1,t2
+where t1.a=t2.a and st<3
+)
+select * from t2;
+a	b	st
+1	1	1
+1	2	2
+1	1	2
+1	2	3
+1	2	3
+1	1	3
+1	1	3
+# recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements)
+with recursive fact(n,f) as
+(
+values(1,1)
+union
+select n+1,f*n from fact where n < 10
+)
+select * from fact;
+n	f
+1	1
+2	1
+3	2
+4	6
+5	24
+6	120
+7	720
+8	5040
+9	40320
+10	362880
+# Derived table that uses VALUES structure(s) : singe VALUES structure
+select * from (values (1,2),(3,4)) as t2;
+1	2
+1	2
+3	4
+# Derived table that uses VALUES structure(s) : UNION with VALUES structure(s)
+select * from (select 1,2 union values (1,2)) as t2;
+1	2
+1	2
+select * from (select 1,2 union values (1,2),(3,4)) as t2;
+1	2
+1	2
+3	4
+select * from (values (1,2) union select 1,2) as t2;
+1	2
+1	2
+select * from (values (1,2),(3,4) union select 1,2) as t2;
+1	2
+1	2
+3	4
+select * from (values (5,6) union values (1,2),(3,4)) as t2;
+5	6
+5	6
+1	2
+3	4
+select * from (values (1,2) union values (1,2),(3,4)) as t2;
+1	2
+1	2
+3	4
+# Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
+select * from (select 1,2 union all values (1,2),(3,4)) as t2;
+1	2
+1	2
+1	2
+3	4
+select * from (values (1,2),(3,4) union all select 1,2) as t2;
+1	2
+1	2
+3	4
+1	2
+select * from (values (1,2) union all values (1,2),(3,4)) as t2;
+1	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
+1	2
+3	4
+drop view v1;
+# CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s)
+create view v1 as
+select 1,2
+union
+values (1,2);
+select * from v1;
+1	2
+1	2
+drop view v1;
+create view v1 as
+select 1,2
+union
+values (1,2),(3,4);
+select * from v1;
+1	2
+1	2
+3	4
+drop view v1;
+create view v1 as
+values (1,2)
+union
+select 1,2;
+select * from v1;
+1	2
+1	2
+drop view v1;
+create view v1 as
+values (1,2),(3,4)
+union
+select 1,2;
+select * from v1;
+1	2
+1	2
+3	4
+drop view v1;
+create view v1 as
+values (5,6)
+union
+values (1,2),(3,4);
+select * from v1;
+5	6
+5	6
+1	2
+3	4
+drop view v1;
+# CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
+create view v1 as
+values (1,2)
+union
+values (1,2),(3,4);
+select * from v1;
+1	2
+1	2
+3	4
+drop view v1;
+create view v1 as
+select 1,2
+union all
+values (1,2),(3,4);
+select * from v1;
+1	2
+1	2
+1	2
+3	4
+drop view v1;
+create view v1 as
+values (1,2),(3,4)
+union all
+select 1,2;
+select * from v1;
+1	2
+1	2
+3	4
+1	2
+drop view v1;
+create view v1 as
+values (1,2)
+union all
+values (1,2),(3,4);
+select * from v1;
+1	2
+1	2
+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);
+";
+execute stmt1;
+1	2
+1	2
+execute stmt1;
+1	2
+1	2
+deallocate prepare stmt1;
+# prepare statement that uses VALUES structure(s): UNION with VALUES structure(s)
+prepare stmt1 from "
+  select 1,2
+  union
+  values (1,2),(3,4);
+";
+execute stmt1;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from "
+  values (1,2),(3,4)
+  union
+  select 1,2;
+";
+execute stmt1;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from "
+  select 1,2
+  union
+  values (3,4)
+  union
+  values (1,2);
+";
+execute stmt1;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from "
+  values (5,6)
+  union
+  values (1,2),(3,4);
+";
+execute stmt1;
+5	6
+5	6
+1	2
+3	4
+execute stmt1;
+5	6
+5	6
+1	2
+3	4
+deallocate prepare stmt1;
+# prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+prepare stmt1 from "
+  select 1,2
+  union
+  values (1,2),(3,4);
+";
+execute stmt1;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from "
+  values (1,2),(3,4)
+  union all
+  select 1,2;
+";
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+deallocate prepare stmt1;
+prepare stmt1 from "
+  select 1,2
+  union all
+  values (3,4)
+  union all
+  values (1,2);
+";
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+deallocate prepare stmt1;
+prepare stmt1 from "
+  values (1,2)
+  union all
+  values (1,2),(3,4);
+";
+execute stmt1;
+1	2
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+# explain query that uses VALUES structure(s): single VALUES structure
+explain
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain format=json
+values (1,2);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<unit1>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# explain query that uses VALUES structure(s): UNION with VALUES structure(s)
+explain
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain
+values (1,2),(3,4)
+union
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain
+values (5,6)
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json
+select 1,2
+union
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+values (1,2),(3,4)
+union
+select 1,2;
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+values (5,6)
+union
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+explain
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain
+values (1,2),(3,4)
+union all
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain
+values (1,2)
+union all
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain format=json
+values (1,2),(3,4)
+union all
+select 1,2;
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+select 1,2
+union
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+values (1,2)
+union all
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain format=json
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# analyze query that uses VALUES structure(s): single VALUES structure
+analyze
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze format=json
+values (1,2);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<unit1>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# analyze query that uses VALUES structure(s): UNION with VALUES structure(s)
+analyze
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze
+values (1,2),(3,4)
+union
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze
+values (5,6)
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	3.00	NULL	NULL	
+analyze format=json
+select 1,2
+union
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+values (1,2),(3,4)
+union
+select 1,2;
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+values (5,6)
+union
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 3,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze format=json
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+analyze
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze
+values (1,2),(3,4)
+union all
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze
+values (1,2)
+union all
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze format=json
+values (1,2),(3,4)
+union all
+select 1,2;
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+select 1,2
+union
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+values (1,2)
+union all
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze format=json
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# 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
+# 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/trigger.result b/mysql-test/r/trigger.result
index b751632307a..2b843f68499 100644
--- a/mysql-test/r/trigger.result
+++ b/mysql-test/r/trigger.result
@@ -2125,7 +2125,7 @@ SHOW TRIGGERS IN db1;
 Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
 trg1	INSERT	t2	CREATE DEFINER=`root`@`localhost` TRIGGER trg1 BEFORE INSERT ON t2 FOR EACH ROW INSERTINTOt1 VALUES (1)	BEFORE	#	STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION		latin1	latin1_swedish_ci	latin1_swedish_ci
 INSERT INTO t2 VALUES (1);
-ERROR 42000: Trigger 'trg1' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VALUES (1)' at line 1'
+ERROR 42000: Trigger 'trg1' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(1)' at line 1'
 SELECT * FROM t1;
 b
 # Work around Bug#45235
diff --git a/mysql-test/r/variables.result b/mysql-test/r/variables.result
index dde58ed0ab2..d79a7bb4bec 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/r/view.result b/mysql-test/r/view.result
index 6eead303c7a..635323a5867 100644
--- a/mysql-test/r/view.result
+++ b/mysql-test/r/view.result
@@ -5637,7 +5637,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,(/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`) AS `c` from `test`.`t1`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1`
 select * from v1;
 a	c
 1	2
@@ -5654,7 +5654,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
-Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`) AS `c` from `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`a`
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t2` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`a`
 select * from t2, v1 where t2.a=v1.a;
 a	b	a	c
 1	2	1	2
@@ -5673,7 +5673,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`) AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t1`.`a`
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t1`.`a`
 select * from t1, v1 where t1.a=v1.a;
 a	b	a	c
 1	2	1	2
@@ -5692,7 +5692,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
-Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`) AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`b` = (/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t1` join `test`.`t1` where `test`.`t1`.`b` = <expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`))
 select * from t1, v1 where t1.b=v1.c;
 a	b	a	c
 1	2	1	2
@@ -5710,7 +5710,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
 Warnings:
 Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #2
-Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,(/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`) AS `c` from `test`.`t2` join `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`a` = `test`.`t2`.`a`
+Note	1003	/* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`a` AS `a`,<expr_cache><`test`.`t1`.`a`>((/* select#3 */ select max(`test`.`t2`.`b`) from `test`.`t2` where `test`.`t1`.`a` = `test`.`t2`.`a`)) AS `c` from `test`.`t2` join `test`.`t1` join `test`.`t1` where `test`.`t1`.`a` = `test`.`t2`.`a` and `test`.`t1`.`a` = `test`.`t2`.`a`
 select * from t2, t1, v1 where t1.a=t2.a and t1.a=v1.a;
 a	b	a	b	a	c
 1	2	1	2	1	2
diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
index 69ecca3312f..19cf4f05312 100644
--- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
+++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result
@@ -1550,6 +1550,20 @@ NUMERIC_BLOCK_SIZE	1
 ENUM_VALUE_LIST	NULL
 READ_ONLY	NO
 COMMAND_LINE_ARGUMENT	REQUIRED
+VARIABLE_NAME	IN_SUBQUERY_CONVERSION_THRESHOLD
+SESSION_VALUE	10000
+GLOBAL_VALUE	10000
+GLOBAL_VALUE_ORIGIN	COMPILE-TIME
+DEFAULT_VALUE	10000
+VARIABLE_SCOPE	SESSION
+VARIABLE_TYPE	BIGINT UNSIGNED
+VARIABLE_COMMENT	The minimum number of scalar elements in the value list of IN predicate that triggers its conversion to IN subquery
+NUMERIC_MIN_VALUE	0
+NUMERIC_MAX_VALUE	18446744073709551615
+NUMERIC_BLOCK_SIZE	1
+ENUM_VALUE_LIST	NULL
+READ_ONLY	NO
+COMMAND_LINE_ARGUMENT	OPTIONAL
 VARIABLE_NAME	IN_TRANSACTION
 SESSION_VALUE	0
 GLOBAL_VALUE	NULL
diff --git a/mysql-test/t/default.test b/mysql-test/t/default.test
index 7ee3b590a83..44778bc568a 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 55461002fd4..6836fc5803c 100644
--- a/mysql-test/t/errors.test
+++ b/mysql-test/t/errors.test
@@ -179,16 +179,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 ac983048129..ebfdced802b 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
new file mode 100644
index 00000000000..6fcb1c5d1aa
--- /dev/null
+++ b/mysql-test/t/opt_tvc.test
@@ -0,0 +1,286 @@
+create table t1 (a int, b int);
+
+insert into t1
+values (1,2), (4,6), (9,7),
+       (1,1), (2,5), (7,8);
+
+create table t2 (a int, b int, c int);
+
+insert into t2
+values (1,2,3), (5,1,2), (4,3,7),
+       (8,9,0), (10,7,1), (5,5,1);
+
+create table t3 (a int, b varchar(16), index idx(a));
+insert into t3 values
+  (1, "abc"), (3, "egh"), (8, "axxx"), (10, "abc"),
+  (2, "ccw"), (8, "wqqe"), (7, "au"), (9, "waa"),
+  (3, "rass"), (9, "ert"), (9, "lok"), (8, "aww"),
+  (1, "todd"), (3, "rew"), (8, "aww"), (3, "sw"),
+  (11, "llk"), (7, "rbw"), (1, "sm"), (2, "jyp"),
+  (4, "yq"), (5, "pled"), (12, "ligin"), (12, "toww"),
+  (6, "mxm"), (15, "wanone"), (9, "sunqq"), (2, "abe");
+
+--echo # optimization is not used
+
+let $query= select * from t1 where a in (1,2);
+eval $query;
+eval explain extended $query;
+
+--echo # set minimum number of values in VALUEs list when optimization works to 2
+
+set @@in_subquery_conversion_threshold= 2;
+
+--echo # single IN-predicate in WHERE-part
+
+let $query= select * from t1 where a in (1,2);
+
+let $optimized_query=
+select * from t1
+where a in
+      (
+        select *
+        from (values (1),(2)) as tvc_0
+      );
+
+eval $query;
+eval $optimized_query;
+eval explain extended $query;
+eval explain extended $optimized_query;
+
+--echo # AND-condition with IN-predicates in WHERE-part
+
+let $query=
+select * from t1
+where a in (1,2) and
+      b in (1,5);
+
+let $optimized_query=
+select * from t1
+where a in
+(
+  select *
+  from (values (1),(2)) as tvc_0
+)
+and b in
+(
+  select *
+  from (values (1),(5)) as tvc_1
+);
+
+eval $query;
+eval $optimized_query;
+eval explain extended $query;
+eval explain extended $optimized_query;
+
+--echo # subquery with IN-predicate
+
+let $query=
+select * from t1
+where a in
+(
+  select a
+  from t2 where b in (3,4)
+);
+
+let $optimized_query=
+select * from t1
+where a in
+(
+  select a from t2
+  where b in
+  (
+    select *
+    from (values (3),(4)) as tvc_0
+  )
+);
+
+eval $query;
+eval $optimized_query;
+eval explain extended $query;
+eval explain extended $optimized_query;
+
+--echo # derived table with IN-predicate
+
+let $query=
+select * from
+(
+  select *
+  from t1
+  where a in (1,2)
+) as dr_table;
+
+let $optimized_query=
+select * from
+(
+  select *
+  from t1
+  where a in
+  (
+    select *
+    from (values (1),(2))
+    as tvc_0
+  )
+) as dr_table;
+
+eval $query;
+eval $optimized_query;
+eval explain extended $query;
+eval explain extended $optimized_query;
+
+--echo # non-recursive CTE with IN-predicate
+
+let $cte_query=
+with tvc_0 as
+(
+  select *
+  from t1
+  where a in (1,2)
+)
+select * from tvc_0;
+
+eval $cte_query;
+eval $optimized_query;
+eval explain extended $cte_query;
+eval explain extended $optimized_query;
+
+--echo # VIEW with IN-predicate
+
+create view v1 as
+  select *
+  from t1
+  where a in (1,2);
+
+create view v2 as
+  select *
+  from t1
+  where a in
+  (
+    select *
+    from (values (1),(2))
+    as tvc_0
+  )
+;
+
+let $query= select * from v1;
+let $optimized_query= select * from v2;
+
+eval $query;
+eval $optimized_query;
+eval explain extended $query;
+eval explain extended $optimized_query;
+
+drop view v1,v2;
+
+--echo # subselect defined by derived table with IN-predicate
+
+let $query=
+select * from t1
+where a in
+(
+  select 1
+  from
+  (
+    select *
+    from t1
+    where a in (1,2)
+  )
+  as dr_table
+);
+
+let $optimized_query=
+select * from t1
+where a in
+(
+  select 1
+  from
+  (
+    select *
+    from t1
+    where a in
+    (
+      select *
+      from (values (1),(2))
+      as tvc_0
+    )
+  )
+  as dr_table
+);
+
+eval $query;
+eval $optimized_query;
+eval explain extended $query;
+eval explain extended $optimized_query;
+
+--echo # derived table with IN-predicate and group by
+
+let $query=
+select * from
+(
+  select max(a),b
+  from t1
+  where b in (3,5)
+  group by b
+) as dr_table;
+
+let $optimized_query=
+select * from
+(
+  select max(a),b
+  from t1
+  where b in
+  (
+    select *
+    from (values (3),(5))
+    as tvc_0
+  )
+  group by b
+) as dr_table;
+
+eval $query;
+eval $optimized_query;
+eval explain extended $query;
+eval explain extended $optimized_query;
+
+--echo # prepare statement
+
+prepare stmt from "select * from t1 where a in (1,2)";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+--echo # use inside out access from tvc rows
+
+let $query= select * from t3 where a in (1,4,10);
+set @@in_subquery_conversion_threshold= default;
+eval $query;
+eval explain extended $query;
+set @@in_subquery_conversion_threshold= 2;
+eval $query;
+eval explain extended $query;
+
+--echo # use vectors in IN predeicate
+
+set @@in_subquery_conversion_threshold= 4;
+
+let $query=
+select * from t1 where (a,b) in ((1,2),(3,4));
+
+eval $query;
+eval explain extended $query;
+set @@in_subquery_conversion_threshold= 2;
+
+--echo # trasformation works for the one IN predicate and doesn't work for the other
+
+set @@in_subquery_conversion_threshold= 5;
+
+let $query=
+select * from t2
+where (a,b) in ((1,2),(8,9)) and
+      (a,c) in ((1,3),(8,0),(5,1));
+
+eval $query;
+eval explain extended $query;
+set @@in_subquery_conversion_threshold= 2;
+
+drop table t1, t2, t3;
+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 b61c4cf2568..ec55b9c9d21 100644
--- a/mysql-test/t/ps.test
+++ b/mysql-test/t/ps.test
@@ -4283,7 +4283,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/sp.test b/mysql-test/t/sp.test
index 7d4758403ad..3b615d29166 100644
--- a/mysql-test/t/sp.test
+++ b/mysql-test/t/sp.test
@@ -5299,7 +5299,7 @@ insert into t3 (id, county) values (1, 'York')|
 
 create procedure bug15441(c varchar(25))
 begin
-  update t3 set id=2, county=values(c);
+  update t3 set id=2, county=value(c);
 end|
 --error ER_BAD_FIELD_ERROR
 call bug15441('county')|
@@ -5326,7 +5326,7 @@ begin
   on duplicate key update county= values(county);
   select * from t3;
 
-  update t3 set id=2, county=values(id);
+  update t3 set id=2, county=value(id);
   select * from t3;
 end|
 call bug15441('Yale')|
diff --git a/mysql-test/t/table_value_constr.test b/mysql-test/t/table_value_constr.test
new file mode 100644
index 00000000000..57a9817f0c2
--- /dev/null
+++ b/mysql-test/t/table_value_constr.test
@@ -0,0 +1,1046 @@
+create table t1 (a int, b int);
+
+insert into t1 values (1,2),(4,6),(9,7),
+		      (1,1),(2,5),(7,8);
+
+--echo # just VALUES
+
+values (1,2);
+
+values (1,2), (3,4), (5.6,0);
+
+values ("abc", "def");
+
+--echo # UNION that uses VALUES structure(s)
+
+select 1,2
+union
+values (1,2);
+
+values (1,2)
+union
+select 1,2;
+
+select 1,2
+union
+values (1,2),(3,4),(5,6),(7,8);
+
+select 3,7
+union
+values (1,2),(3,4),(5,6);
+
+select 3,7,4
+union
+values (1,2,5),(4,5,6);
+
+select 1,2
+union
+values (1,7),(3,6.5);
+
+select 1,2
+union
+values (1,2.0),(3,6);
+
+select 1.8,2
+union
+values (1,2),(3,6);
+
+values (1,2.4),(3,6)
+union
+select 2.8,9;
+
+values (1,2),(3,4),(5,6),(7,8)
+union
+select 5,6;
+
+select "ab","cdf"
+union
+values ("al","zl"),("we","q");
+
+values ("ab", "cdf")
+union
+select "ab","cdf";
+
+values (1,2)
+union
+values (1,2),(5,6);
+
+values (1,2)
+union
+values (3,4),(5,6);
+
+values (1,2)
+union
+values (1,2)
+union values (4,5);
+
+--echo # UNION ALL that uses VALUES structure
+
+values (1,2),(3,4)
+union all
+select 5,6;
+
+values (1,2),(3,4)
+union all
+select 1,2;
+
+select 5,6
+union all
+values (1,2),(3,4);
+
+select 1,2
+union all
+values (1,2),(3,4);
+
+values (1,2)
+union all
+values (1,2),(5,6);
+
+values (1,2)
+union all
+values (3,4),(5,6);
+
+values (1,2)
+union all
+values (1,2)
+union all
+values (4,5);
+
+values (1,2)
+union all
+values (1,2)
+union values (1,2);
+
+values (1,2)
+union
+values (1,2)
+union all
+values (1,2);
+
+--echo # EXCEPT that uses VALUES structure(s)
+
+select 1,2
+except
+values (3,4),(5,6);
+
+select 1,2
+except
+values (1,2),(3,4);
+
+values (1,2),(3,4)
+except
+select 5,6;
+
+values (1,2),(3,4)
+except
+select 1,2;
+
+values (1,2),(3,4)
+except
+values (5,6);
+
+values (1,2),(3,4)
+except
+values (1,2);
+
+--echo # INTERSECT that uses VALUES structure(s)
+
+select 1,2
+intersect
+values (3,4),(5,6);
+
+select 1,2
+intersect
+values (1,2),(3,4);
+
+values (1,2),(3,4)
+intersect
+select 5,6;
+
+values (1,2),(3,4)
+intersect
+select 1,2;
+
+values (1,2),(3,4)
+intersect
+values (5,6);
+
+values (1,2),(3,4)
+intersect
+values (1,2);
+
+--echo # combination of different structures that uses VALUES structures : UNION + EXCEPT
+
+values (1,2),(3,4)
+except
+select 1,2
+union values (1,2);
+
+values (1,2),(3,4)
+except
+values (1,2)
+union
+values (1,2);
+
+values (1,2),(3,4)
+except
+values (1,2)
+union
+values (3,4);
+
+values (1,2),(3,4)
+union
+values (1,2)
+except
+values (1,2);
+
+--echo # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT
+
+values (1,2),(3,4)
+except
+select 1,2
+union all
+values (1,2);
+
+values (1,2),(3,4)
+except
+values (1,2)
+union all
+values (1,2);
+
+values (1,2),(3,4)
+except
+values (1,2)
+union all
+values (3,4);
+
+values (1,2),(3,4)
+union all
+values (1,2)
+except
+values (1,2);
+
+--echo # combination of different structures that uses VALUES structures : UNION + INTERSECT
+
+values (1,2),(3,4)
+intersect
+select 1,2
+union
+values (1,2);
+
+values (1,2),(3,4)
+intersect
+values (1,2)
+union
+values (1,2);
+
+values (1,2),(3,4)
+intersect
+values (1,2)
+union
+values (3,4);
+
+values (1,2),(3,4)
+union
+values (1,2)
+intersect
+values (1,2);
+
+--echo # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT
+
+values (1,2),(3,4)
+intersect
+select 1,2
+union all
+values (1,2);
+
+values (1,2),(3,4)
+intersect
+values (1,2)
+union all
+values (1,2);
+
+values (1,2),(3,4)
+intersect
+values (1,2)
+union all
+values (3,4);
+
+values (1,2),(3,4)
+union all
+values (1,2)
+intersect
+values (1,2);
+
+--echo # combination of different structures that uses VALUES structures : UNION + UNION ALL
+
+values (1,2),(3,4)
+union all
+select 1,2
+union
+values (1,2);
+
+values (1,2),(3,4)
+union all
+values (1,2)
+union
+values (1,2);
+
+values (1,2),(3,4)
+union all
+values (1,2)
+union
+values (3,4);
+
+values (1,2),(3,4)
+union
+values (1,2)
+union all
+values (1,2);
+
+values (1,2)
+union
+values (1,2)
+union all
+values (1,2);
+
+--echo # CTE that uses VALUES structure(s) : non-recursive CTE
+
+with t2 as
+(
+  values (1,2),(3,4)
+)
+select * from t2;
+
+with t2 as
+(
+  select 1,2
+  union
+  values (1,2)
+)
+select * from t2;
+
+with t2 as
+(
+  select 1,2
+  union
+  values (1,2),(3,4)
+)
+select * from t2;
+
+with t2 as
+(
+  values (1,2)
+  union
+  select 1,2
+)
+select * from t2;
+
+with t2 as
+(
+  values (1,2),(3,4)
+  union
+  select 1,2
+)
+select * from t2;
+
+with t2 as
+(
+  values (5,6)
+  union
+  values (1,2),(3,4)
+)
+select * from t2;
+
+with t2 as
+(
+  values (1,2)
+  union
+  values (1,2),(3,4)
+)
+select * from t2;
+
+with t2 as
+(
+  select 1,2
+  union all
+  values (1,2),(3,4)
+)
+select * from t2;
+
+with t2 as
+(
+  values (1,2),(3,4)
+  union all
+  select 1,2
+)
+select * from t2;
+
+with t2 as
+(
+  values (1,2)
+  union all
+  values (1,2),(3,4)
+)
+select * from t2;
+
+--echo # recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor
+
+with recursive t2(a,b) as
+(
+  values(1,1)
+  union
+  select t1.a, t1.b
+  from t1,t2
+  where t1.a=t2.a
+)
+select * from t2;
+
+with recursive t2(a,b) as
+(
+  values(1,1)
+  union
+  select t1.a+1, t1.b
+  from t1,t2
+  where t1.a=t2.a
+)
+select * from t2;
+
+--echo # recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors
+
+with recursive t2(a,b) as
+(
+  values(1,1)
+  union
+  values (3,4)
+  union
+  select t2.a+1, t1.b
+  from t1,t2
+  where t1.a=t2.a
+)
+select * from t2;
+
+--echo # recursive CTE that uses VALUES structure(s) : that uses UNION ALL
+
+with recursive t2(a,b,st) as
+(
+  values(1,1,1)
+  union all
+  select t2.a, t1.b, t2.st+1
+  from t1,t2
+  where t1.a=t2.a and st<3
+)
+select * from t2;
+
+--echo # recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements)
+
+with recursive fact(n,f) as
+(
+  values(1,1)
+  union
+  select n+1,f*n from fact where n < 10
+)
+select * from fact;
+
+--echo # Derived table that uses VALUES structure(s) : singe VALUES structure
+
+select * from (values (1,2),(3,4)) as t2;
+
+--echo # Derived table that uses VALUES structure(s) : UNION with VALUES structure(s)
+
+select * from (select 1,2 union values (1,2)) as t2;
+
+select * from (select 1,2 union values (1,2),(3,4)) as t2;
+
+select * from (values (1,2) union select 1,2) as t2;
+
+select * from (values (1,2),(3,4) union select 1,2) as t2;
+
+select * from (values (5,6) union values (1,2),(3,4)) as t2;
+
+select * from (values (1,2) union values (1,2),(3,4)) as t2;
+
+--echo # Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
+
+select * from (select 1,2 union all values (1,2),(3,4)) as t2;
+
+select * from (values (1,2),(3,4) union all select 1,2) as t2;
+
+select * from (values (1,2) union all values (1,2),(3,4)) as t2;
+
+--echo # CREATE VIEW that uses VALUES structure(s) : singe VALUES structure
+
+let $drop_view= drop view v1;
+let $select_view= select * from v1;
+
+create view v1 as values (1,2),(3,4);
+
+eval $select_view;
+eval $drop_view;
+
+--echo # CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s)
+
+create view v1 as
+  select 1,2
+  union
+  values (1,2);
+
+eval $select_view;
+eval $drop_view;
+
+create view v1 as
+  select 1,2
+  union
+  values (1,2),(3,4);
+
+eval $select_view;
+eval $drop_view;
+
+create view v1 as
+  values (1,2)
+  union
+  select 1,2;
+
+eval $select_view;
+eval $drop_view;
+
+create view v1 as
+  values (1,2),(3,4)
+  union
+  select 1,2;
+
+eval $select_view;
+eval $drop_view;
+
+create view v1 as
+  values (5,6)
+  union
+  values (1,2),(3,4);
+
+eval $select_view;
+eval $drop_view;
+
+--echo # CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
+
+create view v1 as
+  values (1,2)
+  union
+  values (1,2),(3,4);
+
+eval $select_view;
+eval $drop_view;
+
+create view v1 as
+  select 1,2
+  union all
+  values (1,2),(3,4);
+
+eval $select_view;
+eval $drop_view;
+
+create view v1 as
+  values (1,2),(3,4)
+  union all
+  select 1,2;
+
+eval $select_view;
+eval $drop_view;
+
+create view v1 as
+  values (1,2)
+  union all
+  values (1,2),(3,4);
+
+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 "
+values (1,2);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+--echo # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s)
+
+prepare stmt1 from "
+  select 1,2
+  union
+  values (1,2),(3,4);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  values (1,2),(3,4)
+  union
+  select 1,2;
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  select 1,2
+  union
+  values (3,4)
+  union
+  values (1,2);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  values (5,6)
+  union
+  values (1,2),(3,4);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+--echo # prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+
+prepare stmt1 from "
+  select 1,2
+  union
+  values (1,2),(3,4);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  values (1,2),(3,4)
+  union all
+  select 1,2;
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  select 1,2
+  union all
+  values (3,4)
+  union all
+  values (1,2);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  values (1,2)
+  union all
+  values (1,2),(3,4);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+--echo # explain query that uses VALUES structure(s): single VALUES structure
+
+explain
+values (1,2);
+
+explain format=json
+values (1,2);
+
+--echo # explain query that uses VALUES structure(s): UNION with VALUES structure(s)
+
+explain
+select 1,2
+union
+values (1,2),(3,4);
+
+explain
+values (1,2),(3,4)
+union
+select 1,2;
+
+explain
+values (5,6)
+union
+values (1,2),(3,4);
+
+explain format=json
+select 1,2
+union
+values (1,2),(3,4);
+
+explain format=json
+values (1,2),(3,4)
+union
+select 1,2;
+
+explain format=json
+values (5,6)
+union
+values (1,2),(3,4);
+
+explain
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+
+explain format=json
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+
+--echo # explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+
+explain
+select 1,2
+union
+values (1,2),(3,4);
+
+explain
+values (1,2),(3,4)
+union all
+select 1,2;
+
+explain
+values (1,2)
+union all
+values (1,2),(3,4);
+
+explain format=json
+values (1,2),(3,4)
+union all
+select 1,2;
+
+explain format=json
+select 1,2
+union
+values (1,2),(3,4);
+
+explain format=json
+values (1,2)
+union all
+values (1,2),(3,4);
+
+explain
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+
+explain format=json
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+
+--echo # analyze query that uses VALUES structure(s): single VALUES structure
+
+analyze
+values (1,2);
+
+analyze format=json
+values (1,2);
+
+--echo # analyze query that uses VALUES structure(s): UNION with VALUES structure(s)
+
+analyze
+select 1,2
+union
+values (1,2),(3,4);
+
+analyze
+values (1,2),(3,4)
+union
+select 1,2;
+
+analyze
+values (5,6)
+union
+values (1,2),(3,4);
+
+analyze format=json
+select 1,2
+union
+values (1,2),(3,4);
+
+analyze format=json
+values (1,2),(3,4)
+union
+select 1,2;
+
+analyze format=json
+values (5,6)
+union
+values (1,2),(3,4);
+
+analyze
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+
+analyze format=json
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+
+--echo # analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+
+analyze
+select 1,2
+union
+values (1,2),(3,4);
+
+analyze
+values (1,2),(3,4)
+union all
+select 1,2;
+
+analyze
+values (1,2)
+union all
+values (1,2),(3,4);
+
+analyze format=json
+values (1,2),(3,4)
+union all
+select 1,2;
+
+analyze format=json
+select 1,2
+union
+values (1,2),(3,4);
+
+analyze format=json
+values (1,2)
+union all
+values (1,2),(3,4);
+
+analyze
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+
+analyze format=json
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+
+--echo # different number of values in TVC
+--error ER_WRONG_NUMBER_OF_VALUES_IN_TVC
+values (1,2),(3,4,5);
+
+--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/CMakeLists.txt b/sql/CMakeLists.txt
index 8a03692598d..0f67032bcbe 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -142,6 +142,7 @@ SET (SQL_SOURCE
                item_windowfunc.cc sql_window.cc
 	       sql_cte.cc
                sql_sequence.cc sql_sequence.h ha_sequence.h
+               sql_tvc.cc sql_tvc.h
 	       ${WSREP_SOURCES}
                table_cache.cc encryption.cc temporary_tables.cc
                proxy_protocol.cc
diff --git a/sql/gen_lex_token.cc b/sql/gen_lex_token.cc
index ebd966d9301..4b5e0746eac 100644
--- a/sql/gen_lex_token.cc
+++ b/sql/gen_lex_token.cc
@@ -130,6 +130,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 024fbd87d79..d36e6516fb9 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -5778,6 +5778,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
   {
@@ -9194,7 +9201,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.h b/sql/item.h
index 1f743913d78..9f76f081562 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1755,6 +1755,8 @@ public:
   virtual Item *derived_grouping_field_transformer_for_where(THD *thd,
                                                              uchar *arg)
   { return this; }
+  virtual Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg)
+  { return this; }
   virtual bool expr_cache_is_needed(THD *) { return FALSE; }
   virtual Item *safe_charset_converter(THD *thd, CHARSET_INFO *tocs);
   bool needs_charset_converter(uint32 length, CHARSET_INFO *tocs) const
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index f35017629aa..775fb425000 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4423,6 +4423,26 @@ longlong Item_func_in::val_int()
 }
 
 
+void Item_func_in::mark_as_condition_AND_part(TABLE_LIST *embedding)
+{
+  THD *thd= current_thd;
+
+  Query_arena *arena, backup;
+  arena= thd->activate_stmt_arena_if_needed(&backup);
+
+  if (to_be_transformed_into_in_subq(thd))
+  {
+    transform_into_subq= true;
+    thd->lex->current_select->in_funcs.push_back(this, thd->mem_root);
+  }
+
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
+
+  emb_on_expr_nest= embedding;
+}
+
+
 longlong Item_func_bit_or::val_int()
 {
   DBUG_ASSERT(fixed == 1);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 62066ef61e5..71172dd92a0 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -2280,6 +2280,7 @@ class Item_func_in :public Item_func_opt_neg,
 protected:
   SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param,
                              Field *field, Item *value);
+  bool transform_into_subq;
 public:
   /// An array of values, created when the bisection lookup method is used
   in_vector *array;
@@ -2296,11 +2297,13 @@ public:
   */
   bool arg_types_compatible;
 
+  TABLE_LIST *emb_on_expr_nest;
+
   Item_func_in(THD *thd, List<Item> &list):
     Item_func_opt_neg(thd, list),
     Predicant_to_list_comparator(thd, arg_count - 1),
     array(0), have_null(0),
-    arg_types_compatible(FALSE)
+    arg_types_compatible(FALSE), emb_on_expr_nest(0)
   { }
   longlong val_int();
   bool fix_fields(THD *, Item **);
@@ -2392,7 +2395,11 @@ public:
         return NULL;
     }
     return clone;
-  }      
+  }
+  void mark_as_condition_AND_part(TABLE_LIST *embedding);
+  bool to_be_transformed_into_in_subq(THD *thd);
+  bool create_value_list_for_tvc(THD *thd, List< List<Item> > *values);
+  Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg);
 };
 
 class cmp_item_row :public cmp_item
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 1f2949903b4..aa92ae1e6b1 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -116,9 +116,10 @@ void Item_subselect::init(st_select_lex *select_lex,
       do not take into account expression inside aggregate functions because
       they can access original table fields
     */
-    parsing_place= (outer_select->in_sum_expr ? NO_MATTER
-                                              : outer_select->parsing_place);
-    if (unit->is_unit_op())
+    parsing_place= (outer_select->in_sum_expr ?
+                    NO_MATTER :
+                    outer_select->parsing_place);
+    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);
@@ -264,6 +265,13 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref)
   if (check_stack_overrun(thd, STACK_MIN_SIZE, (uchar*)&res))
     return TRUE;
   
+  for (SELECT_LEX *sl= unit->first_select(); sl; sl= sl->next_select())
+  {
+    if (sl->tvc)
+    {
+      wrap_tvc_in_derived_table(thd, sl);
+    }
+  }
   
   if (!(res= engine->prepare(thd)))
   {
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 6112c1c22f4..14394bf6342 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;
@@ -878,6 +879,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/opt_subselect.cc b/sql/opt_subselect.cc
index 6dc5e08152c..25b98434ef3 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1065,6 +1065,8 @@ bool convert_join_subqueries_to_semijoins(JOIN *join)
       DBUG_RETURN(1);
     if (subq_sel->handle_derived(thd->lex, DT_MERGE))
       DBUG_RETURN(TRUE);
+    if (subq_sel->join->transform_in_predicates_into_in_subq(thd))
+      DBUG_RETURN(TRUE);
     subq_sel->update_used_tables();
   }
 
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index e414a674af6..6685b21c1df 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7786,3 +7786,7 @@ ER_COMPRESSED_COLUMN_USED_AS_KEY
   eng "Compressed column '%-.192s' can't be used in key specification"
 ER_UNKNOWN_COMPRESSION_METHOD
   eng "Unknown compression method: %s"
+ER_WRONG_NUMBER_OF_VALUES_IN_TVC
+        eng "The used table value constructor has a different number of values"
+ER_FIELD_REFERENCE_IN_TVC
+	eng "Field reference '%-.192s' can't be used in table value constructor"
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 740e9911838..6a1cfe07e66 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -702,6 +702,7 @@ typedef struct system_variables
   uint idle_write_transaction_timeout;
   uint column_compression_threshold;
   uint column_compression_zlib_level;
+  ulong in_subquery_conversion_threshold;
 } SV;
 
 /**
@@ -6232,6 +6233,64 @@ inline bool lex_string_eq(const LEX_CSTRING *a,
   return strcasecmp(a->str, b->str) != 0;
 }
 
+class Type_holder: public Sql_alloc,
+                   public Item_args,
+                   public Type_handler_hybrid_field_type,
+                   public Type_all_attributes,
+                   public Type_geometry_attributes
+{
+  TYPELIB *m_typelib;
+  bool m_maybe_null;
+public:
+  Type_holder()
+   :m_typelib(NULL),
+    m_maybe_null(false)
+  { }
+
+  void set_maybe_null(bool maybe_null_arg) { m_maybe_null= maybe_null_arg; }
+  bool get_maybe_null() const { return m_maybe_null; }
+
+  uint decimal_precision() const
+  {
+    /*
+      Type_holder is not used directly to create fields, so
+      its virtual decimal_precision() is never called.
+      We should eventually extend create_result_table() to accept
+      an array of Type_holders directly, without having to allocate
+      Item_type_holder's and put them into List<Item>.
+    */
+    DBUG_ASSERT(0);
+    return 0;
+  }
+  void set_geometry_type(uint type)
+  {
+    Type_geometry_attributes::set_geometry_type(type);
+  }
+  uint uint_geometry_type() const
+  {
+    return Type_geometry_attributes::get_geometry_type();
+  }
+  void set_typelib(TYPELIB *typelib)
+  {
+    m_typelib= typelib;
+  }
+  TYPELIB *get_typelib() const
+  {
+    return m_typelib;
+  }
+
+  bool aggregate_attributes(THD *thd)
+  {
+    for (uint i= 0; i < arg_count; i++)
+      m_maybe_null|= args[i]->maybe_null;
+    return
+       type_handler()->Item_hybrid_func_fix_attributes(thd,
+                                                       "UNION", this, this,
+                                                       args, arg_count);
+  }
+};
+
+
 #endif /* MYSQL_SERVER */
 
 #endif /* SQL_CLASS_INCLUDED */
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index ce43a45b872..3070f2a860b 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,24 @@ 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_PART_FUNC)
+      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;
   }
@@ -2224,6 +2242,8 @@ void st_select_lex::init_query()
   m_agg_func_used= false;
   window_specs.empty();
   window_funcs.empty();
+  tvc= 0;
+  in_tvc= false;
 }
 
 void st_select_lex::init_select()
@@ -2261,6 +2281,10 @@ void st_select_lex::init_select()
   with_dep= 0;
   join= 0;
   lock_type= TL_READ_DEFAULT;
+  tvc= 0;
+  in_funcs.empty();
+  curr_tvc_name= 0;
+  in_tvc= false;
 }
 
 /*
@@ -4180,6 +4204,22 @@ bool SELECT_LEX::merge_subquery(THD *thd, TABLE_LIST *derived,
       if (in_subq->emb_on_expr_nest == NO_JOIN_NEST)
          in_subq->emb_on_expr_nest= derived;
     }
+
+    uint cnt= sizeof(expr_cache_may_be_used)/sizeof(bool);
+    for (uint i= 0; i < cnt; i++)
+    {
+      if (subq_select->expr_cache_may_be_used[i])
+	expr_cache_may_be_used[i]= true;
+    }
+
+    List_iterator_fast<Item_func_in> it(subq_select->in_funcs);
+    Item_func_in *in_func;
+    while ((in_func= it++))
+    {
+      in_funcs.push_back(in_func, thd->mem_root);
+      if (in_func->emb_on_expr_nest == NO_JOIN_NEST)
+        in_func->emb_on_expr_nest= derived;
+    }
   }
 
   /* Walk through child's tables and adjust table map, tablenr,
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 7619353d922..dfdeb9d4a83 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -30,7 +30,7 @@
 #include "sql_window.h"
 #include "sql_trigger.h"
 #include "sp.h"                       // enum stored_procedure_type
-
+#include "sql_tvc.h"
 
 /* YACC and LEX Definitions */
 
@@ -881,6 +881,17 @@ public:
     those converted to jtbm nests. The list is emptied when conversion is done.
   */
   List<Item_in_subselect> sj_subselects;
+  /*
+    List of IN-predicates in this st_select_lex that
+    can be transformed into IN-subselect defined with TVC.
+  */
+  List<Item_func_in> in_funcs;
+  /*
+    Number of current derived table made with TVC during the
+    transformation of IN-predicate into IN-subquery for this
+    st_select_lex.
+  */
+  uint curr_tvc_name;
   
   /*
     Needed to correctly generate 'PRIMARY' or 'SIMPLE' for select_type column
@@ -1017,6 +1028,9 @@ public:
 
   /* it is for correct printing SELECT options */
   thr_lock_type lock_type;
+  
+  table_value_constr *tvc;
+  bool in_tvc;
 
   void init_query();
   void init_select();
@@ -1232,7 +1246,7 @@ public:
   ORDER *find_common_window_func_partition_fields(THD *thd);
 
   bool cond_pushdown_is_allowed() const
-  { return !olap && !explicit_limit; }
+  { return !olap && !explicit_limit && !tvc; }
   
 private:
   bool m_non_agg_field_used;
@@ -1256,7 +1270,12 @@ typedef class st_select_lex SELECT_LEX;
 inline bool st_select_lex_unit::is_unit_op ()
 {
   if (!first_select()->next_select())
-    return 0;
+  {
+    if (first_select()->tvc)
+      return 1;
+    else
+      return 0;
+  }
 
   enum sub_select_type linkage= first_select()->next_select()->linkage;
   return linkage == UNION_TYPE || linkage == INTERSECT_TYPE ||
diff --git a/sql/sql_priv.h b/sql/sql_priv.h
index db75dc2198b..b3b041a3602 100644
--- a/sql/sql_priv.h
+++ b/sql/sql_priv.h
@@ -352,6 +352,8 @@ enum enum_parsing_place
   IN_ON,
   IN_GROUP_BY,
   IN_ORDER_BY,
+  IN_UPDATE_ON_DUP_KEY,
+  IN_PART_FUNC,
   PARSING_PLACE_SIZE /* always should be the last */
 };
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 53d445177f7..fe87dd9afbb 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1203,6 +1203,11 @@ JOIN::optimize_inner()
       DBUG_RETURN(TRUE);  
     table_count= select_lex->leaf_tables.elements;
   }
+
+  if (select_lex->first_cond_optimization &&
+      transform_in_predicates_into_in_subq(thd))
+    DBUG_RETURN(1);
+
   // Update used tables after all handling derived table procedures
   select_lex->update_used_tables();
 
@@ -14221,7 +14226,7 @@ Item *eliminate_item_equal(THD *thd, COND *cond, COND_EQUAL *upper_levels,
         equals on top level, or the constant.
       */
       Item *head_item= (!item_const && current_sjm && 
-                        current_sjm_head != field_item) ? current_sjm_head: head; 
+                        current_sjm_head != field_item) ? current_sjm_head: head;
       Item *head_real_item=  head_item->real_item();
       if (head_real_item->type() == Item::FIELD_ITEM)
         head_item= head_real_item;
@@ -25612,6 +25617,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_select.h b/sql/sql_select.h
index b6b8deb99f5..260b86029c6 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1676,6 +1676,7 @@ public:
   bool inject_cond_into_where(Item *injected_cond);
   bool push_splitting_cond_into_derived(THD *thd, Item *cond);
   bool improve_chosen_plan(THD *thd);
+  bool transform_in_predicates_into_in_subq(THD *thd);
 private:
   /**
     Create a temporary table to be used for processing DISTINCT/ORDER
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
new file mode 100644
index 00000000000..f06ea049e3d
--- /dev/null
+++ b/sql/sql_tvc.cc
@@ -0,0 +1,834 @@
+#include "sql_list.h"
+#include "sql_tvc.h"
+#include "sql_class.h"
+#include "opt_range.h"
+#include "sql_select.h"
+#include "sql_explain.h"
+#include "sql_parse.h"
+
+
+/**
+  @brief
+    Fix fields for TVC values
+
+  @param
+    @param thd	 The context of the statement
+    @param li	 The iterator on the list of lists
+
+  @details
+    Call fix_fields procedure for TVC values.
+
+  @retval
+    true     if an error was reported
+    false    otherwise
+*/
+
+bool fix_fields_for_tvc(THD *thd, List_iterator_fast<List_item> &li)
+{
+  DBUG_ENTER("fix_fields_for_tvc");
+  List_item *lst;
+  li.rewind();
+
+  while ((lst= li++))
+  {
+    List_iterator_fast<Item> it(*lst);
+    Item *item;
+
+    while ((item= it++))
+    {
+      if (item->fix_fields(thd, 0))
+	DBUG_RETURN(true);
+    }
+  }
+  DBUG_RETURN(false);
+}
+
+
+/**
+  @brief
+    Defines types of matrix columns elements where matrix rows are defined by
+    some lists of values.
+
+  @param
+    @param thd   	 The context of the statement
+    @param li	     	 The iterator on the list of lists
+    @param holders   	 The structure where types of matrix columns are stored
+    @param first_list_el_count  Count of the list values. It should be the same
+                                for each list of lists elements. It contains
+			        number of elements of the first list from list of
+                                lists.
+
+  @details
+    For each list list_a from list of lists the procedure gets its elements
+    types and aggregates them with the previous ones stored in holders. If
+    list_a is the first one in the list of lists its elements types are put in
+    holders. The errors can be reported when count of list_a elements is
+    different from the first_list_el_count. Also error can be reported whe
+    n aggregation can't be made.
+
+  @retval
+    true    if an error was reported
+    false   otherwise
+*/
+
+bool join_type_handlers_for_tvc(THD *thd, List_iterator_fast<List_item> &li,
+			        Type_holder *holders, uint first_list_el_count)
+{
+  DBUG_ENTER("join_type_handlers_for_tvc");
+  List_item *lst;
+  li.rewind();
+  bool first= true;
+  
+  while ((lst= li++))
+  {
+    List_iterator_fast<Item> it(*lst);
+    Item *item;
+  
+    if (first_list_el_count != lst->elements)
+    {
+      my_message(ER_WRONG_NUMBER_OF_VALUES_IN_TVC,
+                 ER_THD(thd, ER_WRONG_NUMBER_OF_VALUES_IN_TVC),
+                 MYF(0));
+      DBUG_RETURN(true);
+    }
+    for (uint pos= 0; (item=it++); pos++)
+    {
+      const Type_handler *item_type_handler= item->real_type_handler();
+      if (first)
+        holders[pos].set_handler(item_type_handler);
+      else if (holders[pos].aggregate_for_result(item_type_handler))
+      {
+        my_error(ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION, MYF(0),
+                 holders[pos].type_handler()->name().ptr(),
+                 item_type_handler->name().ptr(),
+                 "TABLE VALUE CONSTRUCTOR");
+        DBUG_RETURN(true);
+      }
+    }
+    first= false;
+  }
+  DBUG_RETURN(false);
+}
+
+
+/**
+  @brief
+    Define attributes of matrix columns elements where matrix rows are defined
+    by some lists of values.
+
+  @param
+    @param thd	  	  The context of the statement
+    @param li	     	  The iterator on the list of lists
+    @param holders   	  The structure where names of matrix columns are stored
+    @param count_of_lists Count of list of lists elements
+    @param first_list_el_count  Count of the list values. It should be the same
+                                for each list of lists elements. It contains
+				number of elements of the first list from list
+                                of lists.
+
+  @details
+    For each list list_a from list of lists the procedure gets its elements
+    attributes and aggregates them with the previous ones stored in holders.
+    The errors can be reported when aggregation can't be made.
+
+  @retval
+    true     if an error was reported
+    false    otherwise
+*/
+
+bool get_type_attributes_for_tvc(THD *thd,
+			         List_iterator_fast<List_item> &li, 
+                                 Type_holder *holders, uint count_of_lists,
+				 uint first_list_el_count)
+{
+  DBUG_ENTER("get_type_attributes_for_tvc");
+  List_item *lst;
+  li.rewind();
+  
+  for (uint pos= 0; pos < first_list_el_count; pos++)
+  {
+    if (holders[pos].alloc_arguments(thd, count_of_lists))
+      DBUG_RETURN(true);
+  }
+  
+  while ((lst= li++))
+  {
+    List_iterator_fast<Item> it(*lst);
+    Item *item;
+    for (uint holder_pos= 0 ; (item= it++); holder_pos++)
+    {
+      DBUG_ASSERT(item->fixed);
+      holders[holder_pos].add_argument(item);
+    }
+  }
+  
+  for (uint pos= 0; pos < first_list_el_count; pos++)
+  {
+    if (holders[pos].aggregate_attributes(thd))
+      DBUG_RETURN(true);
+  }
+  DBUG_RETURN(false);
+}
+
+
+/**
+  @brief
+    Prepare of TVC
+
+  @param
+    @param thd	        The context of the statement
+    @param sl	     	The select where this TVC is defined
+    @param tmp_result	Structure that contains the information
+			about where to send the result of the query
+    @param unit_arg  	The union where sl is defined
+
+  @details
+    Gets types and attributes of values of this TVC that will be used
+    for temporary table creation for this TVC. It creates Item_type_holders
+    for each element of the first list from list of lists (VALUES from tvc),
+    using its elements name, defined type and attribute.
+
+  @retval
+    true     if an error was reported
+    false    otherwise
+*/
+
+bool table_value_constr::prepare(THD *thd, SELECT_LEX *sl,
+				 select_result *tmp_result,
+				 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++;
+  uint cnt= first_elem->elements;
+  Type_holder *holders;
+  
+  if (fix_fields_for_tvc(thd, li))
+    DBUG_RETURN(true);
+
+  if (!(holders= new (thd->mem_root)
+                Type_holder[cnt]) || 
+       join_type_handlers_for_tvc(thd, li, holders,
+				  cnt) ||
+       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;
+  
+  sl->item_list.empty();
+  for (uint pos= 0; (item= it++); pos++)
+  {
+    /* Error's in 'new' will be detected after loop */
+    Item_type_holder *new_holder= new (thd->mem_root)
+                      Item_type_holder(thd,
+                                       &item->name,
+                                       holders[pos].type_handler(),
+                                       &holders[pos]/*Type_all_attributes*/,
+                                       holders[pos].get_maybe_null());
+    new_holder->fix_fields(thd, 0);
+    sl->item_list.push_back(new_holder);
+  }
+  
+  if (thd->is_fatal_error)
+    DBUG_RETURN(true); // out of memory
+    
+  result= tmp_result;
+  
+  if (result && result->prepare(sl->item_list, unit_arg))
+    DBUG_RETURN(true);
+
+  select_lex->in_tvc= false;
+  DBUG_RETURN(false);
+}
+
+
+/**
+    Save Query Plan Footprint
+*/
+
+int table_value_constr::save_explain_data_intern(THD *thd,
+						 Explain_query *output)
+{
+  const char *message= "No tables used";
+  DBUG_ENTER("table_value_constr::save_explain_data_intern");
+  DBUG_PRINT("info", ("Select 0x%lx, type %s, message %s",
+		      (ulong)select_lex, select_lex->type,
+		      message));
+  DBUG_ASSERT(have_query_plan == QEP_AVAILABLE);
+
+  /* There should be no attempts to save query plans for merged selects */
+  DBUG_ASSERT(!select_lex->master_unit()->derived ||
+               select_lex->master_unit()->derived->is_materialized_derived() ||
+               select_lex->master_unit()->derived->is_with_table());
+
+  explain= new (output->mem_root) Explain_select(output->mem_root,
+                                                 thd->lex->analyze_stmt);
+  select_lex->set_explain_type(true);
+
+  explain->select_id= select_lex->select_number;
+  explain->select_type= select_lex->type;
+  explain->linkage= select_lex->linkage;
+  explain->using_temporary= NULL;
+  explain->using_filesort=  NULL;
+  /* Setting explain->message means that all other members are invalid */
+  explain->message= message;
+
+  if (select_lex->master_unit()->derived)
+    explain->connection_type= Explain_node::EXPLAIN_NODE_DERIVED;
+
+  output->add_node(explain);
+
+  if (select_lex->is_top_level_node())
+    output->query_plan_ready();
+
+  DBUG_RETURN(0);
+}
+
+
+/**
+  Optimization of TVC
+*/
+
+void table_value_constr::optimize(THD *thd)
+{
+  create_explain_query_if_not_exists(thd->lex, thd->mem_root);
+  have_query_plan= QEP_AVAILABLE;
+
+  if (select_lex->select_number != UINT_MAX &&
+      select_lex->select_number != INT_MAX /* this is not a UNION's "fake select */ &&
+      have_query_plan != QEP_NOT_PRESENT_YET &&
+      thd->lex->explain && // for "SET" command in SPs.
+      (!thd->lex->explain->get_select(select_lex->select_number)))
+  {
+    save_explain_data_intern(thd, thd->lex->explain);
+  }
+}
+
+
+/**
+  Execute of TVC
+*/
+
+bool table_value_constr::exec(SELECT_LEX *sl)
+{
+  DBUG_ENTER("table_value_constr::exec");
+  List_iterator_fast<List_item> li(lists_of_values);
+  List_item *elem;
+  
+  if (select_options & SELECT_DESCRIBE)
+    DBUG_RETURN(false);
+
+  if (result->send_result_set_metadata(sl->item_list,
+                                       Protocol::SEND_NUM_ROWS |
+                                       Protocol::SEND_EOF))
+  {
+    DBUG_RETURN(true);
+  }
+
+  while ((elem= li++))
+  {
+    result->send_data(*elem);
+  }
+
+  if (result->send_eof())
+    DBUG_RETURN(true);
+
+  DBUG_RETURN(false);
+}
+
+
+/**
+  @brief
+    Print list
+
+  @param str         The reference on the string representation of the list
+  @param list	     The list that needed to be print
+  @param query_type  The mode of printing
+
+  @details
+    The method saves a string representation of list in the
+    string str.
+*/
+
+void print_list_item(String *str, List_item *list,
+		     enum_query_type query_type)
+{
+  bool is_first_elem= true;
+  List_iterator_fast<Item> it(*list);
+  Item *item;
+
+  str->append('(');
+
+  while ((item= it++))
+  {
+    if (is_first_elem)
+      is_first_elem= false;
+    else
+      str->append(',');
+
+    item->print(str, query_type);
+  }
+
+  str->append(')');
+}
+
+
+/**
+  @brief
+    Print this TVC
+
+  @param thd         The context of the statement
+  @param str         The reference on the string representation of this TVC
+  @param query_type  The mode of printing
+
+  @details
+    The method saves a string representation of this TVC in the
+    string str.
+*/
+
+void table_value_constr::print(THD *thd, String *str,
+			       enum_query_type query_type)
+{
+  DBUG_ASSERT(thd);
+
+  str->append(STRING_WITH_LEN("values "));
+
+  bool is_first_elem= true;
+  List_iterator_fast<List_item> li(lists_of_values);
+  List_item *list;
+
+  while ((list= li++))
+  {
+    if (is_first_elem)
+      is_first_elem= false;
+    else
+      str->append(',');
+
+    print_list_item(str, list, query_type);
+  }
+}
+
+
+/**
+  @brief
+    Create list of lists for TVC from the list of this IN predicate
+
+  @param thd         The context of the statement
+  @param values      TVC list of values
+
+  @details
+    The method uses the list of values of this IN predicate to build
+    an equivalent list of values that can be used in TVC.
+
+    E.g.:
+
+    <value_list> = 5,2,7
+    <transformed_value_list> = (5),(2),(7)
+
+    <value_list> = (5,2),(7,1)
+    <transformed_value_list> = (5,2),(7,1)
+
+  @retval
+    false     if the method succeeds
+    true      otherwise
+*/
+
+bool Item_func_in::create_value_list_for_tvc(THD *thd,
+				             List< List<Item> > *values)
+{
+  bool is_list_of_rows= args[1]->type() == Item::ROW_ITEM;
+
+  for (uint i=1; i < arg_count; i++)
+  {
+    List<Item> *tvc_value;
+    if (!(tvc_value= new (thd->mem_root) List<Item>()))
+      return true;
+
+    if (is_list_of_rows)
+    {
+      Item_row *row_list= (Item_row *)(args[i]);
+
+      for (uint j=0; j < row_list->cols(); j++)
+      {
+	if (tvc_value->push_back(row_list->element_index(j),
+				 thd->mem_root))
+	  return true;
+      }
+    }
+    else if (tvc_value->push_back(args[i]))
+      return true;
+
+    if (values->push_back(tvc_value, thd->mem_root))
+      return true;
+  }
+  return false;
+}
+
+
+/**
+  @brief
+    Create name for the derived table defined by TVC
+
+  @param thd               The context of the statement
+  @param parent_select     The SELECT where derived table is used
+  @param alias		   The returned created name
+
+  @details
+    Create name for the derived table using current TVC number
+    for this parent_select stored in parent_select
+
+  @retval
+    true     if creation fails
+    false    otherwise
+*/
+
+static bool create_tvc_name(THD *thd, st_select_lex *parent_select,
+			    LEX_CSTRING *alias)
+{
+  char buff[6];
+
+  alias->length= my_snprintf(buff, sizeof(buff),
+                            "tvc_%u", parent_select->curr_tvc_name);
+  alias->str= thd->strmake(buff, alias->length);
+  if (!alias->str)
+    return true;
+
+  return false;
+}
+
+
+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
+
+  @param thd     The context of the statement
+  @param arg     Not used
+
+  @details
+    The method transforms this IN predicate into in equivalent IN subquery:
+
+    <left_expr> IN (<value_list>)
+    =>
+    <left_expr> IN (SELECT * FROM (VALUES <transformed_value_list>) AS tvc_#)
+
+    E.g.:
+
+    <value_list> = 5,2,7
+    <transformed_value_list> = (5),(2),(7)
+
+    <value_list> = (5,2),(7,1)
+    <transformed_value_list> = (5,2),(7,1)
+
+    If the transformation succeeds the method returns the result IN subquery,
+    otherwise this IN predicate is returned.
+
+  @retval
+    pointer to the result of transformation if succeeded
+    pointer to this IN predicate otherwise
+*/
+
+Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd,
+							uchar *arg)
+{
+  if (!transform_into_subq)
+    return this;
+  
+  transform_into_subq= false;
+
+  List<List_item> values;
+
+  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;
+  
+  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);
+
+  /*
+    Create SELECT_LEX of the subquery SQ used in the result of transformation
+  */
+  if (mysql_new_select(lex, 1, 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->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)++;
+  /*
+    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, NULL))
+    goto err;
+  mysql_init_select(lex);
+  tvc_select= lex->current_select;
+  derived_unit= tvc_select->master_unit();
+  tvc_select->linkage= DERIVED_TABLE_TYPE;
+
+  /* Create TVC used in the transformation */
+  if (create_value_list_for_tvc(thd, &values))
+    goto err;
+  if (!(tvc_select->tvc=
+          new (thd->mem_root)
+	    table_value_constr(values,
+                               tvc_select,
+                               tvc_select->options)))
+    goto err;
+
+  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);
+
+  /* Create IN subquery predicate */
+  sq_select->parsing_place= parent_select->parsing_place;
+  Item_in_subselect *in_subs;
+  if (!(in_subs=
+          new (thd->mem_root) Item_in_subselect(thd, args[0], sq_select)))
+    goto err;
+  in_subs->emb_on_expr_nest= emb_on_expr_nest;
+
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
+  thd->lex->current_select= parent_select;
+
+  if (in_subs->fix_fields(thd, (Item **)&in_subs))
+    goto err;
+
+  parent_select->curr_tvc_name++;
+  return in_subs;
+
+err:
+  if (arena)
+    thd->restore_active_arena(arena, &backup);
+  lex->derived_tables= save_derived_tables;
+  thd->lex->current_select= parent_select;
+  return NULL;
+}
+
+
+/**
+  @brief
+    Check if this IN-predicate can be transformed in IN-subquery
+    with TVC
+
+  @param thd     The context of the statement
+
+  @details
+    Compare the number of elements in the list of
+    values in this IN-predicate with the
+    in_subquery_conversion_threshold special variable
+
+  @retval
+    true     if transformation can be made
+    false    otherwise
+*/
+
+bool Item_func_in::to_be_transformed_into_in_subq(THD *thd)
+{
+  uint values_count= arg_count-1;
+
+  if (args[1]->type() == Item::ROW_ITEM)
+    values_count*= ((Item_row *)(args[1]))->cols();
+
+  if (values_count < thd->variables.in_subquery_conversion_threshold)
+    return false;
+
+  return true;
+}
+
+
+/**
+  @brief
+    Transform IN predicates into IN subqueries in WHERE and ON expressions
+
+  @param thd     The context of the statement
+
+  @details
+    For each IN predicate from AND parts of the WHERE condition and/or
+    ON expressions of the SELECT for this join the method performs
+    the intransformation into an equivalent IN sunquery if it's needed.
+
+  @retval
+    false     always
+*/
+
+bool JOIN::transform_in_predicates_into_in_subq(THD *thd)
+{
+  DBUG_ENTER("JOIN::transform_in_predicates_into_in_subq");
+  if (!select_lex->in_funcs.elements)
+    DBUG_RETURN(false);
+
+  SELECT_LEX *save_current_select= thd->lex->current_select;
+  enum_parsing_place save_parsing_place= select_lex->parsing_place;
+  thd->lex->current_select= select_lex;
+  if (conds)
+  {
+    select_lex->parsing_place= IN_WHERE;
+    conds=
+      conds->transform(thd,
+		       &Item::in_predicate_to_in_subs_transformer,
+                       (uchar*) 0);
+    if (!conds)
+      DBUG_RETURN(true);
+    select_lex->prep_where= conds ? conds->copy_andor_structure(thd) : 0;
+    select_lex->where= conds;
+  }
+
+  if (join_list)
+  {
+    TABLE_LIST *table;
+    List_iterator<TABLE_LIST> li(*join_list);
+    select_lex->parsing_place= IN_ON;
+
+    while ((table= li++))
+    {
+      if (table->on_expr)
+      {
+        table->on_expr=
+          table->on_expr->transform(thd,
+		                    &Item::in_predicate_to_in_subs_transformer,
+                                    (uchar*) 0);
+	if (!table->on_expr)
+	  DBUG_RETURN(true);
+	table->prep_on_expr= table->on_expr ?
+                             table->on_expr->copy_andor_structure(thd) : 0;
+      }
+    }
+  }
+
+  select_lex->in_funcs.empty();
+  select_lex->parsing_place= save_parsing_place;
+  thd->lex->current_select= save_current_select;
+  DBUG_RETURN(false);
+}
+
diff --git a/sql/sql_tvc.h b/sql/sql_tvc.h
new file mode 100644
index 00000000000..5524744a03c
--- /dev/null
+++ b/sql/sql_tvc.h
@@ -0,0 +1,50 @@
+#ifndef SQL_TVC_INCLUDED
+#define SQL_TVC_INCLUDED
+#include "sql_type.h"
+#include "item.h"
+
+typedef List<Item> List_item;
+class select_result;
+
+class Explain_select;
+class Explain_query;
+class Item_func_in;
+
+/**
+  @class table_value_constr
+  @brief Definition of a Table Value Construction(TVC)
+	
+  It contains a list of lists of values which this TVC is defined by and
+  reference on SELECT where this TVC is defined.
+*/
+class table_value_constr : public Sql_alloc
+{
+public:
+  List<List_item> lists_of_values;
+  select_result *result;
+  SELECT_LEX *select_lex;
+
+  enum { QEP_NOT_PRESENT_YET, QEP_AVAILABLE} have_query_plan;
+
+  Explain_select *explain;
+  ulonglong select_options;
+  
+  table_value_constr(List<List_item> tvc_values, SELECT_LEX *sl,
+		     ulonglong select_options_arg) :
+    lists_of_values(tvc_values), result(0), select_lex(sl),
+    have_query_plan(QEP_NOT_PRESENT_YET), explain(0),
+    select_options(select_options_arg)
+  { };
+  
+  bool prepare(THD *thd_arg, SELECT_LEX *sl, 
+	       select_result *tmp_result,
+	       st_select_lex_unit *unit_arg);
+
+  int save_explain_data_intern(THD *thd_arg,
+			       Explain_query *output);
+  void optimize(THD *thd_arg);
+  bool exec(SELECT_LEX *sl);
+
+  void print(THD *thd_arg, String *str, enum_query_type query_type);
+};
+#endif /* SQL_TVC_INCLUDED */
\ No newline at end of file
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 362837834f2..511ba1df41b 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -693,64 +693,6 @@ bool st_select_lex_unit::prepare_join(THD *thd_arg, SELECT_LEX *sl,
 }
 
 
-class Type_holder: public Sql_alloc,
-                   public Item_args,
-                   public Type_handler_hybrid_field_type,
-                   public Type_all_attributes,
-                   public Type_geometry_attributes
-{
-  TYPELIB *m_typelib;
-  bool m_maybe_null;
-public:
-  Type_holder()
-   :m_typelib(NULL),
-    m_maybe_null(false)
-  { }
-
-  void set_maybe_null(bool maybe_null_arg) { m_maybe_null= maybe_null_arg; }
-  bool get_maybe_null() const { return m_maybe_null; }
-
-  uint decimal_precision() const
-  {
-    /*
-      Type_holder is not used directly to create fields, so
-      its virtual decimal_precision() is never called.
-      We should eventually extend create_result_table() to accept
-      an array of Type_holders directly, without having to allocate
-      Item_type_holder's and put them into List<Item>.
-    */
-    DBUG_ASSERT(0);
-    return 0;
-  }
-  void set_geometry_type(uint type)
-  {
-    Type_geometry_attributes::set_geometry_type(type);
-  }
-  uint uint_geometry_type() const
-  {
-    return Type_geometry_attributes::get_geometry_type();
-  }
-  void set_typelib(TYPELIB *typelib)
-  {
-    m_typelib= typelib;
-  }
-  TYPELIB *get_typelib() const
-  {
-    return m_typelib;
-  }
-
-  bool aggregate_attributes(THD *thd)
-  {
-    for (uint i= 0; i < arg_count; i++)
-      m_maybe_null|= args[i]->maybe_null;
-    return
-       type_handler()->Item_hybrid_func_fix_attributes(thd,
-                                                       "UNION", this, this,
-                                                       args, arg_count);
-  }
-};
-
-
 /**
   Aggregate data type handlers for the "count" leftmost UNION parts.
 */
@@ -889,6 +831,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
   bool is_union_select;
   bool have_except= FALSE, have_intersect= FALSE;
   bool instantiate_tmp_table= false;
+  bool single_tvc= !first_sl->next_select() && first_sl->tvc;
   DBUG_ENTER("st_select_lex_unit::prepare");
   DBUG_ASSERT(thd == thd_arg);
   DBUG_ASSERT(thd == current_thd);
@@ -915,16 +858,26 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
       /* fast reinit for EXPLAIN */
       for (sl= first_sl; sl; sl= sl->next_select())
       {
-	sl->join->result= result;
-	select_limit_cnt= HA_POS_ERROR;
-	offset_limit_cnt= 0;
-	if (!sl->join->procedure &&
-	    result->prepare(sl->join->fields_list, this))
+	if (sl->tvc)
 	{
-	  DBUG_RETURN(TRUE);
+	  sl->tvc->result= result;
+	  if (result->prepare(sl->item_list, this))
+	    DBUG_RETURN(TRUE);
+	  sl->tvc->select_options|= SELECT_DESCRIBE;
+	}
+	else
+	{
+	  sl->join->result= result;
+	  select_limit_cnt= HA_POS_ERROR;
+	  offset_limit_cnt= 0;
+	  if (!sl->join->procedure &&
+	      result->prepare(sl->join->fields_list, this))
+	  {
+	    DBUG_RETURN(TRUE);
+	  }
+	  sl->join->select_options|= SELECT_DESCRIBE;
+	  sl->join->reinit();
 	}
-	sl->join->select_options|= SELECT_DESCRIBE;
-	sl->join->reinit();
       }
     }
     DBUG_RETURN(FALSE);
@@ -934,7 +887,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
   
   thd_arg->lex->current_select= sl= first_sl;
   found_rows_for_union= first_sl->options & OPTION_FOUND_ROWS;
-  is_union_select= is_unit_op() || fake_select_lex;
+  is_union_select= is_unit_op() || fake_select_lex || single_tvc;
 
   for (SELECT_LEX *s= first_sl; s; s= s->next_select())
   {
@@ -954,8 +907,8 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
 
   if (is_union_select || is_recursive)
   {
-    if (is_unit_op() && !union_needs_tmp_table() &&
-        !have_except && !have_intersect)
+    if ((is_unit_op() && !union_needs_tmp_table() &&
+        !have_except && !have_intersect) || single_tvc)
     {
       SELECT_LEX *last= first_select();
       while (last->next_select())
@@ -990,7 +943,12 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
 
   if (!is_union_select && !is_recursive)
   {
-    if (prepare_join(thd_arg, first_sl, tmp_result, additional_options,
+    if (sl->tvc)
+    {
+      if (sl->tvc->prepare(thd_arg, sl, tmp_result, this))
+	goto err;
+    }
+    else if (prepare_join(thd_arg, first_sl, tmp_result, additional_options,
                      is_union_select))
       goto err;
     types= first_sl->item_list;
@@ -999,8 +957,13 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
  
   for (;sl; sl= sl->next_select(), union_part_count++)
   {
-    if (prepare_join(thd_arg, sl, tmp_result, additional_options,
-                     is_union_select))
+    if (sl->tvc)
+    {
+      if (sl->tvc->prepare(thd_arg, sl, tmp_result, this))
+	goto err;
+    }
+    else if (prepare_join(thd_arg, sl, tmp_result, additional_options,
+                          is_union_select))
       goto err;
 
     /*
@@ -1310,6 +1273,14 @@ bool st_select_lex_unit::optimize()
     }
     for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select())
     {
+      if (sl->tvc)
+      {
+	sl->tvc->select_options=
+          (select_limit_cnt == HA_POS_ERROR || sl->braces) ?
+          sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
+	sl->tvc->optimize(thd);
+	continue;
+      }
       thd->lex->current_select= sl;
 
       if (optimized)
@@ -1333,7 +1304,7 @@ bool st_select_lex_unit::optimize()
           we don't calculate found_rows() per union part.
           Otherwise, SQL_CALC_FOUND_ROWS should be done on all sub parts.
         */
-        sl->join->select_options= 
+        sl->join->select_options=
           (select_limit_cnt == HA_POS_ERROR || sl->braces) ?
           sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
 
@@ -1417,15 +1388,28 @@ bool st_select_lex_unit::exec()
           we don't calculate found_rows() per union part.
           Otherwise, SQL_CALC_FOUND_ROWS should be done on all sub parts.
         */
-        sl->join->select_options= 
-          (select_limit_cnt == HA_POS_ERROR || sl->braces) ?
-          sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
-	saved_error= sl->join->optimize();
+	if (sl->tvc)
+	{
+	  sl->tvc->select_options=
+             (select_limit_cnt == HA_POS_ERROR || sl->braces) ?
+             sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
+	  sl->tvc->optimize(thd);
+	}
+	else
+	{
+          sl->join->select_options= 
+            (select_limit_cnt == HA_POS_ERROR || sl->braces) ?
+            sl->options & ~OPTION_FOUND_ROWS : sl->options | found_rows_for_union;
+	  saved_error= sl->join->optimize();
+	}
       }
       if (!saved_error)
       {
 	records_at_start= table->file->stats.records;
-	sl->join->exec();
+	if (sl->tvc)
+	  sl->tvc->exec(sl);
+	else
+	  sl->join->exec();
         if (sl == union_distinct && !(with_element && with_element->is_recursive))
 	{
           // This is UNION DISTINCT, so there should be a fake_select_lex
@@ -1434,7 +1418,8 @@ bool st_select_lex_unit::exec()
 	    DBUG_RETURN(TRUE);
 	  table->no_keyread=1;
 	}
-	saved_error= sl->join->error;
+	if (!sl->tvc)
+	  saved_error= sl->join->error;
 	offset_limit_cnt= (ha_rows)(sl->offset_limit ?
                                     sl->offset_limit->val_uint() :
                                     0);
@@ -1664,8 +1649,13 @@ bool st_select_lex_unit::exec_recursive()
   for (st_select_lex *sl= start ; sl != end; sl= sl->next_select())
   {
     thd->lex->current_select= sl;
-    sl->join->exec();
-    saved_error= sl->join->error;
+    if (sl->tvc)
+      sl->tvc->exec(sl);
+    else
+    {
+      sl->join->exec();
+      saved_error= sl->join->error;
+    }
     if (!saved_error)
     {
        examined_rows+= thd->get_examined_row_count();
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 67b73dea506..ac1d7de45fb 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -67,6 +67,7 @@
 #include "lex_token.h"
 #include "sql_lex.h"
 #include "sql_sequence.h"
+#include "sql_tvc.h"
 
 /* this is to get the bison compilation windows warnings out */
 #ifdef _MSC_VER
@@ -1554,6 +1555,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 */
@@ -1783,7 +1786,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
         table_primary_ident table_primary_derived
         select_derived derived_table_list
         select_derived_union
+        derived_simple_table
         derived_query_specification
+        derived_table_value_constructor
 %type <date_time_type> date_time_type;
 %type <interval> interval
 
@@ -1824,11 +1829,13 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
 
 %type <select_lex> subselect
         get_select_lex get_select_lex_derived
+        simple_table
         query_specification
         query_term_union_not_ready
         query_term_union_ready
         query_expression_body
         select_paren_derived
+        table_value_constructor
 
 %type <boolfunc2creator> comp_op
 
@@ -4955,8 +4962,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; }
         ;
@@ -5206,7 +5220,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;
@@ -5220,7 +5234,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;
@@ -8477,6 +8491,9 @@ select:
 
 select_init:
           SELECT_SYM select_options_and_item_list select_init3
+        | table_value_constructor
+        | table_value_constructor union_list
+        | table_value_constructor union_order_or_limit
         | '(' select_paren ')'
         | '(' select_paren ')' union_list
         | '(' select_paren ')' union_order_or_limit
@@ -8484,6 +8501,9 @@ select_init:
 
 union_list_part2:
           SELECT_SYM select_options_and_item_list select_init3_union_query_term
+        | table_value_constructor
+        | table_value_constructor union_list
+        | table_value_constructor union_order_or_limit
         | '(' select_paren_union_query_term ')'
         | '(' select_paren_union_query_term ')' union_list
         | '(' select_paren_union_query_term ')' union_order_or_limit
@@ -8491,6 +8511,14 @@ union_list_part2:
 
 select_paren:
           {
+            Lex->current_select->set_braces(true);
+          }
+          table_value_constructor
+          {
+            DBUG_ASSERT(Lex->current_select->braces);
+          }
+        |
+          {
             /*
               In order to correctly parse UNION's global ORDER BY we need to
               set braces before parsing the clause.
@@ -8542,6 +8570,15 @@ select_paren_derived:
           {
             Lex->current_select->set_braces(true);
           }
+          table_value_constructor
+          {
+            DBUG_ASSERT(Lex->current_select->braces);
+            $$= Lex->current_select->master_unit()->first_select();
+          }
+        |
+          {
+            Lex->current_select->set_braces(true);
+          }
           SELECT_SYM select_part2_derived
           opt_table_expression
           opt_order_clause
@@ -9458,7 +9495,7 @@ column_default_non_parenthesized_expr:
             if ($$ == NULL)
               MYSQL_YYABORT;
           }
-        | VALUES '(' simple_ident_nospvar ')'
+        | VALUE_SYM '(' simple_ident_nospvar ')'
           {
             $$= new (thd->mem_root) Item_insert_value(thd, Lex->current_context(),
                                                         $3);
@@ -11254,9 +11291,9 @@ select_derived_union:
             }
           }
           union_list_derived_part2
-        | derived_query_specification opt_select_lock_type
-        | derived_query_specification order_or_limit opt_select_lock_type
-        | derived_query_specification opt_select_lock_type union_list_derived
+        | derived_simple_table opt_select_lock_type
+        | derived_simple_table order_or_limit opt_select_lock_type
+        | derived_simple_table opt_select_lock_type union_list_derived
        ;
 
 union_list_derived_part2:
@@ -11311,6 +11348,10 @@ select_derived:
           }
         ;
 
+derived_simple_table:
+          derived_query_specification     { $$= $1; }
+        | derived_table_value_constructor { $$= $1; }
+        ;
 /*
   Similar to query_specification, but for derived tables.
   Example: the inner parenthesized SELECT in this query:
@@ -11325,6 +11366,41 @@ derived_query_specification:
           }
         ;
 
+derived_table_value_constructor:
+          VALUES
+          {
+	    LEX *lex=Lex;
+            lex->field_list.empty();
+            lex->many_values.empty();
+            lex->insert_list=0;
+	  }
+          values_list
+          {
+            LEX *lex= Lex;
+            lex->derived_tables|= DERIVED_SUBQUERY;
+            if (!lex->expr_allows_subselect ||
+                lex->sql_command == (int)SQLCOM_PURGE)
+            {
+              thd->parse_error();
+              MYSQL_YYABORT;
+            }
+            if (lex->current_select->linkage == GLOBAL_OPTIONS_TYPE ||
+                mysql_new_select(lex, 1, NULL))
+              MYSQL_YYABORT;
+            mysql_init_select(lex);
+            lex->current_select->linkage= DERIVED_TABLE_TYPE;
+
+            if (!(lex->current_select->tvc=
+	          new (lex->thd->mem_root) table_value_constr(lex->many_values,
+							      lex->current_select,
+							      lex->current_select->options)))
+	      MYSQL_YYABORT;
+	    lex->many_values.empty();
+            $$= NULL;
+          }
+        ;
+
+
 select_derived2:
           {
             LEX *lex= Lex;
@@ -12630,7 +12706,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 */
@@ -16242,6 +16325,31 @@ union_option:
         | ALL       { $$=0; }
         ;
 
+simple_table:
+          query_specification      { $$= $1; }
+        | table_value_constructor  { $$= $1; }
+        ;
+        
+table_value_constructor:
+	  VALUES
+	  {
+	    LEX *lex=Lex;
+            lex->field_list.empty();
+            lex->many_values.empty();
+            lex->insert_list=0;
+	  }
+	  values_list
+	  { 
+	    LEX *lex=Lex;
+	    $$= lex->current_select;
+	    mysql_init_select(Lex);
+	    if (!($$->tvc=
+	          new (lex->thd->mem_root) table_value_constr(lex->many_values, $$, $$->options)))
+	      MYSQL_YYABORT;
+	    lex->many_values.empty();
+	  }
+	;
+	
 /*
   Corresponds to the SQL Standard
   <query specification> ::=
@@ -16259,12 +16367,12 @@ query_specification:
         ;
 
 query_term_union_not_ready:
-          query_specification order_or_limit opt_select_lock_type { $$= $1; }
+          simple_table order_or_limit opt_select_lock_type { $$= $1; }
         | '(' select_paren_derived ')' union_order_or_limit       { $$= $2; }
         ;
 
 query_term_union_ready:
-          query_specification opt_select_lock_type                { $$= $1; }
+          simple_table opt_select_lock_type                { $$= $1; }
         | '(' select_paren_derived ')'                            { $$= $2; }
         ;
 
@@ -16475,6 +16583,9 @@ view_select:
 */
 query_expression_body_view:
           SELECT_SYM select_options_and_item_list select_init3_view
+        | table_value_constructor
+        | table_value_constructor union_order_or_limit
+        | table_value_constructor union_list_view
         | '(' select_paren_view ')'
         | '(' select_paren_view ')' union_order_or_limit
         | '(' select_paren_view ')' union_list_view
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index e5f9be5c769..c62a1beb5a5 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -5790,4 +5790,11 @@ static Sys_var_mybool Sys_session_track_state_change(
        ON_CHECK(0),
        ON_UPDATE(update_session_track_state_change));
 
+static Sys_var_ulong Sys_in_subquery_conversion_threshold(
+       "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(10000), BLOCK_SIZE(1));
+
 #endif //EMBEDDED_LIBRARY