Summarized results of two previous commits (26 July, 25 August)

21 files changed, 3370 insertions, 803 deletions

diff --git a/mysql-test/r/opt_tvc.result b/mysql-test/r/opt_tvc.result
index f3a5f182e61..a3c71faff46 100644
--- a/mysql-test/r/opt_tvc.result
+++ b/mysql-test/r/opt_tvc.result
@@ -1,10 +1,10 @@
 create table t1 (a int, b int);
-insert into t1 
+insert into t1
 values (1,2), (4,6), (9,7),
 (1,1), (2,5), (7,8);
 create table t2 (a int, b int, c int);
-insert into t2 
-values (1,2,3), (5,1,2), (4,3,7), 
+insert into t2
+values (1,2,3), (5,1,2), (4,3,7),
 (8,9,0), (10,7,1), (5,5,1);
 # optimization is not used
 select * from t1 where a in (1,2);
@@ -37,484 +37,436 @@ a	b
 1	2
 1	1
 2	5
-select * from t1 
-where a in 
+select * from t1
+where a in
 (
-select * 
-from (values (1),(2)) as new_tvc
+select *
+from (values (1),(2)) as tvc_0
 );
 a	b
 1	2
 1	1
 2	5
-explain select * from t1 where a in (1,2);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-explain select * from t1 
-where a in 
-(
-select * 
-from (values (1),(2)) as new_tvc
+explain extended select * from t1 where a in (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
+explain extended select * from t1
+where a in
+(
+select *
+from (values (1),(2)) as tvc_0
 );
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-explain format=json select * from t1 where a in (1,2);
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "table": {
-      "table_name": "<subquery2>",
-      "access_type": "ALL",
-      "possible_keys": ["distinct_key"],
-      "rows": 2,
-      "filtered": 100,
-      "materialized": {
-        "unique": 1,
-        "query_block": {
-          "select_id": 2,
-          "table": {
-            "table_name": "<derived3>",
-            "access_type": "ALL",
-            "rows": 2,
-            "filtered": 100,
-            "materialized": {
-              "query_block": {
-                "union_result": {
-                  "table_name": "<unit3>",
-                  "access_type": "ALL",
-                  "query_specifications": [
-                    {
-                      "query_block": {
-                        "select_id": 3,
-                        "table": {
-                          "message": "No tables used"
-                        }
-                      }
-                    }
-                  ]
-                }
-              }
-            }
-          }
-        }
-      }
-    },
-    "block-nl-join": {
-      "table": {
-        "table_name": "t1",
-        "access_type": "ALL",
-        "rows": 6,
-        "filtered": 100
-      },
-      "buffer_type": "flat",
-      "buffer_size": "256Kb",
-      "join_type": "BNL",
-      "attached_condition": "t1.a = new_tvc.`1`"
-    }
-  }
-}
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery2>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where `test`.`t1`.`a` = `tvc_0`.`1`
 # AND-condition with IN-predicates in WHERE-part
-select * from t1 
-where a in (1,2) and 
+select * from t1
+where a in (1,2) and
 b in (1,5);
 a	b
 1	1
 2	5
-select * from t1 
-where a in 
+select * from t1
+where a in
 (
-select * 
-from (values (1),(2)) as new_tvc
-) 
+select *
+from (values (1),(2)) as tvc_0
+)
 and b in
 (
-select * 
-from (values (1),(5)) as new_tvc
+select *
+from (values (1),(5)) as tvc_1
 );
 a	b
 1	1
 2	5
-explain select * from t1 
-where a in (1,2) and 
+explain extended select * from t1
+where a in (1,2) and
 b in (1,5);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-explain select * from t1 
-where a in 
-(
-select * 
-from (values (1),(2)) as new_tvc
-) 
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	Using where
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `tvc_0`.`1` = `test`.`t1`.`a` and `test`.`t1`.`b` = `tvc_1`.`1`
+explain extended select * from t1
+where a in
+(
+select *
+from (values (1),(2)) as tvc_0
+)
 and b in
 (
-select * 
-from (values (1),(5)) as new_tvc
+select *
+from (values (1),(5)) as tvc_1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	2	100.00	
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) semi join ((values (1),(5)) `tvc_1`) where `test`.`t1`.`b` = `tvc_1`.`1`
+# subquery with IN-predicate
+select * from t1
+where a in
+(
+select a
+from t2 where b in (3,4)
+);
+a	b
+4	6
+select * from t1
+where a in
+(
+select a from t2
+where b in
+(
+select *
+from (values (3),(4)) as tvc_0
+)
 );
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	<subquery4>	ALL	distinct_key	NULL	NULL	NULL	2	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-explain format=json select * from t1 
-where a in (1,2) and 
-b in (1,5);
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "table": {
-      "table_name": "<subquery4>",
-      "access_type": "ALL",
-      "possible_keys": ["distinct_key"],
-      "rows": 2,
-      "filtered": 100,
-      "materialized": {
-        "unique": 1,
-        "query_block": {
-          "select_id": 4,
-          "table": {
-            "table_name": "<derived5>",
-            "access_type": "ALL",
-            "rows": 2,
-            "filtered": 100,
-            "materialized": {
-              "query_block": {
-                "union_result": {
-                  "table_name": "<unit5>",
-                  "access_type": "ALL",
-                  "query_specifications": [
-                    {
-                      "query_block": {
-                        "select_id": 5,
-                        "table": {
-                          "message": "No tables used"
-                        }
-                      }
-                    }
-                  ]
-                }
-              }
-            }
-          }
-        }
-      }
-    },
-    "block-nl-join": {
-      "table": {
-        "table_name": "t1",
-        "access_type": "ALL",
-        "rows": 6,
-        "filtered": 100
-      },
-      "buffer_type": "flat",
-      "buffer_size": "256Kb",
-      "join_type": "BNL",
-      "attached_condition": "t1.b = new_tvc.`1`"
-    },
-    "table": {
-      "table_name": "<subquery2>",
-      "access_type": "eq_ref",
-      "possible_keys": ["distinct_key"],
-      "key": "distinct_key",
-      "key_length": "4",
-      "used_key_parts": ["1"],
-      "ref": ["func"],
-      "rows": 1,
-      "filtered": 100,
-      "materialized": {
-        "unique": 1,
-        "query_block": {
-          "select_id": 2,
-          "table": {
-            "table_name": "<derived3>",
-            "access_type": "ALL",
-            "rows": 2,
-            "filtered": 100,
-            "materialized": {
-              "query_block": {
-                "union_result": {
-                  "table_name": "<unit3>",
-                  "access_type": "ALL",
-                  "query_specifications": [
-                    {
-                      "query_block": {
-                        "select_id": 3,
-                        "table": {
-                          "message": "No tables used"
-                        }
-                      }
-                    }
-                  ]
-                }
-              }
-            }
-          }
-        }
-      }
-    }
-  }
-}
-# OR-condition with IN-predicates in WHERE-part
-select * from t1 
-where a in (1,2) or 
-b in (4,5);
+a	b
+4	6
+explain extended select * from t1
+where a in
+(
+select a
+from t2 where b in (3,4)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3`
+explain extended select * from t1
+where a in
+(
+select a from t2
+where b in
+(
+select *
+from (values (3),(4)) as tvc_0
+)
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where; Using join buffer (flat, BNL join)
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(4)) `tvc_0` join `test`.`t2`) where `test`.`t2`.`b` = `tvc_0`.`3`
+# derived table with IN-predicate
+select * from
+(
+select *
+from t1
+where a in (1,2)
+) as dr_table;
 a	b
 1	2
 1	1
 2	5
-select * from t1 
-where a in 
+select * from
 (
-select * 
-from (values (1),(2)) as new_tvc
-) 
-or b in
+select *
+from t1
+where a in
 (
-select * 
-from (values (4),(5)) as new_tvc
-);
+select *
+from (values (1),(2))
+as tvc_0
+)
+) as dr_table;
 a	b
 1	2
 1	1
 2	5
-explain select * from t1 
-where a in (1,2) or 
-b in (4,5);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where
-4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	
-5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-explain select * from t1 
-where a in 
-(
-select * 
-from (values (1),(2)) as new_tvc
-) 
-or b in
-(
-select * 
-from (values (4),(5)) as new_tvc
-);
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	Using where
-4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	
-5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-2	MATERIALIZED	<derived3>	ALL	NULL	NULL	NULL	NULL	2	
-3	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-explain format=json select * from t1 
-where a in (1,2) or 
-b in (4,5);
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "table": {
-      "table_name": "t1",
-      "access_type": "ALL",
-      "rows": 6,
-      "filtered": 100,
-      "attached_condition": "<in_optimizer>(t1.a,t1.a in (subquery#2)) or <in_optimizer>(t1.b,t1.b in (subquery#4))"
-    },
-    "subqueries": [
-      {
-        "query_block": {
-          "select_id": 4,
-          "table": {
-            "table_name": "<derived5>",
-            "access_type": "ALL",
-            "rows": 2,
-            "filtered": 100,
-            "materialized": {
-              "query_block": {
-                "union_result": {
-                  "table_name": "<unit5>",
-                  "access_type": "ALL",
-                  "query_specifications": [
-                    {
-                      "query_block": {
-                        "select_id": 5,
-                        "table": {
-                          "message": "No tables used"
-                        }
-                      }
-                    }
-                  ]
-                }
-              }
-            }
-          }
-        }
-      },
-      {
-        "query_block": {
-          "select_id": 2,
-          "table": {
-            "table_name": "<derived3>",
-            "access_type": "ALL",
-            "rows": 2,
-            "filtered": 100,
-            "materialized": {
-              "query_block": {
-                "union_result": {
-                  "table_name": "<unit3>",
-                  "access_type": "ALL",
-                  "query_specifications": [
-                    {
-                      "query_block": {
-                        "select_id": 3,
-                        "table": {
-                          "message": "No tables used"
-                        }
-                      }
-                    }
-                  ]
-                }
-              }
-            }
-          }
-        }
-      }
-    ]
-  }
-}
-# subquery with IN-predicate
-select * from t1 
-where a in 
+explain extended select * from
 (
-select a 
-from t2 where b in (3,4)
+select *
+from t1
+where a in (1,2)
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`))))
+explain extended select * from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
 )
-;
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`))))
+# non-recursive CTE with IN-predicate
+with tvc_0 as
+(
+select *
+from t1
+where a in (1,2)
+)
+select * from tvc_0;
 a	b
-4	6
-select * from t1 
-where a in (
-select a from t2 
-where b in 
+1	2
+1	1
+2	5
+select * from
+(
+select *
+from t1
+where a in
 (
-select * 
-from (values (3),(4)) as new_tvc)
+select *
+from (values (1),(2))
+as tvc_0
 )
-;
+) as dr_table;
 a	b
-4	6
-explain select * from t1 
-where a in 
+1	2
+1	1
+2	5
+explain extended with tvc_0 as
 (
-select a 
-from t2 where b in (3,4)
+select *
+from t1
+where a in (1,2)
 )
-;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
-4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-explain select * from t1 
-where a in (
-select a from t2 
-where b in 
-(
-select * 
-from (values (3),(4)) as new_tvc)
+select * from tvc_0;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	with tvc_0 as (/* select#2 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` in (1,2))/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`))))
+explain extended select * from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
 )
-;
-id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	
-2	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	
-2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	6	Using where; Using join buffer (flat, BNL join)
-4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
-explain format=json select * from t1 
-where a in 
-(
-select a 
-from t2 where b in (3,4)
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`))))
+# VIEW with IN-predicate
+create view v1 as
+select *
+from t1
+where a in (1,2);
+create view v2 as
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
 )
 ;
