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.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description | If a query contains a degenerated subquery with window function then the query may return a wrong result. |
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: |
| 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: |
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: {code:sql} CREATE TABLE t1 (a int DEFAULT 2); |
| 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: {code:sql} CREATE TABLE t1 (a int DEFAULT 2); |
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: {code:sql} CREATE TABLE t1 (a int DEFAULT 10); INSERT INTO t1 VALUES (7), (2); |
| 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: {code:sql} CREATE TABLE t1 (a int DEFAULT 10); INSERT INTO t1 VALUES (7), (2); |
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: {code:sql} CREATE TABLE t1 (a int DEFAULT 10); INSERT INTO t1 VALUES (7), (2), (3); SELECT * FROM t1 WHERE (SELECT AVG(3) OVER ()) > 2); |
| 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: {code:sql} CREATE TABLE t1 (a int DEFAULT 10); INSERT INTO t1 VALUES (7), (2), (3); SELECT * FROM t1 WHERE (SELECT AVG(3) OVER ()) > 2); |
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: {code:sql} 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; {code} For the above test case we have {noformat} 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) {noformat} 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. |
| Assignee | Igor Babaev [ igor ] |
| Fix Version/s | 10.5 [ 23123 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Igor Babaev [ igor ] | Rex Johnston [ JIRAUSER52533 ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Rex Johnston [ JIRAUSER52533 ] | Igor Babaev [ igor ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
A fix for this bug was pushed into 10.5. It has to be merged upstream as it is.
| Fix Version/s | 10.5.28 [ 29952 ] | |
| Fix Version/s | 10.6.21 [ 29953 ] | |
| Fix Version/s | 10.11.11 [ 29954 ] | |
| Fix Version/s | 11.2.7 [ 29955 ] | |
| Fix Version/s | 11.4.5 [ 29956 ] | |
| Fix Version/s | 11.7.2 [ 29914 ] | |
| Fix Version/s | 11.8.1 [ 29961 ] | |
| Fix Version/s | 10.5 [ 23123 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Fix Version/s | 11.2.7 [ 29955 ] |
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.