CREATE VIEW silently rebinds an unnamed empty-string column to a sibling tmp_field column

Creating a view from a derived table containing an unnamed empty-string expression and a sibling column named tmp_field silently changes the first expression to reference tmp_field. The direct query returns an empty string followed by sentinel, while querying the persisted view returns sentinel for both columns.

Reproducer

Run the following SQL in order:

CREATE DATABASE IF NOT EXISTS view_test;

USE view_test;

SET sql_mode='';

SELECT 'direct' AS phase, d.*

FROM (SELECT '', 'sentinel' AS tmp_field) AS d;

CREATE VIEW v (empty_col, named_col) AS

SELECT * FROM (SELECT '', 'sentinel' AS tmp_field) AS d;

SHOW CREATE VIEW v;

SELECT 'persisted' AS phase,

       empty_col,

       named_col,

       empty_col='' AS empty_ok,

       named_col='sentinel' AS named_ok

FROM v;

DROP DATABASE view_test;

Decisive observation

The direct query preserves the two source values:

phase   <unnamed>   tmp_field

direct              sentinel

However, SHOW CREATE VIEW shows that both view columns reference the sibling tmp_field column:

select d.tmp_field AS empty_col,

       d.tmp_field AS named_col

from (select '','sentinel' AS tmp_field) d

The persisted view consequently returns:

phase       empty_col   named_col   empty_ok   named_ok

persisted   sentinel    sentinel    0          1

Semantic mismatch

The view definition selects both derived-table columns. Persisting and reopening that definition should not change the unnamed empty-string expression into a reference to the distinct named sibling column. The persisted result should therefore remain an empty string followed by sentinel, with both comparison columns equal to 1.

This occurs when a view is created from a derived table that combines an unnamed empty-string expression with a sibling column named tmp_field.