Bug #93155 Unknown column 'id2' in window partition by

• Submitted: 11 Nov 2018 5:44
• Modified: 28 Nov 2018 8:31
• Reporter: Jon Armstrong
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.12, 8.0.13
• OS: Any
• CPU Architecture: Any

[11 Nov 2018 5:44] Jon Armstrong

Description:
Window Function OVER clause, both PARTITION BY and ORDER BY clauses can not access a derived column from a CTE term or a derived table.

Problem duplicated in MySQL 8.0.12.

No issue found in MariaDB 10.3.3.

How to repeat:
CREATE TABLE test1 (
    id    int  auto_increment primary key
);

INSERT INTO test1 () VALUES (), (), (), ();

WITH cte1 AS (
       SELECT t.*
            , t.id + 5 AS id2
         FROM test1 AS t
     )
SELECT t.*
     , ROW_NUMBER() OVER (ORDER BY id2) AS n
  FROM cte1 AS t
;

-- Error seen is: 
Error Code: 1054. Unknown column 'id2' in 'window order by'

WITH cte1 AS (
       SELECT t.*
            , t.id + 5 AS id2
         FROM test1 AS t
     )
SELECT t.*
     , ROW_NUMBER() OVER (PARTITION BY id2) AS n
  FROM cte1 AS t
;

-- Error seen is: 
Error Code: 1054. Unknown column 'id2' in 'window partition by'

If we replace id2 with id (which is a concrete column, not derived), the statement executes without error.

[11 Nov 2018 5:50] Jon Armstrong

I just noticed that if I add the table prefix (t.id2), the column is found.  It's the unqualified column reference (which still should be valid) that is not recongized.

[12 Nov 2018 5:01] MySQL Verification Team

Hello Jon,

Thank you for the report and test case.

regards,
Umesh

[28 Nov 2018 8:31] Erlend Dahl

Duplicate of

Bug#90300 "unknown field in window order by " error even when field is found in table