Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: Tidb查询sql在where语句中添加括号，会导致查询的列名字错误
|
username: 酒余尚温
[TiDB Usage Environment] Production Environment
[TiDB Version] Release Version: v7.2.0
[Encountered Problem: Phenomenon and Impact]
The query is as follows
SELECT teacher_id, id_number FROM tb_teacher_user WHERE (teacher_id IN (125));
SELECT teacher_id, id_number FROM tb_teacher_user WHERE teacher_id IN (125);
Result
When I swap the order of teacher_id and id_number, it is also correct. The first modification of the SQL is also correct, but after that, the column names are incorrect, although the data is correct.
The table structure is as follows
teacher_id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘Teacher ID’,
teacher_name varchar(100) NOT NULL COMMENT ‘Teacher Name’,
id_number varchar(30) DEFAULT ‘’ COMMENT ‘ID Number’,
phone varchar(50) DEFAULT NULL COMMENT ‘Phone Number’,
password varchar(100) DEFAULT NULL COMMENT ‘Login Password’,
province_name varchar(50) DEFAULT NULL COMMENT ‘Province Name’,
province_code varchar(20) DEFAULT NULL COMMENT ‘Province Code’,
city_name varchar(50) DEFAULT NULL COMMENT ‘City Name’,
city_code varchar(20) DEFAULT NULL COMMENT ‘City Code’,
county_name varchar(50) DEFAULT NULL COMMENT ‘County Name’,
county_code varchar(20) DEFAULT NULL COMMENT ‘County Code’,
school_id bigint(11) DEFAULT NULL COMMENT ‘School ID’,
school_name varchar(50) DEFAULT NULL COMMENT ‘School Name’,
last_login_time datetime DEFAULT NULL COMMENT ‘Last Login Time’,
login_num int(50) DEFAULT ‘0’ COMMENT ‘Login Count’,
role_name varchar(100) DEFAULT NULL COMMENT ‘Role Name’,
credentials_non_expired tinyint(1) DEFAULT ‘0’ COMMENT ‘Password Expired’,
enabled tinyint(1) DEFAULT ‘1’ COMMENT ‘Enabled Status [1=Normal, 0=Disabled]’,
account_non_locked tinyint(1) DEFAULT ‘1’ COMMENT ‘Account Locked [0=Locked, 1=Normal]’,
account_non_expired tinyint(1) DEFAULT ‘1’ COMMENT ‘Account Expired [0=Expired, 1=Not Expired]’,
user_source_type int(1) DEFAULT NULL COMMENT ‘User Source’,
create_time datetime DEFAULT NULL COMMENT ‘Creation Time’,
account_v tinyint(1) DEFAULT ‘1’ COMMENT ‘Account Type [0=Virtual, 1=Real]’,
To add, when this bug occurs, forcing an alias is also ineffective
Adding a complete reproduction and using the command line
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tb_test
-- ----------------------------
DROP TABLE IF EXISTS `tb_test`;
CREATE TABLE `tb_test` (
`id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`id_number` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
-- ----------------------------
-- Records of tb_test
-- ----------------------------
BEGIN;
INSERT INTO `tb_test` (`id`, `name`, `id_number`) VALUES (1, 'test1', '12716761726');
INSERT INTO `tb_test` (`id`, `name`, `id_number`) VALUES (2, 'test2', '125616261172');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
[Observed Unexpected Behavior]
Column names are returned incorrectly, and alias specification is also incorrect.
[Expected Behavior]
Correct column names in the SELECT statement.
|
username: 
|
username: 
|
username: 
|
username: 
It shouldn’t be like this. Could it be related to the SQL tool? How about trying a different tool?
|
username: 
|
username: 
|
username: 
|
username: 
|
username: