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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
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
Component/s: Storage Engine - Spider
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]>

Attachments

Activity

Ascending order - Click to sort in descending order

suresh ramagiri created issue - 2021-02-02 02:57

Elena Stepanova made changes - 2021-02-02 13:09

Field	Original Value	New Value
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]>	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: {code:sql} \| 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) {code} {code:sql} 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) {code} {code:sql} MariaDB [test]> select @@version; +----------------+ \| @@version \| +----------------+ \| 10.5.8-MariaDB \| +----------------+ 1 row in set (0.000 sec) {code} Spider Nodes: {code:sql} \| 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"' \| {code} {code:sql} 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]> {code}

Elena Stepanova made changes - 2021-02-02 13:09

Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Assignee		Kentoku Shiba [ kentoku ]

Julien Fritsch made changes - 2021-07-01 06:06

Assignee

Kentoku Shiba [ kentoku ]

Nayuta Yanagisawa [ JIRAUSER47117 ]

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-02 02:59 - 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

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-02 02:59 - 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

Nayuta Yanagisawa (Inactive) made changes - 2021-07-02 02:59

Status

Open [ 1 ]

Confirmed [ 10101 ]

Julien Fritsch made changes - 2021-07-16 14:25

Priority

Major [ 3 ]

Critical [ 2 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-07-19 06:18

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-07-19 06:29

Affects Version/s

10.3.30 [ 25732 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-07-19 06:53

Labels

not-10.2

Nayuta Yanagisawa (Inactive) made changes - 2021-07-19 06:53

Fix Version/s

10.3 [ 22126 ]

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-19 06:55

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 (Inactive) added a comment - 2021-07-19 06:55 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 (Inactive) made changes - 2021-07-19 07:11

Fix Version/s

10.6 [ 24028 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-07-19 07:11

Affects Version/s

10.6.3 [ 25904 ]

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-19 12:45

I created an MTR test case: https://github.com/MariaDB/server/commit/7fae7fd76e98b5d4b64a67a6aa0d4ef9aab97840

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-19 12:45 I created an MTR test case: https://github.com/MariaDB/server/commit/7fae7fd76e98b5d4b64a67a6aa0d4ef9aab97840

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-19 13:37

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 (Inactive) added a comment - 2021-07-19 13:37 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 (Inactive) made changes - 2021-07-19 13:38

Labels

not-10.2

not-10.2 regression

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-19 14:38 - 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~~.

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-19 14:38 - 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 .

Nayuta Yanagisawa (Inactive) made changes - 2021-07-19 14:38

Assignee	Nayuta Yanagisawa [ JIRAUSER47117 ]	Sergei Golubchik [ serg ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-07-19 14:59

Assignee

Sergei Golubchik [ serg ]

Nayuta Yanagisawa [ JIRAUSER47117 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-07-19 15:00

Comment

[ I noticed that I have to cover the case of {{CASE_SIMPLE_FUNC}}. Let me withdraw the review request. ]

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-19 15:00

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

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-19 15:00 serg I noticed that I have to cover the case of CASE_SIMPLE_FUNC . Let me withdraw the review request.

Nayuta Yanagisawa (Inactive) made changes - 2021-07-19 15:12

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-19 15:21

serg I'm sorry for repeating myself. Please review. https://github.com/MariaDB/server/commit/cf6d83e7d68bd2bc0af0404bf4dd15218847074d

Nayuta Yanagisawa (Inactive) added a comment - 2021-07-19 15:21 serg I'm sorry for repeating myself. Please review. https://github.com/MariaDB/server/commit/cf6d83e7d68bd2bc0af0404bf4dd15218847074d

Nayuta Yanagisawa (Inactive) made changes - 2021-07-19 15:21

Assignee	Nayuta Yanagisawa [ JIRAUSER47117 ]	Sergei Golubchik [ serg ]
Status	Stalled [ 10000 ]	In Review [ 10002 ]

Sergei Golubchik made changes - 2021-07-21 11:30

Assignee	Sergei Golubchik [ serg ]	Nayuta Yanagisawa [ JIRAUSER47117 ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Nayuta Yanagisawa (Inactive) made changes - 2021-07-21 11:54

Fix Version/s		10.3.31 [ 26028 ]
Fix Version/s		10.4.21 [ 26030 ]
Fix Version/s		10.5.12 [ 26025 ]
Fix Version/s		10.6.4 [ 26033 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:52

Workflow

MariaDB v3 [ 118716 ]

MariaDB v4 [ 158850 ]

Jira Automation (IT) made changes - 2024-07-04 02:35

Zendesk Related Tickets

141652

People

Assignee:: Nayuta Yanagisawa (Inactive)

Reporter:: suresh ramagiri

Votes:: 0 Vote for this issue

Watchers:: 5 Start watching this issue

Dates

Created:: 2021-02-02 02:57

Updated:: 2024-07-07 21:44

Resolved:: 2021-07-21 11:54

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration