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

SELECT..CASE statement syntax error at Spider Engine table

    XMLWordPrintable

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

          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.