MySQL Bugs: #112161: Derived table with rollup returns wrong result

Bug #112161	Derived table with rollup returns wrong result
Submitted:	23 Aug 2023 12:55	Modified:	23 Aug 2023 14:00
Reporter:	Hope Lee (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Query on a derived table with ROLLUP syntax returns the wrong result. The result of a row ROLLUP NULL is lost.

How to repeat:
CREATE TABLE t (
  pk bigint NOT NULL AUTO_INCREMENT,
  integer_test int DEFAULT NULL,
  datetime_test datetime DEFAULT NULL,
  PRIMARY KEY (pk)
);

INSERT INTO t(integer_test, datetime_test) VALUES (23, '2018-09-21'), (8, '1994-11-04');

mysql-8.0.33 > SELECT *
FROM
(
	SELECT  layer_2_column_0                AS layer_1_column_0
	       ,MIN(DISTINCT layer_2_column_0)  AS layer_1_column_1
	FROM
	(
		SELECT  1                         AS layer_2_column_0
		       ,STDDEV(datetime_test / 1) AS layer_2_column_1
		FROM t
	) AS layer_1_table
	GROUP BY  layer_1_column_0
	WITH ROLLUP
) AS layer_0_table;
+------------------+------------------+
| layer_1_column_0 | layer_1_column_1 |
+------------------+------------------+
|                1 |                1 |
+------------------+------------------+
1 row in set (0.00 sec)

Expected results:
+------------------+------------------+
| layer_1_column_0 | layer_1_column_1 |
+------------------+------------------+
|                1 |                1 |
|             NULL |                1 |
+------------------+------------------+

Hi Mr. Lee,

Thank you for your bug report.

We have expanded on your test case.

We ran the query from the inner  SELECT query , then on the TABLE created from the same INNER select, then creating a VIEW from the same  inner SELECT and last, your own test with a derived table.

ROLLUP results are not missing from the inner SELECT, nor from the TABLE created from the same SELECT.

However, they are missing from the VIEW and from the derived table.

Here are the results:

--------------------

INNER QUERY:

layer_1_column_0	layer_1_column_1
1	1
NULL	1

CREATE TABLE FROM SELECT

layer_1_column_0	layer_1_column_1
1	1
NULL	1

CREATE VIEW FROM SELECT

layer_1_column_0	layer_1_column_1
1	1

DERIVED TABLE

layer_1_column_0	layer_1_column_1
1	1

-------------------- 

We do not think that this is expected behaviour.

This report is now a verified bug.

Changing category.