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

Complex CHECK constraint loses JSON property

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.5, 10.6, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.7(EOL), 10.8(EOL)
    • 10.5, 10.6
    • JSON
    • 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

              bar Alexander Barkov
              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.