JSON string comparison seems broken (change in behavior from v0.6.0 and v0.10.0)

[andremarianiello]

In a MySQL server

mysql> select json_extract('{"xid":"hello"}', '$.xid') = "hello";
+----------------------------------------------------+
| json_extract('{"xid":"hello"}', '$.xid') = "hello" |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_unquote(json_extract('{"xid":"hello"}', '$.xid')) = "hello";
+------------------------------------------------------------------+
| json_unquote(json_extract('{"xid":"hello"}', '$.xid')) = "hello" |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

With go-mysql-server v0.6.0

MySQL [(none)]> select json_extract('{"xid":"hello"}', '$.xid') = "hello";
+--------------------------------------------------------+
| JSON_EXTRACT("{\"xid\":\"hello\"}", "$.xid") = "hello" |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
1 row in set (0.001 sec)

MySQL [(none)]> select json_unquote(json_extract('{"xid":"hello"}', '$.xid')) = "hello";
+----------------------------------------------------------------------+
| JSON_UNQUOTE(JSON_EXTRACT("{\"xid\":\"hello\"}", "$.xid")) = "hello" |
+----------------------------------------------------------------------+
|                                                                    1 |
+----------------------------------------------------------------------+
1 row in set (0.001 sec)

With go-mysql-server v0.10.0

MySQL [(none)]> select json_extract('{"xid":"hello"}', '$.xid') = "hello";
+----------------------------------------------------+
| json_extract('{"xid":"hello"}', '$.xid') = "hello" |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.001 sec)

MySQL [(none)]> select json_unquote(json_extract('{"xid":"hello"}', '$.xid')) = "hello";
ERROR 1105 (HY000): incompatible conversion to SQL type: LONGTEXT

The comparison is now false and unquoting fails completely. I believe the root cause is sql.LongText.Convert failing to handle JSONDocument values, so Equals.Compare ends up converting the json value to nil.

[andremarianiello]

Would you like a PR that fixes this behavior?

[timsehn]

We would love one.

…

On Mon, Jun 21, 2021 at 11:50 AM andremarianiello ***@***.***> wrote: Would you like a PR that fixes this behavior? — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#474 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABJAR3B77NFMJGUZANERBCDTT6CW3ANCNFSM464CITIA> .

[andremarianiello]

Looks like @bheni made a change it HEAD that fixes the unquoted behavior, but the quoted behavior still doesn't match with MySQL.

With 8571009

MySQL [(none)]> select json_extract('{"xid":"hello"}', '$.xid') = "hello";
+----------------------------------------------------+
| json_extract('{"xid":"hello"}', '$.xid') = "hello" |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (0.001 sec)

MySQL [(none)]> select json_unquote(json_extract('{"xid":"hello"}', '$.xid')) = "hello";
+------------------------------------------------------------------+
| json_unquote(json_extract('{"xid":"hello"}', '$.xid')) = "hello" |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+
1 row in set (0.002 sec)

I'll make a PR to fix the quoted behavior.

[andremarianiello]

Nevermind, looks like I was beaten to the punch. Thanks @ajnavarro !

[ajnavarro]

Woops! Sorry @andremarianiello ! We were on sync ;)