Details
-
Bug
-
Status: In Progress (View Workflow)
-
Blocker
-
Resolution: Unresolved
-
10.11, 11.4, 11.8, 12.2
Description
# Doesn't have to be empty, just non-Oracle |
SET sql_mode=''; |
CREATE VIEW v AS SELECT 'foo' AS a UNION SELECT 'bar' AS a; |
SELECT a FROM v; |
SET sql_mode=ORACLE; |
SHOW CREATE VIEW v; |
SELECT * FROM v; |
SELECT a FROM v; |
DROP VIEW v; |
|
10.11 b29d3779e42f1cf65b1bbe84876767122dcc76c8 |
SET sql_mode=''; |
CREATE VIEW v AS SELECT 'foo' AS a UNION SELECT 'bar' AS a; |
SELECT a FROM v; |
a
|
foo
|
bar
|
SET sql_mode=ORACLE; |
SHOW CREATE VIEW v; |
View Create View character_set_client collation_connection |
v CREATE VIEW "v" AS select "__4"."a" AS "a" from (select 'foo' AS "a" union select 'bar' AS "a") "__4" latin1 latin1_swedish_ci |
SELECT * FROM v; |
*
|
foo
|
bar
|
SELECT a FROM v; |
bug.t2 [ fail ]
|
Test ended at 2026-02-10 00:16:27 |
|
|
CURRENT_TEST: bug.t2
|
mysqltest: At line 7: query 'SELECT a FROM v' failed: ER_BAD_FIELD_ERROR (1054): Unknown column 'a' in 'SELECT' |
Note that SELECT * FROM v shows the star as a column name.
The failure started happening after this commit in 10.11.16:
commit 10643cffe98a42ee28a9f16b70ad7dff90563a03 (HEAD)
|
Author: Aleksey Midenkov
|
Date: Tue Dec 23 13:42:45 2025 +0300
|
|
|
MDEV-37325 Incorrect results for INTERSECT ALL in ORACLE mode
|
The main problem is not the runtime switch between the default mode and ORACLE – probably not many users do it – but an upgrade for users working in Oracle mode. Before the change above, a view frm was the same whether it was created in the default mode or in ORACLE. After the change, the frms are essentially different. The default mode seems to be able to handle both variants, at least I haven't seen a problem so far; but Oracle mode doesn't properly handle the "old" format, so the problem will happen on all such views after upgrade.
Attachments
Issue Links
- is caused by
-
-
- Closed
-