pingcap / pingcap/tidb

null value not properly assigned to session variable in UPDATE stmt

Open
#47,174 1 comment 0 reactions 0 assignees View on GitHub
type/question
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)

my:root@127.0.0.1:37481=> show create table table1;
Table | Create Table
-------+------------------------------------------------------------
table1 | CREATE TABLE "table1" ( +
| "id" int(11) NOT NULL, +
| "val" int(11) DEFAULT NULL, +
| PRIMARY KEY ("id") /*T![clustered_index] CLUSTERED */ +
| ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

my:root@127.0.0.1:37481=> update table1 set val=null;
UPDATE 1
my:root@127.0.0.1:37481=> select * from table1;
id | val
-{}{}+{}{}--
1 |
(1 row)

my:root@127.0.0.1:37481=> set @val=3;
SET
my:root@127.0.0.1:37481=> select @val;
@val
------
3
(1 row)

my:root@127.0.0.1:37481=> insert into table1 values(1, 3) on duplicate key update val=(@val:=null), val=@val;
INSERT 2
my:root@127.0.0.1:37481=> select @val;
@val
------
3
(1 row)

my:root@127.0.0.1:37481=> select * from table1;
id | val
-{}{}+{}{}--
1 | 3
(1 row)

### 2. What did you expect to see? (Required)
Since I am assigning a null value to @val in the update statement, I would expect the result of @val would be null. But actually,
(note this same buggy behavior also happens to "INSERT ON DUPLICATE KEY UPDATE" stmt).
### 3. What did you see instead (Required)
the original value of 3 was retained.

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

my:root@127.0.0.1:37481=> select tidb_version();
tidb_version()
-----------------------------------------------------------
Release Version: v7.3.0 +
Edition: Community +
Git Commit Hash: 40b72e7a9a4fc9670d4c5d974dd503a3c6097471+
Git Branch: heads/refs/tags/v7.3.0 +
UTC Build Time: 2023-08-08 10:08:14 +
GoVersion: go1.20.7 +
Race Enabled: false +
Check Table Before Drop: false +
Store: tikv
(1 row)

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.