Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. 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'")

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Duplicate
    • Affects Version/s: 10.1.41
    • Fix Version/s: N/A
    • Component/s: N/A
    • Labels:
      None
    • Environment:
      Debian/Linux 9.10; Kernel 4.9.0-11-amd64; MariaDB Server version 10.1.41-MariaDB-0+deb9u1

      Description

      Yesterday I upgraded a Debian/Linux box from 9.9 to 9.10, which causes MariaDB to be upgraded from 10.1.38-MariaDB to 10.1.41-MariaDB-0+deb9u1

      As a result SELECT queries fail that reference columns of a table in their SELECT or WHERE clause, if the statement has at least two "CROSS JOIN" clauses and the table in question is referenced before these two "CROSS JOIN" statements.

      A minimal example looks like this:

      MariaDB [mariab_test]> CREATE TABLE `a` (`id` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
      Query OK, 0 rows affected (0.01 sec)
       
      MariaDB [mariab_test]> INSERT INTO a VALUES (1);
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [mariab_test]> SELECT a.id FROM a AS a1 CROSS JOIN a AS a2 CROSS JOIN a AS a3, a;
      +------+
      | id   |
      +------+
      |    1 |
      +------+
      1 row in set (0.00 sec)
       
      MariaDB [mariab_test]> SELECT a.* FROM a, a AS a1 CROSS JOIN a AS a2 CROSS JOIN a AS a3;
      +------+
      | id   |
      +------+
      |    1 |
      +------+
      1 row in set (0.00 sec)
       
      MariaDB [mariab_test]> SELECT a.id FROM a, a AS a1 CROSS JOIN a AS a2 CROSS JOIN a AS a3;
      ERROR 1054 (42S22): Unknown column 'a.id' in 'field list'
      MariaDB [mariab_test]> SELECT * FROM a, a AS a1 CROSS JOIN a AS a2 CROSS JOIN a AS a3 WHERE a.id=1;
      ERROR 1054 (42S22): Unknown column 'a.id' in 'where clause'
      MariaDB [mariab_test]> 
      

      Only the last two statements produce the error message; the other ones are included to show that with slightly different statements the produced results are as expected.

      Can someone reproduce this with a plain 10.1.41 version of MariaDB, or is this specific to the Debian package?

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned Unassigned
              Reporter:
              crobbenhaar Clemens Robbenhaar
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.