Build tested: Github source 1.1.0-1
[root@localhost mariadb-columnstore-server]# git show
commit 594ef1807a5d6cba45cf7c2bed03cccdc32f177a
Merge: a5f191d ce815f9
Author: David.Hall <david.hall@mariadb.com>
Date: Thu Jun 8 10:12:50 2017 -0500
[root@localhost mariadb-columnstore-engine]# git show
commit ebaf24473c0838989bf504a7c104c511b876fcb8
Author: david hill <david.hill@mariadb.com>
Date: Fri Jun 16 16:53:48 2017 -0500
1) According to the MariaDB's JSON KB articles, there are 26 JSON functions. Syntax wise, they all worked in ColumnStore.
2) Nine (9) of the functions returned the "Row size too large" error when it is applied to a TEXT column.
3) One (1) function, JSON_REPLACE() failed complaining about a missing JSON_UPDATE() function. This error occurred when the JSON_REPLACE is applied on ColumnStore columns (used varchar and text for testing). JSON_UPDATE is not one of the supported function and I could not find information about it. Maybe we need to inherit that function to ColumnStore since the same error did not occur in MariaDB.
4) I have created a test suite for JSON functions in Autopilot for automatic testing.
Function with non-zero diff count failed to match results from MariaDB:
0 Passed JSON_ARRAY_APPEND.sql.diff.log
0 Passed JSON_ARRAY_INSERT.sql.diff.log
0 Passed JSON_ARRAY.sql.diff.log
5 Failed JSON_COMPACT.sql.diff.log
0 Passed JSON_CONTAINS_PATH.sql.diff.log
0 Passed JSON_CONTAINS.sql.diff.log
0 Passed JSON_DEPTH.sql.diff.log
5 Failed JSON_DETAILED.sql.diff.log
0 Passed JSON_EXISTS.sql.diff.log
13 Failed JSON_EXTRACT.sql.diff.log
0 Passed JSON_INSERT.sql.diff.log
11 Failed JSON_KEYS.sql.diff.log
0 Passed JSON_LENGTH.sql.diff.log
5 Failed JSON_LOOSE.sql.diff.log
0 Passed JSON_MERGE.sql.diff.log
0 Passed JSON_OBJECT.sql.diff.log
6 Failed JSON_QUERY.sql.diff.log
0 Passed JSON_QUOTE.sql.diff.log
11 Failed JSON_REMOVE.sql.diff.log
5 Failed JSON_REPLACE.sql.diff.log
0 Passed JSON_SEARCH.sql.diff.log
0 Passed JSON_SET.sql.diff.log
0 Passed JSON_TYPE.sql.diff.log
18 Failed JSON_UNQUOTE.sql.diff.log
0 Passed JSON_VALID.sql.diff.log
6 Failed JSON_VALUE.sql.diff.log
Error message returned for failed functions:
MariaDB [mytest]> SELECT cVarchar, JSON_COMPACT(cVarchar), JSON_COMPACT(cText) from jsontest;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [mytest]> SELECT cVarchar, JSON_DETAILED(cVarchar), cText, JSON_DETAILED(cText) from jsontest;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [mytest]> SELECT cVarchar, JSON_EXTRACT(cVarchar, '$[1]'), cText, JSON_EXTRACT(cText, '$[1]') from jsontest;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [mytest]> SELECT cVarchar, JSON_KEYS(cVarchar, '$.C'), cText, JSON_KEYS(cText, '$.C') from jsontest;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [mytest]> SELECT cVarchar, JSON_LOOSE(cVarchar), cText, JSON_LOOSE(cText) from jsontest;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [mytest]> SELECT cVarchar, JSON_QUERY(cVarchar, '$.key1'), cText, JSON_QUERY(cText, '$.key1') from jsontest;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [mytest]> SELECT cVarchar, JSON_REMOVE(cVarchar, '$.C'), cText, JSON_REMOVE(cText, '$.C') from jsontest;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [mytest]> SELECT cVarchar, JSON_UNQUOTE(cVarchar), cText, JSON_UNQUOTE(cText) from jsontest;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [mytest]> SELECT cVarchar, JSON_VALUE(cVarchar, '$.key1'), cText, JSON_VALUE(cText, '$.key1') from jsontest;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
MariaDB [mytest]> SELECT cVarchar, JSON_REPLACE(cVarchar, '$.B[1]', 4), cText, JSON_REPLACE(cText, '$.B[1]', 4) from jsontest;
ERROR 1305 (42000): FUNCTION mytest.json_update does not exist
These won't work as distributed functions since there is no implementation yet, but can you verify if these will work in the select list and also with text/blob?