[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

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`

MariaDB Server

AVG over incorrect results from a inline view

Details

Description

Attachments

Activity

People

Dates

Git Integration