[MDEV-30776] AVG over incorrect results from a inline view - Jira

Details

Type: Bug
Status: Confirmed (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 10.5.17, 11.0.1, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
Fix Version/s: 10.5, 10.6
Component/s: Optimizer
Labels:
- wrong_result

Description

MariaDB [beta]> select s, lap, avg(s)over() -- , avg(lap)over()

    ->   from (

    ->  select seq s , seq + floor(rand(0)*(2)-1) lap

    ->    from seq_1_to_5

    -> ) as a

    -> order by s

    -> ;

+---+-----+--------------+

| s | lap | avg(s)over() |

+---+-----+--------------+

| 0 |   3 |       5.0000 |

| 0 |   3 |       5.0000 |

| 0 |   5 |       5.0000 |

| 0 |   0 |       5.0000 |

| 0 |   2 |       5.0000 |

+---+-----+--------------+

5 rows in set (0.002 sec)

Correct result:

+---+-----+----------------+

| s | lap | avg(seq)over() |

+---+-----+----------------+

| 1 |   0 |         3.0000 |

| 2 |   2 |         3.0000 |

| 3 |   3 |         3.0000 |

| 4 |   3 |         3.0000 |

| 5 |   5 |         3.0000 |

+---+-----+----------------+

5 rows in set (0.000 sec)

Attachments

Activity

Ascending order - Click to sort in descending order

Timo Raitalaakso created issue - 2023-03-02 14:24

Alice Sherepa made changes - 2023-03-02 15:15

Field	Original Value	New Value
Affects Version/s		10.3 [ 22126 ]
Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]
Affects Version/s		10.8 [ 26121 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]
Affects Version/s		10.11 [ 27614 ]

