[MDEV-20265] Unknown column in field list Created: 2019-08-06  Updated: 2019-09-09  Resolved: 2019-08-31

Status: Closed
Project: MariaDB Server
Component/s: Parser
Affects Version/s: 5.5, 10.1, 10.2.26, 10.2, 10.3, 10.4
Fix Version/s: 10.2.27, 5.5.66, 10.1.42, 10.3.18, 10.4.8

Type: Bug Priority: Blocker
Reporter: Silver Asu Assignee: Igor Babaev
Resolution: Fixed Votes: 1
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-20330 Combination of "," (comma), cross joi... Closed
is duplicated by MDEV-20539 With more than one CROSS JOIN columns... Closed

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



 Comments   
Comment by Alice Sherepa [ 2019-08-06 ]

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'

Comment by Silver Asu [ 2019-08-06 ]

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)

Comment by Igor Babaev [ 2019-08-12 ]

The same bug can be reproduced in 5.5.

Comment by Oleksandr Byelkin [ 2019-08-19 ]

OK to push

Comment by Igor Babaev [ 2019-08-20 ]

A fix for this bug was pushed into 5.5.

Comment by Marko Mäkelä [ 2019-08-22 ]

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.

Comment by Marko Mäkelä [ 2019-08-23 ]

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.

Comment by Igor Babaev [ 2019-08-31 ]

Pushed a fix for this bug that was good for 10.4

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