Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2.14, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Description
The following test case demonstrates the problem:
create table t1 (a int, b int, c int, d int);
|
create table t2 (e int, f int, g int);
|
insert into t1 values
|
(1,1,18,1), (2,1,25,1), (1,3,40,1), (2,1,15,4),
|
(4,2,24,4), (3,2,23,1), (1,2,40,2), (3,4,17,2),
|
(5,5,65,1), (2,3,70,3), (1,4,35,3), (2,3,25,3);
|
insert into t2 values
|
(1,2,38), (2,3,15), (1,3,40), (1,4,35),
|
(2,2,70), (3,4,23), (5,5,12), (5,4,17),
|
(3,3,17), (4,2,24), (2,5,25), (5,1,65);
|
|
select * from t1 where (a>1) and
|
(a) in
|
(
|
select sum(e) over (partition by f)
|
from t2
|
where e<5
|
)
|
;
|
The output for the above query is:
MariaDB [test]> select * from t1 where (a>1) and
|
-> (a) in
|
-> (
|
-> select sum(e) over (partition by f)
|
-> from t2
|
-> where e<5
|
-> )
|
-> ;
|
Empty set (0.00 sec)
|
This not correct as we have:
ariaDB [test]> select sum(e) over (partition by f)
|
-> from t2
|
-> where e<5
|
-> ;
|
+------------------------------+
|
| sum(e) over (partition by f) |
|
+------------------------------+
|
| 7 |
|
| 6 |
|
| 6 |
|
| 4 |
|
| 7 |
|
| 4 |
|
| 6 |
|
| 7 |
|
| 2 |
|
+------------------------------+
|
|
MariaDB [test]> select a from t1 where (a>1);
|
+------+
|
| a |
|
+------+
|
| 2 |
|
| 2 |
|
| 4 |
|
| 3 |
|
| 3 |
|
| 5 |
|
| 2 |
|
| 2 |
|
+------+
|
Attachments
Issue Links
- is duplicated by
-
MDEV-12356 Wrong query result when window function is used in a subquery with HAVING
- Closed
- relates to
-
MDEV-12356 Wrong query result when window function is used in a subquery with HAVING
- Closed