pingcap / pingcap/tidb

Logical bug: numeric format changed after relaxing HAVING condition [v6.0.0, v6.4.0]

Open
#40,012 1 comment 0 reactions 0 assignees View on GitHub
may-affects-4.0 may-affects-5.0 may-affects-5.1 may-affects-5.2 may-affects-5.3 may-affects-5.4 may-affects-6.0 may-affects-6.1 may-affects-6.2 may-affects-6.3 may-affects-6.4 severity/moderate sig/execution 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)

```sql
drop table if exists t;
CREATE TABLE t (c1 INT);
INSERT INTO t VALUES (1);

SELECT 0.1 AS f1 FROM (SELECT 1 FROM t) AS `t1` HAVING (DATE_SUB(-1, INTERVAL 1 DAY_MINUTE) != f1) OR 1; -- sql1
SELECT 0.1 AS f1 FROM (SELECT 1 FROM t) AS `t1` HAVING 1; -- sql2
```

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

In theory, the result of sql1 ⊆ the result of sql2.
Because the `HAVING 1` in sql2 is always true, but the `HAVING (DATE_SUB(-1, INTERVAL 1 DAY_MINUTE) != f1) OR 1` in sql1 may not be true.

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

However, the mumeric format changed after changing `HAVING (DATE_SUB(-1, INTERVAL 1 DAY_MINUTE) != f1) OR 1` to `HAVING 1`, seems like a logical bug:

```sql
mysql> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v6.4.0 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT 0.1 AS f1 FROM (SELECT 1 FROM t) AS `t1` HAVING (DATE_SUB(-1, INTERVAL 1 DAY_MINUTE) != f1) OR 1;
+----------------------------------+
| f1 |
+----------------------------------+
| 0.100000000000000000000000000000 |
+----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT 0.1 AS f1 FROM (SELECT 1 FROM t) AS `t1` HAVING 1;
+-----+
| f1 |
+-----+
| 0.1 |
+-----+
1 row in set (0.00 sec)
```
### 4. What is your TiDB version? (Required)

```sql
Release Version: v6.4.0
Edition: Community
Git Commit Hash: cf36a9ce2fe1039db3cf3444d51930b887df18a1
Git Branch: heads/refs/tags/v6.4.0
UTC Build Time: 2022-11-13 05:25:30
GoVersion: go1.19.2
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore
```

### 5. Hope these can be helpful for your debugging:

1. The bug cannot be reproduced after changing `DATE_SUB(-1, INTERVAL 1 DAY_MINUTE` to `NULL`.
2. We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/r/pingcap/tidb/tags
We found that the bug first occurred in pingcap/tidb:v6.0.0, it cannot be reproduced in pingcap/tidb:v5.4.3 (we can't find more intermediate versions in dockerhub):

```sql
mysql> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v6.0.0 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT 0.1 AS f1 FROM (SELECT 1 FROM t) AS `t1` HAVING (DATE_SUB(-1, INTERVAL 1 DAY_MINUTE) != f1) OR 1;
+----------------------------------+
| f1 |
+----------------------------------+
| 0.100000000000000000000000000000 |
+----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT 0.1 AS f1 FROM (SELECT 1 FROM t) AS `t1` HAVING 1;
+-----+
| f1 |
+-----+
| 0.1 |
+-----+
1 row in set (0.00 sec)

mysql> select version();
+--------------------+
| version() |
+--------------------+
| 5.7.25-TiDB-v5.4.3 |
+--------------------+
1 row in set (0.00 sec)

mysql> SELECT 0.1 AS f1 FROM (SELECT 1 FROM t) AS `t1` HAVING (DATE_SUB(-1, INTERVAL 1 DAY_MINUTE) != f1) OR 1;
+-----+
| f1 |
+-----+
| 0.1 |
+-----+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT 0.1 AS f1 FROM (SELECT 1 FROM t) AS `t1` HAVING 1;
+-----+
| f1 |
+-----+
| 0.1 |
+-----+
1 row in set (0.01 sec)
```

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.