-EXPLAIN
-{
-  "query_block": {
-    "select_id": 1,
-    "table": {
-      "table_name": "t1",
-      "access_type": "ALL",
-      "rows": 6,
-      "filtered": 100
-    },
-    "table": {
-      "table_name": "<subquery2>",
-      "access_type": "eq_ref",
-      "possible_keys": ["distinct_key"],
-      "key": "distinct_key",
-      "key_length": "4",
-      "used_key_parts": ["a"],
-      "ref": ["func"],
-      "rows": 1,
-      "filtered": 100,
-      "materialized": {
-        "unique": 1,
-        "query_block": {
-          "select_id": 2,
-          "table": {
-            "table_name": "<derived4>",
-            "access_type": "ALL",
-            "rows": 2,
-            "filtered": 100,
-            "materialized": {
-              "query_block": {
-                "union_result": {
-                  "table_name": "<unit4>",
-                  "access_type": "ALL",
-                  "query_specifications": [
-                    {
-                      "query_block": {
-                        "select_id": 4,
-                        "table": {
-                          "message": "No tables used"
-                        }
-                      }
-                    }
-                  ]
-                }
-              }
-            }
-          },
-          "block-nl-join": {
-            "table": {
-              "table_name": "t2",
-              "access_type": "ALL",
-              "rows": 6,
-              "filtered": 100
-            },
-            "buffer_type": "flat",
-            "buffer_size": "256Kb",
-            "join_type": "BNL",
-            "attached_condition": "t2.b = new_tvc.`3`"
-          }
-        }
-      }
-    }
-  }
-}
-drop table t1;
+select * from v1;
+a	b
+1	2
+1	1
+2	5
+select * from v2;
+a	b
+1	2
+1	1
+2	5
+explain extended select * from v1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`))))
+explain extended select * from v2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#3 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery3>`.`1`))))
+drop view v1,v2;
+# subselect defined by derived table with IN-predicate
+select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in (1,2)
+)
+as dr_table
+);
+a	b
+1	2
+1	1
+select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+)
+as dr_table
+);
+a	b
+1	2
+1	1
+explain extended select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in (1,2)
+)
+as dr_table
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t1`) where `test`.`t1`.`a` = 1 and <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#4 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery4>`.`1`))))
+explain extended select * from t1
+where a in
+(
+select 1
+from
+(
+select *
+from t1
+where a in
+(
+select *
+from (values (1),(2))
+as tvc_0
+)
+)
+as dr_table
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+2	MATERIALIZED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+5	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join (`test`.`t1`) where `test`.`t1`.`a` = 1 and <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in ( <materialize> (/* select#4 */ select `tvc_0`.`1` from (values (1),(2)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery4>`.`1`))))
+# derived table with IN-predicate and group by
+select * from
+(
+select max(a),b
+from t1
+where b in (3,5)
+group by b
+) as dr_table;
+max(a)	b
+2	5
+select * from
+(
+select max(a),b
+from t1
+where b in
+(
+select *
+from (values (3),(5))
+as tvc_0
+)
+group by b
+) as dr_table;
+max(a)	b
+2	5
+explain extended select * from
+(
+select max(a),b
+from t1
+where b in (3,5)
+group by b
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	12	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using temporary; Using filesort
+2	DERIVED	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	Using where
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where `tvc_0`.`3` = `test`.`t1`.`b` group by `test`.`t1`.`b`) `dr_table`
+explain extended select * from
+(
+select max(a),b
+from t1
+where b in
+(
+select *
+from (values (3),(5))
+as tvc_0
+)
+group by b
+) as dr_table;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	12	100.00	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using temporary; Using filesort
+2	DERIVED	<subquery3>	eq_ref	distinct_key	distinct_key	4	func	1	100.00	
+3	MATERIALIZED	<derived4>	ALL	NULL	NULL	NULL	NULL	2	100.00	
+4	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1003	/* select#1 */ select `dr_table`.`max(a)` AS `max(a)`,`dr_table`.`b` AS `b` from (/* select#2 */ select max(`test`.`t1`.`a`) AS `max(a)`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (3),(5)) `tvc_0`) where 1 group by `test`.`t1`.`b`) `dr_table`
+drop table t1, t2;
 set @@in_subquery_conversion_threshold= default;
diff --git a/mysql-test/r/table_value_constr.result b/mysql-test/r/table_value_constr.result
index dac84fc5dcb..411edc53168 100644
--- a/mysql-test/r/table_value_constr.result
+++ b/mysql-test/r/table_value_constr.result
@@ -12,289 +12,591 @@ values (1,2), (3,4), (5.6,0);
 values ("abc", "def");
 abc	def
 abc	def
-# UNION using VALUES structure(s)
-select 1,2 union values (1,2);
+# UNION that uses VALUES structure(s)
+select 1,2
+union
+values (1,2);
 1	2
 1	2
-values (1,2) union select 1,2;
+values (1,2)
+union
+select 1,2;
 1	2
 1	2
-select 1,2 union values (1,2),(3,4),(5,6),(7,8);
+select 1,2
+union
+values (1,2),(3,4),(5,6),(7,8);
 1	2
 1	2
 3	4
 5	6
 7	8
-select 3,7 union values (1,2),(3,4),(5,6);
+select 3,7
+union
+values (1,2),(3,4),(5,6);
 3	7
 3	7
 1	2
 3	4
 5	6
-select 3,7,4 union values (1,2,5),(4,5,6);
+select 3,7,4
+union
+values (1,2,5),(4,5,6);
 3	7	4
 3	7	4
 1	2	5
 4	5	6
-select 1,2 union values (1,7),(3,6.5);
+select 1,2
+union
+values (1,7),(3,6.5);
 1	2
 1	2.0
 1	7.0
 3	6.5
-select 1,2 union values (1,2.0),(3,6);
+select 1,2
+union
+values (1,2.0),(3,6);
 1	2
 1	2.0
 3	6.0
-select 1.8,2 union values (1,2),(3,6);
+select 1.8,2
+union
+values (1,2),(3,6);
 1.8	2
 1.8	2
 1.0	2
 3.0	6
-values (1,2.4),(3,6) union select 2.8,9;
+values (1,2.4),(3,6)
+union
+select 2.8,9;
 1	2.4
 1.0	2.4
 3.0	6.0
 2.8	9.0
-values (1,2),(3,4),(5,6),(7,8) union select 5,6;
+values (1,2),(3,4),(5,6),(7,8)
+union
+select 5,6;
 1	2
 1	2
 3	4
 5	6
 7	8
-select "ab","cdf" union values ("al","zl"),("we","q");
+select "ab","cdf"
+union
+values ("al","zl"),("we","q");
 ab	cdf
 ab	cdf
 al	zl
 we	q
-values ("ab", "cdf") union select "ab","cdf";
+values ("ab", "cdf")
+union
+select "ab","cdf";
 ab	cdf
 ab	cdf
-values (1,2) union values (1,2),(5,6);
+values (1,2)
+union
+values (1,2),(5,6);
 1	2
 1	2
 5	6
-values (1,2) union values (3,4),(5,6);
+values (1,2)
+union
+values (3,4),(5,6);
 1	2
 1	2
 3	4
 5	6
-values (1,2) union values (1,2) union values (4,5);
+values (1,2)
+union
+values (1,2)
+union values (4,5);
 1	2
 1	2
 4	5
-# UNION ALL using VALUES structure
-values (1,2),(3,4) union all select 5,6;
+# UNION ALL that uses VALUES structure
+values (1,2),(3,4)
+union all
+select 5,6;
 1	2
 1	2
 3	4
 5	6
-values (1,2),(3,4) union all select 1,2;
+values (1,2),(3,4)
+union all
+select 1,2;
 1	2
 1	2
 3	4
 1	2
-select 5,6 union all values (1,2),(3,4);
+select 5,6
+union all
+values (1,2),(3,4);
 5	6
 5	6
 1	2
 3	4
-select 1,2 union all values (1,2),(3,4);
+select 1,2
+union all
+values (1,2),(3,4);
 1	2
 1	2
 1	2
 3	4
-values (1,2) union all values (1,2),(5,6);
+values (1,2)
+union all
+values (1,2),(5,6);
 1	2
 1	2
 1	2
 5	6
-values (1,2) union all values (3,4),(5,6);
+values (1,2)
+union all
+values (3,4),(5,6);
 1	2
 1	2
 3	4
 5	6
-values (1,2) union all values (1,2) union all values (4,5);
+values (1,2)
+union all
+values (1,2)
+union all
+values (4,5);
 1	2
 1	2
 1	2
 4	5
-values (1,2) union all values (1,2) union values (1,2);
+values (1,2)
+union all
+values (1,2)
+union values (1,2);
 1	2
 1	2
-values (1,2) union values (1,2) union all values (1,2);
+values (1,2)
+union
+values (1,2)
+union all
+values (1,2);
 1	2
 1	2
 1	2
-# EXCEPT using VALUES structure(s)
-select 1,2 except values (3,4),(5,6);
+# EXCEPT that uses VALUES structure(s)
+select 1,2
+except
+values (3,4),(5,6);
 1	2
 1	2
-select 1,2 except values (1,2),(3,4);
+select 1,2
+except
+values (1,2),(3,4);
 1	2
-values (1,2),(3,4) except select 5,6;
+values (1,2),(3,4)
+except
+select 5,6;
 1	2
 1	2
 3	4
-values (1,2),(3,4) except select 1,2;
+values (1,2),(3,4)
+except
+select 1,2;
 1	2
 3	4
-values (1,2),(3,4) except values (5,6);
+values (1,2),(3,4)
+except
+values (5,6);
 1	2
 1	2
 3	4
-values (1,2),(3,4) except values (1,2);
+values (1,2),(3,4)
+except
+values (1,2);
 1	2
 3	4
-# INTERSECT using VALUES structure(s)
-select 1,2 intersect values (3,4),(5,6);
+# INTERSECT that uses VALUES structure(s)
+select 1,2
+intersect
+values (3,4),(5,6);
 1	2
-select 1,2 intersect values (1,2),(3,4);
+select 1,2
+intersect
+values (1,2),(3,4);
 1	2
 1	2
-values (1,2),(3,4) intersect select 5,6;
+values (1,2),(3,4)
+intersect
+select 5,6;
 1	2
-values (1,2),(3,4) intersect select 1,2;
+values (1,2),(3,4)
+intersect
+select 1,2;
 1	2
 1	2
-values (1,2),(3,4) intersect values (5,6);
+values (1,2),(3,4)
+intersect
+values (5,6);
 1	2
-values (1,2),(3,4) intersect values (1,2);
+values (1,2),(3,4)
+intersect
+values (1,2);
 1	2
 1	2
-# combination of different structures using VALUES structures : UNION + EXCEPT
-values (1,2),(3,4) except select 1,2 union values (1,2);
+# combination of different structures that uses VALUES structures : UNION + EXCEPT
+values (1,2),(3,4)
+except
+select 1,2
+union values (1,2);
 1	2
 1	2
 3	4
-values (1,2),(3,4) except values (1,2) union values (1,2);
+values (1,2),(3,4)
+except
+values (1,2)
+union
+values (1,2);
 1	2
 1	2
 3	4
-values (1,2),(3,4) except values (1,2) union values (3,4);
+values (1,2),(3,4)
+except
+values (1,2)
+union
+values (3,4);
 1	2
 3	4
-values (1,2),(3,4) union values (1,2) except values (1,2);
+values (1,2),(3,4)
+union
+values (1,2)
+except
+values (1,2);
 1	2
 3	4
-# combination of different structures using VALUES structures : UNION ALL + EXCEPT
-values (1,2),(3,4) except select 1,2 union all values (1,2);
+# combination of different structures that uses VALUES structures : UNION ALL + EXCEPT
+values (1,2),(3,4)
+except
+select 1,2
+union all
+values (1,2);
 1	2
 1	2
 3	4
-values (1,2),(3,4) except values (1,2) union all values (1,2);
+values (1,2),(3,4)
+except
+values (1,2)
+union all
+values (1,2);
 1	2
 1	2
 3	4
-values (1,2),(3,4) except values (1,2) union all values (3,4);
+values (1,2),(3,4)
+except
+values (1,2)
+union all
+values (3,4);
 1	2
 3	4
 3	4
-values (1,2),(3,4) union all values (1,2) except values (1,2);
+values (1,2),(3,4)
+union all
+values (1,2)
+except
+values (1,2);
 1	2
 3	4
-# combination of different structures using VALUES structures : UNION + INTERSECT
-values (1,2),(3,4) intersect select 1,2 union values (1,2);
+# combination of different structures that uses VALUES structures : UNION + INTERSECT
+values (1,2),(3,4)
+intersect
+select 1,2
+union
+values (1,2);
 1	2
 1	2
-values (1,2),(3,4) intersect values (1,2) union values (1,2);
+values (1,2),(3,4)
+intersect
+values (1,2)
+union
+values (1,2);
 1	2
 1	2
-values (1,2),(3,4) intersect values (1,2) union values (3,4);
+values (1,2),(3,4)
+intersect
+values (1,2)
+union
+values (3,4);
 1	2
 1	2
 3	4
-values (1,2),(3,4) union values (1,2) intersect values (1,2);
+values (1,2),(3,4)
+union
+values (1,2)
+intersect
+values (1,2);
 1	2
 1	2
 3	4
-# combination of different structures using VALUES structures : UNION ALL + INTERSECT
-values (1,2),(3,4) intersect select 1,2 union all values (1,2);
+# combination of different structures that uses VALUES structures : UNION ALL + INTERSECT
+values (1,2),(3,4)
+intersect
+select 1,2
+union all
+values (1,2);
 1	2
 1	2
 1	2
-values (1,2),(3,4) intersect values (1,2) union all values (1,2);
+values (1,2),(3,4)
+intersect
+values (1,2)
+union all
+values (1,2);
 1	2
 1	2
 1	2
-values (1,2),(3,4) intersect values (1,2) union all values (3,4);
+values (1,2),(3,4)
+intersect
+values (1,2)
+union all
+values (3,4);
 1	2
 1	2
 3	4
-values (1,2),(3,4) union all values (1,2) intersect values (1,2);
+values (1,2),(3,4)
+union all
+values (1,2)
+intersect
+values (1,2);
 1	2
 1	2
 3	4
 1	2
-# combination of different structures using VALUES structures : UNION + UNION ALL
-values (1,2),(3,4) union all select 1,2 union values (1,2);
+# combination of different structures that uses VALUES structures : UNION + UNION ALL
+values (1,2),(3,4)
+union all
+select 1,2
+union
+values (1,2);
 1	2
 1	2
 3	4
-values (1,2),(3,4) union all values (1,2) union values (1,2);
+values (1,2),(3,4)
+union all
+values (1,2)
+union
+values (1,2);
 1	2
 1	2
 3	4
-values (1,2),(3,4) union all values (1,2) union values (3,4);
+values (1,2),(3,4)
+union all
+values (1,2)
+union
+values (3,4);
 1	2
 1	2
 3	4
-values (1,2),(3,4) union values (1,2) union all values (1,2);
+values (1,2),(3,4)
+union
+values (1,2)
+union all
+values (1,2);
 1	2
 1	2
 3	4
 1	2
-values (1,2) union values (1,2) union all values (1,2);
+values (1,2)
+union
+values (1,2)
+union all
+values (1,2);
 1	2
 1	2
 1	2
-# CTE using VALUES structure(s)
-with t2 as (values (1,2),(3,4)) select * from t2;
+# CTE that uses VALUES structure(s) : non-recursive CTE
+with t2 as
+(
+values (1,2),(3,4)
+)
+select * from t2;
 1	2
 1	2
 3	4
-with t2 as (select 1,2 union values (1,2)) select * from t2;
+with t2 as
+(
+select 1,2
+union
+values (1,2)
+)
+select * from t2;
 1	2
 1	2
-with t2 as (select 1,2 union values (1,2),(3,4)) select * from t2;
+with t2 as
+(
+select 1,2
+union
+values (1,2),(3,4)
+)
+select * from t2;
 1	2
 1	2
 3	4
-with t2 as (values (1,2) union select 1,2) select * from t2;
+with t2 as
+(
+values (1,2)
+union
+select 1,2
+)
+select * from t2;
 1	2
 1	2
-with t2 as (values (1,2),(3,4) union select 1,2) select * from t2;
+with t2 as
+(
+values (1,2),(3,4)
+union
+select 1,2
+)
+select * from t2;
 1	2
 1	2
 3	4
-with t2 as (values (5,6) union values (1,2),(3,4)) select * from t2;
+with t2 as
+(
+values (5,6)
+union
+values (1,2),(3,4)
+)
+select * from t2;
 5	6
 5	6
 1	2
 3	4
-with t2 as (values (1,2) union values (1,2),(3,4)) select * from t2;
+with t2 as
+(
+values (1,2)
+union
+values (1,2),(3,4)
+)
+select * from t2;
 1	2
 1	2
 3	4
-with t2 as (select 1,2 union all values (1,2),(3,4)) select * from t2;
+with t2 as
+(
+select 1,2
+union all
+values (1,2),(3,4)
+)
+select * from t2;
 1	2
 1	2
 1	2
 3	4
-with t2 as (values (1,2),(3,4) union all select 1,2) select * from t2;
+with t2 as
+(
+values (1,2),(3,4)
+union all
+select 1,2
+)
+select * from t2;
 1	2
 1	2
 3	4
 1	2
-with t2 as (values (1,2) union all values (1,2),(3,4)) select * from t2;
+with t2 as
+(
+values (1,2)
+union all
+values (1,2),(3,4)
+)
+select * from t2;
 1	2
 1	2
 1	2
 3	4
-# Derived table using VALUES structure(s)
+# recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor
+with recursive t2(a,b) as
+(
+values(1,1)
+union
+select t1.a, t1.b
+from t1,t2
+where t1.a=t2.a
+)
+select * from t2;
+a	b
+1	1
+1	2
+with recursive t2(a,b) as
+(
+values(1,1)
+union
+select t1.a+1, t1.b
+from t1,t2
+where t1.a=t2.a
+)
+select * from t2;
+a	b
+1	1
+2	2
+2	1
+3	5
+# recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors
+with recursive t2(a,b) as
+(
+values(1,1)
+union
+values (3,4)
+union
+select t2.a+1, t1.b
+from t1,t2
+where t1.a=t2.a
+)
+select * from t2;
+a	b
+1	1
+3	4
+2	2
+2	1
+3	5
+# recursive CTE that uses VALUES structure(s) : that uses UNION ALL
+with recursive t2(a,b,st) as
+(
+values(1,1,1)
+union all
+select t2.a, t1.b, t2.st+1
+from t1,t2
+where t1.a=t2.a and st<3
+)
+select * from t2;
+a	b	st
+1	1	1
+1	2	2
+1	1	2
+1	2	3
+1	2	3
+1	1	3
+1	1	3
+# recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements)
+with recursive fact(n,f) as
+(
+values(1,1)
+union
+select n+1,f*n from fact where n < 10
+)
+select * from fact;
+n	f
+1	1
+2	1
+3	2
+4	6
+5	24
+6	120
+7	720
+8	5040
+9	40320
+10	362880
+# Derived table that uses VALUES structure(s) : singe VALUES structure
 select * from (values (1,2),(3,4)) as t2;
 1	2
 1	2
 3	4
+# Derived table that uses VALUES structure(s) : UNION with VALUES structure(s)
 select * from (select 1,2 union values (1,2)) as t2;
 1	2
 1	2
@@ -318,6 +620,7 @@ select * from (values (1,2) union values (1,2),(3,4)) as t2;
 1	2
 1	2
 3	4
+# Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
 select * from (select 1,2 union all values (1,2),(3,4)) as t2;
 1	2
 1	2
@@ -333,67 +636,997 @@ select * from (values (1,2) union all values (1,2),(3,4)) as t2;
 1	2
 1	2
 3	4
-# CREATE VIEW using VALUES structure(s)
+# CREATE VIEW that uses VALUES structure(s) : singe VALUES structure
 create view v1 as values (1,2),(3,4);
 select * from v1;
 1	2
 1	2
 3	4
 drop view v1;
-create view v1 as select 1,2 union values (1,2);
+# CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s)
+create view v1 as
+select 1,2
+union
+values (1,2);
 select * from v1;
 1	2
 1	2
 drop view v1;
-create view v1 as select 1,2 union values (1,2),(3,4);
+create view v1 as
+select 1,2
+union
+values (1,2),(3,4);
 select * from v1;
 1	2
 1	2
 3	4
 drop view v1;
-create view v1 as values (1,2) union select 1,2;
+create view v1 as
+values (1,2)
+union
+select 1,2;
 select * from v1;
 1	2
 1	2
 drop view v1;
-create view v1 as values (1,2),(3,4) union select 1,2;
+create view v1 as
+values (1,2),(3,4)
+union
+select 1,2;
 select * from v1;
 1	2
 1	2
 3	4
 drop view v1;
-create view v1 as values (5,6) union values (1,2),(3,4);
+create view v1 as
+values (5,6)
+union
+values (1,2),(3,4);
 select * from v1;
 5	6
 5	6
 1	2
 3	4
 drop view v1;
-create view v1 as values (1,2) union values (1,2),(3,4);
+# CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
+create view v1 as
+values (1,2)
+union
+values (1,2),(3,4);
 select * from v1;
 1	2
 1	2
 3	4
 drop view v1;
-create view v1 as select 1,2 union all values (1,2),(3,4);
+create view v1 as
+select 1,2
+union all
+values (1,2),(3,4);
 select * from v1;
 1	2
 1	2
 1	2
 3	4
 drop view v1;
-create view v1 as values (1,2),(3,4) union all select 1,2;
+create view v1 as
+values (1,2),(3,4)
+union all
+select 1,2;
 select * from v1;
 1	2
 1	2
 3	4
 1	2
 drop view v1;
-create view v1 as values (1,2) union all values (1,2),(3,4);
+create view v1 as
+values (1,2)
+union all
+values (1,2),(3,4);
 select * from v1;
 1	2
 1	2
 1	2
 3	4
 drop view v1;
+# prepare statement that uses VALUES structure(s): single VALUES structure
+prepare stmt1 from "
+values (1,2);
+";
+execute stmt1;
+1	2
+1	2
+execute stmt1;
+1	2
+1	2
+deallocate prepare stmt1;
+# prepare statement that uses VALUES structure(s): UNION with VALUES structure(s)
+prepare stmt1 from "
+  select 1,2
+  union
+  values (1,2),(3,4);
+";
+execute stmt1;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from "
+  values (1,2),(3,4)
+  union
+  select 1,2;
+";
+execute stmt1;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from "
+  select 1,2
+  union
+  values (3,4)
+  union
+  values (1,2);
+";
+execute stmt1;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from "
+  values (5,6)
+  union
+  values (1,2),(3,4);
+";
+execute stmt1;
+5	6
+5	6
+1	2
+3	4
+execute stmt1;
+5	6
+5	6
+1	2
+3	4
+deallocate prepare stmt1;
+# prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+prepare stmt1 from "
+  select 1,2
+  union
+  values (1,2),(3,4);
+";
+execute stmt1;
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+prepare stmt1 from "
+  values (1,2),(3,4)
+  union all
+  select 1,2;
+";
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+deallocate prepare stmt1;
+prepare stmt1 from "
+  select 1,2
+  union all
+  values (3,4)
+  union all
+  values (1,2);
+";
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+execute stmt1;
+1	2
+1	2
+3	4
+1	2
+deallocate prepare stmt1;
+prepare stmt1 from "
+  values (1,2)
+  union all
+  values (1,2),(3,4);
+";
+execute stmt1;
+1	2
+1	2
+1	2
+3	4
+execute stmt1;
+1	2
+1	2
+1	2
+3	4
+deallocate prepare stmt1;
+# explain query that uses VALUES structure(s): single VALUES structure
+explain
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain format=json
+values (1,2);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<unit1>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# explain query that uses VALUES structure(s): UNION with VALUES structure(s)
+explain
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain
+values (1,2),(3,4)
+union
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain
+values (5,6)
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json
+select 1,2
+union
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+values (1,2),(3,4)
+union
+select 1,2;
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+values (5,6)
+union
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain format=json
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+explain
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	
+explain
+values (1,2),(3,4)
+union all
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain
+values (1,2)
+union all
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain format=json
+values (1,2),(3,4)
+union all
+select 1,2;
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+select 1,2
+union
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain format=json
+values (1,2)
+union all
+values (1,2),(3,4);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+explain
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+explain format=json
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+EXPLAIN
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# analyze query that uses VALUES structure(s): single VALUES structure
+analyze
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	SIMPLE	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze format=json
+values (1,2);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<unit1>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# analyze query that uses VALUES structure(s): UNION with VALUES structure(s)
+analyze
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze
+values (1,2),(3,4)
+union
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze
+values (5,6)
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	3.00	NULL	NULL	
+analyze format=json
+select 1,2
+union
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+values (1,2),(3,4)
+union
+select 1,2;
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+values (5,6)
+union
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 3,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2,3>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze format=json
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+# analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+analyze
+select 1,2
+union
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+NULL	UNION RESULT	<union1,2>	ALL	NULL	NULL	NULL	NULL	NULL	2.00	NULL	NULL	
+analyze
+values (1,2),(3,4)
+union all
+select 1,2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze
+values (1,2)
+union all
+values (1,2),(3,4);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze format=json
+values (1,2),(3,4)
+union all
+select 1,2;
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+select 1,2
+union
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 1,
+      "r_rows": 2,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze format=json
+values (1,2)
+union all
+values (1,2),(3,4);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
+analyze
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	r_rows	filtered	r_filtered	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	UNION	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+analyze format=json
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+ANALYZE
+{
+  "query_block": {
+    "union_result": {
+      "table_name": "<union1,2,3>",
+      "access_type": "ALL",
+      "r_loops": 0,
+      "r_rows": null,
+      "query_specifications": [
+        {
+          "query_block": {
+            "select_id": 1,
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 2,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        },
+        {
+          "query_block": {
+            "select_id": 3,
+            "operation": "UNION",
+            "table": {
+              "message": "No tables used"
+            }
+          }
+        }
+      ]
+    }
+  }
+}
 drop table t1;
diff --git a/mysql-test/t/opt_tvc.test b/mysql-test/t/opt_tvc.test
index 1529672fc69..fe5110ece51 100644
--- a/mysql-test/t/opt_tvc.test
+++ b/mysql-test/t/opt_tvc.test
@@ -1,13 +1,13 @@
 create table t1 (a int, b int);
 
-insert into t1 
+insert into t1
 values (1,2), (4,6), (9,7),
        (1,1), (2,5), (7,8);
 
 create table t2 (a int, b int, c int);
 
-insert into t2 
-values (1,2,3), (5,1,2), (4,3,7), 
+insert into t2
+values (1,2,3), (5,1,2), (4,3,7),
        (8,9,0), (10,7,1), (5,5,1);
 
 --echo # optimization is not used
@@ -15,7 +15,7 @@ values (1,2,3), (5,1,2), (4,3,7),
 let $query= select * from t1 where a in (1,2);
 eval $query;
 eval explain $query;
-eval explain format=json $query; 
+eval explain format=json $query;
 
 --echo # set minimum number of values in VALUEs list when optimization works to 2
 
@@ -25,100 +25,212 @@ set @@in_subquery_conversion_threshold= 2;
 
 let $query= select * from t1 where a in (1,2);
 
-let $optimized_query= 
-select * from t1 
-where a in 
+let $optimized_query=
+select * from t1
+where a in
       (
-        select * 
-        from (values (1),(2)) as new_tvc
+        select *
+        from (values (1),(2)) as tvc_0
       );
 
 eval $query;
 eval $optimized_query;
-eval explain $query;
-eval explain $optimized_query;
-eval explain format=json $query; 
+eval explain extended $query;
+eval explain extended $optimized_query;
 
 --echo # AND-condition with IN-predicates in WHERE-part
 
-let $query= 
-select * from t1 
-where a in (1,2) and 
+let $query=
+select * from t1
+where a in (1,2) and
       b in (1,5);
 
-let $optimized_query= 
-select * from t1 
-where a in 
+let $optimized_query=
+select * from t1
+where a in
 (
-  select * 
-  from (values (1),(2)) as new_tvc
-) 
+  select *
+  from (values (1),(2)) as tvc_0
+)
 and b in
 (
-  select * 
-  from (values (1),(5)) as new_tvc
+  select *
+  from (values (1),(5)) as tvc_1
 );
-      
+
 eval $query;
 eval $optimized_query;
-eval explain $query;
-eval explain $optimized_query;
-eval explain format=json $query; 
-
---echo # OR-condition with IN-predicates in WHERE-part
+eval explain extended $query;
+eval explain extended $optimized_query;
 
-let $query= 
-select * from t1 
-where a in (1,2) or 
-      b in (4,5);
+--echo # subquery with IN-predicate
 
-let $optimized_query= 
-select * from t1 
-where a in 
+let $query=
+select * from t1
+where a in
 (
-  select * 
-  from (values (1),(2)) as new_tvc
-) 
-or b in
+  select a
+  from t2 where b in (3,4)
+);
+
+let $optimized_query=
+select * from t1
+where a in
 (
-  select * 
-  from (values (4),(5)) as new_tvc
+  select a from t2
+  where b in
+  (
+    select *
+    from (values (3),(4)) as tvc_0
+  )
 );
-      
+
 eval $query;
 eval $optimized_query;
-eval explain $query;
-eval explain $optimized_query;
-eval explain format=json $query; 
+eval explain extended $query;
+eval explain extended $optimized_query;
 
---echo # subquery with IN-predicate
+--echo # derived table with IN-predicate
+
+let $query=
+select * from
+(
+  select *
+  from t1
+  where a in (1,2)
+) as dr_table;
 
-let $query= 
-select * from t1 
-where a in 
+let $optimized_query=
+select * from
+(
+  select *
+  from t1
+  where a in
   (
-   select a 
-   from t2 where b in (3,4)
+    select *
+    from (values (1),(2))
+    as tvc_0
   )
-;
+) as dr_table;
 
-let $optimized_query= 
-select * from t1 
-where a in (
-  select a from t2 
-  where b in 
+eval $query;
+eval $optimized_query;
+eval explain extended $query;
+eval explain extended $optimized_query;
+
+--echo # non-recursive CTE with IN-predicate
+
+let $cte_query=
+with tvc_0 as
+(
+  select *
+  from t1
+  where a in (1,2)
+)
+select * from tvc_0;
+
+eval $cte_query;
+eval $optimized_query;
+eval explain extended $cte_query;
+eval explain extended $optimized_query;
+
+--echo # VIEW with IN-predicate
+
+create view v1 as
+  select *
+  from t1
+  where a in (1,2);
+
+create view v2 as
+  select *
+  from t1
+  where a in
   (
-    select * 
-    from (values (3),(4)) as new_tvc)
+    select *
+    from (values (1),(2))
+    as tvc_0
   )
 ;
-      
+
+let $query= select * from v1;
+let $optimized_query= select * from v2;
+
 eval $query;
 eval $optimized_query;
-eval explain $query;
-eval explain $optimized_query;
-eval explain format=json $query; 
+eval explain extended $query;
+eval explain extended $optimized_query;
 
-drop table t1;
-set @@in_subquery_conversion_threshold= default;
+drop view v1,v2;
+
+--echo # subselect defined by derived table with IN-predicate
+
+let $query=
+select * from t1
+where a in
+(
+  select 1
+  from
+  (
+    select *
+    from t1
+    where a in (1,2)
+  )
+  as dr_table
+);
+
+let $optimized_query=
+select * from t1
+where a in
+(
+  select 1
+  from
+  (
+    select *
+    from t1
+    where a in
+    (
+      select *
+      from (values (1),(2))
+      as tvc_0
+    )
+  )
+  as dr_table
+);
 
+eval $query;
+eval $optimized_query;
+eval explain extended $query;
+eval explain extended $optimized_query;
+
+--echo # derived table with IN-predicate and group by
+
+let $query=
+select * from
+(
+  select max(a),b
+  from t1
+  where b in (3,5)
+  group by b
+) as dr_table;
+
+let $optimized_query=
+select * from
+(
+  select max(a),b
+  from t1
+  where b in
+  (
+    select *
+    from (values (3),(5))
+    as tvc_0
+  )
+  group by b
+) as dr_table;
+
+eval $query;
+eval $optimized_query;
+eval explain extended $query;
+eval explain extended $optimized_query;
+
+drop table t1, t2;
+set @@in_subquery_conversion_threshold= default;
diff --git a/mysql-test/t/table_value_const.test b/mysql-test/t/table_value_const.test
deleted file mode 100644
index 00cd1baad95..00000000000
--- a/mysql-test/t/table_value_const.test
+++ /dev/null
@@ -1,36 +0,0 @@
-create table t1 (a int, b int);
-
-insert into t1 values (1,2),(4,6),(9,7),(1,1),(2,5),(7,8);
-
-values (1,2);
-
-select 1,2 union values (1,2);
-
-values (1,2) union select (1,2);
-
-values (1,2), (3,4) union select 1,2;
-
-select * from t1 where (t1.a,t1.b) in (select 5,7 union values (1,2),(2,3));
-
-select * from t1 where (t1.a,t1.b) in (values (1,2),(2,3) union select 5,7);
-
-let $drop_view= drop view v1;
-
-create view v1 as values (1,2);
-
-eval $drop_view;
-
-create view v1 as values (1,2) union select 3,4;
-
-eval $drop_view;
-
-create view v1 as select 1,2 union values (3,4);
-
-eval $drop_view;
-
-create view v1 as select 1,2 union values (3,4),(5,6);
-
-eval $drop_view;
-
-drop table t1;
-
diff --git a/mysql-test/t/table_value_constr.test b/mysql-test/t/table_value_constr.test
index 4aba843bbac..0021c27486c 100644
--- a/mysql-test/t/table_value_constr.test
+++ b/mysql-test/t/table_value_constr.test
@@ -1,6 +1,7 @@
 create table t1 (a int, b int);
 
-insert into t1 values (1,2),(4,6),(9,7),(1,1),(2,5),(7,8);
+insert into t1 values (1,2),(4,6),(9,7),
+		      (1,1),(2,5),(7,8);
 
 --echo # just VALUES
 
@@ -10,164 +11,443 @@ values (1,2), (3,4), (5.6,0);
 
 values ("abc", "def");
 
---echo # UNION using VALUES structure(s)
+--echo # UNION that uses VALUES structure(s)
 
-select 1,2 union values (1,2);
-
-values (1,2) union select 1,2;
-
-select 1,2 union values (1,2),(3,4),(5,6),(7,8);
-
-select 3,7 union values (1,2),(3,4),(5,6);
-
-select 3,7,4 union values (1,2,5),(4,5,6);
-
-select 1,2 union values (1,7),(3,6.5);
-
-select 1,2 union values (1,2.0),(3,6);
-
-select 1.8,2 union values (1,2),(3,6);
-
-values (1,2.4),(3,6) union select 2.8,9;
-
-values (1,2),(3,4),(5,6),(7,8) union select 5,6;
-
-select "ab","cdf" union values ("al","zl"),("we","q");
-
-values ("ab", "cdf") union select "ab","cdf";
+select 1,2
+union
+values (1,2);
 
-values (1,2) union values (1,2),(5,6);
+values (1,2)
+union
+select 1,2;
 
-values (1,2) union values (3,4),(5,6);
+select 1,2
+union
+values (1,2),(3,4),(5,6),(7,8);
 
-values (1,2) union values (1,2) union values (4,5);
+select 3,7
+union
+values (1,2),(3,4),(5,6);
 
---echo # UNION ALL using VALUES structure
+select 3,7,4
+union
+values (1,2,5),(4,5,6);
 
-values (1,2),(3,4) union all select 5,6;
+select 1,2
+union
+values (1,7),(3,6.5);
 
-values (1,2),(3,4) union all select 1,2;
+select 1,2
+union
+values (1,2.0),(3,6);
 
-select 5,6 union all values (1,2),(3,4);
+select 1.8,2
+union
+values (1,2),(3,6);
 
-select 1,2 union all values (1,2),(3,4);
+values (1,2.4),(3,6)
+union
+select 2.8,9;
 
-values (1,2) union all values (1,2),(5,6);
+values (1,2),(3,4),(5,6),(7,8)
+union
+select 5,6;
 
-values (1,2) union all values (3,4),(5,6);
+select "ab","cdf"
+union
+values ("al","zl"),("we","q");
 
-values (1,2) union all values (1,2) union all values (4,5);
+values ("ab", "cdf")
+union
+select "ab","cdf";
 
-values (1,2) union all values (1,2) union values (1,2);
+values (1,2)
+union
+values (1,2),(5,6);
 
-values (1,2) union values (1,2) union all values (1,2);
+values (1,2)
+union
+values (3,4),(5,6);
 
---echo # EXCEPT using VALUES structure(s)
+values (1,2)
+union
+values (1,2)
+union values (4,5);
 
-select 1,2 except values (3,4),(5,6);
+--echo # UNION ALL that uses VALUES structure
 
-select 1,2 except values (1,2),(3,4);
+values (1,2),(3,4)
+union all
+select 5,6;
 
-values (1,2),(3,4) except select 5,6;
+values (1,2),(3,4)
+union all
+select 1,2;
 
-values (1,2),(3,4) except select 1,2;
+select 5,6
+union all
+values (1,2),(3,4);
 
-values (1,2),(3,4) except values (5,6);
+select 1,2
+union all
+values (1,2),(3,4);
 
-values (1,2),(3,4) except values (1,2);
+values (1,2)
+union all
+values (1,2),(5,6);
 
---echo # INTERSECT using VALUES structure(s)
+values (1,2)
+union all
+values (3,4),(5,6);
 
-select 1,2 intersect values (3,4),(5,6);
+values (1,2)
+union all
+values (1,2)
+union all
+values (4,5);
 
-select 1,2 intersect values (1,2),(3,4);
+values (1,2)
+union all
+values (1,2)
+union values (1,2);
 
-values (1,2),(3,4) intersect select 5,6;
+values (1,2)
+union
+values (1,2)
+union all
+values (1,2);
 
-values (1,2),(3,4) intersect select 1,2;
+--echo # EXCEPT that uses VALUES structure(s)
 
-values (1,2),(3,4) intersect values (5,6);
+select 1,2
+except
+values (3,4),(5,6);
 
-values (1,2),(3,4) intersect values (1,2);
+select 1,2
+except
+values (1,2),(3,4);
 
---echo # combination of different structures using VALUES structures : UNION + EXCEPT
+values (1,2),(3,4)
+except
+select 5,6;
 
-values (1,2),(3,4) except select 1,2 union values (1,2);
+values (1,2),(3,4)
+except
+select 1,2;
 
-values (1,2),(3,4) except values (1,2) union values (1,2);
+values (1,2),(3,4)
+except
+values (5,6);
 
-values (1,2),(3,4) except values (1,2) union values (3,4);
+values (1,2),(3,4)
+except
+values (1,2);
 
-values (1,2),(3,4) union values (1,2) except values (1,2);
+--echo # INTERSECT that uses VALUES structure(s)
 
---echo # combination of different structures using VALUES structures : UNION ALL + EXCEPT
+select 1,2
+intersect
+values (3,4),(5,6);
 
-values (1,2),(3,4) except select 1,2 union all values (1,2);
+select 1,2
+intersect
+values (1,2),(3,4);
 
-values (1,2),(3,4) except values (1,2) union all values (1,2);
+values (1,2),(3,4)
+intersect
+select 5,6;
 
-values (1,2),(3,4) except values (1,2) union all values (3,4);
+values (1,2),(3,4)
+intersect
+select 1,2;
 
-values (1,2),(3,4) union all values (1,2) except values (1,2);
+values (1,2),(3,4)
+intersect
+values (5,6);
 
---echo # combination of different structures using VALUES structures : UNION + INTERSECT
+values (1,2),(3,4)
+intersect
+values (1,2);
 
-values (1,2),(3,4) intersect select 1,2 union values (1,2);
+--echo # combination of different structures that uses VALUES structures : UNION + EXCEPT
 
-values (1,2),(3,4) intersect values (1,2) union values (1,2);
+values (1,2),(3,4)
+except
+select 1,2
+union values (1,2);
 
-values (1,2),(3,4) intersect values (1,2) union values (3,4);
+values (1,2),(3,4)
+except
+values (1,2)
+union
+values (1,2);
 
-values (1,2),(3,4) union values (1,2) intersect values (1,2);
+values (1,2),(3,4)
+except
+values (1,2)
+union
+values (3,4);
 
---echo # combination of different structures using VALUES structures : UNION ALL + INTERSECT
+values (1,2),(3,4)
+union
+values (1,2)
+except
+values (1,2);
 
-values (1,2),(3,4) intersect select 1,2 union all values (1,2);
+--echo # combination of different structures that uses VALUES structures : UNION ALL + EXCEPT
 
-values (1,2),(3,4) intersect values (1,2) union all values (1,2);
+values (1,2),(3,4)
+except
+select 1,2
+union all
+values (1,2);
 
-values (1,2),(3,4) intersect values (1,2) union all values (3,4);
+values (1,2),(3,4)
+except
+values (1,2)
+union all
+values (1,2);
 
-values (1,2),(3,4) union all values (1,2) intersect values (1,2);
+values (1,2),(3,4)
+except
+values (1,2)
+union all
+values (3,4);
 
---echo # combination of different structures using VALUES structures : UNION + UNION ALL
+values (1,2),(3,4)
+union all
+values (1,2)
+except
+values (1,2);
 
-values (1,2),(3,4) union all select 1,2 union values (1,2);
+--echo # combination of different structures that uses VALUES structures : UNION + INTERSECT
 
-values (1,2),(3,4) union all values (1,2) union values (1,2);
+values (1,2),(3,4)
+intersect
+select 1,2
+union
+values (1,2);
 
-values (1,2),(3,4) union all values (1,2) union values (3,4);
+values (1,2),(3,4)
+intersect
+values (1,2)
+union
+values (1,2);
 
-values (1,2),(3,4) union values (1,2) union all values (1,2);
+values (1,2),(3,4)
+intersect
+values (1,2)
+union
+values (3,4);
 
-values (1,2) union values (1,2) union all values (1,2);
+values (1,2),(3,4)
+union
+values (1,2)
+intersect
+values (1,2);
 
---echo # CTE using VALUES structure(s)
+--echo # combination of different structures that uses VALUES structures : UNION ALL + INTERSECT
 
-with t2 as (values (1,2),(3,4)) select * from t2;
+values (1,2),(3,4)
+intersect
+select 1,2
+union all
+values (1,2);
 
-with t2 as (select 1,2 union values (1,2)) select * from t2;
+values (1,2),(3,4)
+intersect
+values (1,2)
+union all
+values (1,2);
 
-with t2 as (select 1,2 union values (1,2),(3,4)) select * from t2;
+values (1,2),(3,4)
+intersect
+values (1,2)
+union all
+values (3,4);
 
-with t2 as (values (1,2) union select 1,2) select * from t2;
+values (1,2),(3,4)
+union all
+values (1,2)
+intersect
+values (1,2);
 
-with t2 as (values (1,2),(3,4) union select 1,2) select * from t2;
+--echo # combination of different structures that uses VALUES structures : UNION + UNION ALL
 
-with t2 as (values (5,6) union values (1,2),(3,4)) select * from t2;
+values (1,2),(3,4)
+union all
+select 1,2
+union
+values (1,2);
 
-with t2 as (values (1,2) union values (1,2),(3,4)) select * from t2;
+values (1,2),(3,4)
+union all
+values (1,2)
+union
+values (1,2);
 
-with t2 as (select 1,2 union all values (1,2),(3,4)) select * from t2;
+values (1,2),(3,4)
+union all
+values (1,2)
+union
+values (3,4);
 
-with t2 as (values (1,2),(3,4) union all select 1,2) select * from t2;
+values (1,2),(3,4)
+union
+values (1,2)
+union all
+values (1,2);
 
-with t2 as (values (1,2) union all values (1,2),(3,4)) select * from t2;
+values (1,2)
+union
+values (1,2)
+union all
+values (1,2);
 
---echo # Derived table using VALUES structure(s)
+--echo # CTE that uses VALUES structure(s) : non-recursive CTE
+
+with t2 as
+(
+  values (1,2),(3,4)
+)
+select * from t2;
+
+with t2 as
+(
+  select 1,2
+  union
+  values (1,2)
+)
+select * from t2;
+
+with t2 as
+(
+  select 1,2
+  union
+  values (1,2),(3,4)
+)
+select * from t2;
+
+with t2 as
+(
+  values (1,2)
+  union
+  select 1,2
+)
+select * from t2;
+
+with t2 as
+(
+  values (1,2),(3,4)
+  union
+  select 1,2
+)
+select * from t2;
+
+with t2 as
+(
+  values (5,6)
+  union
+  values (1,2),(3,4)
+)
+select * from t2;
+
+with t2 as
+(
+  values (1,2)
+  union
+  values (1,2),(3,4)
+)
+select * from t2;
+
+with t2 as
+(
+  select 1,2
+  union all
+  values (1,2),(3,4)
+)
+select * from t2;
+
+with t2 as
+(
+  values (1,2),(3,4)
+  union all
+  select 1,2
+)
+select * from t2;
+
+with t2 as
+(
+  values (1,2)
+  union all
+  values (1,2),(3,4)
+)
+select * from t2;
+
+--echo # recursive CTE that uses VALUES structure(s) : singe VALUES structure as anchor
+
+with recursive t2(a,b) as
+(
+  values(1,1)
+  union
+  select t1.a, t1.b
+  from t1,t2
+  where t1.a=t2.a
+)
+select * from t2;
+
+with recursive t2(a,b) as
+(
+  values(1,1)
+  union
+  select t1.a+1, t1.b
+  from t1,t2
+  where t1.a=t2.a
+)
+select * from t2;
+
+--echo # recursive CTE that uses VALUES structure(s) : several VALUES structures as anchors
+
+with recursive t2(a,b) as
+(
+  values(1,1)
+  union
+  values (3,4)
+  union
+  select t2.a+1, t1.b
+  from t1,t2
+  where t1.a=t2.a
+)
+select * from t2;
+
+--echo # recursive CTE that uses VALUES structure(s) : that uses UNION ALL
+
+with recursive t2(a,b,st) as
+(
+  values(1,1,1)
+  union all
+  select t2.a, t1.b, t2.st+1
+  from t1,t2
+  where t1.a=t2.a and st<3
+)
+select * from t2;
+
+--echo # recursive CTE that uses VALUES structure(s) : computation of factorial (first 10 elements)
+
+with recursive fact(n,f) as
+(
+  values(1,1)
+  union
+  select n+1,f*n from fact where n < 10
+)
+select * from fact;
+
+--echo # Derived table that uses VALUES structure(s) : singe VALUES structure
 
 select * from (values (1,2),(3,4)) as t2;
 
+--echo # Derived table that uses VALUES structure(s) : UNION with VALUES structure(s)
+
 select * from (select 1,2 union values (1,2)) as t2;
 
 select * from (select 1,2 union values (1,2),(3,4)) as t2;
@@ -180,13 +460,15 @@ select * from (values (5,6) union values (1,2),(3,4)) as t2;
 
 select * from (values (1,2) union values (1,2),(3,4)) as t2;
 
+--echo # Derived table that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
+
 select * from (select 1,2 union all values (1,2),(3,4)) as t2;
 
 select * from (values (1,2),(3,4) union all select 1,2) as t2;
 
 select * from (values (1,2) union all values (1,2),(3,4)) as t2;
 
---echo # CREATE VIEW using VALUES structure(s)
+--echo # CREATE VIEW that uses VALUES structure(s) : singe VALUES structure
 
 let $drop_view= drop view v1;
 let $select_view= select * from v1;
@@ -196,49 +478,378 @@ create view v1 as values (1,2),(3,4);
 eval $select_view;
 eval $drop_view;
 
-create view v1 as select 1,2 union values (1,2);
+--echo # CREATE VIEW that uses VALUES structure(s) : UNION with VALUES structure(s)
+
+create view v1 as
+  select 1,2
+  union
+  values (1,2);
 
 eval $select_view;
 eval $drop_view;
 
-create view v1 as select 1,2 union values (1,2),(3,4);
+create view v1 as
+  select 1,2
+  union
+  values (1,2),(3,4);
 
 eval $select_view;
 eval $drop_view;
 
-create view v1 as values (1,2) union select 1,2;
+create view v1 as
+  values (1,2)
+  union
+  select 1,2;
 
 eval $select_view;
 eval $drop_view;
 
-create view v1 as values (1,2),(3,4) union select 1,2;
+create view v1 as
+  values (1,2),(3,4)
+  union
+  select 1,2;
 
 eval $select_view;
 eval $drop_view;
 
-create view v1 as values (5,6) union values (1,2),(3,4);
+create view v1 as
+  values (5,6)
+  union
+  values (1,2),(3,4);
 
 eval $select_view;
 eval $drop_view;
 
-create view v1 as values (1,2) union values (1,2),(3,4);
+--echo # CREATE VIEW that uses VALUES structure(s) : UNION ALL with VALUES structure(s)
+
+create view v1 as
+  values (1,2)
+  union
+  values (1,2),(3,4);
 
 eval $select_view;
 eval $drop_view;
 
-create view v1 as select 1,2 union all values (1,2),(3,4);
+create view v1 as
+  select 1,2
+  union all
+  values (1,2),(3,4);
 
 eval $select_view;
 eval $drop_view;
 
-create view v1 as values (1,2),(3,4) union all select 1,2;
+create view v1 as
+  values (1,2),(3,4)
+  union all
+  select 1,2;
 
 eval $select_view;
 eval $drop_view;
 
-create view v1 as values (1,2) union all values (1,2),(3,4);
+create view v1 as
+  values (1,2)
+  union all
+  values (1,2),(3,4);
 
 eval $select_view;
 eval $drop_view;
 
+--echo # prepare statement that uses VALUES structure(s): single VALUES structure
+
+prepare stmt1 from "
+values (1,2);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+--echo # prepare statement that uses VALUES structure(s): UNION with VALUES structure(s)
+
+prepare stmt1 from "
+  select 1,2
+  union
+  values (1,2),(3,4);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  values (1,2),(3,4)
+  union
+  select 1,2;
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  select 1,2
+  union
+  values (3,4)
+  union
+  values (1,2);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  values (5,6)
+  union
+  values (1,2),(3,4);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+--echo # prepare statement that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+
+prepare stmt1 from "
+  select 1,2
+  union
+  values (1,2),(3,4);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  values (1,2),(3,4)
+  union all
+  select 1,2;
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  select 1,2
+  union all
+  values (3,4)
+  union all
+  values (1,2);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+prepare stmt1 from "
+  values (1,2)
+  union all
+  values (1,2),(3,4);
+";
+
+execute stmt1;
+execute stmt1;
+deallocate prepare stmt1;
+
+--echo # explain query that uses VALUES structure(s): single VALUES structure
+
+explain
+values (1,2);
+
+explain format=json
+values (1,2);
+
+--echo # explain query that uses VALUES structure(s): UNION with VALUES structure(s)
+
+explain
+select 1,2
+union
+values (1,2),(3,4);
+
+explain
+values (1,2),(3,4)
+union
+select 1,2;
+
+explain
+values (5,6)
+union
+values (1,2),(3,4);
+
+explain format=json
+select 1,2
+union
+values (1,2),(3,4);
+
+explain format=json
+values (1,2),(3,4)
+union
+select 1,2;
+
+explain format=json
+values (5,6)
+union
+values (1,2),(3,4);
+
+explain
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+
+explain format=json
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+
+--echo # explain query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+
+explain
+select 1,2
+union
+values (1,2),(3,4);
+
+explain
+values (1,2),(3,4)
+union all
+select 1,2;
+
+explain
+values (1,2)
+union all
+values (1,2),(3,4);
+
+explain format=json
+values (1,2),(3,4)
+union all
+select 1,2;
+
+explain format=json
+select 1,2
+union
+values (1,2),(3,4);
+
+explain format=json
+values (1,2)
+union all
+values (1,2),(3,4);
+
+explain
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+
+explain format=json
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+
+--echo # analyze query that uses VALUES structure(s): single VALUES structure
+
+analyze
+values (1,2);
+
+analyze format=json
+values (1,2);
+
+--echo # analyze query that uses VALUES structure(s): UNION with VALUES structure(s)
+
+analyze
+select 1,2
+union
+values (1,2),(3,4);
+
+analyze
+values (1,2),(3,4)
+union
+select 1,2;
+
+analyze
+values (5,6)
+union
+values (1,2),(3,4);
+
+analyze format=json
+select 1,2
+union
+values (1,2),(3,4);
+
+analyze format=json
+values (1,2),(3,4)
+union
+select 1,2;
+
+analyze format=json
+values (5,6)
+union
+values (1,2),(3,4);
+
+analyze
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+
+analyze format=json
+select 1,2
+union
+values (3,4)
+union
+values (1,2);
+
+--echo # analyze query that uses VALUES structure(s): UNION ALL with VALUES structure(s)
+
+analyze
+select 1,2
+union
+values (1,2),(3,4);
+
+analyze
+values (1,2),(3,4)
+union all
+select 1,2;
+
+analyze
+values (1,2)
+union all
+values (1,2),(3,4);
+
+analyze format=json
+values (1,2),(3,4)
+union all
+select 1,2;
+
+analyze format=json
+select 1,2
+union
+values (1,2),(3,4);
+
+analyze format=json
+values (1,2)
+union all
+values (1,2),(3,4);
+
+analyze
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+
+analyze format=json
+select 1,2
+union all
+values (3,4)
+union all
+values (1,2);
+
 drop table t1;
\ No newline at end of file
diff --git a/sql/item.h b/sql/item.h
index 76ce4aa935f..c5f236179fd 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1667,6 +1667,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)
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 07b5f90bf69..d05d1602044 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -4327,6 +4327,16 @@ longlong Item_func_in::val_int()
 }
 
 
+void Item_func_in::mark_as_condition_AND_part(TABLE_LIST *embedding)
+{
+  THD *thd= current_thd;
+  if (can_be_transformed_in_tvc(thd))
+    thd->lex->current_select->in_funcs.push_back(this, thd->mem_root);
+
+  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 e21e074a7a3..a41da00da2c 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -2144,11 +2144,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 **);
@@ -2240,7 +2242,10 @@ public:
         return NULL;
     }
     return clone;
-  }      
+  }
+  void mark_as_condition_AND_part(TABLE_LIST *embedding);
+  bool can_be_transformed_in_tvc(THD *thd);
+  Item *in_predicate_to_in_subs_transformer(THD *thd, uchar *arg);
 };
 
 class cmp_item_row :public cmp_item
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index b18fb8f2ae5..f608e826f9b 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1047,6 +1047,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_predicate_into_tvc(thd))
+      DBUG_RETURN(TRUE);
     subq_sel->update_used_tables();
   }
 
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 1f282e6aee5..1a547e44820 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7490,3 +7490,5 @@ ER_WRONG_INSERT_INTO_SEQUENCE
         eng "Wrong INSERT into a SEQUENCE. One can only do single table INSERT into a squence object (like with mysqldump).  If you want to change the SEQUENCE, use ALTER SEQUENCE instead."
 ER_SP_STACK_TRACE
         eng "At line %u in %s"
+ER_WRONG_NUMBER_OF_COLUMNS_IN_TABLE_VALUE_CONSTRUCTOR
+        eng "The used TABLE VALUE CONSTRUCTOR has a different number of columns"
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 609d4ad23eb..d2b57b4d2b9 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -691,6 +691,7 @@ typedef struct system_variables
   uint idle_transaction_timeout;
   uint idle_readonly_transaction_timeout;
   uint idle_readwrite_transaction_timeout;
+  ulong in_subquery_conversion_threshold;
 } SV;
 
 /**
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index baab673011f..4c59b666acb 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2224,6 +2224,7 @@ void st_select_lex::init_query()
   m_agg_func_used= false;
   window_specs.empty();
   window_funcs.empty();
+  tvc= 0;
 }
 
 void st_select_lex::init_select()
@@ -2263,6 +2264,8 @@ void st_select_lex::init_select()
   join= 0;
   lock_type= TL_READ_DEFAULT;
   tvc= 0;
+  in_funcs.empty();
+  cur_tvc= 0;
 }
 
 /*
@@ -2807,7 +2810,10 @@ void st_select_lex_unit::print(String *str, enum_query_type query_type)
     }
     if (sl->braces)
       str->append('(');
-    sl->print(thd, str, query_type);
+    if (sl->tvc)
+      sl->tvc->print(thd, str, query_type);
+    else
+      sl->print(thd, str, query_type);
     if (sl->braces)
       str->append(')');
   }
@@ -4188,6 +4194,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 dbe881f2926..b607a3c479c 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -864,6 +864,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 cur_tvc;
   
   /*
     Needed to correctly generate 'PRIMARY' or 'SIMPLE' for select_type column
@@ -1215,7 +1226,7 @@ public:
   bool have_window_funcs() const { return (window_funcs.elements !=0); }
 
   bool cond_pushdown_is_allowed() const
-  { return !have_window_funcs() && !olap && !explicit_limit; }
+  { return !have_window_funcs() && !olap && !explicit_limit && !tvc; }
   
 private:
   bool m_non_agg_field_used;
@@ -1239,7 +1250,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_select.cc b/sql/sql_select.cc
index c6613facde7..ab87bc00c8c 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -347,7 +347,8 @@ bool handle_select(THD *thd, LEX *lex, select_result *result,
   MYSQL_SELECT_START(thd->query());
 
   if (select_lex->master_unit()->is_unit_op() ||
-      select_lex->master_unit()->fake_select_lex)
+      select_lex->master_unit()->fake_select_lex ||
+      select_lex->tvc)
     res= mysql_union(thd, lex, result, &lex->unit, setup_tables_done_option);
   else
   {
@@ -1187,6 +1188,11 @@ JOIN::optimize_inner()
       DBUG_RETURN(TRUE);  
     table_count= select_lex->leaf_tables.elements;
   }
+
+  if (select_lex->first_cond_optimization &&
+      transform_in_predicate_into_tvc(thd))
+    DBUG_RETURN(1);
+
   // Update used tables after all handling derived table procedures
   select_lex->update_used_tables();
 
@@ -13628,8 +13634,9 @@ static int compare_fields_by_table_order(Item *field1,
 static TABLE_LIST* embedding_sjm(Item *item)
 {
   Item_field *item_field= (Item_field *) (item->real_item());
-  TABLE_LIST *nest= item_field->field->table->pos_in_table_list->embedding;
-  if (nest && nest->sj_mat_info && nest->sj_mat_info->is_used)
+  TABLE_LIST *tbl= item_field->field->table->pos_in_table_list;
+  TABLE_LIST *nest= tbl->embedding;
+  if (nest && nest->sj_mat_info && nest->sj_mat_info->is_used && !tbl->is_for_tvc)
     return nest;
   else
     return NULL;
@@ -13706,6 +13713,7 @@ Item *eliminate_item_equal(THD *thd, COND *cond, COND_EQUAL *upper_levels,
   Item *head;
   TABLE_LIST *current_sjm= NULL;
   Item *current_sjm_head= NULL;
+  bool force_producing_equality= false;
 
   DBUG_ASSERT(!cond ||
               cond->type() == Item::INT_ITEM ||
@@ -13727,6 +13735,8 @@ Item *eliminate_item_equal(THD *thd, COND *cond, COND_EQUAL *upper_levels,
     TABLE_LIST *emb_nest;
     head= item_equal->get_first(NO_PARTICULAR_TAB, NULL);
     it++;
+    if (((Item_field *)(head->real_item()))->field->table->pos_in_table_list->is_for_tvc)
+      force_producing_equality= true;
     if ((emb_nest= embedding_sjm(head)))
     {
       current_sjm= emb_nest;
@@ -13794,7 +13804,7 @@ Item *eliminate_item_equal(THD *thd, COND *cond, COND_EQUAL *upper_levels,
         produce_equality= FALSE;
     }
 
-    if (produce_equality)
+    if (produce_equality || force_producing_equality)
     {
       if (eq_item && eq_list.push_back(eq_item, thd->mem_root))
         return 0;
@@ -13809,7 +13819,8 @@ 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 &&
+                        !force_producing_equality) ? 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;
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 96764fd7f00..ad15ce5eb2c 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1648,6 +1648,7 @@ public:
                                bool need_order, bool distinct,
                                const char *message);
   JOIN_TAB *first_breadth_first_tab() { return join_tab; }
+  bool transform_in_predicate_into_tvc(THD *thd_arg);
 private:
   /**
     Create a temporary table to be used for processing DISTINCT/ORDER
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 323ce5eacb9..e9476254f9e 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -1,14 +1,40 @@
 #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"
 
 /**
-  The method searches types of columns for temporary table where values from TVC will be stored
+  @brief
+    Defines types of matrix columns elements where matrix rows are defined by
+    some lists of values.
+
+  @param
+    @param thd_arg   	        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 that should be. 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 when aggregation can't be made.
+
+  @retval
+    true    if an error was reported
+    false   otherwise
 */
 
-bool join_type_handlers_for_tvc(List_iterator_fast<List_item> &li, 
-			        Type_holder *holders, uint cnt)
+bool join_type_handlers_for_tvc(THD *thd_arg, 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;
@@ -18,10 +44,12 @@ bool join_type_handlers_for_tvc(List_iterator_fast<List_item> &li,
     List_iterator_fast<Item> it(*lst);
     Item *item;
   
-    if (cnt != lst->elements)
+    if (first_list_el_count != lst->elements)
     {
-      /*error wrong number of values*/
-      return true;
+      my_message(ER_WRONG_NUMBER_OF_COLUMNS_IN_TABLE_VALUE_CONSTRUCTOR,
+                 ER_THD(thd_arg, ER_WRONG_NUMBER_OF_COLUMNS_IN_TABLE_VALUE_CONSTRUCTOR),
+                 MYF(0));
+      DBUG_RETURN(true);
     }
     for (uint pos= 0; (item=it++); pos++)
     {
@@ -30,54 +58,105 @@ bool join_type_handlers_for_tvc(List_iterator_fast<List_item> &li,
         holders[pos].set_handler(item_type_handler);
       else if (holders[pos].aggregate_for_result(item_type_handler))
       {
-        /*error ER_ILLEGAL_PARAMETER_DATA_TYPES2_FOR_OPERATION*/
-        return true;
+        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;
   }
-  return false;
+  DBUG_RETURN(false);
 }
 
+
 /**
-  The method searches names of columns for temporary table where values from TVC will be stored
+  @brief
+    Defines attributes of matrix columns elements where matrix rows are defined by
+    some lists of values.
+
+  @param
+    @param thd_arg   	        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 that should be. 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_arg,
 			         List_iterator_fast<List_item> &li, 
-                                 Type_holder *holders, uint count)
+                                 Type_holder *holders, uint count_of_lists,
+				 uint first_list_el_count)
 {
+  DBUG_ENTER("get_type_attributes_for_tvc");
   List_item *lst;
   li.rewind();
   
-  lst= li++;
-  uint first_list_el_count= lst->elements;
-  
   for (uint pos= 0; pos < first_list_el_count; pos++)
   {
-    if (holders[pos].alloc_arguments(thd_arg, count))
-      return true;
+    if (holders[pos].alloc_arguments(thd_arg, count_of_lists))
+      DBUG_RETURN(true);
   }
   
-  List_iterator_fast<Item> it(*lst);
-  Item *item;
-  
-  for (uint holder_pos= 0 ; (item= it++); holder_pos++)
+  while ((lst=li++))
   {
-    DBUG_ASSERT(item->fixed);
-    holders[holder_pos].add_argument(item);
+    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_arg))
-      return true;
+      DBUG_RETURN(true);
   }
-  return false;
+  DBUG_RETURN(false);
 }
 
-bool table_value_constr::prepare(THD *thd_arg, SELECT_LEX *sl, select_result *tmp_result)
+
+/**
+  @brief
+    Prepare of TVC
+
+  @param
+    @param thd_arg      The context of the statement
+    @param sl	     	The select where this TVC is defined
+    @param tmp_result	Structure that contains the information
+			about where result of the query should be sent
+    @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_arg, SELECT_LEX *sl,
+				 select_result *tmp_result,
+				 st_select_lex_unit *unit_arg)
 {
+  DBUG_ENTER("table_value_constr::prepare");
   List_iterator_fast<List_item> li(lists_of_values);
   
   List_item *first_elem= li++;
@@ -86,9 +165,11 @@ bool table_value_constr::prepare(THD *thd_arg, SELECT_LEX *sl, select_result *tm
   
   if (!(holders= new (thd_arg->mem_root)
                 Type_holder[cnt]) || 
-       join_type_handlers_for_tvc(li, holders, cnt) ||
-       get_type_attributes_for_tvc(thd_arg, li, holders, cnt))
-    return true;
+       join_type_handlers_for_tvc(thd_arg, li, holders,
+				  cnt) ||
+       get_type_attributes_for_tvc(thd_arg, li, holders,
+				   lists_of_values.elements, cnt))
+    DBUG_RETURN(true);
   
   List_iterator_fast<Item> it(*first_elem);
   Item *item;
@@ -108,21 +189,448 @@ bool table_value_constr::prepare(THD *thd_arg, SELECT_LEX *sl, select_result *tm
   }
   
   if (thd_arg->is_fatal_error)
-    return true; // out of memory
+    DBUG_RETURN(true); // out of memory
     
   result= tmp_result;
   
-  return false;
+  if (result && result->prepare(sl->item_list, unit_arg))
+    DBUG_RETURN(true);
+
+  DBUG_RETURN(false);
 }
 
-bool table_value_constr::exec()
+
+/**
+    Save Query Plan Footprint
+*/
+
+int table_value_constr::save_explain_data_intern(THD *thd_arg,
+						 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_arg->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_arg)
+{
+  create_explain_query_if_not_exists(thd_arg->lex, thd_arg->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_arg->lex->explain && // for "SET" command in SPs.
+      (!thd_arg->lex->explain->get_select(select_lex->select_number)))
+  {
+    save_explain_data_intern(thd_arg, thd_arg->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 of lists
+
+  @param str         Where to print to
+  @param query_type  The mode of printing
+  @param values      List of lists that needed to be print
+
+  @details
+    The method prints a string representation of list of lists in the
+    string str. The parameter query_type specifies the mode of printing.
+*/
+
+void print_list_of_lists(String *str,
+			 enum_query_type query_type,
+			 List<List_item> *values)
+{
+  str->append(STRING_WITH_LEN("values "));
+
+  bool first= 1;
+  List_iterator_fast<List_item> li(*values);
+  List_item *list;
+  while ((list=li++))
+  {
+    if (first)
+      first= 0;
+    else
+      str->append(',');
+
+    str->append('(');
+
+    List_iterator_fast<Item> it(*list);
+    Item *item;
+    first= 1;
+
+    while ((item=it++))
+    {
+      if (first)
+        first= 0;
+      else
+        str->append(',');
+
+      item->print(str, query_type);
+    }
+    str->append(')');
+  }
+}
+
+
+/**
+  @brief
+    Print this TVC
+
+  @param thd_arg     The context of the statement
+  @param str         Where to print to
+  @param query_type  The mode of printing
+
+  @details
+    The method prints a string representation of this TVC in the
+    string str. The parameter query_type specifies the mode of printing.
+*/
+
+void table_value_constr::print(THD *thd_arg, String *str,
+			       enum_query_type query_type)
+{
+  DBUG_ASSERT(thd_arg);
+
+  print_list_of_lists(str, query_type, &lists_of_values);
+}
+
+
+/**
+  @brief
+    Creates new SELECT defined by TVC as derived table
+
+  @param thd_arg     The context of the statement
+  @param values      List of values that defines TVC
+
+  @details
+    The method creates this SELECT statement:
+
+    SELECT * FROM (VALUES  values) AS new_tvc
+
+    If during creation of SELECT statement some action is
+    unsuccesfull backup is made to the state in which system
+    was at the beginning of the method.
+
+  @retval
+    pointer to the created SELECT statement
+    NULL - if creation was unsuccesfull
+*/
+
+st_select_lex *make_new_subselect_for_tvc(THD *thd_arg,
+				          List<List_item> *values)
+{
+  LEX *lex= thd_arg->lex;
+  Item *item;
+  SELECT_LEX *sel;
+  SELECT_LEX_UNIT *unit;
+  TABLE_LIST *new_tab;
+  Table_ident *ti;
+
+  Query_arena backup;
+  Query_arena *arena= thd_arg->activate_stmt_arena_if_needed(&backup);
+
+  char buff[6];
+  LEX_CSTRING alias;
+  alias.length= my_snprintf(buff, sizeof(buff),
+                            "tvc_%u", thd_arg->lex->current_select->cur_tvc);
+  alias.str= thd_arg->strmake(buff, alias.length);
+  if (!alias.str)
+    goto err;
+
+  /*
+    Creation of SELECT statement: SELECT * FROM ...
+  */
+
+  if (mysql_new_select(lex, 1, NULL))
+    goto err;
+
+  mysql_init_select(lex);
+  lex->current_select->parsing_place= SELECT_LIST;
+
+  item= new (thd_arg->mem_root)
+                Item_field(thd_arg, &lex->current_select->context,
+                           NULL, NULL, &star_clex_str);
+  if (item == NULL)
+    goto err;
+  if (add_item_to_list(thd_arg, item))
+    goto err;
+  (lex->current_select->with_wild)++;
+
+  /*
+    Creation of TVC as derived table
+  */
+
+  lex->derived_tables|= DERIVED_SUBQUERY;
+  if (mysql_new_select(lex, 1, NULL))
+    goto err;
+
+  mysql_init_select(lex);
+
+  sel= lex->current_select;
+  unit= sel->master_unit();
+  sel->linkage= DERIVED_TABLE_TYPE;
+
+  if (!(sel->tvc=
+          new (thd_arg->mem_root)
+	    table_value_constr(*values,
+                               sel,
+                               sel->options)))
+    goto err;
+
+  lex->check_automatic_up(UNSPECIFIED_TYPE);
+  lex->current_select= sel= unit->outer_select();
+
+  ti= new (thd_arg->mem_root) Table_ident(unit);
+  if (ti == NULL)
+    goto err;
+
+  if (!(new_tab= sel->add_table_to_list(thd_arg,
+                                        ti, &alias, 0,
+                                        TL_READ, MDL_SHARED_READ)))
+    goto err;
+
+  new_tab->is_for_tvc= true; //shows that this derived table is defined by TVC
+  sel->add_joined_table(new_tab);
+
+  new_tab->select_lex->add_where_field(new_tab->derived->first_select());
+
+  sel->context.table_list=
+  sel->context.first_name_resolution_table=
+  sel->table_list.first;
+
+  sel->where= 0;
+  sel->set_braces(false);
+  unit->with_clause= 0;
+
+  return sel;
+
+err:
+  if (arena)
+    thd_arg->restore_active_arena(arena, &backup);
+  return NULL;
+}
+
+
+/**
+  @brief
+    Transforms IN-predicate in IN-subselect
+
+  @param thd_arg     The context of the statement
+  @param arg         Argument is 0 in this context
+
+  @details
+    The method creates this SELECT statement:
+
+    SELECT * FROM (VALUES  values) AS new_tvc
+
+    If during creation of SELECT statement some action is
+    unsuccesfull backup is made to the state in which system
+    was at the beginning of the procedure.
+
+  @retval
+    pointer to the created SELECT statement
+    NULL - if creation was unsuccesfull
+*/
+
+Item *Item_func_in::in_predicate_to_in_subs_transformer(THD *thd,
+							uchar *arg)
+{
+  SELECT_LEX *old_select= thd->lex->current_select;
+  List<List_item> values;
+  bool list_of_lists= false;
+
+  if (args[1]->type() == Item::ROW_ITEM)
+    list_of_lists= true;
+
+  for (uint i=1; i < arg_count; i++)
+  {
+    List<Item> *new_value= new (thd->mem_root) List<Item>();
+
+    if (list_of_lists)
+    {
+      Item_row *in_list= (Item_row *)(args[i]);
+
+      for (uint j=0; j < in_list->cols(); i++)
+	new_value->push_back(in_list->element_index(j), thd->mem_root);
+    }
+    else
+      new_value->push_back(args[i]);
+
+    values.push_back(new_value, thd->mem_root);
+  }
+
+  st_select_lex *new_subselect=
+    make_new_subselect_for_tvc(thd, &values);
+
+  if (new_subselect)
+  {
+    new_subselect->parsing_place= old_select->parsing_place;
+    new_subselect->table_list.first->derived_type= 10;
+
+    Item_in_subselect *in_subs= new (thd->mem_root) Item_in_subselect
+                                  (thd, args[0], new_subselect);
+    thd->lex->derived_tables |= DERIVED_SUBQUERY;
+    in_subs->emb_on_expr_nest= emb_on_expr_nest;
+    in_subs->fix_fields(thd, (Item **)&in_subs);
+
+    old_select->cur_tvc++;
+    thd->lex->current_select= old_select;
+    return in_subs;
+  }
+
+  thd->lex->current_select= old_select;
+  return this;
+}
+
+/**
+  @brief
+    Checks if this IN-predicate can be transformed in IN-subquery
+    with TVC
+
+  @param thd     The context of the statement
+
+  @details
+    Compares 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::can_be_transformed_in_tvc(THD *thd)
+{
+  uint opt_can_be_used= arg_count;
+
+  if (args[1]->type() == Item::ROW_ITEM)
+    opt_can_be_used*= ((Item_row *)(args[1]))->cols();
+
+  if (opt_can_be_used < thd->variables.in_subquery_conversion_threshold)
+    return false;
+
+  return true;
+}
+
+/**
+  @brief
+    Calls transformer that transforms IN-predicate into IN-subquery
+    for this select
+
+  @param thd_arg     The context of the statement
+
+  @details
+    Calls in_predicate_to_in_subs_transformer
+    for WHERE-part and each table from join list of this SELECT
+*/
+
+bool JOIN::transform_in_predicate_into_tvc(THD *thd_arg)
+{
+  if (!select_lex->in_funcs.elements)
+    return false;
+
+  SELECT_LEX *old_select= thd_arg->lex->current_select;
+  enum_parsing_place old_parsing_place= select_lex->parsing_place;
+
+  thd_arg->lex->current_select= select_lex;
+  if (conds)
+  {
+    select_lex->parsing_place= IN_WHERE;
+    conds=
+      conds->transform(thd_arg,
+		       &Item::in_predicate_to_in_subs_transformer,
+                       (uchar*) 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_arg,
+		                    &Item::in_predicate_to_in_subs_transformer,
+                                    (uchar*) 0);
+      }
+    }
+  }
+  select_lex->parsing_place= old_parsing_place;
+  thd_arg->lex->current_select= old_select;
   return false;
 }
\ No newline at end of file
diff --git a/sql/sql_tvc.h b/sql/sql_tvc.h
index 007b50d81df..5524744a03c 100644
--- a/sql/sql_tvc.h
+++ b/sql/sql_tvc.h
@@ -6,26 +6,45 @@
 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 that this TVC contains.
+  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) : 
-    lists_of_values(tvc_values), result(0)
-  { }
+  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);
-  bool exec();
-};
+	       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 52880cd4442..717863754fc 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -819,6 +819,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);
@@ -845,16 +846,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);
@@ -864,7 +875,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())
   {
@@ -884,8 +895,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())
@@ -922,7 +933,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
   {
     if (sl->tvc)
     {
-      if (sl->tvc->prepare(thd_arg, sl, tmp_result))
+      if (sl->tvc->prepare(thd_arg, sl, tmp_result, this))
 	goto err;
     }
     else if (prepare_join(thd_arg, first_sl, tmp_result, additional_options,
@@ -936,7 +947,7 @@ bool st_select_lex_unit::prepare(THD *thd_arg, select_result *sel_result,
   {
     if (sl->tvc)
     {
-      if (sl->tvc->prepare(thd_arg, sl, tmp_result))
+      if (sl->tvc->prepare(thd_arg, sl, tmp_result, this))
 	goto err;
     }
     else if (prepare_join(thd_arg, sl, tmp_result, additional_options,
@@ -1249,7 +1260,13 @@ 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)
@@ -1273,7 +1290,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;
 
@@ -1357,7 +1374,14 @@ 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.
         */
-	if (!sl->tvc)
+	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) ?
@@ -1369,7 +1393,7 @@ bool st_select_lex_unit::exec()
       {
 	records_at_start= table->file->stats.records;
 	if (sl->tvc)
-	  sl->tvc->exec();
+	  sl->tvc->exec(sl);
 	else
 	  sl->join->exec();
         if (sl == union_distinct && !(with_element && with_element->is_recursive))
@@ -1611,8 +1635,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 b4a0e52f693..e0e09b0b3c4 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1782,7 +1782,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
 
@@ -8549,6 +8551,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
@@ -11250,9 +11261,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:
@@ -11307,6 +11318,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:
@@ -11321,6 +11336,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;
@@ -16273,13 +16323,22 @@ simple_table:
         ;
         
 table_value_constructor:
-	  VALUES values_list 
+	  VALUES
+	  {
+	    LEX *lex=Lex;
+            lex->field_list.empty();
+            lex->many_values.empty();
+            lex->insert_list=0;
+	  }
+	  values_list
 	  { 
 	    LEX *lex=Lex;
-	    $$= Lex->current_select;
+	    $$= lex->current_select;
 	    mysql_init_select(Lex);
-	    table_value_constr tvc(lex->many_values);
-	    $$->tvc= &tvc;
+	    if (!($$->tvc=
+	          new (lex->thd->mem_root) table_value_constr(lex->many_values, $$, $$->options)))
+	      MYSQL_YYABORT;
+	    lex->many_values.empty();
 	  }
 	;
 	
diff --git a/sql/sys_vars.cc b/sql/sys_vars.cc
index c2a5e183187..aadf47235c4 100644
--- a/sql/sys_vars.cc
+++ b/sql/sys_vars.cc
@@ -5550,4 +5550,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(1000), BLOCK_SIZE(1));
+
 #endif //EMBEDDED_LIBRARY
diff --git a/sql/table.h b/sql/table.h
index 478b65efec5..b9606145e2a 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1930,6 +1930,7 @@ struct TABLE_LIST
   */
   st_select_lex_unit *derived;		/* SELECT_LEX_UNIT of derived table */
   With_element *with;          /* With element defining this table (if any) */
+  bool is_for_tvc;		/* If specification of this table contains tvc*/
   /* Bitmap of the defining with element */
   table_map with_internal_reference_map; 
   bool block_handle_derived;