Alice Sherepa made changes - 2023-03-02 15:16

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]
Fix Version/s		10.8 [ 26121 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s		11.0 [ 28320 ]

Alice Sherepa added a comment - 2023-03-02 15:17 - edited

Thank you for the report! I repeated as described on 10.3-10.11,11.0

 MariaDB [test]> select seq, avg(seq) over ()   from (  select seq from seq_1_to_5  ) as a ;

+-----+------------------+

| seq | avg(seq) over () |

+-----+------------------+

|   1 |           3.0000 |

|   2 |           3.0000 |

|   3 |           3.0000 |

|   4 |           3.0000 |

|   5 |           3.0000 |

+-----+------------------+

5 rows in set (0,001 sec)

MariaDB [test]> select seq, avg(seq) over ()   from (  select seq, rand()  from seq_1_to_5  ) as a ;

+-----+------------------+

| seq | avg(seq) over () |

+-----+------------------+

|   0 |           5.0000 |

|   0 |           5.0000 |

|   0 |           5.0000 |

|   0 |           5.0000 |

|   0 |           5.0000 |

+-----+------------------+

5 rows in set (0,001 sec)

MariaDB [test]> explain extended select seq, avg(seq) over ()   from (  select seq, rand()  from seq_1_to_5  ) as a ;

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+

| id   | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+

|    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    5 |   100.00 | Using temporary |

|    2 | DERIVED     | seq_1_to_5 | index | NULL          | PRIMARY | 8       | NULL |    5 |   100.00 | Using index     |

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+

2 rows in set, 1 warning (0,001 sec)

Note (Code 1003): /* select#1 */ select `a`.`seq` AS `seq`,avg(`a`.`seq`) over () AS `avg(seq) over ()` from (/* select#2 */ select `test`.`seq_1_to_5`.`seq` AS `seq`,rand() AS `rand()` from `test`.`seq_1_to_5`) `a`

MariaDB [test]> explain extended select seq, avg(seq) over ()   from (  select seq  from seq_1_to_5  ) as a ;

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+

| id   | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                        |

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+

|    1 | SIMPLE      | seq_1_to_5 | index | NULL          | PRIMARY | 8       | NULL |    5 |   100.00 | Using index; Using temporary |

+------+-------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+

1 row in set, 1 warning (0,000 sec)

Note (Code 1003): select `test`.`seq_1_to_5`.`seq` AS `seq`,avg(`test`.`seq_1_to_5`.`seq`) over () AS `avg(seq) over ()` from `test`.`seq_1_to_5`

Alice Sherepa added a comment - 2023-03-02 15:17 - edited Thank you for the report! I repeated as described on 10.3-10.11,11.0 MariaDB [test]> select seq, avg(seq) over () from ( select seq from seq_1_to_5 ) as a ; +-----+------------------+ | seq | avg(seq) over () | +-----+------------------+ | 1 | 3.0000 | | 2 | 3.0000 | | 3 | 3.0000 | | 4 | 3.0000 | | 5 | 3.0000 | +-----+------------------+ 5 rows in set (0,001 sec) MariaDB [test]> select seq, avg(seq) over () from ( select seq, rand() from seq_1_to_5 ) as a ; +-----+------------------+ | seq | avg(seq) over () | +-----+------------------+ | 0 | 5.0000 | | 0 | 5.0000 | | 0 | 5.0000 | | 0 | 5.0000 | | 0 | 5.0000 | +-----+------------------+ 5 rows in set (0,001 sec) MariaDB [test]> explain extended select seq, avg(seq) over () from ( select seq, rand() from seq_1_to_5 ) as a ; +------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | Using temporary | | 2 | DERIVED | seq_1_to_5 | index | NULL | PRIMARY | 8 | NULL | 5 | 100.00 | Using index | +------+-------------+------------+-------+---------------+---------+---------+------+------+----------+-----------------+ 2 rows in set, 1 warning (0,001 sec) Note (Code 1003): /* select#1 */ select `a`.`seq` AS `seq`,avg(`a`.`seq`) over () AS `avg(seq) over ()` from (/* select#2 */ select `test`.`seq_1_to_5`.`seq` AS `seq`,rand() AS `rand()` from `test`.`seq_1_to_5`) `a` MariaDB [test]> explain extended select seq, avg(seq) over () from ( select seq from seq_1_to_5 ) as a ; +------+-------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+ | 1 | SIMPLE | seq_1_to_5 | index | NULL | PRIMARY | 8 | NULL | 5 | 100.00 | Using index; Using temporary | +------+-------------+------------+-------+---------------+---------+---------+------+------+----------+------------------------------+ 1 row in set, 1 warning (0,000 sec) Note (Code 1003): select `test`.`seq_1_to_5`.`seq` AS `seq`,avg(`test`.`seq_1_to_5`.`seq`) over () AS `avg(seq) over ()` from `test`.`seq_1_to_5`

Alice Sherepa made changes - 2023-03-02 15:19

Assignee

Sergei Petrunia [ psergey ]

Alice Sherepa made changes - 2023-03-02 15:19

Status

Open [ 1 ]

Confirmed [ 10101 ]

Julien Fritsch made changes - 2023-03-06 08:07

Fix Version/s

10.7 [ 24805 ]

Sergei Golubchik made changes - 2023-03-30 11:42

Description

MariaDB [beta]> select s, lap, avg(s)over() -- , avg(lap)over()
    -> from (
    -> select seq s , seq + floor(rand(0)*(2)-1) lap
    -> from seq_1_to_5
    -> ) as a
    -> order by s
    -> ;
+---+-----+--------------+
| s | lap | avg(s)over() |
+---+-----+--------------+
| 0 | 3 | 5.0000 |
| 0 | 3 | 5.0000 |
| 0 | 5 | 5.0000 |
| 0 | 0 | 5.0000 |
| 0 | 2 | 5.0000 |
+---+-----+--------------+
5 rows in set (0.002 sec)

Correct result:

+---+-----+----------------+
| s | lap | avg(seq)over() |
+---+-----+----------------+
| 1 | 0 | 3.0000 |
| 2 | 2 | 3.0000 |
| 3 | 3 | 3.0000 |
| 4 | 3 | 3.0000 |
| 5 | 5 | 3.0000 |
+---+-----+----------------+
5 rows in set (0.000 sec)

{noformat}
MariaDB [beta]> select s, lap, avg(s)over() -- , avg(lap)over()
    -> from (
    -> select seq s , seq + floor(rand(0)*(2)-1) lap
    -> from seq_1_to_5
    -> ) as a
    -> order by s
    -> ;
+---+-----+--------------+
| s | lap | avg(s)over() |
+---+-----+--------------+
| 0 | 3 | 5.0000 |
| 0 | 3 | 5.0000 |
| 0 | 5 | 5.0000 |
| 0 | 0 | 5.0000 |
| 0 | 2 | 5.0000 |
+---+-----+--------------+
5 rows in set (0.002 sec)
{noformat}
Correct result:
{noformat}
+---+-----+----------------+
| s | lap | avg(seq)over() |
+---+-----+----------------+
| 1 | 0 | 3.0000 |
| 2 | 2 | 3.0000 |
| 3 | 3 | 3.0000 |
| 4 | 3 | 3.0000 |
| 5 | 5 | 3.0000 |
+---+-----+----------------+
5 rows in set (0.000 sec)
{noformat}

Julien Fritsch made changes - 2023-04-27 13:46

Fix Version/s

10.3 [ 22126 ]

Julien Fritsch made changes - 2023-04-27 14:51

Fix Version/s

10.8 [ 26121 ]

Julien Fritsch made changes - 2023-11-28 10:38

Fix Version/s

10.9 [ 26905 ]

Julien Fritsch made changes - 2024-05-03 14:42

Fix Version/s

11.0 [ 28320 ]

Julien Fritsch made changes - 2024-09-10 14:58

Fix Version/s

10.4 [ 22408 ]

People

Assignee:: Sergei Petrunia

Reporter:: Timo Raitalaakso

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2023-03-02 14:24

Updated:: 2024-09-10 14:58

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration