pingcap / pingcap/tidb

after changing NULLIF function ,SET value unexpectedly change

Open
#66,323 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)

```
DROP DATABASE IF EXISTS test;
CREATE DATABASE IF NOT EXISTS test;
SET GLOBAL sort_buffer_size = 64 * 1024 * 1024;
SET GLOBAL read_rnd_buffer_size = 8 * 1024 * 1024;
USE test;

CREATE TABLE t3 (
c6 TINYINT NULL,
c14 SET('x','y','z') NULL
);

INSERT INTO t3 (c6, c14) VALUES (80, 'y,z');

SELECT NULLIF(t3.c14, t3.c6) AS type1, NULLIF(NULLIF(t3.c14, t3.c6), t3.c6) AS type2 FROM t3;

```

### 2. What did you expect to see? (Required)
type1 and type2 should bew the same ,but because of the NULLIF the SET value unexpectedly is transformed to INT value

### 3. What did you see instead (Required)
```
mysql> SELECT NULLIF(t3.c14, t3.c6) AS type1, NULLIF(NULLIF(t3.c14, t3.c6), t3.c6) AS type2 FROM t3;
+-------+-------+
| type1 | type2 |
+-------+-------+
| y,z | 6 |
+-------+-------+
1 row in set (0.01 sec)
```

### 4. What is your TiDB version? (Required)

```
mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version() |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v7.5.1
Edition: Community
Git Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70
Git Branch: heads/refs/tags/v7.5.1
UTC Build Time: 2024-02-27 14:28:32
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

Contributor guide

Open the contributing guide

Research direction

Start by running the reported SQL reproduction against TiDB v7.5.1 and compare the types and values returned by the nested NULLIF expressions. Trace NULLIF type resolution and SET handling from that entry point, then add or update a regression test showing that type1 and type2 remain equivalent and preserve the SET value.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
45/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.