Bug #90755 Windowing Partition columns don't work with derived/calculated column

• Submitted: 4 May 2018 16:05
• Modified: 18 Jun 2018 16:53
• Reporter: James Harbal
• Status: No Feedback
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: x86
• Tags: derived, partitions, windowing

[4 May 2018 16:05] James Harbal

Description:
Trying to do windowing on a derived table where one column to be partitioned is not in the original table so it gives an error "Unknown column 'derived_column_name' in 'window partition by'

I tried to create as sub-query with extra select* FROM (derived table) as t 
type of thing but still same issue, have to create temp table with derived column as 'real' column in table schema to make it work.
Bug happens with other windowing functions too.

How to repeat:

  SELECT
        ROW_NUMBER() OVER(PARTITION BY id,qty ORDER BY ts ASC) as rn
  FROM (SELECT id,1+1 as qty, ts
        FROM orig_table) as t

[9 May 2018 15:31] MySQL Verification Team

HI,

You can not use columns from the derived table in the outer query, as the entities in the nested or other nested queries are invisible to the outer query.

However, I do not see this documented in the chapter on the windowing functions restrictions.

Hence, this is verified as a documentation bug.

[18 May 2018 12:03] MySQL Verification Team

HI,

Turns out this works in 8.0.11. Are you using 8.0.11 ????

If you get that error with 8.0.11, then we would need the structure and contents of the `orig_table'.

Thanks in advance.

[18 Jun 2018 16:53] Paul DuBois

No feedback provided in 30 days. Closing.