Wrong result set from query with in subquery that uses window function

XMLWordPrintable

Details

• Bug
• Status: Confirmed
• Major
• Resolution: Unresolved
• 10.2.14, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10

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 |` ```+------+ ```

People

Sergei Petrunia
Igor Babaev
Votes:
0 Vote for this issue
Watchers:
3 Start watching this issue

Dates

Created:
Updated:

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.