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

Unknown column in field list

Details

    Description

      This query gives Unknown column 't12.a' in 'field list' error after upgrade to 10.2.26 from 10.2.25.

      SELECT
      		 
          t12.a
      		 
      FROM
      		 
          `t1` AS t11,
      		 
          `t2` AS t12,
      		 
          `t3` AS t8
      		 
      JOIN `t4` AS t10
      		 
      LEFT JOIN `t5` AS t9
      		 
      ON
      		 
          t9.`a` = t10.`a`
      		 
      WHERE
      		 
          t10.`a` = t11.`a` AND t10.`a` = t12.`a` AND t8.`a` = t11.`a`;

      Sample data:

      create table t1 (a int);
      		 
      insert into t1 values (7), (2), (9);
      		 
      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 (2), (7), (3);
      		 
      create table t5 (a int);
      		 
      insert into t5 values (2), (7), (3);

      Attachments

        Issue Links

          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20330 Combination of "," (comma), cross join and left join fails to parse (regression since 10.1.40)

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20539 With more than one CROSS JOIN columns of tables mentioned earlier in the FROM clause cannot be referenced in SELECT or WHERE clause (Error message: "Unknown column 'table.column' in 'where clause'")

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            alice Alice Sherepa added a comment - - edited

            I confirm that behavior changed in 10.1-10.4, query returns result in 5.5,10.0
            I am not sure if Standart allows such a query (MySQL 8.0.16 returned results, postgres 9.6 returned error)
            Error message "Unknown column .. in 'field list'" is misleading.

            create table t2 (a int);
            		 
            insert into t2 values (5), (1), (7);
            		 
             
            		 
            create table t3 as select * from t2;
            		 
            create table t4 as select * from t2;
            		 
            create table t5 as select * from t2;
            		 
             
            		 
            SELECT t2.a FROM  t2,t3  JOIN t4 left JOIN t5 ON t5.a = t4.a; 


            mysqltest: At line 25: query 'SELECT t2.a FROM  t2,t3  JOIN t4 left JOIN t5 ON t5.a = t4.a' failed: 1054: Unknown column 't2.a' in 'field list'

            alice Alice Sherepa added a comment - - edited I confirm that behavior changed in 10.1-10.4, query returns result in 5.5,10.0 I am not sure if Standart allows such a query (MySQL 8.0.16 returned results, postgres 9.6 returned error) Error message "Unknown column .. in 'field list'" is misleading. create table t2 (a int ); insert into t2 values (5), (1), (7);   create table t3 as select * from t2; create table t4 as select * from t2; create table t5 as select * from t2;   SELECT t2.a FROM t2,t3 JOIN t4 left JOIN t5 ON t5.a = t4.a; mysqltest: At line 25: query 'SELECT t2.a FROM t2,t3 JOIN t4 left JOIN t5 ON t5.a = t4.a' failed: 1054: Unknown column 't2.a' in 'field list'
            BB Silver Asu added a comment -

            I am pretty sure that this worked in 10.2.25.
            I have 10.2.22, where it also works.

            (root:localhost) [test4]> SELECT t2.a FROM  t2,t3  JOIN t4 left JOIN t5 ON t5.a = t4.a; 
            +------+
            		 
            | a    |
            		 
            +------+
            		 
            |    5 |
            		 
            |    5 |
            		 
            |    5 |
            		 
            |    5 |
            		 
            |    5 |
            		 
            |    5 |
            		 
            |    5 |
            		 
            |    5 |
            		 
            |    5 |
            		 
            |    1 |
            		 
            |    1 |
            		 
            |    1 |
            		 
            |    1 |
            		 
            |    1 |
            		 
            |    1 |
            		 
            |    1 |
            		 
            |    1 |
            		 
            |    1 |
            		 
            |    7 |
            		 
            |    7 |
            		 
            |    7 |
            		 
            |    7 |
            		 
            |    7 |
            		 
            |    7 |
            		 
            |    7 |
            		 
            |    7 |
            		 
            |    7 |
            		 
            +------+
            		 
            27 rows in set (0.01 sec)

            BB Silver Asu added a comment - I am pretty sure that this worked in 10.2.25. I have 10.2.22, where it also works. (root:localhost) [test4]> SELECT t2.a FROM t2,t3 JOIN t4 left JOIN t5 ON t5.a = t4.a; +------+ | a | +------+ | 5 | | 5 | | 5 | | 5 | | 5 | | 5 | | 5 | | 5 | | 5 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 1 | | 7 | | 7 | | 7 | | 7 | | 7 | | 7 | | 7 | | 7 | | 7 | +------+ 27 rows in set (0.01 sec)
            igor Igor Babaev (Inactive) added a comment -

            The same bug can be reproduced in 5.5.

            igor Igor Babaev (Inactive) added a comment - The same bug can be reproduced in 5.5.
            sanja Oleksandr Byelkin added a comment -

            OK to push

            sanja Oleksandr Byelkin added a comment - OK to push
            igor Igor Babaev (Inactive) added a comment -

            A fix for this bug was pushed into 5.5.

            igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 5.5.
            marko Marko Mäkelä added a comment -

            My merge of the fix caused crashes on 10.4. In order to be able to test the rest of the merge, I reverted the fix in the temporary bb-10.4-merge branch. This should be properly addressed before the next 10.4 release.

            marko Marko Mäkelä added a comment - My merge of the fix caused crashes on 10.4. In order to be able to test the rest of the merge, I reverted the fix in the temporary bb-10.4-merge branch. This should be properly addressed before the next 10.4 release.
            marko Marko Mäkelä added a comment -

            In my final merge to 10.4 I omitted this fix due to assertion failures. A separate fix will have to be implemented for 10.4.

            marko Marko Mäkelä added a comment - In my final merge to 10.4 I omitted this fix due to assertion failures. A separate fix will have to be implemented for 10.4.
            igor Igor Babaev (Inactive) added a comment -

            Pushed a fix for this bug that was good for 10.4

            igor Igor Babaev (Inactive) added a comment - Pushed a fix for this bug that was good for 10.4

            People

              igor Igor Babaev (Inactive)
              BB Silver Asu
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.