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

JSON_EXTRACT as select conditions triggers syntax errors on Spider

Details

    Description

      How to repeat:

      On Spider:

      spd.sql

      mysql> SHOW CREATE TABLE t1\G
      *************************** 1. row ***************************
             Table: t1
      Create Table: CREATE TABLE `t1` (
        `i` int(11) NOT NULL,
        `jdoc` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
        PRIMARY KEY (`i`)
      ) ENGINE=SPIDER DEFAULT CHARSET=latin1
       PARTITION BY LIST (`i` MOD 4)
      (PARTITION `pt0` VALUES IN (0) COMMENT = 'database "yxtest", table "t1", server "SPT0"' ENGINE = SPIDER,
       PARTITION `pt1` VALUES IN (1) COMMENT = 'database "yxtest", table "t1", server "SPT1"' ENGINE = SPIDER,
       PARTITION `pt2` VALUES IN (2) COMMENT = 'database "yxtest", table "t1", server "SPT2"' ENGINE = SPIDER,
       PARTITION `pt3` VALUES IN (3) COMMENT = 'database "yxtest", table "t1", server "SPT3"' ENGINE = SPIDER)
      1 row in set (0.00 sec)
       
      mysql> INSERT INTO t1 VALUES(1, '{"Name":"Tom", "Age":18}'), (2, '{"Name":"Jerry", "Age":20}');
      Query OK, 2 rows affected (0.00 sec)
      Records: 2  Duplicates: 0  Warnings: 0
       
      mysql> SELECT * FROM t1 WHERE json_extract(jdoc, '$.Age')=20;
      ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'json_extract '$.Age') = 20)' at line 1
      

      Suggest fix:

      https://github.com/MariaDB/server/pull/1732

      Attachments

        Activity

          yongxinxu Yongxin Xu added a comment -

          I've created a new merge request to the newest 10.6.2 version.

          https://github.com/MariaDB/server/pull/1839

          yongxinxu Yongxin Xu added a comment - I've created a new merge request to the newest 10.6.2 version. https://github.com/MariaDB/server/pull/1839

          At a first glance, the bug looks similar to MDEV-24760. I've not tested it yet but I guess that the root cause of the bug seems to be the following commit: https://github.com/MariaDB/server/commit/4de0d920bede330ddf7790d0aee4bf90a00122ae.

          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - At a first glance, the bug looks similar to MDEV-24760 . I've not tested it yet but I guess that the root cause of the bug seems to be the following commit: https://github.com/MariaDB/server/commit/4de0d920bede330ddf7790d0aee4bf90a00122ae .
          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - Closed by https://github.com/MariaDB/server/commit/73d32cc100fd08a7c563dceca29bb42cbd199a41

          https://buildbot.askmonty.org/buildbot/grid?category=main&branch=10.3 — the test on fulltest builder started failing in --ps-protocol after the commit

          From 73d32cc100fd08a7c563dceca29bb42cbd199a41 Mon Sep 17 00:00:00 2001
          From: Yongxin Xu <55976466+yongxin-xu@users.noreply.github.com>
          Date: Fri, 23 Jul 2021 22:36:27 +0800
          Subject: [PATCH] MDEV-24517: JSON_EXTRACT as conditions triggers syntax error on Spider (#1839)
          

          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - https://buildbot.askmonty.org/buildbot/grid?category=main&branch=10.3 — the test on fulltest builder started failing in --ps-protocol after the commit From 73d32cc100fd08a7c563dceca29bb42cbd199a41 Mon Sep 17 00:00:00 2001 From: Yongxin Xu <55976466+yongxin-xu@users.noreply.github.com> Date: Fri, 23 Jul 2021 22:36:27 +0800 Subject: [PATCH] MDEV-24517: JSON_EXTRACT as conditions triggers syntax error on Spider (#1839)

          serg I created a follow-up patch for the present issue. Please review https://github.com/MariaDB/server/commit/f52d39369a6e3f1a9ce89189180d142e54299862.

          > git diff HEAD^
          diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_24517.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_24517.test
          index f5e53e03a65..21b9dda4f12 100644
          --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_24517.test
          +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_24517.test
          @@ -21,8 +21,10 @@ USE auto_test_remote;
           
           --connection child2_1
           --disable_query_log
          +--disable_ps_protocol
           echo CHILD2_1_CREATE_TABLES;
           eval $CHILD2_1_CREATE_TABLES;
          +--enable_ps_protocol
           --enable_query_log
           TRUNCATE TABLE mysql.general_log;
          

          > ./mysql-test/mtr spider/bugfix.mdev_24517 --ps-protocol
          ...
          ==============================================================================
           
          TEST                                      RESULT   TIME (ms) or COMMENT
          --------------------------------------------------------------------------
           
          worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
          spider/bugfix.mdev_24517                 [ pass ]    116
          --------------------------------------------------------------------------
          The servers were restarted 0 times
          Spent 0.116 of 26 seconds executing testcases
           
          Completed: All 1 tests were successful.
          

          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - serg I created a follow-up patch for the present issue. Please review https://github.com/MariaDB/server/commit/f52d39369a6e3f1a9ce89189180d142e54299862 . > git diff HEAD^ diff --git a/storage/spider/mysql-test/spider/bugfix/t/mdev_24517.test b/storage/spider/mysql-test/spider/bugfix/t/mdev_24517.test index f5e53e03a65..21b9dda4f12 100644 --- a/storage/spider/mysql-test/spider/bugfix/t/mdev_24517.test +++ b/storage/spider/mysql-test/spider/bugfix/t/mdev_24517.test @@ -21,8 +21,10 @@ USE auto_test_remote;   --connection child2_1 --disable_query_log +--disable_ps_protocol echo CHILD2_1_CREATE_TABLES; eval $CHILD2_1_CREATE_TABLES; +--enable_ps_protocol --enable_query_log TRUNCATE TABLE mysql.general_log; > ./mysql-test/mtr spider/bugfix.mdev_24517 --ps-protocol ... ==============================================================================   TEST RESULT TIME (ms) or COMMENT --------------------------------------------------------------------------   worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019 spider/bugfix.mdev_24517 [ pass ] 116 -------------------------------------------------------------------------- The servers were restarted 0 times Spent 0.116 of 26 seconds executing testcases   Completed: All 1 tests were successful.

          Sure, please push. Thanks!

          serg Sergei Golubchik added a comment - Sure, please push. Thanks!

          People

            nayuta-yanagisawa Nayuta Yanagisawa (Inactive)
            yongxinxu Yongxin Xu
            Votes:
            0 Vote for this issue
            Watchers:
            5 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.