pingcap / pingcap/tidb

tidb 7.1.0 use JSON_OBJECT with if() func cause bug

Open
#47,197 2 comments 0 reactions 1 assignee Claimed by @YangKeao View on GitHub
component/json severity/moderate sig/sql-infra type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

tidb 7.1.0 use JSON_OBJECT with if() func cause bug

### 1. create table and init data....
```
CREATE TABLE `tt2` (
`I_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`I_HAS_RECEIPT` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0:否,1:是',
PRIMARY KEY (`I_ID`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 COMMENT='xx';

insert into tt2 values(1, 0),(2, 1);

select JSON_OBJECT('has_receipt',if(I_HAS_RECEIPT = 0, false, true))from tt2 limit 10;

```
### 2. What did you expect to see? (Required)
expect sql execute result return true or false, but not expect 1,0

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

![image](https://github.com/pingcap/tidb/assets/37577060/8ecc1676-2af4-47a1-b0d9-640b85407f54)

### 4. What is your TiDB version? (Required)
sql in tidb 5.0.4 is expected,but 7.1.0 is not expect....

ps:
1. in my case, use `case when then ` instead of `if()` ,and then result is ok~
![image](https://github.com/pingcap/tidb/assets/37577060/33b3a54d-4481-4210-abf0-bce64d0839f3)

2. in mysql 5.7 return 1 or 0;
![image](https://github.com/pingcap/tidb/assets/37577060/73ba0583-e08c-4020-9545-a053cdd5768b)

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.