Details
Major Description
Description:
When using a subquery result in an IN condition, the query returns inconsistent results. The first query evaluates (SELECT c1 FROM t1 ORDER BY t1.c1 LIMIT 1) IN (SELECT c2 FROM t2 WHERE condition) and returns 1. However, when replacing the subquery with its actual result (2), the same IN condition returns 0. Both queries should return the same result, but they do not.
Steps to Reproduce:
CREATE TABLE `t1` (
|
`c1` int |
);
|
|
|
INSERT INTO `t1` VALUES (2); |
|
|
CREATE TABLE `t2` (
|
`c2` int |
);
|
|
|
INSERT INTO `t2` VALUES (3); |
|
|
CREATE TABLE `t3` (
|
`c3` int |
);
|
|
|
INSERT INTO `t3` VALUES (4); |
Query 1: Using SELECT subquery in IN condition
select
|
(select c1 from t1 order by t1.c1 limit 1) |
in (select
|
ref_2.c2 as c_0
|
from
|
t2 as ref_2
|
where (((ref_3.c3) != (ref_3.c3)) or (true)) |
) as w_1
|
from t3 as ref_3;
|
Output:
+------+
|
| w_1 |
|
+------+
|
| 1 | |
+------+
|
1 row in set (0.01 sec) |
Query 2: Replacing subquery with actual value
select c1 from t1 order by t1.c1 limit 1; |
Output:
+------+
|
| c1 |
|
+------+
|
| 2 | |
+------+
|
1 row in set (0.01 sec) |
select
|
(2) |
in (select
|
ref_2.c2 as c_0
|
from
|
t2 as ref_2
|
where (((ref_3.c3) != (ref_3.c3)) or (true)) |
) as w_1
|
from t3 as ref_3;
|
Output:
+------+
|
| w_1 |
|
+------+
|
| 0 | |
+------+
|
1 row in set (0.00 sec) |
Expected Behavior:
The result of the second query should match the result of the first query since (SELECT c1 FROM t1 ORDER BY t1.c1 LIMIT 1) resolves to 2.
Actual Behavior:
The results of query1 and query2 is different.