Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.8.0, 10.6, 10.11, 11.4, 10.5(EOL)
-
None
-
git rev-parse HEAD
11a6c1b30a12c448ddfe05e1b818a6a228e90e43
Description
Description:
When using an IN condition with a subquery that contains window function, the query returns inconsistent results. The first query correctly evaluates IN, but the second query, which includes additional filtering using NOT IN and IS NULL, unexpectedly removes a row (c1 = 36).
Steps to Reproduce:
CREATE TABLE `t1` (
|
`c1` int, |
`c2` int |
);
|
|
|
INSERT INTO `t1` VALUES (30,NULL),(34,88),(36,7),(37,0); |
|
|
CREATE TABLE `t2` (
|
`c3` int |
);
|
|
|
INSERT INTO `t2` VALUES (3); |
Query 1: Evaluating IN condition with subquery in select clause
select
|
c1,
|
((t1.c2) in (select
|
PERCENT_RANK() over w0 as c_0
|
from
|
t2 as ref_1
|
window w0 as (partition by t1.c1 order by t1.c1 asc)
|
)) as w_3
|
from t1;
|
Output:
+------+------+
|
| c1 | w_1 |
|
+------+------+
|
| 30 | NULL | |
| 34 | 0 | |
| 36 | NULL | |
| 37 | NULL | |
+------+------+
|
4 rows in set (0.00 sec) |
Query 2: Applying NOT IN and IS NULL conditions in where clause
select
|
c1,
|
((t1.c2) in (select
|
PERCENT_RANK() over w0 as c_0
|
from
|
t2 as ref_1
|
window w0 as (partition by t1.c1 order by t1.c1 asc)
|
)) as w_1
|
from t1
|
where ((not ((((t1.c2) in (select
|
PERCENT_RANK() over w0 as c_0
|
from
|
t2 as ref_1
|
window w0 as (partition by t1.c1 order by t1.c1 asc)
|
))))))
|
or ((( ( ((t1.c2) in (select
|
PERCENT_RANK() over w0 as c_0
|
from
|
t2 as ref_2
|
window w0 as (partition by t1.c1 order by t1.c1 asc)
|
)))) is null)) |
Output:
+------+------+
|
| c1 | w_1 |
|
+------+------+
|
| 30 | NULL | |
| 34 | 0 | |
| 37 | NULL | |
+------+------+
|
3 rows in set (0.00 sec) |
Expected Behavior:
The second query should retain all rows from the first query, as the filtering conditions (NOT IN and IS NULL) should not remove any rows. However, c1 = 36 is unexpectedly removed.
Actual Behavior:
c1 = 36 is removed unexpectedly, despite the logical conditions that should retain it.