Unexpected result

[DerZc]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

The following test case has two equivalent queries, one in normal format, and another one in prepared statement, but they return different result.

CREATE TABLE t0(c2 BOOL);
INSERT INTO t0(c2) VALUES (false), (true);
SELECT t0.c2 FROM t0 GROUP BY t0.c2 HAVING BIT_AND(BINARY (- (-1)));   -- 0, 1
SET @a = -1;
PREPARE prepare_query FROM 'SELECT t0.c2 FROM t0 GROUP BY t0.c2 HAVING BIT_AND(BINARY (- (?)))';
EXECUTE prepare_query USING @a;   -- empty result

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

The prepared statement should also return 0 and 1, which is the result of this test case in MySQL

3. What did you see instead (Required)

The prepared statement returns empty result.

4. What is your TiDB version? (Required)

tidb_version()
Release Version: v7.5.1\nEdition: Community\nGit Commit Hash: 7d16cc79e81bbf573124df3fd9351c26963f3e70\nGit Branch: heads/refs/tags/v7.5.1\nUTC Build Time: 2024-02-27 14:28:32\nGoVersion: go1.21.6\nRace Enabled: false\nCheck Table Before Drop: false\nStore: unistore

[yihong0618]

try the latest TiDB version, the result is the same as mysql 0,1

[DerZc]

hi @yihong0618

thanks for your reply. I can still reproduce it on v9.0.0-beta.2.pre-75-g6b7a0ed

tidb_version()
Release Version: v9.0.0-beta.2.pre-75-g6b7a0ed\nEdition: Community\nGit Commit Hash: 6b7a0ed5b65820526a2e119df653d1feec502184\nGit Branch: HEAD\nUTC Build Time: 2025-07-08 03:24:23\nGoVersion: go1.23.10\nRace Enabled: false\nCheck Table Before Drop: false\nStore: unistore\nKernel Type: Classic

As there are two queries in this test case, so I expect the result should be

c2
0
1
c2
0
1

However, the actual result is

c2
0
1

[hawkingrei]

@DerZc I still haven't reproduced this issue.

mysql> create database test1
    -> ；
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 2 column 4 near "；"
mysql> create database test1;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t0(c2 BOOL);
ERROR 1050 (42S01): Table 'test.t0' already exists
mysql> INSERT INTO t0(c2) VALUES (false), (true);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT t0.c2 FROM t0 GROUP BY t0.c2 HAVING BIT_AND(BINARY (- (-1)));   -- 0, 1
+------+
| c2   |
+------+
|    0 |
|    1 |
+------+
2 rows in set (0.01 sec)

mysql> SET @a = -1;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE prepare_query FROM 'SELECT t0.c2 FROM t0 GROUP BY t0.c2 HAVING BIT_AND(BINARY (- (?)))';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE prepare_query USING @a;   -- empty result
+------+
| c2   |
+------+
|    0 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v9.0.0-beta.2.pre-92-ga7c824dfe0
Edition: Community
Git Commit Hash: a7c824dfe0a2ed9eea89605e50fff4ed95068273
Git Branch: HEAD
UTC Build Time: 2025-07-11 02:39:36
GoVersion: go1.23.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
Kernel Type: Classic |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql>

[hawkingrei]

and I test it with mysql. It is the same result.

mysql> use test
Database changed
mysql> CREATE TABLE t0(c2 BOOL);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t0(c2) VALUES (false), (true);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT t0.c2 FROM t0 GROUP BY t0.c2 HAVING BIT_AND(BINARY (- (-1)));   -- 0, 1
+------+
| c2   |
+------+
|    0 |
|    1 |
+------+
2 rows in set, 1 warning (0.00 sec)

mysql> SET @a = -1;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE prepare_query FROM 'SELECT t0.c2 FROM t0 GROUP BY t0.c2 HAVING BIT_AND(BINARY (- (?)))';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Statement prepared

mysql> EXECUTE prepare_query USING @a;   -- empty result
+------+
| c2   |
+------+
|    0 |
|    1 |
+------+
2 rows in set (0.00 sec)

mysql> select version()
    -> ;
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

[hawkingrei]

@DerZc Have you any update about this bug ？

[DerZc]

Hi @hawkingrei thank you very much for your work on analyzing this test case and sorry for delay. I run TiDB in Docker and this is the step I can trigger the bug.

