[Commits] 48d155b: MDEV-12387 Push conditions into materialized subqueries

shagalla galina.shalygina at mariadb.com
Wed Apr 25 17:57:15 EEST 2018


revision-id: 48d155b5126807c31223cb25f7df473227c1f601 (mariadb-10.3.4-61-g48d155b)
parent(s): ccae16dfc128bfb95421c98a7ce331c38fc747ce
author: Galina Shalygina
committer: Galina Shalygina
timestamp: 2018-04-25 16:57:15 +0200
message:

MDEV-12387 Push conditions into materialized subqueries

Comments changed, tests changed

---
 mysql-test/r/in_subq_cond_pushdown.result | 980 +++++++++++++++---------------
 mysql-test/t/in_subq_cond_pushdown.test   | 182 +++---
 sql/opt_subselect.cc                      |  24 +-
 3 files changed, 595 insertions(+), 591 deletions(-)

diff --git a/mysql-test/r/in_subq_cond_pushdown.result b/mysql-test/r/in_subq_cond_pushdown.result
index 97abd21..6676d3b 100644
--- a/mysql-test/r/in_subq_cond_pushdown.result
+++ b/mysql-test/r/in_subq_cond_pushdown.result
@@ -1,6 +1,6 @@
-create table t1 (a int, b int, c int, d int);
-create table t2 (e int, f int, g int);
-create table t3 (x int, y int);
+create table t1 (t1_a int, t1_b int, t1_c int, t1_d int);
+create table t2 (t2_e int, t2_f int, t2_g int);
+create table t3 (t3_x int, t3_y int);
 insert into t1 values
 (1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4),
 (4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2),
@@ -14,31 +14,31 @@ insert into t3 values
 (1,35), (3,23), (3,17), (2,15);
 create view v1 as
 (
-select * from t3 where x<=3
+select t3_x as v1_x, t3_y as v1_y from t3 where t3_x<=3
 );
 # conjunctive subformula : pushing into HAVING
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where c<25 and
-(a,c) in (select e,max(g) from t2 where e<5 group by e);
-a	b	c	d
+where t1_c<25 and
+(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
+t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 3	2	23	1
 select * from t1
-where c<25 and
-(a,c) in (select e,max(g) from t2 where e<5 group by e);
-a	b	c	d
+where t1_c<25 and
+(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
+t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 3	2	23	1
 explain select * from t1
-where c<25 and
-(a,c) in (select e,max(g) from t2 where e<5 group by e);
+where t1_c<25 and
+(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.a,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where c<25 and
-(a,c) in (select e,max(g) from t2 where e<5 group by e);
+where t1_c<25 and
+(t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
 EXPLAIN
 {
   "query_block": {
@@ -48,7 +48,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "t1.c < 25 and t1.a is not null and t1.c is not null"
+      "attached_condition": "t1.t1_c < 25 and t1.t1_a is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -56,22 +56,22 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["e", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.c"],
+      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "`max(g)` < 25",
+          "having_condition": "`max(t2_g)` < 25",
           "temporary_table": {
             "table": {
               "table_name": "t2",
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5"
+              "attached_condition": "t2.t2_e < 5"
             }
           }
         }
@@ -81,51 +81,51 @@ EXPLAIN
 }
 # extracted and formula : pushing into HAVING
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where c>55 and b<4 and
-(a,b,c) in
+where t1_c>55 and t1_b<4 and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 2	3	70	3
 select * from t1
-where c>55 and b<4 and
-(a,b,c) in
+where t1_c>55 and t1_b<4 and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 2	3	70	3
 explain select * from t1
-where c>55 and b<4 and
-(a,b,c) in
+where t1_c>55 and t1_b<4 and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.a,test.t1.b,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where c>55 and b<4 and
-(a,b,c) in
+where t1_c>55 and t1_b<4 and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -137,7 +137,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "t1.c > 55 and t1.b < 4 and t1.a is not null and t1.b is not null and t1.c is not null"
+      "attached_condition": "t1.t1_c > 55 and t1.t1_b < 4 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -145,22 +145,22 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["e", "f", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "`max(g)` > 55 and t2.f < 4",
+          "having_condition": "`max(t2_g)` > 55 and t2.t2_f < 4",
           "temporary_table": {
             "table": {
               "table_name": "t2",
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5"
+              "attached_condition": "t2.t2_e < 5"
             }
           }
         }
@@ -170,53 +170,53 @@ EXPLAIN
 }
 # extracted or formula : pushing into HAVING
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where (c>60 or c<25) and
-(a,b,c) in
+where (t1_c>60 or t1_c<25) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 2	3	70	3
 select * from t1
-where (c>60 or c<25) and
-(a,b,c) in
+where (t1_c>60 or t1_c<25) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 2	3	70	3
 explain select * from t1
-where (c>60 or c<25) and
-(a,b,c) in
+where (t1_c>60 or t1_c<25) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.a,test.t1.b,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where (c>60 or c<25) and
-(a,b,c) in
+where (t1_c>60 or t1_c<25) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -228,7 +228,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.a is not null and t1.b is not null and t1.c is not null"
+      "attached_condition": "(t1.t1_c > 60 or t1.t1_c < 25) and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -236,22 +236,22 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["e", "f", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "`max(g)` > 60 or `max(g)` < 25",
+          "having_condition": "`max(t2_g)` > 60 or `max(t2_g)` < 25",
           "temporary_table": {
             "table": {
               "table_name": "t2",
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5"
+              "attached_condition": "t2.t2_e < 5"
             }
           }
         }
