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.
