Description:
Order by using multi-valued index and order by desc by another index return only one row.
How to repeat:
CREATE TABLE `tasks` (
`TaskID` int NOT NULL,
`TaskAccess` json DEFAULT NULL,
PRIMARY KEY (`TaskID`),
KEY `access` ((cast(json_extract(`TaskAccess`,_utf8mb4'$.access') as unsigned array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED;
INSERT INTO `tasks` VALUES (20959,'{\"access\": [3584, 89698, 89906, 89972, 86422, 3583, 89662] }'),(21050,'{\"access\": [89698, 89906, 89972, 86422, 3752, 3583, 89662]}'),(21966,'{\"access\": [3584, 89698, 89906, 89972, 86422, 3583, 89662]}');
mysql> select TaskID FROM t WHERE 3584 MEMBER OF(TaskAccess->"$.access") ORDER BY TaskID ;
+--------+
| TaskID |
+--------+
| 20959 |
| 21966 |
+--------+
2 rows in set (0.00 sec)
mysql> select TaskID FROM t WHERE 3584 MEMBER OF(TaskAccess->"$.access") ORDER BY TaskID DESC;
+--------+
| TaskID |
+--------+
| 21966 |
+--------+
1 row in set (0.00 sec)
> explain select TaskID FROM t WHERE 3584 MEMBER OF(TaskAccess->"$.access") ORDER BY TaskID DESC;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+
| 1 | SIMPLE | t | NULL | ref | access | access | 9 | const | 1 | 100.00 | Using where; Backward index scan |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)
Description: Order by using multi-valued index and order by desc by another index return only one row. How to repeat: CREATE TABLE `tasks` ( `TaskID` int NOT NULL, `TaskAccess` json DEFAULT NULL, PRIMARY KEY (`TaskID`), KEY `access` ((cast(json_extract(`TaskAccess`,_utf8mb4'$.access') as unsigned array))) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPRESSED; INSERT INTO `tasks` VALUES (20959,'{\"access\": [3584, 89698, 89906, 89972, 86422, 3583, 89662] }'),(21050,'{\"access\": [89698, 89906, 89972, 86422, 3752, 3583, 89662]}'),(21966,'{\"access\": [3584, 89698, 89906, 89972, 86422, 3583, 89662]}'); mysql> select TaskID FROM t WHERE 3584 MEMBER OF(TaskAccess->"$.access") ORDER BY TaskID ; +--------+ | TaskID | +--------+ | 20959 | | 21966 | +--------+ 2 rows in set (0.00 sec) mysql> select TaskID FROM t WHERE 3584 MEMBER OF(TaskAccess->"$.access") ORDER BY TaskID DESC; +--------+ | TaskID | +--------+ | 21966 | +--------+ 1 row in set (0.00 sec) > explain select TaskID FROM t WHERE 3584 MEMBER OF(TaskAccess->"$.access") ORDER BY TaskID DESC; +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+ | 1 | SIMPLE | t | NULL | ref | access | access | 9 | const | 1 | 100.00 | Using where; Backward index scan | +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+----------------------------------+ 1 row in set, 1 warning (0.00 sec)