@@ -261,51 +261,51 @@ EXPLAIN
 }
 # extracted and-or formula : pushing into HAVING
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((c>60 or c<25) and b>2) and
-(a,b,c) in
+where ((t1_c>60 or t1_c<25) and t1_b>2) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 2	3	70	3
 select * from t1
-where ((c>60 or c<25) and b>2) and
-(a,b,c) in
+where ((t1_c>60 or t1_c<25) and t1_b>2) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 2	3	70	3
 explain select * from t1
-where ((c>60 or c<25) and b>2) and
-(a,b,c) in
+where ((t1_c>60 or t1_c<25) and t1_b>2) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.a,test.t1.b,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where ((c>60 or c<25) and b>2) and
-(a,b,c) in
+where ((t1_c>60 or t1_c<25) and t1_b>2) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -317,7 +317,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "(t1.c > 60 or t1.c < 25) and t1.b > 2 and t1.a is not null and t1.b is not null and t1.c is not null"
+      "attached_condition": "(t1.t1_c > 60 or t1.t1_c < 25) and t1.t1_b > 2 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -325,22 +325,22 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["e", "f", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "(`max(g)` > 60 or `max(g)` < 25) and t2.f > 2",
+          "having_condition": "(`max(t2_g)` > 60 or `max(t2_g)` < 25) and t2.t2_f > 2",
           "temporary_table": {
             "table": {
               "table_name": "t2",
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5"
+              "attached_condition": "t2.t2_e < 5"
             }
           }
         }
@@ -350,49 +350,53 @@ EXPLAIN
 }
 # conjunctive subformula : pushing into HAVING
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((a<2 or d>2) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_d>3) and t1_b>1) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
+4	2	24	4
+1	2	40	2
 select * from t1
-where ((a<2 or d>2) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_d>3) and t1_b>1) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
+4	2	24	4
+1	2	40	2
 explain select * from t1
-where ((a<2 or d>2) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_d>3) and t1_b>1) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.a,test.t1.b,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where ((a<2 or d>2) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_d>3) and t1_b>1) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -404,7 +408,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "(t1.a < 2 or t1.d > 2) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null"
+      "attached_condition": "(t1.t1_a < 2 or t1.t1_d > 3) and t1.t1_b > 1 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -412,22 +416,22 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["e", "f", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "t2.f > 3",
+          "having_condition": "t2.t2_f > 1",
           "temporary_table": {
             "table": {
               "table_name": "t2",
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5"
+              "attached_condition": "t2.t2_e < 5"
             }
           }
         }
