Erroneous `from_binary()` Wrapper Added to Predicate on Join Build Side Causes Empty Result
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Bug Report
Please answer these questions before submitting your issue. Thanks!
### 1. Minimal Reproduce Step (Required)
```sql
DROP DATABASE IF EXISTS repro_tidb615_db2_min;
CREATE DATABASE repro_tidb615_db2_min;
USE repro_tidb615_db2_min;
SET SESSION sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET time_zone='+00:00';
CREATE TABLE src(
id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c0 BIGINT
);
INSERT INTO src(c0) VALUES (10);
CREATE TABLE l(
id BIGINT NOT NULL PRIMARY KEY,
c0 BIGINT
);
CREATE TABLE r(
id BIGINT NOT NULL PRIMARY KEY,
c0 BIGINT
);
INSERT INTO l SELECT id, c0 FROM src;
INSERT INTO r SELECT id, c0 FROM src;
-- Single‑table: returns 1 row
SELECT BIT_AND(id) AS a, id
FROM src
WHERE '^' >= BINARY(id | '2020-01-18')
GROUP BY id;
-- Split/join: returns empty set (wrong)
SELECT BIT_AND(v.id) AS a, v.id
FROM (
SELECT l.id, l.c0
FROM l JOIN r ON l.id = r.id
) v
WHERE '^' >= BINARY(v.id | '2020-01-18')
GROUP BY v.id;
```
### 2. What Did You Expect to See? (Required)
Both queries should return a single row because the underlying data is identical (`id=1`, `c0=10`), and the `WHERE` condition should evaluate identically:
- `id | '2020-01-18'` → `1 | 2020` = `2021`
- `BINARY(2021)` → binary string `'2021'`
- `'^' >= BINARY(...)` → comparison should be true
Therefore the single‑table and the join query should both return:
```
+---+----+
| a | id |
+---+----+
| 1 | 1 |
+---+----+
```
### 3. What Did You See Instead? (Required)
- Single‑table query: returns `(1, 1)` — correct
- Join query: returns `Empty set` — wrong
### 4. What Is Your TiDB Version? (Required)
I tested such a case in TiDB-v9.0.0.
### 5. Execution Plan Differences
**Single‑table query (correct):**
```
Selection_8 [cop[tikv]] ge("^", cast(bitor(src.id, 2020), binary(1)))
```
The predicate is straightforward: `BINARY(id | 2020)` is represented as `cast(bitor(...), binary(1))`.
**Split/join query (incorrect)** — predicate pushed to both sides, but the Build side is wrong:
```
-- Build side (table r)
Selection_32 [cop[tikv]] ge("^", from_binary(cast(bitor(r.id, 2020), binary(1))))
-- Probe side (table l)
Selection_29 [cop[tikv]] ge("^", cast(bitor(l.id, 2020), binary(1)))
```
The predicate on table `r` (Build side) is wrapped with an extra `from_binary()`, while the Probe side keeps the original expression. This causes the comparison `'^' >= from_binary(cast(...))` to evaluate to `FALSE`, filtering out all rows on the Build side and leading to an empty join result.
### 6. Root Cause
When the optimizer pushes the `WHERE` condition down to both sides of a join, it replicates the predicate expression. For the expression `BINARY(id | '2020-01-18')`, the internal representation is `Cast(BitOr(id, 2020), BinaryCharset)`. During the rewrite/push‑down process, the expression on the Build side is incorrectly transformed into `from_binary(Cast(BitOr(id, 2020), BinaryCharset))`.
`from_binary()` is intended to convert a binary string back to a normal string, which reverses the effect of `BINARY`. Adding it here changes the data type and comparison semantics, causing the predicate to incorrectly evaluate as `FALSE`. The Probe side and the single‑table path do not get this erroneous wrapper, so they evaluate correctly.
This is a bug in the predicate push‑down or expression cloning logic for join conditions, where a `Cast(..., BinaryCharset)` node is mistakenly paired with a compensating `from_binary()` call on one side only.
Contributor guide
Assessment
This issue has not been assessed yet.