Regression since 10.11.16: ORACLE mode fails to resolve outer table alias in a correlated UNION ALL subquery

Description

A query using a correlated subquery with UNION ALL works correctly in MariaDB 10.11.15 and earlier, but fails in MariaDB 10.11.16 and later when sql_mode=ORACLE is enabled.

The query fails because the table alias from the outer query cannot be resolved inside the UNION ALL subquery.

Version comparison

10.11.14 : Works

10.11.15 : Works

10.11.16 : Fails

10.11.18 : Fails

11.4.12  : Fails

The affected query works on the same server when the ORACLE flag is removed from sql_mode.

Environment

Operating system: [Enter the operating system and version]

Architecture: x86_64

Installation method: [RPM / binary tarball / repository]

Affected versions: 10.11.16, 10.11.18, 11.4.12

Last known working version: 10.11.15

Steps to reproduce

#######10.11.15
MariaDB [(none)]> select @@version;
----------------------

----------------------

----------------------
1 row in set (0.001 sec)

MariaDB [(none)]> show session variables like 'sql_mode';
--------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> SELECT 1
-> FROM t_bbs_article art
-> WHERE EXISTS (
-> SELECT 1
-> FROM DUAL
-> WHERE 'Y' = art.PUBLICYN
->
-> UNION ALL
->
-> SELECT 1
-> FROM DUAL
-> WHERE 'N' = art.PUBLICYN
-> )
-> LIMIT 1;
---

---

---
1 row in set (0.002 sec)

MariaDB [test]> SET SESSION sql_mode = 'ORACLE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> show session variables like 'sql_mode';
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]> SELECT 1
-> FROM t_bbs_article art
-> WHERE EXISTS (
-> SELECT 1
-> FROM DUAL
-> WHERE 'Y' = art.PUBLICYN
->
-> UNION ALL
->
-> SELECT 1
-> FROM DUAL
-> WHERE 'N' = art.PUBLICYN
-> )
-> LIMIT 1;
---

---

---
1 row in set (0.001 sec)

MariaDB [test]>
MariaDB [test]> show session variables like 'sql_mode';
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

##########10.11.16
MariaDB [(none)]> select @@version;
----------------------

----------------------

----------------------
1 row in set (0.000 sec)

MariaDB [(none)]> show session variables like 'sql_mode';
--------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SET SESSION sql_mode = 'ORACLE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> SELECT 1
-> FROM t_bbs_article art
-> WHERE EXISTS (
-> SELECT 1
-> FROM DUAL
-> WHERE 'Y' = art.PUBLICYN
->
-> UNION ALL
->
-> SELECT 1
-> FROM DUAL
-> WHERE 'N' = art.PUBLICYN
-> )
-> LIMIT 1;
ERROR 1109 (42S02): Unknown table 'art' in WHERE
MariaDB [test]> exit
Bye
[root@localhost MARIA]# mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.11.16-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> SELECT 1
-> FROM t_bbs_article art
-> WHERE EXISTS (
-> SELECT 1
-> FROM DUAL
-> WHERE 'Y' = art.PUBLICYN
->
-> UNION ALL
->
-> SELECT 1
-> FROM DUAL
-> WHERE 'N' = art.PUBLICYN
-> )
-> LIMIT 1;
---

---

---
1 row in set (0.001 sec)

#######10.11.17

MariaDB [test]> select @@version;
----------------------

----------------------

----------------------
1 row in set (0.000 sec)

MariaDB [test]> show session variables like 'sql_mode';
--------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------+

--------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MariaDB [test]> SET SESSION sql_mode = 'ORACLE,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> SELECT 1
-> FROM t_bbs_article art
-> WHERE EXISTS (
-> SELECT 1
-> FROM DUAL
-> WHERE 'Y' = art.PUBLICYN
->
-> UNION ALL
->
-> SELECT 1
-> FROM DUAL
-> WHERE 'N' = art.PUBLICYN
-> )
-> LIMIT 1;
ERROR 1109 (42S02): Unknown table 'art' in WHERE
MariaDB [test]>
MariaDB [test]> exit
Bye
[root@localhost MARIA]#
[root@localhost MARIA]#
[root@localhost MARIA]# mariadb
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 30
Server version: 10.11.17-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [test]> SELECT 1
-> FROM t_bbs_article art
-> WHERE EXISTS (
-> SELECT 1
-> FROM DUAL
-> WHERE 'Y' = art.PUBLICYN
->
-> UNION ALL
->
-> SELECT 1
-> FROM DUAL
-> WHERE 'N' = art.PUBLICYN
-> )
-> LIMIT 1;
---

---

---
1 row in set (0.001 sec)

Actual result

On MariaDB 10.11.16, 10.11.18 and 11.4.12:

ERROR 1109 (42S02): Unknown table 'art' in WHERE

Expected result

The outer table alias "art" should be resolved inside both SELECT statements of the correlated UNION ALL subquery.

The query should return one row:

+---+

| 1 |

+---+

| 1 |

+---+

This is the behavior observed in MariaDB 10.11.14 and 10.11.15.

Control test

The same query works on the affected versions if the ORACLE flag is removed while the component SQL modes are retained.

SET SESSION sql_mode =

'PIPES_AS_CONCAT,

ANSI_QUOTES,

IGNORE_SPACE,

NO_KEY_OPTIONS,

NO_TABLE_OPTIONS,

NO_FIELD_OPTIONS,

NO_AUTO_CREATE_USER,

SIMULTANEOUS_ASSIGNMENT';

SELECT 1

FROM t_bbs_article art

WHERE EXISTS (

    SELECT 1

    FROM DUAL

    WHERE 'Y' = art.PUBLICYN

    UNION ALL

    SELECT 1

    FROM DUAL

    WHERE 'N' = art.PUBLICYN

)

LIMIT 1;

Additional information

The issue appears to be a regression introduced between MariaDB 10.11.15 and 10.11.16.

It may be related to changes in the handling of set operations in ORACLE mode, possibly MDEV-37325, but this relationship has not been confirmed.