Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20365

JSON_VALID and any JSON function doesnt work correctly whe there is a double-quote char (escaped) inside a value in a complex json array (more tha 2 elements)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Trivial
    • Resolution: Not a Bug
    • 10.3.17, 10.4.7
    • N/A
    • JSON
    • Windows 10 64 bit

    Description

      Still 10.3.17. any JSON function will return NULL when there is a quoted char in a complex JSON array. Witth a Json single element array there is no problem:

      Tests:
      Phrase: Venture "Extended" Edition

      1. SELECT JSON_QUOTE('Venture "Extended" Edition');
        Returns: "Venture \"Extended\" Edition"
      2. SELECT CONCAT('[ {"Year":1999,"Make":"Chevy","Model":', JSON_QUOTE('Venture \"Extended\" Edition'),'}

        ]') INTO @_json;
        SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
        Returns 1 and 1 (for a single element there is no problem)

      3. SELECT '[ {"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"}

        ,

        {"Year":1999,"Make":"Chevy","Model":"Venture Extended Edition","Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}

        ]' INTO @_json;
        SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
        Returns 1 and 2

      4. SELECT '[ {"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"}

        ,

        {"Year":1999,"Make":"Chevy","Model":"Venture \"Extended\" Edition","Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}

        ]' INTO @_json;
        SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
        Returns 0 and null

      5. SELECT CONCAT('[ {"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"}

        ,',
        '

        {"Year":1999,"Make":"Chevy","Model":', JSON_QUOTE('Venture \"Extended\" Edition'),'"Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}

        ]') INTO @_json;
        SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
        Returns 0 and null

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          fduenas Francisco Dueñas created issue -
          fduenas Francisco Dueñas made changes -
          Field Original Value New Value
          fduenas Francisco Dueñas made changes -
          Description  second this issue, still 10.3.17. any JSON function will return NULL when there is a quoted char in a complex JSON array. Witth a Json single element array there is no problem:

          Tests:
          Phrase: Venture "Extended" Edition

              SELECT JSON_QUOTE('Venture "Extended" Edition');
              Returns: "Venture \"Extended\" Edition"
              SELECT CONCAT('[ {"Year":1999,"Make":"Chevy","Model":', JSON_QUOTE('Venture \"Extended\" Edition'),'}

              ]') INTO @_json;
              SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
              Returns 1 and 1 (for a single element there is no problem)
              SELECT '[ {"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"}

              ,
              {"Year":1999,"Make":"Chevy","Model":"Venture Extended Edition","Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}

              ]' INTO @_json;
              SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
              Returns 1 and 2
              SELECT '[ {"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"}

              ,
              {"Year":1999,"Make":"Chevy","Model":"Venture \"Extended\" Edition","Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}

              ]' INTO @_json;
              SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
              Returns 0 and null
              SELECT CONCAT('[ {"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"}

              ,',
              '
              {"Year":1999,"Make":"Chevy","Model":', JSON_QUOTE('Venture \"Extended\" Edition'),'"Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}

              ]') INTO @_json;
              SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
              Returns 0 and null
          		Still 10.3.17. any JSON function will return NULL when there is a quoted char in a complex JSON array. Witth a Json single element array there is no problem:

          Tests:
          Phrase: Venture "Extended" Edition

          # SELECT JSON_QUOTE('Venture "Extended" Edition');
          *Returns: "Venture \"Extended\" Edition"*
          # SELECT CONCAT('[{"Year":1999,"Make":"Chevy","Model":', JSON_QUOTE('Venture \"Extended\" Edition'),'}]') INTO @_json;
          SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
          *Returns 1 and 1 (for a single element there is no problem)*
          # SELECT '[{"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"},
                   {"Year":1999,"Make":"Chevy","Model":"Venture Extended Edition","Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}]' INTO @_json;
          SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
          *Returns 1 and 2*
          # SELECT '[{"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"},
                   {"Year":1999,"Make":"Chevy","Model":"Venture \"Extended\" Edition","Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}]' INTO @_json;
          SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
          *Returns 0 and null*
          # SELECT CONCAT('[{"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"},',
                         '{"Year":1999,"Make":"Chevy","Model":', JSON_QUOTE('Venture \"Extended\" Edition'),'"Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}]') INTO @_json;
          SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
          *Returns 0 and null*
          Summary JSON_VALIDS and any JSON function doesnt work correctly where is a double quote char inside a value ina complex json array JSON_VALID and any JSON function doesnt work correctly where is a double quote char inside a value ina complex json array
          fduenas Francisco Dueñas made changes -
          Summary JSON_VALID and any JSON function doesnt work correctly where is a double quote char inside a value ina complex json array JSON_VALID and any JSON function doesnt work correctly where is a double quote char inside a value in a complex json array
          fduenas Francisco Dueñas made changes -
          Summary JSON_VALID and any JSON function doesnt work correctly where is a double quote char inside a value in a complex json array JSON_VALID and any JSON function doesnt work correctly whe there is a double-quote char (escaped) inside a value in a complex json array (more tha 2 elements)
          fduenas Francisco Dueñas added a comment -

          Sorry found there a mistake in my sql senteces. This bug can be closed safely

          fduenas Francisco Dueñas added a comment - Sorry found there a mistake in my sql senteces. This bug can be closed safely
          fduenas Francisco Dueñas made changes -
          Priority Major [ 3 ] Trivial [ 5 ]
          alice Alice Sherepa added a comment -

          yes, comma, it happens

          MariaDB [(none)]> SELECT CONCAT('[ {"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"},', '{"Year":1999,"Make":"Chevy","Model":', JSON_QUOTE('Venture \"Extended\" Edition'),',"Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}]') INTO @_json;
          		 
          Query OK, 1 row affected (0.000 sec)
          		 
           
          		 
          MariaDB [(none)]> SELECT JSON_VALID(@_json), JSON_LENGTH(@_json);
          		 
          +--------------------+---------------------+
          		 
          | JSON_VALID(@_json) | JSON_LENGTH(@_json) |
          		 
          +--------------------+---------------------+
          		 
          |                  1 |                   2 |
          		 
          +--------------------+---------------------+
          		 
          1 row in set (0.000 sec)

          alice Alice Sherepa added a comment - yes, comma, it happens MariaDB [(none)]> SELECT CONCAT('[ {"Year":1997,"Make":"Ford","Model":"E350","Description":"ac, abs, moon","Price":3000,"UpForSale":"2017-10-01"},', '{"Year":1999,"Make":"Chevy","Model":', JSON_QUOTE('Venture \"Extended\" Edition'),',"Description":null,"Price":4900,"UpForSale":"2017-10-03T12:15"}]') INTO @_json; Query OK, 1 row affected (0.000 sec)   MariaDB [(none)]> SELECT JSON_VALID(@_json), JSON_LENGTH(@_json); +--------------------+---------------------+ | JSON_VALID(@_json) | JSON_LENGTH(@_json) | +--------------------+---------------------+ | 1 | 2 | +--------------------+---------------------+ 1 row in set (0.000 sec)
          alice Alice Sherepa made changes -
          Fix Version/s N/A [ 14700 ]
          Resolution Not a Bug [ 6 ]
          Status Open [ 1 ] Closed [ 6 ]
          alice Alice Sherepa made changes -
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 98990 ] MariaDB v4 [ 156637 ]

          People

            Unassigned Unassigned
            fduenas Francisco Dueñas
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.