Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
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
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)
|
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`