Bug #103051 Window with buffering follows equijoin on unique index returns wrong NULL result
Submitted: 21 Mar 9:30 Modified: 21 Mar 10:00
Reporter: Hope Lee (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any

[21 Mar 9:30] Hope Lee
Description:
When a window with buffering follows an equijoin on a unique index(JT_EQ_REF), the server may return the wrong NULL results on some rows.

How to repeat:
CREATE TABLE lineitem (
  l_quantity INT,
  l_partkey INT
);

CREATE TABLE part (
  p_partkey  INT PRIMARY KEY
);

INSERT INTO lineitem VALUES (34, 1), (10, 1), (4, 1), (6, 1), (9, 2), (64, 2), (31, 2);
INSERT INTO part VALUES (1), (2), (3), (4), (5), (6), (7);

ANALYZE TABLE part, lineitem;

mysql > EXPLAIN SELECT 0.2 * avg(l_quantity) OVER (PARTITION BY l_partkey ) AS avg, l_quantity
FROM lineitem JOIN part ON p_partkey = l_partkey;

+----+-------------+----------+------------+--------+---------------+---------+---------+-------------------------+------+----------+----------------------------------------------+
| id | select_type | table    | partitions | type   | possible_keys | key     | key_len | ref                     | rows | filtered | Extra                                        |
+----+-------------+----------+------------+--------+---------------+---------+---------+-------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | lineitem | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                    |    7 |   100.00 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | part     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.lineitem.l_partkey |    1 |   100.00 | Using index                                  |
+----+-------------+----------+------------+--------+---------------+---------+---------+-------------------------+------+----------+----------------------------------------------+
2 rows in set, 2 warnings (0.01 sec)

mysql > SELECT 0.2 * avg(l_quantity) OVER (PARTITION BY l_partkey ) AS avg, l_quantity
FROM lineitem JOIN part ON p_partkey = l_partkey;

Actual results:
+-----------+---------+------------+
| l_partkey | avg     | l_quantity |
+-----------+---------+------------+
|         1 |    NULL |         34 |
|         1 | 2.70000 |         10 |
|         1 | 2.70000 |          4 |
|         1 | 2.70000 |          6 |
|         2 | 6.93334 |          9 |
|         2 | 6.93334 |         64 |
|         2 | 6.93334 |         31 |
+-----------+---------+------------+
7 rows in set (0.00 sec)

Expected results:
+-----------+---------+------------+
| l_partkey | avg     | l_quantity |
+-----------+---------+------------+
|         1 | 2.70000 |         34 |
|         1 | 2.70000 |         10 |
|         1 | 2.70000 |          4 |
|         1 | 2.70000 |          6 |
|         2 | 6.93334 |          9 |
|         2 | 6.93334 |         64 |
|         2 | 6.93334 |         31 |
+-----------+---------+------------+
7 rows in set (0.00 sec)

Suggested fix:
In function `BufferingWindowingIterator::Read()`, after the window function calculation in a partition has completed, multiple rows are ready for output. And the server is going to output the first row in the partition.

But if we read the row again from the input table because `Window::needs_restore_input_row()` returns true, it will override the NULL bit flags which have already been set to the correct value in `process_buffered_windowing_record()->copy_funcs()`. So the server returns wrong NULL values.

If there are multiple rows ready for output, we don't need to read the record again.
[21 Mar 9:31] Hope Lee
Patch to fix this issue

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

Contribution: 0001-Bugfix-Window-with-buffering-follows-JT_EQ_REF-retur.patch (application/octet-stream, text), 1.60 KiB.

[21 Mar 10:00] MySQL Verification Team
Hello Lee,

Thank you for the report and contribution.

regards,
Umesh