Incorrect JSON object compare

[breezewish]

Description

Bug Report

MySQL:

mysql> select json_object('k', -1) > json_object('k', 2);
+--------------------------------------------+
| json_object('k', -1) > json_object('k', 2) |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_object('k', -1) < json_object('k', 2);
+--------------------------------------------+
| json_object('k', -1) < json_object('k', 2) |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)

TiDB:

mysql> select json_object('k', -1) > json_object('k', 2);
+--------------------------------------------+
| json_object('k', -1) > json_object('k', 2) |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_object('k', -1) < json_object('k', 2);
+--------------------------------------------+
| json_object('k', -1) < json_object('k', 2) |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @SunRunAway

[breezewish]

Another case (root cause should be the same):

MySQL:

mysql> create table tx(a double, b int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into tx values (3.0, 3);
Query OK, 1 row affected (0.01 sec)

mysql> select json_object('k', a) = json_object('k', b) from tx;
+-------------------------------------------+
| json_object('k', a) = json_object('k', b) |
+-------------------------------------------+
|                                         1 |
+-------------------------------------------+
1 row in set (0.00 sec)

TiDB:

mysql> create table tx(a double, b int);
Query OK, 0 rows affected (0.13 sec)

mysql> insert into tx values (3.0, 3);
Query OK, 1 row affected (0.03 sec)

mysql> select json_object('k', a) = json_object('k', b) from tx;
+-------------------------------------------+
| json_object('k', a) = json_object('k', b) |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)

[hailanwhu]

I debug it. I find that compare json_object('k', -1) and json_object('k', 2) in bytes. Because -1 equals 255 and 2 equals 2. Thus, the result is 1. Should we need to compare each value with same key using the actual value type?

[breezewish]

@hailanwhu Yes. Currently we compare by bytes for json_object, which is incorrect.

[hailanwhu]

@breeswish In mysql document, it says 'The order of two objects that are not equal is unspecified but deterministic.' I confuse whether it needs to make the same behavior with mysql. I test select json_object('k1',-1,'k2',2) > json_object('k1',-1,'k3',2); in mysql. It returns 0. I guess it still compares in bytes in mysql.

[breezewish]

@hailanwhu It would be better to be the same. @XuHuaiyu PTAL

[ghost]

Confirmed this still exists in master:

select json_object('k', -1) > json_object('k', 2);
select json_object('k', -1) < json_object('k', 2);
drop table if exists tx;
create table tx(a double, b int);
insert into tx values (3.0, 3);
select json_object('k', a) = json_object('k', b) from tx;

..

mysql> select json_object('k', -1) > json_object('k', 2);
+--------------------------------------------+
| json_object('k', -1) > json_object('k', 2) |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> select json_object('k', -1) < json_object('k', 2);
+--------------------------------------------+
| json_object('k', -1) < json_object('k', 2) |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (0.00 sec)

mysql> drop table if exists tx;
Query OK, 0 rows affected (0.23 sec)

mysql> create table tx(a double, b int);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into tx values (3.0, 3);
Query OK, 1 row affected (0.03 sec)

mysql> select json_object('k', a) = json_object('k', b) from tx;
+-------------------------------------------+
| json_object('k', a) = json_object('k', b) |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.01 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[ti-challenge-bot]

The issue has been removed from the challenge program.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions