Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Unexpected Results of IN expression With NATURAL RIGHT JOIN #49476

Closed
suyZhong opened this issue Dec 14, 2023 · 3 comments · Fixed by #53370
Closed

Unexpected Results of IN expression With NATURAL RIGHT JOIN #49476

suyZhong opened this issue Dec 14, 2023 · 3 comments · Fixed by #53370
Assignees
@winoros
Labels
affects-6.1 affects-6.5 affects-7.1 affects-7.5 affects-8.1 fuzz/sqlancer severity/major sig/planner SIG: Planner type/bug This issue is a bug.

Comments

@suyZhong
Copy link

Bug Report

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 INT);
CREATE TABLE t1(c0 BOOLEAN);
INSERT INTO t0 (c0) VALUES (0);

SELECT * FROM t1 NATURAL RIGHT JOIN t0; -- 0
SELECT (false IN (t1.c0, t0.c0)) FROM t1 NATURAL RIGHT JOIN t0; -- 1
SELECT * FROM t1 NATURAL RIGHT JOIN t0 WHERE (false IN (t1.c0, t0.c0));
-- Expected: 0
-- Actual: Empty set

2. What did you expect to see? (Required)

The third SELECT returns an empty set, which is surprising: If the result of second query is 1 (TRUE), the value of the IN expression should be 1, and thus the third query should return 0.

Not sure if the issue is related to #49108

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

Release Version: v7.6.0-alpha
Edition: Community
Git Commit Hash: 918df0ae50568ebc54bd6d89f2d9a49bc9b79b06
Git Branch: heads/refs/tags/v7.6.0-alpha
UTC Build Time: 2023-12-14 04:15:47
GoVersion: go1.21.5
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
@suyZhong suyZhong added the type/bug This issue is a bug. label Dec 14, 2023
@suyZhong
Copy link
Author

/label fuzz/sqlancer

@winoros
Copy link
Member

winoros commented May 13, 2024

This one is related to the null-rejective checking.
Cannot solve in a short time.

@ghazalfamilyusa
Copy link
Contributor

As @winoros said, it is caused by null rejection logic which incorrectly converts the right outer join to inner join.
See plan below (plan 1). The equivalent query explain SELECT * FROM t1 NATURAL RIGHT JOIN t0 WHERE (false IN (t1.c0) or false in (t0.c0)) works OK (see plan 2). The null rejection logic executes incorrectly false in (0,NULL) as false since false = 0 is true. But, it does executes false in (0) or false in (null) as true.

Plan1
explain format="brief" SELECT * FROM t1 NATURAL RIGHT JOIN t0 WHERE (false IN (t1.c0, t0.c0))

+---------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------+
| HashJoin | 1.00 | root | | inner join, equal:[eq(test.t1.c0, test.t0.c0)], other cond:in(0, test.t1.c0, test.t0.c0) |
| ├─TableReader(Build) | 1.00 | root | | data:Selection |
| │ └─Selection | 1.00 | cop[tikv] | | not(isnull(test.t0.c0)) |
| │ └─TableFullScan | 1.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
| └─TableReader(Probe) | 1.00 | root | | data:Selection |
| └─Selection | 1.00 | cop[tikv] | | not(isnull(test.t1.c0)) |
| └─TableFullScan | 1.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+---------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------+

Plan2
explain format="brief" SELECT * FROM t1 NATURAL RIGHT JOIN t0 WHERE (false IN (t1.c0) or false in (t0.c0))

+-------------------------------+---------+-----------+---------------+------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+---------------+------------------------------------------------------+
| Projection | 0.80 | root | | test.t0.c0 |
| └─Selection | 0.80 | root | | or(eq(0, test.t1.c0), eq(0, test.t0.c0)) |
| └─HashJoin | 1.00 | root | | right outer join, equal:[eq(test.t1.c0, test.t0.c0)] |
| ├─TableReader(Build) | 1.00 | root | | data:Selection |
| │ └─Selection | 1.00 | cop[tikv] | | not(isnull(test.t1.c0)) |
| │ └─TableFullScan | 1.00 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
| └─TableReader(Probe) | 1.00 | root | | data:TableFullScan |
| └─TableFullScan | 1.00 | cop[tikv] | table:t0 | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+---------------+------------------------------------------------------+

@ghazalfamilyusa ghazalfamilyusa mentioned this issue May 17, 2024
Merged
13 tasks
@winoros winoros added affects-6.1 and removed may-affects-6.1 may-affects-5.4 This bug maybe affects 5.4.x versions. labels May 19, 2024
ti-chi-bot bot pushed a commit that referenced this issue May 21, 2024
@ghazalfamilyusa
Optimizer: Fix null filtering logic for IN list (#53370)
61a2981 
close #49476
@ti-chi-bot ti-chi-bot mentioned this issue May 21, 2024
Closed
13 tasks
RidRisR pushed a commit to RidRisR/tidb that referenced this issue May 23, 2024
@ghazalfamilyusa @RidRisR
Optimizer: Fix null filtering logic for IN list (pingcap#53370)
6b5476d 
close pingcap#49476
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@winoros winoros

Labels
affects-6.1 affects-6.5 affects-7.1 affects-7.5 affects-8.1 fuzz/sqlancer severity/major sig/planner SIG: Planner type/bug This issue is a bug.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Optimizer: Fix null filtering logic for IN list ghazalfamilyusa/tidb
5 participants
@winoros @aytrack @suyZhong @ti-chi-bot @ghazalfamilyusa