MDEV-20265 Unknown column in field list

This patch corrects the fix of the patch for mdev-19421 that resolved
the problem of parsing some embedded join expressions such as
  t1 join t2 left join t3 on t2.a=t3.a on t1.a=t2.a.
Yet the patch contained a bug that prevented proper context analysis
of the queries where such expressions were used together with comma
separated table references in from clauses.

2 files changed, 385 insertions, 0 deletions

diff --git a/mysql-test/r/join.result b/mysql-test/r/join.result
index 6b051acc108..cb2d00664e2 100644
--- a/mysql-test/r/join.result
+++ b/mysql-test/r/join.result
@@ -2921,5 +2921,331 @@ NULL	NULL	NULL	9
 NULL	NULL	NULL	5
 drop table t1,t2,t3,t4,s1,s2;
 #
+# MDEV-20265: Mix of comma joins with JOIN expressions
+#             (correction of the fix for MDEV-19421)
+# MDEV-20330: duplicate
+#
+create table t1 (a int);
+insert into t1 values (7), (5), (3);
+create table t2 (a int);
+insert into t2 values (5), (1), (7);
+create table t3 (a int);
+insert into t3 values (2), (7), (3);
+create table t4 (a int);
+insert into t4 values (4), (7), (9), (5);
+create table t5 (a int);
+insert into t5 values (3), (7), (9), (2);
+explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a
+from t1, t2 join t3 left join t4 on t3.a=t4.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (flat, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` left join `test`.`t4` on((`test`.`t4`.`a` = `test`.`t3`.`a`)) where 1
+select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a
+from t1, t2 join t3 left join t4 on t3.a=t4.a;
+t1_a	t2_a	t3_a	t4_a
+7	5	7	7
+5	5	7	7
+3	5	7	7
+7	1	7	7
+5	1	7	7
+3	1	7	7
+7	7	7	7
+5	7	7	7
+3	7	7	7
+7	5	2	NULL
+5	5	2	NULL
+3	5	2	NULL
+7	1	2	NULL
+5	1	2	NULL
+3	1	2	NULL
+7	7	2	NULL
+5	7	2	NULL
+3	7	2	NULL
+7	5	3	NULL
+5	5	3	NULL
+3	5	3	NULL
+7	1	3	NULL
+5	1	3	NULL
+3	1	3	NULL
+7	7	3	NULL
+5	7	3	NULL
+3	7	3	NULL
+explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a
+from t1, t2 join t3 right join t4 on t3.a=t4.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	100.00	Using join buffer (flat, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a` from `test`.`t1` join `test`.`t4` left join (`test`.`t2` join `test`.`t3`) on((`test`.`t3`.`a` = `test`.`t4`.`a`)) where 1
+select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a
+from t1, t2 join t3 right join t4 on t3.a=t4.a;
+t1_a	t2_a	t3_a	t4_a
+7	5	7	7
+5	5	7	7
+3	5	7	7
+7	1	7	7
+5	1	7	7
+3	1	7	7
+7	7	7	7
+5	7	7	7
+3	7	7	7
+7	NULL	NULL	4
+5	NULL	NULL	4
+3	NULL	NULL	4
+7	NULL	NULL	9
+5	NULL	NULL	9
+3	NULL	NULL	9
+7	NULL	NULL	5
+5	NULL	NULL	5
+3	NULL	NULL	5
+explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a
+from t1, t2 join t3 join t4 left join t5 on t4.a=t5.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (flat, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	100.00	Using join buffer (incremental, BNL join)
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a`,`test`.`t5`.`a` AS `t5_a` from `test`.`t1` join `test`.`t2` join `test`.`t3` join `test`.`t4` left join `test`.`t5` on((`test`.`t5`.`a` = `test`.`t4`.`a`)) where 1
+select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a
+from t1, t2 join t3 join t4 left join t5 on t4.a=t5.a;
+t1_a	t2_a	t3_a	t4_a	t5_a
+7	5	2	7	7
+5	5	2	7	7
+3	5	2	7	7
+7	1	2	7	7
+5	1	2	7	7
+3	1	2	7	7
+7	7	2	7	7
+5	7	2	7	7
+3	7	2	7	7
+7	5	7	7	7
+5	5	7	7	7
+3	5	7	7	7
+7	1	7	7	7
+5	1	7	7	7
+3	1	7	7	7
+7	7	7	7	7
+5	7	7	7	7
+3	7	7	7	7
+7	5	3	7	7
+5	5	3	7	7
+3	5	3	7	7
+7	1	3	7	7
+5	1	3	7	7
+3	1	3	7	7
+7	7	3	7	7
+5	7	3	7	7
+3	7	3	7	7
+7	5	2	9	9
+5	5	2	9	9
+3	5	2	9	9
+7	1	2	9	9
+5	1	2	9	9
+3	1	2	9	9
+7	7	2	9	9
+5	7	2	9	9
+3	7	2	9	9
+7	5	7	9	9
+5	5	7	9	9
+3	5	7	9	9
+7	1	7	9	9
+5	1	7	9	9
+3	1	7	9	9
+7	7	7	9	9
+5	7	7	9	9
+3	7	7	9	9
+7	5	3	9	9
+5	5	3	9	9
+3	5	3	9	9
+7	1	3	9	9
+5	1	3	9	9
+3	1	3	9	9
+7	7	3	9	9
+5	7	3	9	9
+3	7	3	9	9
+7	5	2	4	NULL
+5	5	2	4	NULL
+3	5	2	4	NULL
+7	1	2	4	NULL
+5	1	2	4	NULL
+3	1	2	4	NULL
+7	7	2	4	NULL
+5	7	2	4	NULL
+3	7	2	4	NULL
+7	5	7	4	NULL
+5	5	7	4	NULL
+3	5	7	4	NULL
+7	1	7	4	NULL
+5	1	7	4	NULL
+3	1	7	4	NULL
+7	7	7	4	NULL
+5	7	7	4	NULL
+3	7	7	4	NULL
+7	5	3	4	NULL
+5	5	3	4	NULL
+3	5	3	4	NULL
+7	1	3	4	NULL
+5	1	3	4	NULL
+3	1	3	4	NULL
+7	7	3	4	NULL
+5	7	3	4	NULL
+3	7	3	4	NULL
+7	5	2	5	NULL
+5	5	2	5	NULL
+3	5	2	5	NULL
+7	1	2	5	NULL
+5	1	2	5	NULL
+3	1	2	5	NULL
+7	7	2	5	NULL
+5	7	2	5	NULL
+3	7	2	5	NULL
+7	5	7	5	NULL
+5	5	7	5	NULL
+3	5	7	5	NULL
+7	1	7	5	NULL
+5	1	7	5	NULL
+3	1	7	5	NULL
+7	7	7	5	NULL
+5	7	7	5	NULL
+3	7	7	5	NULL
+7	5	3	5	NULL
+5	5	3	5	NULL
+3	5	3	5	NULL
+7	1	3	5	NULL
+5	1	3	5	NULL
+3	1	3	5	NULL
+7	7	3	5	NULL
+5	7	3	5	NULL
+3	7	3	5	NULL
+explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a
+from t1, t2 join t3 join t4 right join t5 on t4.a=t5.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	4	100.00	Using join buffer (flat, BNL join)
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a`,`test`.`t5`.`a` AS `t5_a` from `test`.`t1` join `test`.`t5` left join (`test`.`t2` join `test`.`t3` join `test`.`t4`) on((`test`.`t4`.`a` = `test`.`t5`.`a`)) where 1
+select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a
+from t1, t2 join t3 join t4 right join t5 on t4.a=t5.a;
+t1_a	t2_a	t3_a	t4_a	t5_a
+7	5	2	7	7
+5	5	2	7	7
+3	5	2	7	7
+7	1	2	7	7
+5	1	2	7	7
+3	1	2	7	7
+7	7	2	7	7
+5	7	2	7	7
+3	7	2	7	7
+7	5	7	7	7
+5	5	7	7	7
+3	5	7	7	7
+7	1	7	7	7
+5	1	7	7	7
+3	1	7	7	7
+7	7	7	7	7
+5	7	7	7	7
+3	7	7	7	7
+7	5	3	7	7
+5	5	3	7	7
+3	5	3	7	7
+7	1	3	7	7
+5	1	3	7	7
+3	1	3	7	7
+7	7	3	7	7
+5	7	3	7	7
+3	7	3	7	7
+7	5	2	9	9
+5	5	2	9	9
+3	5	2	9	9
+7	1	2	9	9
+5	1	2	9	9
+3	1	2	9	9
+7	7	2	9	9
+5	7	2	9	9
+3	7	2	9	9
+7	5	7	9	9
+5	5	7	9	9
+3	5	7	9	9
+7	1	7	9	9
+5	1	7	9	9
+3	1	7	9	9
+7	7	7	9	9
+5	7	7	9	9
+3	7	7	9	9
+7	5	3	9	9
+5	5	3	9	9
+3	5	3	9	9
+7	1	3	9	9
+5	1	3	9	9
+3	1	3	9	9
+7	7	3	9	9
+5	7	3	9	9
+3	7	3	9	9
+7	NULL	NULL	NULL	3
+5	NULL	NULL	NULL	3
+3	NULL	NULL	NULL	3
+7	NULL	NULL	NULL	2
+5	NULL	NULL	NULL	2
+3	NULL	NULL	NULL	2
+explain extended select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a
+from t1 left join t2 on t1.a=t2.a, t3 join t4 right join t5 on t4.a=t5.a;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	3	100.00	
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
+1	SIMPLE	t5	ALL	NULL	NULL	NULL	NULL	4	100.00	Using join buffer (incremental, BNL join)
+1	SIMPLE	t3	ALL	NULL	NULL	NULL	NULL	3	100.00	Using join buffer (incremental, BNL join)
+1	SIMPLE	t4	ALL	NULL	NULL	NULL	NULL	4	100.00	Using where; Using join buffer (incremental, BNL join)
+Warnings:
+Note	1003	select `test`.`t1`.`a` AS `t1_a`,`test`.`t2`.`a` AS `t2_a`,`test`.`t3`.`a` AS `t3_a`,`test`.`t4`.`a` AS `t4_a`,`test`.`t5`.`a` AS `t5_a` from `test`.`t1` left join `test`.`t2` on((`test`.`t2`.`a` = `test`.`t1`.`a`)) join `test`.`t5` left join (`test`.`t3` join `test`.`t4`) on((`test`.`t4`.`a` = `test`.`t5`.`a`)) where 1
+select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a
+from t1 left join t2 on t1.a=t2.a, t3 join t4 right join t5 on t4.a=t5.a;
+t1_a	t2_a	t3_a	t4_a	t5_a
+5	5	2	7	7
+7	7	2	7	7
+3	NULL	2	7	7
+5	5	7	7	7
+7	7	7	7	7
+3	NULL	7	7	7
+5	5	3	7	7
+7	7	3	7	7
+3	NULL	3	7	7
+5	5	2	9	9
+7	7	2	9	9
+3	NULL	2	9	9
+5	5	7	9	9
+7	7	7	9	9
+3	NULL	7	9	9
+5	5	3	9	9
+7	7	3	9	9
+3	NULL	3	9	9
+5	5	NULL	NULL	3
+7	7	NULL	NULL	3
+3	NULL	NULL	NULL	3
+5	5	NULL	NULL	2
+7	7	NULL	NULL	2
+3	NULL	NULL	NULL	2
+drop table t1,t2,t3,t4,t5;
+select a.a
+from (select 1 as a) a,
+(select 2 as b) b
+cross join
+(select 3 as c) c
+left join
+(select 4 as d) d
+on 1;
+a
+1
+#
 # End of MariaDB 5.5 tests
 #
diff --git a/mysql-test/t/join.test b/mysql-test/t/join.test
index 168cfe4e477..c24fe3bf577 100644
--- a/mysql-test/t/join.test
+++ b/mysql-test/t/join.test
@@ -1614,5 +1614,64 @@ eval $q;
 drop table t1,t2,t3,t4,s1,s2;
 
 --echo #
+--echo # MDEV-20265: Mix of comma joins with JOIN expressions
+--echo #             (correction of the fix for MDEV-19421)
+--echo # MDEV-20330: duplicate
+--echo #
+
+create table t1 (a int);
+insert into t1 values (7), (5), (3);
+create table t2 (a int);
+insert into t2 values (5), (1), (7);
+create table t3 (a int);
+insert into t3 values (2), (7), (3);
+create table t4 (a int);
+insert into t4 values (4), (7), (9), (5);
+create table t5 (a int);
+insert into t5 values (3), (7), (9), (2);
+
+
+let $q=
+select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a
+from t1, t2 join t3 left join t4 on t3.a=t4.a;
+eval explain extended $q;
+eval $q;
+
+let $q=
+select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a
+from t1, t2 join t3 right join t4 on t3.a=t4.a;
+eval explain extended $q;
+eval $q;
+
+let $q=
+select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a
+from t1, t2 join t3 join t4 left join t5 on t4.a=t5.a;
+eval explain extended $q;
+eval $q;
+
+let $q=
+select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a
+from t1, t2 join t3 join t4 right join t5 on t4.a=t5.a;
+eval explain extended $q;
+eval $q;
+
+let $q=
+select t1.a as t1_a, t2.a as t2_a, t3.a as t3_a, t4.a as t4_a, t5.a as t5_a
+from t1 left join t2 on t1.a=t2.a, t3 join t4 right join t5 on t4.a=t5.a;
+eval explain extended $q;
+eval $q;
+
+drop table t1,t2,t3,t4,t5;
+
+select a.a
+from (select 1 as a) a,
+     (select 2 as b) b
+     cross join
+     (select 3 as c) c
+     left join
+     (select 4 as d) d
+     on 1;
+
+--echo #
 --echo # End of MariaDB 5.5 tests
 --echo #