Bug #115127 invalid result set with window function and generated rand value

• Submitted: 26 May 2024 5:51
• Modified: 27 May 2024 9:50
• Reporter: soheil rahsaz
• Status: Verified
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[26 May 2024 5:51] soheil rahsaz

Description:
Window function `sum` does not return proper value for a generated value with rand() like this:

How to repeat:
DROP TABLE IF EXISTS mooz;
CREATE TABLE mooz
(
    id     INT AUTO_INCREMENT PRIMARY KEY,
    number INT
);

INSERT INTO `mooz` (`id`, `number`)
VALUES (1, 2), (2, 2), (3, 5),(4, 2),(5, 5),(6, 3),(7, 1),(8, 2),(9, 5), (10, 2);

SELECT a.*, SUM(a.rand) OVER (ROWS UNBOUNDED PRECEDING) AS sumTill
FROM (SELECT number, CEIL(RAND() *5) AS rand
      FROM mooz) a;

returns this. The sumTill column is expected to have these values:
5,10,13,16,17,18,19,22,27,32
but does not have the correct value

+--------+------+---------+
| number | rand | sumTill |
+--------+------+---------+
|      2 |    5 |       5 |
|      2 |    5 |       6 |
|      5 |    3 |       9 |
|      2 |    3 |      11 |
|      5 |    1 |      14 |
|      3 |    1 |      18 |
|      1 |    1 |      20 |
|      2 |    3 |      22 |
|      5 |    5 |      27 |
|      2 |    5 |      32 |
+--------+------+---------+

[27 May 2024 9:50] MySQL Verification Team

Hi Mr. rahsaz,

Thank you for your bug report.

We have fully repeated the behaviour that you reported and we agree with your conclusions:

mysql> DROP TABLE IF EXISTS mooz;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE mooz (     id     INT AUTO_INCREMENT PRIMARY KEY,     number INT );
Query OK, 0 rows affected (0.05 sec)

mysql>
mysql> INSERT INTO `mooz` (`id`, `number`) VALUES (1, 2), (2, 2), (3, 5),(4, 2),(5, 5),(6, 3),(7, 1),(8, 2),(9, 5), (10, 2);
Query OK, 10 rows affected (0.02 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT a.*, SUM(a.rand) OVER (ROWS UNBOUNDED PRECEDING) AS sumTill FROM (SELECT number, CEIL(RAND() *5) AS rand       FROM mooz) a;
+--------+------+---------+
| number | rand | sumTill |
+--------+------+---------+
|      2 |    2 |       4 |
|      2 |    5 |       9 |
|      5 |    4 |      12 |
|      2 |    1 |      13 |
|      5 |    2 |      15 |
|      3 |    2 |      17 |
|      1 |    5 |      19 |
|      2 |    3 |      20 |
|      5 |    4 |      25 |
|      2 |    3 |      28 |
+--------+------+---------+
10 rows in set (0.00 sec)

Verified as reported.