MDEV-25362 Incorrect name resolution for subqueries in ON expressionsprot-10.2

This patch sets the proper name resolution context for outer references
used in a subquery from an ON clause. Usually this context is more narrow
than the name resolution context of the parent select that were used before
this fix.
This fix revealed another problem that concerned ON expressions used in
from clauses of specifications of derived tables / views / CTEs. The name
resolution outer context for such ON expression must be set to NULL to
prevent name resolution beyond the derived table where it is used.
The solution to resolve this problem applied in sql_derived.cc was provided
by Sergei Petrunia <sergey@mariadb.com>.

The change in sql_parse.cc is not good for 10.4+. A corresponding diff for
10.4+ will be provided in JIRA entry for this bug.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

1 files changed, 50 insertions, 0 deletions

diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test
index c5b2c985ff7..9ad2c48bfca 100644
--- a/mysql-test/t/join_outer.test
+++ b/mysql-test/t/join_outer.test
@@ -2190,4 +2190,54 @@ DROP TABLE t1,t2,t3,t4;
 
 --echo # end of 10.1 tests
 
+--echo #
+--echo # MDEV-25362: name resolution for subqueries in ON expressions
+--echo #
+
+create table t1 (a int, b int);
+create table t2 (c int, d int);
+create table t3 (e int, f int);
+create table t4 (g int, h int);
+
+--error ER_BAD_FIELD_ERROR
+explain
+select *
+from
+  t1 left join
+   (t2
+    join
+    t3 on
+    (t3.f=t1.a)
+   ) on (t2.c=t1.a );
+
+# This must produce an error:
+--error ER_BAD_FIELD_ERROR
+explain
+select *
+from
+  t1 left join
+   (t2
+    join
+    t3 on
+    (t3.f=(select max(g) from t4 where t4.h=t1.a))
+   ) on (t2.c=t1.a );
+
+drop table t1,t2,t3,t4;
+
+create table t1 (a int);
+insert into t1 values (1),(2);
+create table t2 (b int);
+insert into t2 values (1),(2);
+create table t3 (c int);
+insert into t3 values (1),(2);
+
+--error ER_BAD_FIELD_ERROR
+select * from ( select * from t1 left join t2
+                              on b in (select x from t3 as sq1)
+              ) as sq2;
+
+drop table t1,t2,t3;
+
+--echo # end of 10.2 tests
+
 SET optimizer_switch=@org_optimizer_switch;