Hi, Nayuta! Great, ok to push! On Jul 21, Nayuta Yanagisawa wrote:

revision-id: cf6d83e7d68 (mariadb-10.3.30-28-gcf6d83e7d68) parent(s): efa311ab8ea author: Nayuta Yanagisawa committer: Nayuta Yanagisawa timestamp: 2021-07-19 15:16:25 +0000 message:

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

The root cause of the bug is in `spider_db_mbase_util::open_item_func()`. The function handles an instance of the `Item_func` class based on its `Item_func::Functype`. The `Functype` of `CASE WHEN ... THEN` is `CASE_SEARCHED_FUNC`. However, the Spider SE doesn't recognize this `Functype` because `CASE_SEARCHED_FUNC` is newly added by 4de0d92. This results in the wrong handling of `CASE WHEN ... THEN`.

The above also applies to `CASE_SIMPLE_FUNC`.

--- /dev/null +++ b/storage/spider/mysql-test/spider/bugfix/r/mdev_24760.result ... +) ENGINE=Spider DEFAULT CHARSET=utf8 COMMENT='table "tbl_a"' +PARTITION BY HASH(id) ( +PARTITION pt1 COMMENT='srv "s_2_1"' +); +SELECT id, CASE WHEN name='Alice' THEN "A" WHEN name='Bob' THEN "B" END FROM tbl_a; +id CASE WHEN name='Alice' THEN "A" WHEN name='Bob' THEN "B" END +1 A +2 B +SELECT id, CASE name WHEN 'Alice' THEN "A" WHEN 'Bob' THEN "B" END FROM tbl_a; +id CASE name WHEN 'Alice' THEN "A" WHEN 'Bob' THEN "B" END +1 A +2 B

a _very_ slight preference - could you use different names for local (spider) and remote tables? That is, by all means, please, follow the conventions that are used in existing spider tests. But if it's possible to use different tables names for remote and local without violating spider test conventions, I think it'd make tests easier to read. Regards, Sergei VP of MariaDB Server Engineering and security@mariadb.org