pingcap / pingcap/tidb

Incorrect Join Result After adding an additional true expression

Open
#60,087 0 comments 0 reactions 0 assignees View on GitHub
impact/wrong-result may-affects-6.1 may-affects-6.5 may-affects-7.1 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/planner type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

### 1. Minimal reproduce step (Required)

```
CREATE TABLE t0(c0 TEXT(234) );
CREATE TABLE t1 LIKE t0;

INSERT INTO t1(c0) VALUES ('P');

INSERT INTO t0(c0) VALUES ('W');

UPDATE t0 SET c0=714418897;

select * FROM t1
INNER JOIN t0
ON (((CAST(HEX(t0.c0) AS TIME))) is NULL);

SELECT *
FROM t1
INNER JOIN t0
ON (((((CAST(HEX(t0.c0) AS TIME)) AND
(((((t1.c0) ^ 1)))))) IS NULL));
```

### 2. What did you expect to see? (Required)
```
Q1:
P,714418897

Q2:
P,714418897

```

### 3. What did you see instead (Required)

```
Q1:
P,714418897

Q2:
empty result
```
From the Q1's result, we can conclude that TiDB evaluate `((CAST(HEX(t0.c0) AS TIME)))` as NULL in the join condition, but after we add `and (t1.c0 ^ 1)`, which is a true expression based on the value of t1.c0. The second query should produce the same result as the first one, but it doesn't.
### 4. What is your TiDB version? (Required)
```
Release Version: v9.0.0-beta.1.pre-389-g21f9bb7b7a
Edition: Community
Git Commit Hash: 21f9bb7b7a7478d825c4dee4fbf2ce913e3e7cf1
Git Branch: HEAD
UTC Build Time: 2025-03-11 09:37:34
GoVersion: go1.23.7
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.