pingcap / pingcap/tidb

Inconsistent results while executing functionally equivalent queries

Open
#65,652 2 comments 0 reactions 0 assignees View on GitHub
contribution may-affects-7.1 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/execution type/bug
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
CREATE TABLE t0(c0 TEXT(77) );

CREATE ALGORITHM=TEMPTABLE VIEW v0(c0) AS SELECT CAST(t0.c0 AS DECIMAL) FROM t0;

REPLACE INTO t0 VALUES ('P邀');
INSERT INTO t0 VALUES (NULL);
INSERT IGNORE INTO t0 VALUES ('d') ON DUPLICATE KEY UPDATE c0=NULL;

SELECT * FROM v0, t0;
SELECT * FROM v0, t0 WHERE (CASE false WHEN t0.c0 THEN 1645193734 WHEN NULL THEN DEGREES(v0.c0) ELSE t0.c0 END ) UNION ALL SELECT * FROM v0, t0 WHERE (NOT ((CASE false WHEN t0.c0 THEN 1645193734 WHEN NULL THEN DEGREES(v0.c0) ELSE t0.c0 END ))) UNION ALL SELECT * FROM v0, t0 WHERE (((CASE false WHEN t0.c0 THEN 1645193734 WHEN NULL THEN DEGREES(v0.c0) ELSE t0.c0 END )) IS NULL);

```

### 2. What did you expect to see? (Required)
Queries return same result

### 3. What did you see instead (Required)
```sql
mysql> SELECT * FROM v0, t0;
+------+------+
| c0 | c0 |
+------+------+
| 0 | P邀 |
| NULL | P邀 |
| 0 | P邀 |
| 0 | NULL |
| NULL | NULL |
| 0 | NULL |
| 0 | d |
| NULL | d |
| 0 | d |
+------+------+
9 rows in set, 2 warnings (0.00 sec)

mysql> SELECT * FROM v0, t0 WHERE (CASE false WHEN t0.c0 THEN 1645193734 WHEN NULL THEN DEGREES(v0.c0) ELSE t0.c0 END ) UNION ALL SELECT * FROM v0, t0 WHERE (NOT ((CASE false WHEN t0.c0 THEN 1645193734 WHEN NULL THEN DEGREES(v0.c0) ELSE t0.c0 END ))) UNION ALL SELECT * FROM v0, t0 WHERE (((CASE false WHEN t0.c0 THEN 1645193734 WHEN NULL THEN DEGREES(v0.c0) ELSE t0.c0 END )) IS NULL);
+----------------------------------+------+
| c0 | c0 |
+----------------------------------+------+
| 0.000000000000000000000000000000 | P邀 |
| NULL | P邀 |
| 0.000000000000000000000000000000 | P邀 |
| 0.000000000000000000000000000000 | d |
| NULL | d |
| 0.000000000000000000000000000000 | d |
| 0.000000000000000000000000000000 | NULL |
| NULL | NULL |
| 0.000000000000000000000000000000 | NULL |
+----------------------------------+------+
9 rows in set, 21 warnings (0.01 sec)

```

### 4. What is your TiDB version? (Required)
8.0.11-TiDB-v9.0.0-beta.1.pre-640-g24903d6b24

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.