Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Duplicate
-
12.3.3
-
None
-
Linux/amd64
-
Not for Release Notes
Description
The same correlated mixed-set query returns the expected row in default SQL mode, but in Oracle mode it fails to resolve the outer-table reference with ERROR 1109. This was reproduced with MariaDB 12.3.3 on Linux/amd64.
Create the test database and table:
CREATE DATABASE set_operations; |
CREATE TABLE set_operations.outer_rows (row_id INT); |
INSERT INTO set_operations.outer_rows VALUES (1),(2); |
Run the query in default SQL mode:
USE set_operations; |
SET sql_mode=''; |
SELECT outer_rows.row_id FROM outer_rows WHERE EXISTS (SELECT 1 FROM dual WHERE outer_rows.row_id=1 UNION SELECT 2 FROM dual INTERSECT SELECT 1 FROM dual) ORDER BY 1; |
It returns:
1
|
Run the same query in Oracle mode:
USE set_operations; |
SET sql_mode='oracle'; |
SELECT outer_rows.row_id FROM outer_rows WHERE EXISTS (SELECT 1 FROM dual WHERE outer_rows.row_id=1 UNION SELECT 2 FROM dual INTERSECT SELECT 1 FROM dual) ORDER BY 1; |
It fails instead of returning the row:
ERROR 1109 (42S02) at line 3: Unknown table 'outer_rows' in WHERE
|
Preparing the query in Oracle mode fails with the same error:
USE set_operations; |
SET sql_mode='oracle'; |
PREPARE correlated_query FROM 'SELECT outer_rows.row_id FROM outer_rows WHERE EXISTS (SELECT 1 FROM dual WHERE outer_rows.row_id=1 UNION SELECT 2 FROM dual INTERSECT SELECT 1 FROM dual) ORDER BY 1;'; |
EXECUTE correlated_query; |
EXECUTE correlated_query; |
DEALLOCATE PREPARE correlated_query; |
A UNION-only Oracle-mode control resolves the same outer reference and returns 1:
USE set_operations; |
SET sql_mode='oracle'; |
SELECT outer_rows.row_id FROM outer_rows WHERE EXISTS (SELECT 1 FROM dual WHERE outer_rows.row_id=1 UNION SELECT 1 FROM dual WHERE outer_rows.row_id=1) ORDER BY 1; |
1
|
The mixed set expression has the same exact expected result in both tested SQL modes: EXISTS is true for the row whose ID is 1 and false for the row whose ID is 2. Set-operation precedence handling must preserve visibility of the valid reference to outer_rows, so the Oracle-mode query should also return 1. The source history identifies premature derived-table wrapping as hiding the correlated outer reference, which is consistent with the name-resolution failure.
This occurs when an Oracle-mode correlated EXISTS subquery combines UNION and INTERSECT across the correlated operand.
Attachments
Issue Links
- duplicates
-
-
- Closed
-