Bug #112655 In `json_{object|array}`, types of values are inconsistent with scalar function

• Submitted: 7 Oct 2023 8:00
• Modified: 10 Oct 2023 7:33
• Reporter: Yang Keao
• Status: Duplicate
• Category: MySQL Server: JSON
• Severity: S4 (Feature request)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[7 Oct 2023 8:00] Yang Keao

Description:
The following SQL statements will not return `true/false` in the json value, but return `1/0`.

```
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;
```

As a reference, using `TRUE/FALSE` directly without `if` works pretty well:

```
select JSON_OBJECT('has_receipt', TRUE)from tt2 limit 10;
```

Always gives `true`.

How to repeat:
Run the following SQL statements:

```
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;
```

It'll return 

```
+---------------------------------------------------------------+
| JSON_OBJECT('has_receipt',if(I_HAS_RECEIPT = 0, false, true)) |
+---------------------------------------------------------------+
| {"has_receipt": 0} |
| {"has_receipt": 1} |
+---------------------------------------------------------------+
2 rows in set (0.00 sec)
```

It's expected to return:

```
+---------------------------------------------------------------+
| JSON_OBJECT('has_receipt',if(I_HAS_RECEIPT = 0, false, true)) |
+---------------------------------------------------------------+
| {"has_receipt": false} |
| {"has_receipt": true} |
+---------------------------------------------------------------+
2 rows in set (0.00 sec)
```

[9 Oct 2023 10:40] MySQL Verification Team

Hi Mr. Keao,

Thank you for your bug report.

We have managed to repeat it on both 8.0 and 8.1.

However, 0 and 1 are actually aliases for false and true.

Hence, this is not a bug, but it is a very nice feature request.

Verified as a feature request.

[10 Oct 2023 7:33] Knut Anders Hatlen

This looks like the same issue as the one reported in bug#79813. Closing as duplicate. Thanks.

[10 Oct 2023 7:46] Roy Lyseng

The IF clause has an internal data type that is an integer,
this is why an integer value is reported.

In this case, it is possible to rewrite the IF clause from

  if (I_HAS_RECEIPT = 0, false, true)

to

  (I_HAS_RECEIPT <> 0)

which will give the desired boolean value.

[10 Oct 2023 9:41] MySQL Verification Team

Hi Roy, Knut,

Thank you for noticing that this is a duplicate.