[#MDEV-24760] SELECT..CASE statement syntax error at Spider Engine table

[MDEV-24760] SELECT..CASE statement syntax error at Spider Engine table Created: 2021-02-02 Updated: 2021-09-27 Resolved: 2021-07-21
Status:	Closed
Project:	MariaDB Server
Component/s:	Storage Engine - Spider
Affects Version/s:	10.4.17, 10.5.8, 10.3.30, 10.6.3
Fix Version/s:	10.3.31, 10.4.21, 10.5.12, 10.6.4

Type:

Bug

Priority:

Critical

Reporter:

suresh ramagiri

Assignee:

Nayuta Yanagisawa (Inactive)

Resolution:

Fixed

Votes:

Labels:

not-10.2, regression

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]>

Comments

Comment by Nayuta Yanagisawa (Inactive) [ 2021-07-02 ]

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