Bug #105692 Incorrect result with aggregation by temporary table
Submitted: 24 Nov 2021 10:20 Modified: 24 Nov 2021 16:11
Reporter: Steinar Gunderson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.29 OS:Any
Assigned to: CPU Architecture:Any

[24 Nov 2021 10:20] Steinar Gunderson 
Description:
This gives the wrong result (one row gets missing) in the old join optimizer:

CREATE TABLE t1 ( a INTEGER, b VARCHAR(1) );
INSERT INTO t1 VALUES (3, 'Q'), (4, '5');

CREATE TABLE t2 ( a INTEGER );
INSERT INTO t2 VALUES (3), (4);

SELECT
  t2_alias.a,
  SUM(t1.b) AS field2
FROM
  t1
  JOIN ( SELECT * FROM t2 ) AS t2_alias ON t1.a = t2_alias.a
GROUP BY t2_alias.a
HAVING t2_alias.a <> 3 OR field2 < 5;

DROP TABLE t1, t2;

The exact cause is not understood, but it's probably something related to that fields need to be copied first.

How to repeat:
N/A

Suggested fix:
N/A
[24 Nov 2021 16:11] Jon Stephens 
Documented fix as follows in the MySQL 8.0.28 changelog:

    In some cases where warnings were issued, rows were missing from
    the results of aggregation using a temporary table.

Closed.
[6 Jan 2022 14:13] Erlend Dahl 
Bug#106063 Condition on aggregared expr in HAVING clause produces bad result in some cases

has been marked as a duplicate.