@@ -437,51 +441,51 @@ EXPLAIN
 }
 # using view in subquery definition : pushing into HAVING
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where c>20 and
-(a,c) in
+where t1_c>20 and
+(t1_a,t1_c) in
 (
-select x,max(y)
+select v1_x,max(v1_y)
 from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 3	2	23	1
 select * from t1
-where c>20 and
-(a,c) in
+where t1_c>20 and
+(t1_a,t1_c) in
 (
-select x,max(y)
+select v1_x,max(v1_y)
 from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 3	2	23	1
 explain select * from t1
-where c>20 and
-(a,c) in
+where t1_c>20 and
+(t1_a,t1_c) in
 (
-select x,max(y)
+select v1_x,max(v1_y)
 from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.a,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary
 explain format=json select * from t1
-where c>20 and
-(a,c) in
+where t1_c>20 and
+(t1_a,t1_c) in
 (
-select x,max(y)
+select v1_x,max(v1_y)
 from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
 )
 ;
 EXPLAIN
@@ -493,7 +497,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "t1.c > 20 and t1.a is not null and t1.c is not null"
+      "attached_condition": "t1.t1_c > 20 and t1.t1_a is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -501,22 +505,22 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["x", "max(y)"],
-      "ref": ["test.t1.a", "test.t1.c"],
+      "used_key_parts": ["v1_x", "max(v1_y)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "`max(y)` > 20",
+          "having_condition": "`max(v1_y)` > 20",
           "temporary_table": {
             "table": {
               "table_name": "t3",
               "access_type": "ALL",
               "rows": 8,
               "filtered": 100,
-              "attached_condition": "t3.x > 1 and t3.x <= 3"
+              "attached_condition": "t3.t3_x > 1 and t3.t3_x <= 3"
             }
           }
         }
@@ -526,52 +530,52 @@ EXPLAIN
 }
 # using equality : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1,v1
-where c>20 and c=y and
-(a,c) in
+where t1_c>20 and t1_c=v1_y and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d	x	y
+t1_a	t1_b	t1_c	t1_d	v1_x	v1_y
 3	2	23	1	3	23
 select * from t1,v1
-where c>20 and c=y and
-(a,c) in
+where t1_c>20 and t1_c=v1_y and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d	x	y
+t1_a	t1_b	t1_c	t1_d	v1_x	v1_y
 3	2	23	1	3	23
 explain select * from t1,v1
-where c>20 and c=y and
-(a,c) in
+where t1_c>20 and t1_c=v1_y and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	8	Using where
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.a,test.t3.y	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t3.t3_y	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1,v1
-where c>20 and c=y and
-(a,c) in
+where t1_c>20 and t1_c=v1_y and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -583,7 +587,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 8,
       "filtered": 100,
-      "attached_condition": "t3.y > 20 and t3.x <= 3 and t3.y is not null"
+      "attached_condition": "t3.t3_y > 20 and t3.t3_x <= 3 and t3.t3_y is not null"
     },
     "block-nl-join": {
       "table": {
@@ -595,7 +599,7 @@ EXPLAIN
       "buffer_type": "flat",
       "buffer_size": "256Kb",
       "join_type": "BNL",
-      "attached_condition": "t1.c = t3.y and t1.a is not null"
+      "attached_condition": "t1.t1_c = t3.t3_y and t1.t1_a is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -603,22 +607,22 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["e", "max(g)"],
-      "ref": ["test.t1.a", "test.t3.y"],
+      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t3.t3_y"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "`max(g)` > 20",
+          "having_condition": "`max(t2_g)` > 20",
           "temporary_table": {
             "table": {
               "table_name": "t2",
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5"
+              "attached_condition": "t2.t2_e < 5"
             }
           }
         }
@@ -628,53 +632,53 @@ EXPLAIN
 }
 # conjunctive subformula : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where a<2 and
-(a,c) in
+where t1_a<2 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	3	40	1
 1	2	40	2
 select * from t1
-where a<2 and
-(a,c) in
+where t1_a<2 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	3	40	1
 1	2	40	2
 explain select * from t1
-where a<2 and
-(a,c) in
+where t1_a<2 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.a,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where a<2 and
-(a,c) in
+where t1_a<2 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -686,7 +690,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "t1.a < 2 and t1.a is not null and t1.c is not null"
+      "attached_condition": "t1.t1_a < 2 and t1.t1_a is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -694,8 +698,8 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["e", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.c"],
+      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
@@ -708,7 +712,7 @@ EXPLAIN
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5 and t2.e < 2"
+              "attached_condition": "t2.t2_e < 5 and t2.t2_e < 2"
             }
           }
         }
@@ -718,53 +722,53 @@ EXPLAIN
 }
 # extracted and formula : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where a>2 and a<5 and
-(a,c) in
+where t1_a>2 and t1_a<5 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 3	2	23	1
 select * from t1
-where a>2 and a<5 and
-(a,c) in
+where t1_a>2 and t1_a<5 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 3	2	23	1
 explain select * from t1
-where a>2 and a<5 and
-(a,c) in
+where t1_a>2 and t1_a<5 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.a,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where a>2 and a<5 and
-(a,c) in
+where t1_a>2 and t1_a<5 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -776,7 +780,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "t1.a > 2 and t1.a < 5 and t1.a is not null and t1.c is not null"
+      "attached_condition": "t1.t1_a > 2 and t1.t1_a < 5 and t1.t1_a is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -784,8 +788,8 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["e", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.c"],
+      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
@@ -798,7 +802,7 @@ EXPLAIN
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5 and t2.e > 2 and t2.e < 5"
+              "attached_condition": "t2.t2_e < 5 and t2.t2_e > 2 and t2.t2_e < 5"
             }
           }
         }
@@ -808,55 +812,55 @@ EXPLAIN
 }
 # extracted or formula : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where (a<2 or a>=4) and
-(a,c) in
+where (t1_a<2 or t1_a>=4) and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	3	40	1
 4	2	24	4
 1	2	40	2
 select * from t1
-where (a<2 or a>=4) and
-(a,c) in
+where (t1_a<2 or t1_a>=4) and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	3	40	1
 4	2	24	4
 1	2	40	2
 explain select * from t1
-where (a<2 or a>=4) and
-(a,c) in
+where (t1_a<2 or t1_a>=4) and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.a,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where (a<2 or a>=4) and
-(a,c) in
+where (t1_a<2 or t1_a>=4) and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -868,7 +872,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "(t1.a < 2 or t1.a >= 4) and t1.a is not null and t1.c is not null"
+      "attached_condition": "(t1.t1_a < 2 or t1.t1_a >= 4) and t1.t1_a is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -876,8 +880,8 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["e", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.c"],
+      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
@@ -890,7 +894,7 @@ EXPLAIN
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e >= 4)"
+              "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e >= 4)"
             }
           }
         }
