[MDEV-30472] Unresolvalble column reference in ORDER BY clause of union ignored Created: 2023-01-26  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data Manipulation - Subquery
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4, 10.5, 10.6, 10.11

Type: Bug Priority: Major
Reporter: Igor Babaev Assignee: Igor Babaev
Resolution: Unresolved Votes: 0
Labels: None


 Description   

If an unresolvalble column reference occurs in ORDER BY clause of an union contained in an EXISTs subquery of in the left part of a subquery predicand then it is ignored and no error message is reported.

Here is a test case demonstrating the problem:

create table t1 (a int, b int);
 
insert into t1 values (3,8), (7,2), (1,4), (5,9);
 
 
 
create table t2 (a int, b int);
 
insert into t2 values (9,1), (7,3), (2,6);
 
 
 
create table t3 (c int, d int);
 
insert into t3 values (7,8), (1,2), (3,8), (9,1);
 
 
 
select * from t3 as t
 
where 
  (t.c,t.d) in 
  (
 
    select a,b from t1 where t1.a > 3
 
    union
 
    select a,b from t2 where t2.b < 6
 
    order by (a - b/x)
 
  )
 
;
 
 
 
drop table t1,t2,t3;

When executing the above query we have

MariaDB [test]> select * from t3 as t
 
    -> where 
    ->   (t.c,t.d) in 
    ->   (
 
    ->     select a,b from t1 where t1.a > 3
 
    ->     union
 
    ->     select a,b from t2 where t2.b < 6
 
    ->     order by (a - b/x)
 
    ->   )
 
    -> ;
 
+------+------+
 
| c    | d    |
 
+------+------+
 
|    9 |    1 |
 
+------+------+

However the same error message as from the query

    select a,b from t1 where t1.a > 3
 
    union
 
    select a,b from t2 where t2.b < 6
 
    order by (a - b/x)

is expected

MariaDB [test]>     select a,b from t1 where t1.a > 3
 
    ->     union
 
    ->     select a,b from t2 where t2.b < 6
 
    ->     order by (a - b/x)
 
    -> ;
 
ERROR 1054 (42S22): Unknown column 'x' in 'order clause'


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