Bug #112112 Cannot find column on derived table with given names and ROLLUP

• Submitted: 18 Aug 2023 10:20
• Modified: 18 Aug 2023 12:28
• Reporter: Hope Lee (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[18 Aug 2023 10:20] Hope Lee

Description:
SQL cannot find column on a derived table with given column names. Actually it should have found the corresponding column.

How to repeat:
CREATE TABLE sale(
    cn INT NOT null,
    vn INT NOT null,
    pn INT NOT null,
    dt DATE NOT null,
    qty INT NOT null,
    prc FLOAT NOT null,
    PRIMARY KEY (cn, vn, on)
);

mysql-8.0.33 > SELECT g, cn, vn, pn, s, RANK() OVER (PARTITION BY g ORDER BY s)
FROM (
	SELECT GROUPING(cn, vn, pn), cn
		, vn, pn, SUM(qty * prc)
	FROM sale
	GROUP BY cn, vn, pn WITH ROLLUP
) AS olap_tmp_for_window (g, cn, vn, pn, s);
ERROR 1054 (42S22): Unknown column 's' in 'field list'

Suggested fix:
When generating materialized table's field in Item_rollup_sum_switcher::create_tmp_field, Item_rollup_sum_switcher doesn't pass the correct item_name to its master Item_sum.

[18 Aug 2023 11:48] Hope Lee

Temptable field generated from Item_rollup_sum_switcher has the wrong name

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-Temptable-field-generated-from-Item_rollup_sum_switc.patch (application/octet-stream, text), 1.80 KiB.

[18 Aug 2023 12:28] MySQL Verification Team

Hi Mr. Lee,

Thank you very much for your bug report.

We were fully able to repeat it with 8.034 and 8.1.0.

Verified as reported.

Thank you also for your contribution !!!!