[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'") Created: 2019-09-08  Updated: 2019-09-09  Resolved: 2019-09-09

Status: Closed
Project: MariaDB Server
Component/s: N/A
Affects Version/s: 10.1.41
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Clemens Robbenhaar Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Environment:

Debian/Linux 9.10; Kernel 4.9.0-11-amd64; MariaDB Server version 10.1.41-MariaDB-0+deb9u1

Issue Links:
Duplicate
duplicates MDEV-20265 Unknown column in field list Closed

 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?

 Comments   
Comment by Alice Sherepa [ 2019-09-09 ]

thanks! it was fixed by e746f451d57def4be6 commit by Igor Babaev:

commit e746f451d57def4be679caafc29976741b3e89f7
 
Author: Igor Babaev <igor@askmonty.org>
 
Date:   Thu Aug 15 17:27:49 2019 -0700
 
 
 
    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.

Comment by Clemens Robbenhaar [ 2019-09-09 ]

Oh, I missed that issue and did not notice mine was a duplicate.
Thanks alice for handling my report, and thanks to igor for the fix!

Generated at Thu Feb 08 09:00:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.