Wrong Result in Cast Blob to Json

[curry-oss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 BLOB);
INSERT INTO t0 VALUES ('1xx');

SELECT CAST(t0.c0 AS JSON) IS TRUE FROM t0;

2. What did you expect to see? (Required)

see the following case:

3. What did you see instead (Required)

// TiDB
mysql> SELECT CAST(t0.c0 AS JSON) IS TRUE FROM t0;
+-----------------------------+
| CAST(t0.c0 AS JSON) IS TRUE |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set, 1 warning (0.04 sec)

// MySQL
MySQL [test]> SELECT CAST(t0.c0 AS JSON) IS TRUE FROM t0;
+-----------------------------+
| CAST(t0.c0 AS JSON) IS TRUE |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set, 1 warning (0.01 sec)

4. What is your TiDB version? (Required)

TiDB v8.5.0

[Defined2014]

I think the root cause already includes in warning message. In TiDB, we will cast the json result to double, then use the istrue function to get the result.

mysql> SELECT CAST(t0.c0 AS JSON) IS TRUE FROM t0;
+-----------------------------+
| CAST(t0.c0 AS JSON) IS TRUE |
+-----------------------------+
|                           1 |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                   |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 3986 | Evaluating a JSON value in SQL boolean context does an implicit comparison against JSON integer 0; if this is not what you want, consider converting JSON to a SQL numeric type with JSON_VALUE RETURNING |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[zanmato1984]

Seems like in MySQL, when evaluated as a logical expression, a JSON type behaves differently than other types such as string. For example:

mysql> SELECT '"base64:type252:MXh4"' IS TRUE;
+---------------------------------+
| '"base64:type252:MXh4"' IS TRUE |
+---------------------------------+
|                               0 |
+---------------------------------+

mysql> SELECT cast('"base64:type252:MXh4"' as json) IS TRUE;
+-----------------------------------------------+
| cast('"base64:type252:MXh4"' as json) IS TRUE |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+

And I can't find official document about this behavior of MySQL.

In TiDB, when evaluated as a logical expression, a JSON type behaves the same as other non-numeric types, namely, they are first casted to FLOAT, so the results are:

tidb> SELECT '"base64:type252:MXh4"' IS TRUE;
+---------------------------------+
| '"base64:type252:MXh4"' IS TRUE |
+---------------------------------+
|                               0 |
+---------------------------------+

tidb> SELECT cast('"base64:type252:MXh4"' as json) IS TRUE;
+-----------------------------------------------+
| cast('"base64:type252:MXh4"' as json) IS TRUE |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+

This is a very corner usage so I'm setting severity to minor.