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

SELECT..CASE statement syntax error at Spider Engine table

Details

    Description

      SELECT ... CASE statement throwing syntax error at the Spider node.

      Here are the details of the same:
      One of the customer hit this problem, any workaround/quick fix is expected for this.

      Data Node:

      | spider_example | CREATE TABLE `spider_example` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(50) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
       
      MariaDB [test]> select * from spider_example;
      +----+------+
      | id | name |
      +----+------+
      |  1 | beam |
      |  2 | beam |
      +----+------+
      2 rows in set (0.003 sec)
      

      MariaDB [test]> select id,
          -> CASE
          -> when name='ram' then "wrong.."
          -> when name='beam' then "correct.."
          -> END
          -> from spider_example;
      +----+---------------------------------------------------------------------------+
      | id | CASE
      when name='ram' then "wrong.."
      when name='beam' then "correct.."
      END |
      +----+---------------------------------------------------------------------------+
      |  1 | correct..                                                                 |
      |  2 | correct..                                                                 |
      +----+---------------------------------------------------------------------------+
      2 rows in set (0.000 sec)
      

      MariaDB [test]> select @@version;
      +----------------+
      | @@version      |
      +----------------+
      | 10.5.8-MariaDB |
      +----------------+
      1 row in set (0.000 sec)
      

      Spider Nodes:

      | spider_example | CREATE TABLE `spider_example` (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        `name` varchar(50) DEFAULT NULL,
        PRIMARY KEY (`id`)
      ) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='wrapper "mysql", srv "dataNode1", table "spider_example"' |
      

      MariaDB [test]> select id,
          -> CASE
          -> when name='ram' then "wrong.."
          -> when name='beam' then "correct.."
          -> END
          -> from spider_example;
      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 'case (t0.`name` = 'beam')  _utf8'wrong..'  _utf8'correct..') `CASE
      when name=...' at line 1
      MariaDB [test]>
      

      Attachments

        Activity

          I tested on 10.0.38, 10.1.48, 10.2.39, and 10.4.20, and I confirmed that the bug is reproducible in all versions.

          The Spider storage engine rewrites a query before routing it to a data node. The rewrite process seems to be buggy and the engine generates a broken query like the following:

          select t0.`id` `id`,((t0.`name` = 'ram') case (t0.`name` = 'beam')  'wrong..'  'correct..') `CASE when name='ram' then "wrong.." when name='beam' then "correct.." END` from `test`.`spider_example` t0
          

          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - - edited I tested on 10.0.38, 10.1.48, 10.2.39, and 10.4.20, and I confirmed that the bug is reproducible in all versions. The Spider storage engine rewrites a query before routing it to a data node. The rewrite process seems to be buggy and the engine generates a broken query like the following: select t0.`id` `id`,((t0.`name` = 'ram') case (t0.`name` = 'beam') 'wrong..' 'correct..') `CASE when name='ram' then "wrong.." when name='beam' then "correct.." END` from `test`.`spider_example` t0

          The bug is not reproducible on 10.2.39. The Spider bundled with 10.2.39 just do a full scan on a data node rather than trying to pushdown CASE WHEN ... THEN statement.

          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - The bug is not reproducible on 10.2.39. The Spider bundled with 10.2.39 just do a full scan on a data node rather than trying to pushdown CASE WHEN ... THEN statement.
          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - I created an MTR test case: https://github.com/MariaDB/server/commit/7fae7fd76e98b5d4b64a67a6aa0d4ef9aab97840

          The root cause of the bug is in spider_db_mbase_util::open_item_func(Item_func *item_func, ...). The item_func->functype()) of the CASE WHEN ... THEN is CASE_SEARCHED_FUNC. The Spider SE doesn't recognize this Functype because the Functype is newly added by 4de0d92. This results in the wrong handling of the CASE WHEN ... THEN}.

          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - The root cause of the bug is in spider_db_mbase_util::open_item_func(Item_func *item_func, ...) . The item_func->functype()) of the CASE WHEN ... THEN is CASE_SEARCHED_FUNC . The Spider SE doesn't recognize this Functype because the Functype is newly added by 4de0d92 . This results in the wrong handling of the CASE WHEN ... THEN }.

          Please review the following patch: https://github.com/MariaDB/server/commit/2fafc02a12ea45c20f0aeaa948cc4c62b55b3119
          Note that I did not remove a part protected by ITEM_FUNC_CASE_PARAMS_ARE_PUBLIC because I prefer to change one thing in one commit. I will remove these by MDEV-26178.

          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - - edited Please review the following patch : https://github.com/MariaDB/server/commit/2fafc02a12ea45c20f0aeaa948cc4c62b55b3119 Note that I did not remove a part protected by ITEM_FUNC_CASE_PARAMS_ARE_PUBLIC because I prefer to change one thing in one commit. I will remove these by MDEV-26178 .

          serg I noticed that I have to cover the case of CASE_SIMPLE_FUNC. Let me withdraw the review request.

          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - serg I noticed that I have to cover the case of CASE_SIMPLE_FUNC . Let me withdraw the review request.
          nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - serg I'm sorry for repeating myself. Please review. https://github.com/MariaDB/server/commit/cf6d83e7d68bd2bc0af0404bf4dd15218847074d

          People

            nayuta-yanagisawa Nayuta Yanagisawa (Inactive)
            suresh.ramagiri@mariadb.com suresh ramagiri
            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.