Details

    • New Feature
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 1.1.0
    • 1.1.0
    • ExeMgr
    • 2017-12, 2017-13

    Description

      MariaDB 10.2 has added JSON Functions to its arsenal. Columnstore should also support these functions. Distributed JSON functions would be preferred, but perhaps a way could be found to shorten the cycle and not distribute them (Just a guess).

      Attachments

        Issue Links

          Activity

            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?

            dthompson David Thompson (Inactive) added a comment - 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?

            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

            dleeyh Daniel Lee (Inactive) added a comment - 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

            MCOL-713 is the likely cause of the row size too larger error.

            dthompson David Thompson (Inactive) added a comment - MCOL-713 is the likely cause of the row size too larger error.

            we should look into the missing json_update function, could be a merge issue.

            dthompson David Thompson (Inactive) added a comment - we should look into the missing json_update function, could be a merge issue.

            Completed investigation for this ticket. Two tickets have been created and linked.

            dleeyh Daniel Lee (Inactive) added a comment - Completed investigation for this ticket. Two tickets have been created and linked.

            People

              dleeyh Daniel Lee (Inactive)
              David.Hall David Hall (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.