Bug #116974 SHOW CREATE VIEW produces invalid view if view contains CTE w/ an unaliased window function column

• Submitted: 13 Dec 23:32
• Modified: 14 Dec 7:44
• Reporter: Duke Lee
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S3 (Non-critical)
• Version: 8.0.39, 8.0.40
• OS: Any
• CPU Architecture: Any

[13 Dec 23:32] Duke Lee

Description:
If you create view that contains a CTE, and the CTE SELECTs an unnamed/unaliased window function as a column, the creation of this view will succeed. However, if you run SHOW CREATE VIEW against the view, mysql will generate an invalid CREATE VIEW statement.

Mysql generates a column name for the window function, but this column name is invalid. e.g. with the following view:

CREATE VIEW invalid_view AS
WITH test_cte (col1) AS (
    SELECT
        RANK() OVER (
            PARTITION BY id
            ORDER BY id ASC
        )
    FROM test_table
)
SELECT * FROM test_cte
;

mysql will generate the following invalid view via SHOW CREATE VIEW:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `invalid_view` AS with `test_cte` (`col1`) as (select rank() OVER (PARTITION BY `test_table`.`id` ORDER BY `test_table`.`id` )  AS `RANK() OVER (
            PARTITION BY id
            ORDER BY id ASC
        )` from `test_table`) select `test_cte`.`col1` AS `col1` from `test_cte`

This error can result in silent failures that break backup/restore processes, etc.

How to repeat:
CREATE DATABASE test;

USE test;

-- Copy, paste, and execute results of SHOW CREATE VIEW here.
-- You can see that valid_view can be recreated from SHOW CREATE VIEW results

-- Moving on to invalid SHOW CREATE VIEW results

CREATE VIEW invalid_view AS
WITH test_cte (col1) AS (
    SELECT
        RANK() OVER (
            PARTITION BY id
            ORDER BY id ASC
        )
    FROM test_table
)
SELECT * FROM test_cte
;

SHOW CREATE VIEW invalid_view;

DROP VIEW invalid_view;

-- Copy, paste, and execute results of SHOW CREATE VIEW here.
-- You can see that invalid_view CANNOT be recreated from SHOW CREATE VIEW results

-- Moving onto workaround example

CREATE VIEW workaround_view AS
WITH test_cte AS (
    SELECT
        RANK() OVER (
            PARTITION BY id
            ORDER BY id ASC
        ) AS forced_name
    FROM test_table
)
SELECT * FROM test_cte
;

SHOW CREATE VIEW workaround_view;

DROP VIEW workaround_view;

-- Copy, paste, and execute results of SHOW CREATE VIEW here.
-- You can see that workaround_view can be recreated from SHOW CREATE VIEW results
-- Giving the window function a column alias prevents mysql from generating a
-- column alias that is derived from the window function

-- However, the workaround only works if the column alias is at the SELECT-level
-- within the CTE. If the name is given as an argument to the CTE, then
-- SHOW CREATE VIEW fails:

CREATE VIEW invalid_workaround_view AS
WITH test_cte (forced_name) AS (
    SELECT
        RANK() OVER (
            PARTITION BY id
            ORDER BY id ASC
        )
    FROM test_table
)
SELECT * FROM test_cte
;

SHOW CREATE VIEW invalid_workaround_view;

DROP VIEW invalid_workaround_view;

-- Copy, paste, and execute results of SHOW CREATE VIEW here.
-- Result will fail

-- The issue seems specifically related to window functions because regular
-- functions with unaliased CTE columns will work just fine:

CREATE VIEW valid_view AS
WITH test_cte (col1) AS (
    SELECT
        LEFT(
            'long text',
            4
        )
    FROM test_table
)
SELECT * FROM test_cte
;

SHOW CREATE VIEW valid_view;

DROP VIEW valid_view;

-- Copy, paste, and execute results of SHOW CREATE VIEW here.
-- Result will succeed

[14 Dec 7:44] MySQL Verification Team

Hello Duke Lee,

Thank you for the report and test case.

regards,
Umesh