@@ -900,51 +904,51 @@ EXPLAIN
 }
 # extracted and-or formula : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	4	35	3
 select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	4	35	3
 explain select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.a,test.t1.b,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
 )
 ;
 EXPLAIN
@@ -956,7 +960,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null"
+      "attached_condition": "(t1.t1_a < 2 or t1.t1_a = 5) and t1.t1_b > 3 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -964,8 +968,8 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["e", "f", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
@@ -978,7 +982,7 @@ EXPLAIN
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3"
+              "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e = 5) and t2.t2_f > 3"
             }
           }
         }
@@ -988,51 +992,51 @@ EXPLAIN
 }
 # extracted and-or formula : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	4	35	3
 select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	4	35	3
 explain select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.a,test.t1.b,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where ((a<2 or a=5) and b>3) and
-(a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e,f
+where t2_e<5
+group by t2_e,t2_f
 )
 ;
 EXPLAIN
@@ -1044,7 +1048,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "(t1.a < 2 or t1.a = 5) and t1.b > 3 and t1.a is not null and t1.b is not null and t1.c is not null"
+      "attached_condition": "(t1.t1_a < 2 or t1.t1_a = 5) and t1.t1_b > 3 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -1052,8 +1056,8 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["e", "f", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
@@ -1066,7 +1070,7 @@ EXPLAIN
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5 and (t2.e < 2 or t2.e = 5) and t2.f > 3"
+              "attached_condition": "t2.t2_e < 5 and (t2.t2_e < 2 or t2.t2_e = 5) and t2.t2_f > 3"
             }
           }
         }
@@ -1076,51 +1080,51 @@ EXPLAIN
 }
 # conjunctive subformula : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((b<3 or d>2) and a<2) and
-(a,b,c) in
+where ((t1_b<3 or t1_d>2) and t1_a<2) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	2	40	2
 select * from t1
-where ((b<3 or d>2) and a<2) and
-(a,b,c) in
+where ((t1_b<3 or t1_d>2) and t1_a<2) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	2	40	2
 explain select * from t1
-where ((b<3 or d>2) and a<2) and
-(a,b,c) in
+where ((t1_b<3 or t1_d>2) and t1_a<2) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.a,test.t1.b,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where ((b<3 or d>2) and a<2) and
-(a,b,c) in
+where ((t1_b<3 or t1_d>2) and t1_a<2) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -1132,7 +1136,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "(t1.b < 3 or t1.d > 2) and t1.a < 2 and t1.a is not null and t1.b is not null and t1.c is not null"
+      "attached_condition": "(t1.t1_b < 3 or t1.t1_d > 2) and t1.t1_a < 2 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -1140,8 +1144,8 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["e", "f", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
@@ -1154,7 +1158,7 @@ EXPLAIN
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5 and t2.e < 2"
+              "attached_condition": "t2.t2_e < 5 and t2.t2_e < 2"
             }
           }
         }
@@ -1164,51 +1168,51 @@ EXPLAIN
 }
 # using equalities : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where d=1 and a=d and
-(a,c) in
+where t1_d=1 and t1_a=t1_d and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	3	40	1
 select * from t1
-where d=1 and a=d and
-(a,c) in
+where t1_d=1 and t1_a=t1_d and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	3	40	1
 explain select * from t1
-where d=1 and a=d and
-(a,c) in
+where t1_d=1 and t1_a=t1_d and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	const,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	const,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where
 explain format=json select * from t1
-where d=1 and a=d and
-(a,c) in
+where t1_d=1 and t1_a=t1_d and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -1220,7 +1224,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "t1.a = 1 and t1.d = 1 and t1.c is not null"
+      "attached_condition": "t1.t1_a = 1 and t1.t1_d = 1 and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -1228,8 +1232,8 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["e", "max(g)"],
-      "ref": ["const", "test.t1.c"],
+      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "ref": ["const", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
@@ -1241,7 +1245,7 @@ EXPLAIN
             "access_type": "ALL",
             "rows": 12,
             "filtered": 100,
-            "attached_condition": "t2.e = 1"
+            "attached_condition": "t2.t2_e = 1"
           }
         }
       }
@@ -1250,51 +1254,51 @@ EXPLAIN
 }
 # using equality : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where d>1 and a=d and
-(a,c) in
+where t1_d>1 and t1_a=t1_d and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 select * from t1
-where d>1 and a=d and
-(a,c) in
+where t1_d>1 and t1_a=t1_d and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 4	2	24	4
 explain select * from t1
-where d>1 and a=d and
-(a,c) in
+where t1_d>1 and t1_a=t1_d and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.a,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where d>1 and a=d and
-(a,c) in
+where t1_d>1 and t1_a=t1_d and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -1306,7 +1310,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "t1.d = t1.a and t1.a > 1 and t1.a is not null and t1.c is not null"
+      "attached_condition": "t1.t1_d = t1.t1_a and t1.t1_a > 1 and t1.t1_a is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -1314,8 +1318,8 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["e", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.c"],
+      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
@@ -1328,7 +1332,7 @@ EXPLAIN
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5 and t2.e > 1"
+              "attached_condition": "t2.t2_e < 5 and t2.t2_e > 1"
             }
           }
         }
