[MCOL-4108] JSON functions are broken Created: 2020-06-25  Updated: 2020-10-22  Resolved: 2020-10-22

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr
Affects Version/s: 1.4.4, 1.5.2
Fix Version/s: 5.5.1

Type: Bug Priority: Critical
Reporter: Daniel Lee (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None


 Description   

Build tested: 1.4.4-1, 1.5.2-1 (community edition, b33685)

The following json functions return a "got bad column" error.

JSON_ARRAY_APPEND.sql
JSON_ARRAY_INSERT.sql
JSON_COMPACT.sql
JSON_CONTAINS_PATH.sql
JSON_CONTAINS.sql
JSON_DEPTH.sql
JSON_DETAILED.sql
JSON_EXISTS.sql
JSON_EXTRACT.sql
JSON_INSERT.sql
JSON_KEYS.sql
JSON_LENGTH.sql
JSON_LOOSE.sql
JSON_MERGE.sql
JSON_QUERY.sql
JSON_QUOTE.sql
JSON_REMOVE.sql
JSON_REPLACE.sql
JSON_SEARCH.sql
JSON_SET.sql
JSON_TYPE.sql
JSON_UNQUOTE.sql
JSON_VALID.sql
JSON_VALUE.sql

For example:

[centos7:root~]# cat -n /data/qa/autopilot/features/json/sql/JSON_EXISTS.sql
1 # ---------------------------------------------------------------------- #
2 # The string is already a JSON string so we can insert it into the table
3 # ---------------------------------------------------------------------- #
4 TRUNCATE TABLE jsontest;
5 SET @json = '

{"key1":"xxxx", "key2":[1, 2, 3]}

';
6 SELECT @json;
7 INSERT INTO jsontest VALUES (1, @json, @json);
8 #
9 SELECT JSON_EXISTS(@json, "$.key2");
10 SELECT JSON_EXISTS(@json, "$.key3");
11 SELECT JSON_EXISTS(@json, "$.key2[1]");
12 SELECT JSON_EXISTS(@json, "$.key2[10]");
13 #
14 SELECT * FROM jsontest;
15 SELECT cVarchar, JSON_EXISTS(cVarchar, "$.key2"), cText, JSON_EXISTS(cText, "$.key2") from jsontest;
16 SELECT cVarchar, JSON_EXISTS(cVarchar, "$.key3"), cText, JSON_EXISTS(cText, "$.key3") from jsontest;
17 SELECT cVarchar, JSON_EXISTS(cVarchar, "$.key2[1]"), cText, JSON_EXISTS(cText, "$.key2[1]") from jsontest;
18 SELECT cVarchar, JSON_EXISTS(cVarchar, "$.key2[10]"), cText, JSON_EXISTS(cText, "$.key2[10]") from jsontest;
19 #
20 TRUNCATE TABLE jsontest;

[centos7:root~]# cat JSON_EXISTS.sql.tst.log
ERROR 1815 (HY000) at line 15: Internal error: Error while fetching from ExeMgr: Row::isNullValue(): got bad column type (738197656). Width=738398448

ERROR 1815 (HY000) at line 16: Internal error: Unknown error
ERROR 1815 (HY000) at line 17: Internal error: Error while fetching from ExeMgr: Row::isNullValue(): got bad column type (738393232). Width=738409744

ERROR 1815 (HY000) at line 18: Internal error: Unknown error
--------------

 Comments   
Comment by Daniel Lee (Inactive) [ 2020-07-14 ]

Build tests: 1.5.3-1 gamma

Some JSON functions, such as JSON_CONTAIN caused server to crash on ColumnStore tables. InnoDB tables work fine.

Comment by David Hall (Inactive) [ 2020-09-04 ]

I created a patch to fix the error message to NOT IMPLEMENTED until we add these functions.

Comment by Roman [ 2020-09-08 ]

4QA as of now there will be an explicit error about unsupported functions instead of vague "got bad column" message.
JSON functions won't work in MCS b/c they were never implemented as native functions so the implementation will be another project.

Comment by Daniel Lee (Inactive) [ 2020-10-22 ]

Build tested: 5.5.1-1 (hasty BB, build #35830)

These json functions now return messages like the following

> ERROR 1178 (42000) at line 15: The storage engine for the table doesn't support IDB-1001: Function 'json_array_append' isn't supported.
> ERROR 1178 (42000) at line 16: The storage engine for the table doesn't support IDB-1001: Function 'json_array_append' isn't supported.
> ERROR 1178 (42000) at line 17: The storage engine for the table doesn't support IDB-1001: Function 'json_array_append' isn't supported.
> ERROR 1178 (42000) at line 18: The storage engine for the table doesn't support IDB-1001: Function 'json_array_append' isn't supported.
> ERROR 1178 (42000) at line 27: The storage engine for the table doesn't support IDB-1001: Function 'json_array_append' isn't supported.

Generated at Thu Feb 08 02:47:51 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.