Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5
-
None
Description
If a query contains a degenerated subquery with window function then the query may return a wrong result.
This can be seen for the following examples:
CREATE TABLE t1 (a int DEFAULT 10); |
INSERT INTO t1 VALUES (7), (2), (3); |
SELECT * FROM t1 WHERE (SELECT AVG(3)) > 2; |
SELECT * FROM t1 WHERE (SELECT AVG(3) OVER ()) > 2; |
INSERT INTO t1 VALUES((SELECT avg(4) OVER ())); |
SELECT * FROM t1; |
For the above test case we have
MariaDB [test]> CREATE TABLE t1 (a int DEFAULT 10);
|
Query OK, 0 rows affected (0.017 sec)
|
|
MariaDB [test]> INSERT INTO t1 VALUES (7), (2), (3);
|
Query OK, 3 rows affected (0.002 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> SELECT * FROM t1 WHERE (SELECT AVG(3)) > 2;
|
+------+
|
| a |
|
+------+
|
| 7 |
|
| 2 |
|
| 3 |
|
+------+
|
3 rows in set (0.003 sec)
|
|
MariaDB [test]> SELECT * FROM t1 WHERE (SELECT AVG(3) OVER ()) > 2;
|
Empty set (0.000 sec)
|
|
MariaDB [test]> INSERT INTO t1 VALUES((SELECT avg(4) OVER ()));
|
Query OK, 1 row affected (0.002 sec)
|
|
MariaDB [test]> SELECT * FROM t1;
|
+------+
|
| a |
|
+------+
|
| 7 |
|
| 2 |
|
| 3 |
|
| NULL |
|
+------+
|
4 rows in set (0.001 sec)
|
For the second select the expected result is the same as for the first select. For the last select the expected result has to contain 4 instead of NULL.
The transformation of a such a subquery
(SELECT <expr>)
into
<expr>
is not valid if <expr> contains set_functions or window function.
The transformation was not applied when <expr> contained window function, but erroneously was applied if <expr> contained only window functions.