@@ -1338,51 +1342,51 @@ EXPLAIN
 }
 # using view in subquery definition : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where a<3 and
-(a,c) in
+where t1_a<3 and
+(t1_a,t1_c) in
 (
-select x,max(y)
+select v1_x,max(v1_y)
 from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 2	1	15	4
 select * from t1
-where a<3 and
-(a,c) in
+where t1_a<3 and
+(t1_a,t1_c) in
 (
-select x,max(y)
+select v1_x,max(v1_y)
 from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 2	1	15	4
 explain select * from t1
-where a<3 and
-(a,c) in
+where t1_a<3 and
+(t1_a,t1_c) in
 (
-select x,max(y)
+select v1_x,max(v1_y)
 from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.a,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t1.t1_a,test.t1.t1_c	1	
 2	MATERIALIZED	t3	ALL	NULL	NULL	NULL	NULL	8	Using where; Using temporary
 explain format=json select * from t1
-where a<3 and
-(a,c) in
+where t1_a<3 and
+(t1_a,t1_c) in
 (
-select x,max(y)
+select v1_x,max(v1_y)
 from v1
-where x>1
-group by x
+where v1_x>1
+group by v1_x
 )
 ;
 EXPLAIN
@@ -1394,7 +1398,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "t1.a < 3 and t1.a is not null and t1.c is not null"
+      "attached_condition": "t1.t1_a < 3 and t1.t1_a is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -1402,8 +1406,8 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["x", "max(y)"],
-      "ref": ["test.t1.a", "test.t1.c"],
+      "used_key_parts": ["v1_x", "max(v1_y)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
@@ -1416,7 +1420,7 @@ EXPLAIN
               "access_type": "ALL",
               "rows": 8,
               "filtered": 100,
-              "attached_condition": "t3.x > 1 and t3.x <= 3 and t3.x < 3"
+              "attached_condition": "t3.t3_x > 1 and t3.t3_x <= 3 and t3.t3_x < 3"
             }
           }
         }
@@ -1426,54 +1430,54 @@ EXPLAIN
 }
 # using equality : pushing into WHERE
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1,v1
-where t1.a=v1.x and v1.x<2 and v1.y>30 and
-(t1.a,t1.c) in
+where t1_a=v1_x and v1_x<2 and v1_y>30 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d	x	y
+t1_a	t1_b	t1_c	t1_d	v1_x	v1_y
 1	3	40	1	1	35
 1	2	40	2	1	35
 select * from t1,v1
-where t1.a=v1.x and v1.x<2 and v1.y>30 and
-(t1.a,t1.c) in
+where t1_a=v1_x and v1_x<2 and v1_y>30 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d	x	y
+t1_a	t1_b	t1_c	t1_d	v1_x	v1_y
 1	3	40	1	1	35
 1	2	40	2	1	35
 explain select * from t1,v1
-where t1.a=v1.x and v1.x<2 and v1.y>30 and
-(t1.a,t1.c) in
+where t1_a=v1_x and v1_x<2 and v1_y>30 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	8	Using where
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where; Using join buffer (flat, BNL join)
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t3.x,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	8	test.t3.t3_x,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1,v1
-where t1.a=v1.x and v1.x<2 and v1.y>30 and
-(t1.a,t1.c) in
+where t1_a=v1_x and v1_x<2 and v1_y>30 and
+(t1_a,t1_c) in
 (
-select e,max(g)
+select t2_e,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -1485,7 +1489,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 8,
       "filtered": 100,
-      "attached_condition": "t3.x < 2 and t3.y > 30 and t3.x <= 3 and t3.x is not null"
+      "attached_condition": "t3.t3_x < 2 and t3.t3_y > 30 and t3.t3_x <= 3 and t3.t3_x is not null"
     },
     "block-nl-join": {
       "table": {
@@ -1497,7 +1501,7 @@ EXPLAIN
       "buffer_type": "flat",
       "buffer_size": "256Kb",
       "join_type": "BNL",
-      "attached_condition": "t1.a = t3.x and t1.c is not null"
+      "attached_condition": "t1.t1_a = t3.t3_x and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -1505,8 +1509,8 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "8",
-      "used_key_parts": ["e", "max(g)"],
-      "ref": ["test.t3.x", "test.t1.c"],
+      "used_key_parts": ["t2_e", "max(t2_g)"],
+      "ref": ["test.t3.t3_x", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
@@ -1519,7 +1523,7 @@ EXPLAIN
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5 and t2.e <= 3"
+              "attached_condition": "t2.t2_e < 5 and t2.t2_e <= 3"
             }
           }
         }