zc@DESKTOP-AA5PU2J:~$ docker run --name tidb -d -p 4000:4000 -p 10080:10080 pingcap/tidb:nightly                                                                
65cf8f3aeae90859dc88fbcce06e1757ec91cb411e90cee9b5cc4410c8affa81  
zc@DESKTOP-AA5PU2J:~$ docker exec -it tidb /bin/bash                                                                                                            
[root@65cf8f3aeae9 /]# yum install mysql                                                                                                                        
Rocky Linux 9 - BaseOS                                                                                                          1.9 MB/s | 2.5 MB     00:01     
Rocky Linux 9 - AppStream                                                                                                       6.4 MB/s | 9.6 MB     00:01     
Rocky Linux 9 - Extras                                                                                                           22 kB/s |  17 kB     00:00     
Dependencies resolved.                                                                                                                                          
================================================================================================================================================================
 Package                                         Architecture                Version                                       Repository                      Size 
================================================================================================================================================================
Installing:                                                                                                                                                     
 mysql                                           x86_64                      8.0.41-2.el9_5                                appstream                      2.8 M 
Installing dependencies:                                                                                                                                        
 libedit                                         x86_64                      3.1-38.20210216cvs.el9                        baseos                         103 k 
 mariadb-connector-c-config                      noarch                      3.2.6-1.el9_0                                 appstream                      9.8 k 
 mysql-common                                    x86_64                      8.0.41-2.el9_5                                appstream                       68 k 
                                                                                                                                                                
Transaction Summary                                                                                                                                             
================================================================================================================================================================
Install  4 Packages                                                                                                                                             
                                                                                                                                                                
Total download size: 3.0 M                                                                                                                                      
Installed size: 60 M                                                                                                                                            
Is this ok [y/N]: y                          
Downloading Packages:
(1/4): mariadb-connector-c-config-3.2.6-1.el9_0.noarch.rpm                                                                      117 kB/s | 9.8 kB     00:00    
(2/4): libedit-3.1-38.20210216cvs.el9.x86_64.rpm                                                                                766 kB/s | 103 kB     00:00    
(3/4): mysql-common-8.0.41-2.el9_5.x86_64.rpm                                                                                   1.1 MB/s |  68 kB     00:00    
(4/4): mysql-8.0.41-2.el9_5.x86_64.rpm                                                                                          8.0 MB/s | 2.8 MB     00:00    
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                                                           1.9 MB/s | 3.0 MB     00:01     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                                                                                                        1/1 
  Installing       : mariadb-connector-c-config-3.2.6-1.el9_0.noarch                                                                                        1/4 
  Installing       : mysql-common-8.0.41-2.el9_5.x86_64                                                                                                     2/4 
  Installing       : libedit-3.1-38.20210216cvs.el9.x86_64                                                                                                  3/4 
  Installing       : mysql-8.0.41-2.el9_5.x86_64                                                                                                            4/4 
  Running scriptlet: mysql-8.0.41-2.el9_5.x86_64                                                                                                            4/4 
  Verifying        : libedit-3.1-38.20210216cvs.el9.x86_64                                                                                                  1/4 
  Verifying        : mysql-8.0.41-2.el9_5.x86_64                                                                                                            2/4 
  Verifying        : mariadb-connector-c-config-3.2.6-1.el9_0.noarch                                                                                        3/4 
  Verifying        : mysql-common-8.0.41-2.el9_5.x86_64                                                                                                     4/4 

Installed:
  libedit-3.1-38.20210216cvs.el9.x86_64   mariadb-connector-c-config-3.2.6-1.el9_0.noarch   mysql-8.0.41-2.el9_5.x86_64   mysql-common-8.0.41-2.el9_5.x86_64  

Complete!
[root@65cf8f3aeae9 /]# mysql -h 127.0.0.1 -P 4000 -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2097154
Server version: 8.0.11-TiDB-v9.0.0-beta.2.pre-75-g6b7a0ed TiDB Server (Apache License 2.0) Community Edition, MySQL 8.0 compatible

Copyright (c) 2000, 2025, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test1;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE t0(c2 BOOL);
ERROR 1046 (3D000): No database selected 
mysql> USE test1;
Database changed
mysql> CREATE TABLE t0(c2 BOOL);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t0(c2) VALUES (false), (true);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT t0.c2 FROM t0 GROUP BY t0.c2 HAVING BIT_AND(BINARY (- (-1)));
+------+
| c2   |
+------+
|    0 |
|    1 |
+------+
2 rows in set, 1 warning (0.00 sec)

mysql> SET @a = -1;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE prepare_query FROM 'SELECT t0.c2 FROM t0 GROUP BY t0.c2 HAVING BIT_AND(BINARY (- (?)))';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE prepare_query USING @a;
Empty set, 13 warnings (0.01 sec)

mysql> select tidb_version();
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                        
                                                                                                                                |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v9.0.0-beta.2.pre-75-g6b7a0ed
Edition: Community
Git Commit Hash: 6b7a0ed5b65820526a2e119df653d1feec502184
Git Branch: HEAD
UTC Build Time: 2025-07-08 03:24:23
GoVersion: go1.23.10
Race Enabled: false
Check Table Before Drop: false
Store: unistore
Kernel Type: Classic |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)