Concatenation Causes Incorrect Query Results

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: concat导致查询结果不正确

| username: wakaka

[TiDB Usage Environment] Production Environment
[TiDB Version] 5.1.4
[Reproduction Path]

image
image1192×799 47.3 KB

Concatenating two columns results in unstable query results
Table structure:

CREATE TABLE `ZSCT023` (
  `MANDT` varchar(3) NOT NULL DEFAULT '000',
  `SC_YEAR` varchar(4) NOT NULL DEFAULT '0000',
  `SC_MONTH` varchar(2) NOT NULL DEFAULT '00',
  `ZGUID` varchar(32) NOT NULL DEFAULT '',
  `KPTZ` varchar(13) NOT NULL DEFAULT '',
  `KPTZHX` varchar(6) NOT NULL DEFAULT '000000',
  `LIFNR` varchar(10) NOT NULL DEFAULT '',
  `NAME1` varchar(35) NOT NULL DEFAULT '',
  `ZFKRQ` varchar(8) NOT NULL DEFAULT '00000000',
  `STATUS_T` varchar(20) NOT NULL DEFAULT '',
  `JSDLX_T` varchar(10) NOT NULL DEFAULT '',
  `BUKRS` varchar(4) NOT NULL DEFAULT '',
  `WERKS` varchar(4) NOT NULL DEFAULT '',
  `EBELN` varchar(10) NOT NULL DEFAULT '',
  `EBELP` varchar(5) NOT NULL DEFAULT '00000',
  `EKNAM` varchar(18) NOT NULL DEFAULT '',
  `PSM` varchar(18) NOT NULL DEFAULT '',
  `VBELN` varchar(10) NOT NULL DEFAULT '',
  `POSNR` varchar(6) NOT NULL DEFAULT '000000',
  `ZSTATU` varchar(20) NOT NULL DEFAULT '',
  `MATNR` varchar(40) NOT NULL DEFAULT '',
  `MAKTX` varchar(40) NOT NULL DEFAULT '',
  `BELNR` varchar(10) NOT NULL DEFAULT '',
  `BUZEI` varchar(4) NOT NULL DEFAULT '0000',
  `ZF_TEXT1` varchar(30) NOT NULL DEFAULT '',
  `ZTERM` varchar(4) NOT NULL DEFAULT '',
  `BUDAT` varchar(8) NOT NULL DEFAULT '00000000',
  `XBLNR` varchar(35) NOT NULL DEFAULT '',
  `MENGE_SH` decimal(13,3) NOT NULL DEFAULT '0',
  `YJFKRQ` varchar(6) NOT NULL DEFAULT '',
  `TQFK` varchar(1) NOT NULL DEFAULT '',
  `NETPR` decimal(11,2) NOT NULL DEFAULT '0',
  `WRBTR` decimal(13,2) NOT NULL DEFAULT '0',
  `SHUILV` decimal(11,2) NOT NULL DEFAULT '0',
  `SHUIE` decimal(13,2) NOT NULL DEFAULT '0',
  `WRBTR_H` decimal(13,2) NOT NULL DEFAULT '0',
  `WRBTR_G` decimal(13,2) NOT NULL DEFAULT '0',
  `SHUIE_G` decimal(13,2) NOT NULL DEFAULT '0',
  `WRBTR_H_G` decimal(13,2) NOT NULL DEFAULT '0',
  `RE_BELNR` varchar(10) NOT NULL DEFAULT '',
  `DZRQ` varchar(8) NOT NULL DEFAULT '00000000',
  `CJR` varchar(12) NOT NULL DEFAULT '',
  `CJRQ` varchar(8) NOT NULL DEFAULT '00000000',
  `FBR` varchar(12) NOT NULL DEFAULT '',
  `FBRQ` varchar(8) NOT NULL DEFAULT '00000000',
  `QRRQ` varchar(8) NOT NULL DEFAULT '00000000',
  `SHR` varchar(12) NOT NULL DEFAULT '',
  `SHRQ` varchar(8) NOT NULL DEFAULT '00000000',
  `SHFBR` varchar(12) NOT NULL DEFAULT '',
  `SHFBRQ` varchar(8) NOT NULL DEFAULT '00000000',
  `JYR` varchar(12) NOT NULL DEFAULT '',
  `JYRQ` varchar(8) NOT NULL DEFAULT '00000000',
  `JYFBR` varchar(12) NOT NULL DEFAULT '',
  `JYFBRQ` varchar(8) NOT NULL DEFAULT '00000000',
  `ZCWBZ` varchar(200) NOT NULL DEFAULT '',
  `ZSWBZ` varchar(200) NOT NULL DEFAULT '',
  `LOEKZ` varchar(1) NOT NULL DEFAULT '',
  `DAYS1` int(11) NOT NULL DEFAULT '0',
  `DAYS2` int(11) NOT NULL DEFAULT '0',
  `DATE1` varchar(8) NOT NULL DEFAULT '00000000',
  `DAYS6` int(11) NOT NULL DEFAULT '0',
  `DATE3` varchar(8) NOT NULL DEFAULT '00000000',
  `DAYS3` int(11) NOT NULL DEFAULT '0',
  `DATE2` varchar(8) NOT NULL DEFAULT '00000000',
  `DATE4` varchar(8) NOT NULL DEFAULT '00000000',
  `DAYS7` int(11) NOT NULL DEFAULT '0',
  `DATE5` varchar(8) NOT NULL DEFAULT '00000000',
  `DAYS4` int(11) NOT NULL DEFAULT '0',
  `BILLNO` varchar(32) NOT NULL DEFAULT '',
  `BILLAMT` decimal(21,2) NOT NULL DEFAULT '0',
  `ZFLX` varchar(20) NOT NULL DEFAULT '',
  `DMBT1` decimal(21,2) NOT NULL DEFAULT '0',
  `DMBT3` decimal(21,2) NOT NULL DEFAULT '0',
  `DMBT4` decimal(21,2) NOT NULL DEFAULT '0',
  `DMBT7` decimal(21,2) NOT NULL DEFAULT '0',
  `DMBT5` decimal(21,2) NOT NULL DEFAULT '0',
  `DMBT6` decimal(21,2) NOT NULL DEFAULT '0',
  `STATE_JY` varchar(10) NOT NULL DEFAULT '',
  `UNAME` varchar(12) NOT NULL DEFAULT '',
  `DATUM` varchar(8) NOT NULL DEFAULT '00000000',
  `UZEIT` varchar(6) NOT NULL DEFAULT '000000',
  PRIMARY KEY (`MANDT`,`SC_YEAR`,`SC_MONTH`,`ZGUID`) /*T![clustered_index] NONCLUSTERED */,
  KEY `ZSCT023_SC_MONTH_IDX` (`SC_MONTH`),
  KEY `ZSCT023_ZGUID_IDX` (`ZGUID`),
  KEY `ZSCT023_SC_YEAR_IDX` (`SC_YEAR`,`SC_MONTH`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Execution plan:

image
image1380×490 93 KB

[Encountered Problem: Problem Phenomenon and Impact] Multiple query results are inconsistent
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]

2
| username: Kongdom | Original post link

This is foreseeable. It is recommended to adjust the statement, changing the where condition to sc_year=left(‘202210’,4) and sc_month=right(‘202210’,2).

3
| username: wakaka | Original post link

Is it a bug with concat? Is there a related link?

4
| username: Kongdom | Original post link

Using functions on index columns will invalidate the index, causing the execution plan to not use the index.

5
| username: wakaka | Original post link

There’s no problem with not using the index; a full table scan is also fine. My execution plan uses TiFlash, but the results are incorrect after multiple executions, which seems like a significant issue.

6
| username: 我是咖啡哥 | Original post link

The table data hasn’t changed? Are the query results different each time?
Can you create a new table to reproduce it?

7
| username: tidb菜鸟一只 | Original post link

Are you not using an index to query data like this? Using concat definitely won’t use an index.
Would the result be different if you query like this:

select count(1) from To23 where sc_year='2022' and sc_month='10'

The table data shouldn’t have changed, right?

8
| username: wakaka | Original post link

The data in the table hasn’t changed. What I mean is that whether the index is used or not shouldn’t be the cause of the problem. Concat didn’t use the index, but rewriting it into two conditional queries works fine, although the statement still uses concat.

9
| username: wakaka | Original post link

Several tables have issues, and the data for October and November keeps changing, but the previous data does not.

10
| username: 我是咖啡哥 | Original post link

Create a new table and insert some data into these two columns for testing to see if the issue can be reproduced.