@@ -1530,51 +1534,51 @@ EXPLAIN
 # conjunctive subformula : pushing into WHERE
 # extracted or formula : pushing into HAVING
 set statement optimizer_switch='condition_pushdown_for_subquery=off' for select * from t1
-where ((b<3 or b=4) and a<3) and
-(a,b,c) in
+where ((t1_b<3 or t1_b=4) and t1_a<3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	2	40	2
 select * from t1
-where ((b<3 or b=4) and a<3) and
-(a,b,c) in
+where ((t1_b<3 or t1_b=4) and t1_a<3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
-a	b	c	d
+t1_a	t1_b	t1_c	t1_d
 1	2	40	2
 explain select * from t1
-where ((b<3 or b=4) and a<3) and
-(a,b,c) in
+where ((t1_b<3 or t1_b=4) and t1_a<3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	12	Using where
-1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.a,test.t1.b,test.t1.c	1	
+1	PRIMARY	<subquery2>	eq_ref	distinct_key	distinct_key	12	test.t1.t1_a,test.t1.t1_b,test.t1.t1_c	1	
 2	MATERIALIZED	t2	ALL	NULL	NULL	NULL	NULL	12	Using where; Using temporary
 explain format=json select * from t1
-where ((b<3 or b=4) and a<3) and
-(a,b,c) in
+where ((t1_b<3 or t1_b=4) and t1_a<3) and
+(t1_a,t1_b,t1_c) in
 (
-select e,f,max(g)
+select t2_e,t2_f,max(t2_g)
 from t2
-where e<5
-group by e
+where t2_e<5
+group by t2_e
 )
 ;
 EXPLAIN
@@ -1586,7 +1590,7 @@ EXPLAIN
       "access_type": "ALL",
       "rows": 12,
       "filtered": 100,
-      "attached_condition": "(t1.b < 3 or t1.b = 4) and t1.a < 3 and t1.a is not null and t1.b is not null and t1.c is not null"
+      "attached_condition": "(t1.t1_b < 3 or t1.t1_b = 4) and t1.t1_a < 3 and t1.t1_a is not null and t1.t1_b is not null and t1.t1_c is not null"
     },
     "table": {
       "table_name": "<subquery2>",
@@ -1594,22 +1598,22 @@ EXPLAIN
       "possible_keys": ["distinct_key"],
       "key": "distinct_key",
       "key_length": "12",
-      "used_key_parts": ["e", "f", "max(g)"],
-      "ref": ["test.t1.a", "test.t1.b", "test.t1.c"],
+      "used_key_parts": ["t2_e", "t2_f", "max(t2_g)"],
+      "ref": ["test.t1.t1_a", "test.t1.t1_b", "test.t1.t1_c"],
       "rows": 1,
       "filtered": 100,
       "materialized": {
         "unique": 1,
         "query_block": {
           "select_id": 2,
-          "having_condition": "t2.f < 3 or t2.f = 4",
+          "having_condition": "t2.t2_f < 3 or t2.t2_f = 4",
           "temporary_table": {
             "table": {
               "table_name": "t2",
               "access_type": "ALL",
               "rows": 12,
               "filtered": 100,
-              "attached_condition": "t2.e < 5 and t2.e < 3"
+              "attached_condition": "t2.t2_e < 5 and t2.t2_e < 3"
             }
           }
         }
diff --git a/mysql-test/t/in_subq_cond_pushdown.test b/mysql-test/t/in_subq_cond_pushdown.test
index a022491..a40ba1a 100644
--- a/mysql-test/t/in_subq_cond_pushdown.test
+++ b/mysql-test/t/in_subq_cond_pushdown.test
@@ -1,8 +1,8 @@
 let $no_pushdown= set statement optimizer_switch='condition_pushdown_for_subquery=off' for; 
 
-create table t1 (a int, b int, c int, d int);
-create table t2 (e int, f int, g int);
-create table t3 (x int, y int);
+create table t1 (t1_a int, t1_b int, t1_c int, t1_d int);
+create table t2 (t2_e int, t2_f int, t2_g int);
+create table t3 (t3_x int, t3_y int);
 
 insert into t1 values
 (1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4),
@@ -20,14 +20,14 @@ insert into t3 values
 
 create view v1 as
 (
-  select * from t3 where x<=3
+  select t3_x as v1_x, t3_y as v1_y from t3 where t3_x<=3
 );
 
 --echo # conjunctive subformula : pushing into HAVING
 let $query=
 select * from t1
-where c<25 and
-  (a,c) in (select e,max(g) from t2 where e<5 group by e);
+where t1_c<25 and
+  (t1_a,t1_c) in (select t2_e,max(t2_g) from t2 where t2_e<5 group by t2_e);
 
 eval $no_pushdown $query;
 eval $query;
@@ -37,13 +37,13 @@ eval explain format=json $query;
 --echo # extracted and formula : pushing into HAVING
 let $query=
 select * from t1
-where c>55 and b<4 and
-  (a,b,c) in
+where t1_c>55 and t1_b<4 and
+  (t1_a,t1_b,t1_c) in
   (
-    select e,f,max(g)
+    select t2_e,t2_f,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -55,13 +55,13 @@ eval explain format=json $query;
 --echo # extracted or formula : pushing into HAVING
 let $query=
 select * from t1
-where (c>60 or c<25) and
-  (a,b,c) in
+where (t1_c>60 or t1_c<25) and
+  (t1_a,t1_b,t1_c) in
   (
-    select e,f,max(g)
+    select t2_e,t2_f,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -73,13 +73,13 @@ eval explain format=json $query;
 --echo # extracted and-or formula : pushing into HAVING
 let $query=
 select * from t1
-where ((c>60 or c<25) and b>2) and
-  (a,b,c) in
+where ((t1_c>60 or t1_c<25) and t1_b>2) and
+  (t1_a,t1_b,t1_c) in
   (
-    select e,f,max(g)
+    select t2_e,t2_f,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -91,13 +91,13 @@ eval explain format=json $query;
 --echo # conjunctive subformula : pushing into HAVING
 let $query=
 select * from t1
-where ((a<2 or d>2) and b>3) and
-  (a,b,c) in
+where ((t1_a<2 or t1_d>3) and t1_b>1) and
+  (t1_a,t1_b,t1_c) in
   (
-    select e,f,max(g)
+    select t2_e,t2_f,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -109,13 +109,13 @@ eval explain format=json $query;
 --echo # using view in subquery definition : pushing into HAVING
 let $query=
 select * from t1
-where c>20 and
-  (a,c) in
+where t1_c>20 and
+  (t1_a,t1_c) in
   (
-    select x,max(y)
+    select v1_x,max(v1_y)
     from v1
-    where x>1
-    group by x
+    where v1_x>1
+    group by v1_x
   )
 ;
 
@@ -127,13 +127,13 @@ eval explain format=json $query;
 --echo # using equality : pushing into WHERE
 let $query=
 select * from t1,v1
-where c>20 and c=y and
-  (a,c) in
+where t1_c>20 and t1_c=v1_y and
+  (t1_a,t1_c) in
   (
-    select e,max(g)
+    select t2_e,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -145,13 +145,13 @@ eval explain format=json $query;
 --echo # conjunctive subformula : pushing into WHERE
 let $query=
 select * from t1
-where a<2 and
-  (a,c) in
+where t1_a<2 and
+  (t1_a,t1_c) in
   (
-    select e,max(g)
+    select t2_e,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -163,13 +163,13 @@ eval explain format=json $query;
 --echo # extracted and formula : pushing into WHERE
 let $query=
 select * from t1
-where a>2 and a<5 and
-  (a,c) in
+where t1_a>2 and t1_a<5 and
+  (t1_a,t1_c) in
   (
-    select e,max(g)
+    select t2_e,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -181,13 +181,13 @@ eval explain format=json $query;
 --echo # extracted or formula : pushing into WHERE
 let $query=
 select * from t1
-where (a<2 or a>=4) and
-  (a,c) in
+where (t1_a<2 or t1_a>=4) and
+  (t1_a,t1_c) in
   (
-    select e,max(g)
+    select t2_e,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -199,13 +199,13 @@ eval explain format=json $query;
 --echo # extracted and-or formula : pushing into WHERE
 let $query=
 select * from t1
-where ((a<2 or a=5) and b>3) and
-  (a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+  (t1_a,t1_b,t1_c) in
   (
-    select e,f,max(g)
+    select t2_e,t2_f,max(t2_g)
     from t2
-    where e<5
-    group by e,f
+    where t2_e<5
+    group by t2_e,t2_f
   )
 ;
 
@@ -217,13 +217,13 @@ eval explain format=json $query;
 --echo # extracted and-or formula : pushing into WHERE
 let $query=
 select * from t1
-where ((a<2 or a=5) and b>3) and
-  (a,b,c) in
+where ((t1_a<2 or t1_a=5) and t1_b>3) and
+  (t1_a,t1_b,t1_c) in
   (
-    select e,f,max(g)
+    select t2_e,t2_f,max(t2_g)
     from t2
-    where e<5
-    group by e,f
+    where t2_e<5
+    group by t2_e,t2_f
   )
 ;
 
@@ -235,13 +235,13 @@ eval explain format=json $query;
 --echo # conjunctive subformula : pushing into WHERE
 let $query=
 select * from t1
-where ((b<3 or d>2) and a<2) and
-  (a,b,c) in
+where ((t1_b<3 or t1_d>2) and t1_a<2) and
+  (t1_a,t1_b,t1_c) in
   (
-    select e,f,max(g)
+    select t2_e,t2_f,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -253,13 +253,13 @@ eval explain format=json $query;
 --echo # using equalities : pushing into WHERE
 let $query=
 select * from t1
-where d=1 and a=d and
-  (a,c) in
+where t1_d=1 and t1_a=t1_d and
+  (t1_a,t1_c) in
   (
-    select e,max(g)
+    select t2_e,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -271,13 +271,13 @@ eval explain format=json $query;
 --echo # using equality : pushing into WHERE
 let $query=
 select * from t1
-where d>1 and a=d and
-  (a,c) in
+where t1_d>1 and t1_a=t1_d and
+  (t1_a,t1_c) in
   (
-    select e,max(g)
+    select t2_e,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -289,13 +289,13 @@ eval explain format=json $query;
 --echo # using view in subquery definition : pushing into WHERE
 let $query=
 select * from t1
-where a<3 and
-  (a,c) in
+where t1_a<3 and
+  (t1_a,t1_c) in
   (
-    select x,max(y)
+    select v1_x,max(v1_y)
     from v1
-    where x>1
-    group by x
+    where v1_x>1
+    group by v1_x
   )
 ;
 
@@ -307,13 +307,13 @@ eval explain format=json $query;
 --echo # using equality : pushing into WHERE
 let $query=
 select * from t1,v1
-where t1.a=v1.x and v1.x<2 and v1.y>30 and
-  (t1.a,t1.c) in
+where t1_a=v1_x and v1_x<2 and v1_y>30 and
+  (t1_a,t1_c) in
   (
-    select e,max(g)
+    select t2_e,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
@@ -326,13 +326,13 @@ eval explain format=json $query;
 --echo # extracted or formula : pushing into HAVING
 let $query=
 select * from t1
-where ((b<3 or b=4) and a<3) and
-  (a,b,c) in
+where ((t1_b<3 or t1_b=4) and t1_a<3) and
+  (t1_a,t1_b,t1_c) in
   (
-    select e,f,max(g)
+    select t2_e,t2_f,max(t2_g)
     from t2
-    where e<5
-    group by e
+    where t2_e<5
+    group by t2_e
   )
 ;
 
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index c3a392c..49747e4 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -6495,7 +6495,7 @@ Item *Item_direct_view_ref::in_subq_field_transformer_for_having(THD *thd,
     Find fields that are used in the GROUP BY of the select
 
   @param thd     the thread handle
-  @param sel     the select of the IN subquery predicate select
+  @param sel     the select of the IN subquery predicate
   @param fields  fields of the left part of the IN subquery predicate
 
   @details
@@ -6568,7 +6568,7 @@ void grouping_fields_in_the_in_subq_left_part(THD *thd,
        right_part and delete cond_where from the extr.
     5. Transforms extr so it can be pushed into the HAVING clause of the right_part
   @note
-    This method is similar with pushdown_cond_for_derived
+    This method is similar with pushdown_cond_for_derived()
 
   @retval TRUE   if an error occurs
   @retval FALSE  otherwise
@@ -6591,10 +6591,9 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
   /*
     Create the list of In_subq_field items for this IN subquery:
     it consists of the pairs of fields from the left part of the IN subquery
-    'left_part' and the respective fields from the right part of the
-    IN subquery (fields from the projections list of the select of the right
-    part of the IN subquery 'right_part' that stay on the same places in
-    the list of projections as the fields from the left_part).
+    'left_part' and the respective fields from the select of the right part of
+    the IN subquery 'right_part' (fields that stay on the same places as fields
+    of left_part in the projection list of right_part).
   */
   comparable_fields.empty();
   List_iterator_fast<Item> it(sel->join->fields_list);
@@ -6611,7 +6610,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
   }
 
   /*
-    Build the new condition from the cond that can be pushed into sel
+    Build the new condition from cond that can be pushed into sel
   */
   Item *extracted_cond;
   cond->check_pushable_cond(0, this);
@@ -6658,7 +6657,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
   }
 
   /*
-    Checks what can be pushed into the WHERE clause of the sel from the
+    Checks what can be pushed into the WHERE clause of sel from the
     extracted condition
   */
   Item *cond_over_grouping_fields;
@@ -6670,7 +6669,7 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
 
   /*
     Transforms the references to the left_part fields so they can be pushed
-    into the sel of the WHERE clause.
+    into sel of the WHERE clause.
   */
   if (cond_over_grouping_fields)
       cond_over_grouping_fields=
@@ -6681,7 +6680,8 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
   if (cond_over_grouping_fields)
   {
     /*
-      Removes from extracted_cond all parts that can be pushed into the WHERE clause
+      Removes from extracted_cond all parts that can be pushed into the
+      WHERE clause
     */
     extracted_cond= remove_pushed_top_conjuncts(thd, extracted_cond);
 
@@ -6693,8 +6693,8 @@ bool Item_in_subselect::pushdown_cond_for_in_subquery(THD *thd, Item *cond)
       goto exit;
   }
   /*
-    Transforms the references to the left_part fields so they can be pushed into
-    the sel of the HAVING clause
+    Transforms the references to the left_part fields so the transformed
+    condition can be pushed into sel of the HAVING clause
   */
   extracted_cond= extracted_cond->transform(thd,
                          &Item::in_subq_field_transformer_for_having,


More information about the commits mailing list