Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.4.3, 11.4, 11.8
-
None
Description
From Freeaqingme on Zulip simplified from https://sqlfiddle.com/mariadb/online-compiler?id=06632887-bfeb-4b0e-aecd-4e398b874680.
A basic JSON_INTERSECT works:
select json_array_intersect('[["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]]', '[["2","6"],["3","8"],["4","5"],["1","7"]]');
|
+-----------------------------------------------------------------------------------------------------------------------+
|
| json_array_intersect('[["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]]', '[["2","6"],["3","8"],["4","5"],["1","7"]]') |
|
+-----------------------------------------------------------------------------------------------------------------------+
|
| [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] |
|
+-----------------------------------------------------------------------------------------------------------------------+
|
As part of a table however:
CREATE TABLE `test1` ( |
`question_id` bigint(20) unsigned NOT NULL DEFAULT 0, |
`selected_answer` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL, |
`correct_answer` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL |
);
|
 |
INSERT INTO `test1` VALUES |
(304,'[\"2\"]','[\"2\"]'), |
(305,'[\"4\"]','[\"1\"]'), |
(306,'[\"4\"]','[\"2\"]'), |
(307,'[\"3\"]','[\"2\",\"3\"]'), |
(108,'[\"1\"]','[\"1\"]'), |
(402,'[[\"1\", \"7\"], [\"2\", \"6\"], [\"4\", \"5\"], [\"3\", \"8\"]]','[[\"2\",\"6\"],[\"3\",\"8\"],[\"4\",\"5\"],[\"1\",\"7\"]]'), |
(403,'[\"2\"]','[\"2\"]'), |
(1080,'[\"2\", \"4\"]','[\"2\",\"4\"]'); |
First answer is a non-overlap, and NULL becomes the answer for all the others per below:
MariaDB [test]> select question_id, selected_answer, correct_answer, json_array_intersect(selected_answer, correct_answer) from test1 where question_id > 305;
|
+-------------+--------------------------------------------------+-------------------------------------------+-------------------------------------------------------+
|
| question_id | selected_answer | correct_answer | json_array_intersect(selected_answer, correct_answer) |
|
+-------------+--------------------------------------------------+-------------------------------------------+-------------------------------------------------------+
|
| 306 | ["4"] | ["2"] | NULL |
|
| 307 | ["3"] | ["2","3"] | NULL |
|
| 402 | [["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]] | [["2","6"],["3","8"],["4","5"],["1","7"]] | NULL |
|
| 403 | ["2"] | ["2"] | NULL |
|
| 1080 | ["2", "4"] | ["2","4"] | NULL |
|
+-------------+--------------------------------------------------+-------------------------------------------+-------------------------------------------------------+
|
5 rows in set (0.002 sec)
|
As long as a non-null is in the order of results the next answer is correct.
MariaDB [test]> select question_id, selected_answer, correct_answer, json_array_intersect(selected_answer, correct_answer) from test1 where question_id > 306;
|
+-------------+--------------------------------------------------+-------------------------------------------+-------------------------------------------------------+
|
| question_id | selected_answer | correct_answer | json_array_intersect(selected_answer, correct_answer) |
|
+-------------+--------------------------------------------------+-------------------------------------------+-------------------------------------------------------+
|
| 307 | ["3"] | ["2","3"] | ["3"] |
|
| 402 | [["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]] | [["2","6"],["3","8"],["4","5"],["1","7"]] | [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] |
|
| 403 | ["2"] | ["2"] | ["2"] |
|
| 1080 | ["2", "4"] | ["2","4"] | ["2", "4"] |
|
+-------------+--------------------------------------------------+-------------------------------------------+-------------------------------------------------------+
|
MariaDB [test]> select question_id, selected_answer, correct_answer, json_array_intersect(selected_answer, correct_answer) from test1 where question_id order by question_id desc;
|
+-------------+--------------------------------------------------+-------------------------------------------+-------------------------------------------------------+
|
| question_id | selected_answer | correct_answer | json_array_intersect(selected_answer, correct_answer) |
|
+-------------+--------------------------------------------------+-------------------------------------------+-------------------------------------------------------+
|
| 1080 | ["2", "4"] | ["2","4"] | ["2", "4"] |
|
| 403 | ["2"] | ["2"] | ["2"] |
|
| 402 | [["1", "7"], ["2", "6"], ["4", "5"], ["3", "8"]] | [["2","6"],["3","8"],["4","5"],["1","7"]] | [["2", "6"], ["3", "8"], ["4", "5"], ["1", "7"]] |
|
| 307 | ["3"] | ["2","3"] | ["3"] |
|
| 306 | ["4"] | ["2"] | NULL |
|
| 305 | ["4"] | ["1"] | NULL |
|
| 304 | ["2"] | ["2"] | NULL |
|
| 108 | ["1"] | ["1"] | NULL |
|
+-------------+--------------------------------------------------+-------------------------------------------+-------------------------------------------------------+
|
Attachments
Issue Links
- relates to
-
-
- In Review
-
-
-
- Confirmed
-
-
-
- Open
-