Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25362

Incorrect name resolution for subqueries in ON expressions

    XMLWordPrintable

    Details

      Description

      (I've found this issue when fixing MDEV-25202. the fix is a part of MDEV-25202 fix )
      ON expressions inside a join nest cannot refer to tables outside the nest. This can be "circumvented" by putting the outside reference into a subquery.

      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);
      

      A reference to t1.a in the ON expression produces an error, as expected:

      explain 
      select * 
      from 
        t1 left join 
         (t2 
          join
          t3 on 
          (t3.f=t1.a)
         ) on (t2.c=t1.a );
      

      ERROR 1054 (42S22): Unknown column 't1.a' in 'on clause'
      

      A reference from a subquery does not produce an 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 );
      

      This is wrong/

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              psergey Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: