| Bug #116634 | Inconsistent Comparison Behavior with JSON and Boolean Expressions | ||
|---|---|---|---|
| Submitted: | 12 Nov 2024 14:58 | Modified: | 13 Nov 2024 10:39 |
| Reporter: | Wenqian Deng | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 9.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Nov 2024 10:39]
MySQL Verification Team
Hi Mr. Deng, Thank you for your bug report. However, this is not a bug. Here comes a full explanation why is this not a bug. Difference in your test case is that one comparison is between JSON and BOOLEAN , while in other it is between JSON and INT. Both of these are totally incompatible type combinations. Let us also inform you about the more important reasons of why this is not a bug. SQL standard is very strict that in both cases, According to SQL standard, MySQL should return a hard error, since SQL standard does not allow mixing of data types in any expression, including the comparisons. Hence, all relational databases should return a hard error, but most of them do not. Most of the databases try to accommodate user queries, although this is prohibited. Hence, MySQL does its best effort in each case. That means that in many cases, you would get inconsistent results. But, there is nothing we can do there since your attribute types and their values are completely incompatible. Comparing such vastly different domains may lead to anything ............ In case of JSON versus any other types, MySQL always tries to convert the other value into JSON, since JSON can not be converted into BOOLEAN nor to INT. Hence, when comparing with JSON, we recommend you to use the JSON_VALUE() function. That function and it's use is fully explained in our Reference Manual. Not a bug.
Description: Specifically, comparing JSON values against boolean expressions (c0 <> c1) in the original table (t0) yields no results, while performing a similar comparison in a derived table (t1) produces rows. This indicates inconsistent handling of JSON and boolean comparisons between the original and derived tables. How to repeat: -- Step 1: Create table t0 and insert data CREATE TABLE t0 (c0 LONGBLOB, c1 TINYBLOB, c2 JSON, c3 DOUBLE); INSERT INTO t0 (c0, c1, c2, c3) VALUES ('VoLBIHmkv3mLJAQHJIuIn4CQHC8RdxoDyvrxb7BeDm1JCAIxBoxKizD68t9X58Wm5tFFOmSftOGFyPEFDNeEm4TWE974rb3ZQYauucprx238E2hfuAeGyRsw73OuUu5qje', '4luFjLBeNdpbcfyJ', '{"key": "value"}', -5.299790219007137e+29); -- Step 2: Create derived table t1 with boolean comparison CREATE TABLE t1 AS (SELECT (c0 <> c1) AS c0, c2 AS c2, c3 AS c3 FROM t0); -- Step 3: Compare JSON and boolean expression in original table t0 mysql> SELECT c2, c3 FROM t0 WHERE (c2 > (c0 <> c1)); Field 1: `c2` Catalog: `def` Database: `test` Table: `t0` Org_table: `t0` Type: JSON Collation: binary (63) Length: 4294967295 Max_length: 0 Decimals: 0 Flags: BLOB BINARY Field 2: `c3` Catalog: `def` Database: `test` Table: `t0` Org_table: `t0` Type: DOUBLE Collation: binary (63) Length: 22 Max_length: 0 Decimals: 31 Flags: NUM 0 rows in set (0.02 sec) -- Step 4: Perform similar comparison in derived table t1 mysql> SELECT c2, c3 FROM t1 WHERE (c2 > (c0)); Field 1: `c2` Catalog: `def` Database: `test` Table: `t1` Org_table: `t1` Type: JSON Collation: binary (63) Length: 4294967295 Max_length: 16 Decimals: 0 Flags: BLOB BINARY Field 2: `c3` Catalog: `def` Database: `test` Table: `t1` Org_table: `t1` Type: DOUBLE Collation: binary (63) Length: 22 Max_length: 21 Decimals: 31 Flags: NUM +------------------+-----------------------+ | c2 | c3 | +------------------+-----------------------+ | {"key": "value"} | -5.299790219007137e29 | | {"key": "value"} | -8.152483654216898e29 | +------------------+-----------------------+ 2 rows in set (0.00 sec) Expected Result: Both queries should yield consistent results. If the expression (c2 > (c0 <> c1)) in t0 yields no rows, then the derived table query (c2 > c0) in t1 should also yield no rows.