11.1.0-dbg>show create table t1;
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`c1` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`c1`)),
|
`c2` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`c2`))
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
11.1.0-dbg>
|
|
11.1.0-dbg>select c1,c2 from t1;
|
+--------------+----------------+
|
| c1 | c2 |
|
+--------------+----------------+
|
| [1, 2, 3, 4] | [3, 4, 5, 6 ] |
|
+--------------+----------------+
|
1 row in set (0.000 sec)
|
|
11.1.0-dbg>select @obj1,@obj2;
|
+--------------+----------------+
|
| @obj1 | @obj2 |
|
+--------------+----------------+
|
| [1, 2, 3, 4] | [3, 4, 5, 6 ] |
|
+--------------+----------------+
|
1 row in set (0.000 sec)
|
|
11.1.0-dbg>SELECT json_array_intersect(@obj1,@obj2);
|
+-----------------------------------+
|
| json_array_intersect(@obj1,@obj2) |
|
+-----------------------------------+
|
| [3, 4] |
|
+-----------------------------------+
|
1 row in set (0.000 sec)
|
|
11.1.0-dbg>
|
|
Actual Result
|
|
11.1.0-dbg>SELECT json_array_intersect(c1, c2) from t1;
|
+------------------------------+
|
| json_array_intersect(c1, c2) |
|
+------------------------------+
|
| NULL |
|
+------------------------------+
|
1 row in set (0.000 sec)
|
|
11.1.0-dbg>
|
|
Expected result
|
11.1.0-dbg>SELECT json_array_intersect(c1, c2) from t1;
|
+-----------------------------------+
|
| json_array_intersect(c1, c2) |
|
+-----------------------------------+
|
| [3, 4] |
|
+-----------------------------------+
|
1 row in set (0.000 sec)
|
|
11.1.0-dbg>
|
|
JSON_OBJECT_FILTER_KEYS
|
-------------------------------------------
|
11.1.0-dbg>insert into t1 values ('{ "a": 1, "b": 2, "c": 3}', '{"b" : 10, "c": 20, "d": 30}');
|
Query OK, 1 row affected (0.018 sec)
|
|
11.1.0-dbg>
|
|
Actual Result
|
|
11.1.0-dbg>SELECT JSON_OBJECT_FILTER_KEYS (c1, json_array_intersect(json_keys(c1), json_keys(c2))) from t1;
|
+----------------------------------------------------------------------------------+
|
| JSON_OBJECT_FILTER_KEYS (c1, json_array_intersect(json_keys(c1), json_keys(c2))) |
|
+----------------------------------------------------------------------------------+
|
| NULL |
|
+----------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
11.1.0-dbg>
|
|
Expected result
|
|
11.1.0-dbg>SELECT JSON_OBJECT_FILTER_KEYS (c1, json_array_intersect(json_keys(c1), json_keys(c2))) from t1;
|
+----------------------------------------------------------------------------------+
|
| JSON_OBJECT_FILTER_KEYS (c1, json_array_intersect(json_keys(c1), json_keys(c2))) |
|
+----------------------------------------------------------------------------------+
|
| {"b": 2, "c": 3} |
|
+----------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
11.1.0-dbg>
|
Bug
Critical
MDEV-26182 Create a function to check for JSON intersection
