pingcap / pingcap/tidb

Unexpected result when use HEX as the WHERE condition of prepared statement

Open
#63,649 0 comments 0 reactions 0 assignees View on GitHub
contribution sig/planner 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)

Hi,

In the following test case, the expression `HEX(-(?))` in the first statement returns 1, however, when using this expression in the WHERE condition, as shown in the second expression, the query returns an empty result. The equivalent normal query can produce the correct result. And I checked in MySQL, the second query also returns 1.

```
CREATE TABLE t0(c0 DECIMAL);
INSERT t0 VALUES (1);
SET @b = '-1';

PREPARE prepare_query FROM 'SELECT HEX((- (?)))';
EXECUTE prepare_query USING @b; -- 1
DEALLOCATE PREPARE prepare_query;

PREPARE prepare_query FROM 'SELECT t0.c0 FROM t0 WHERE HEX((- (?)))';
EXECUTE prepare_query USING @b; -- empty result
DEALLOCATE PREPARE prepare_query;

SELECT t0.c0 FROM t0 WHERE HEX((- ('-1'))); -- 1
```

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

The second prepared statement returns 1.

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

The second prepared statement returns an empty result.

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

Release Version: v9.0.0-beta.2.pre-196-g312226f869\nEdition: Community\nGit Commit Hash: 312226f869d56922135e1c177cac6c90319c3134\nGit Branch: HEAD\nUTC Build Time: 2025-09-21 08:27:14\nGoVersion: go1.23.11\nRace Enabled: false\nCheck Table Before Drop: false\nStore: unistore\nKernel Type: Classic

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.