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

Complex CHECK constraint loses JSON property

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8
    • Fix Version/s: 10.5, 10.6, 10.7, 10.8
    • Component/s: JSON
    • Labels:
      None

      Description

      I run this script:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (
        j longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(j))
      );
      INSERT INTO t1 VALUES ('{"b":"c"}');
      SELECT JSON_OBJECT('a', j) FROM t1;
      

      +---------------------+
      | JSON_OBJECT('a', j) |
      +---------------------+
      | {"a": {"b":"c"}}    |
      +---------------------+
      

      Looks good so far.

      Now I change the contraint a little bit by adding an AND-ed condition:

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (
        j longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(j) AND length(j)<100)
      );
      INSERT INTO t1 VALUES ('{"b":"c"}');
      SELECT JSON_OBJECT('a', j) FROM t1;
      

      +------------------------+
      | JSON_OBJECT('a', j)    |
      +------------------------+
      | {"a": "{\"b\":\"c\"}"} |
      +------------------------+
      

      Looks wrong. The value t1.j was interpreted as a scalar string rather than a JSON, and therefore was escaped.

      Note, Oracle 21c (tested on dbfiddle.uk) does not escape in similar scripts (neither with a simple, nor with an AND-ed constraint):

      DROP TABLE t1;
      CREATE TABLE t1 (
        j CLOB CONSTRAINT cnt1 CHECK (j IS JSON)
      );
      INSERT INTO t1 VALUES ('{"b":"c"}');
      SELECT JSON_OBJECT('a' VALUE j) FROM t
      

      JSON_OBJECT('A'VALUEJ)
      {"a" : {"b" : "c"}}
      

      DROP TABLE t1;
      CREATE TABLE t1 (
        j CLOB CONSTRAINT cnt1 CHECK (j IS JSON AND LENGTH(j)<100)
      );
      INSERT INTO t1 VALUES ('{"b":"c"}');
      SELECT JSON_OBJECT('a' VALUE j) FROM t1;
      

      JSON_OBJECT('A'VALUEJ)
      {"a" : {"b" : "c"}}
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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