[MXS-4307] Parser can't recognize convert function parameters and cause wrong routing decision Created: 2022-09-21  Updated: 2022-09-22  Resolved: 2022-09-22

Status: Closed
Project: MariaDB MaxScale
Component/s: qc_sqlite
Affects Version/s: 2.4, 2.5, 6.4, 22.08
Fix Version/s: 6.4.3, 22.08.2

Type: Bug Priority: Major
Reporter: danielforever Assignee: Johan Wikman
Resolution: Fixed Votes: 0
Labels: None


 Description    

select CONVERT(substring(receive_code,8), UNSIGNED INTEGER) as sort, `id`, `receive_code` from `hz_lab_sample_receive` where `created_at` >= '2022-09-16 00:00:00' and `laboratory_code` = LY0023A order by `sort` desc limit 1 lock in share mode;

in `CONVERT(substring(receive_code,8), UNSIGNED INTEGER)`, UNSIGNED INTEGER is not recognized.

The SQL is parsed as QUERY_TYPE_READ.
`lock in share mode` has no chance of parsing.



 Comments   
Comment by danielforever [ 2022-09-21 ]

@markus makela

Comment by markus makela [ 2022-09-21 ]

Can you try if this affects other releases as well?

Given that LOCK IN SHARE MODE and FOR UPDATE do not work outside of a transaction, this doesn't seem like a big problem. Can you confirm that if a transaction is open, the query is still routed to the active master server?

Comment by danielforever [ 2022-09-21 ]

here is the thing:

if users use `for update` or `LOCK IN SHARE MODE`, they want to route this SQL to the master server.
Regardlessly, the parser stops in the middle of SQL will cause error in the following routing rounds. (for example, tables are not collected, temp table, etc).

As for the other versions, I've checked the parse.y, it's the same. (It has the same issue).

Comment by markus makela [ 2022-09-21 ]

Yes, temporary tables can indeed be missed due to this which will cause problems outside of transactions.

Comment by Johan Wikman [ 2022-09-22 ]

daniel_xu_forever If you rewrite CONVERT(substring(receive_code,8), UNSIGNED INTEGER) as CAST(substring(receive_code,8) AS UNSIGNED INTEGER) the whole statement should be parsed correctly. The CONVERT case will be fixed, but it will take a while before it is available.

Generated at Thu Feb 08 04:27:42 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.