[MXS-1688] Some date functions are not parsed properly with schemarouter Created: 2018-02-26  Updated: 2020-08-25  Resolved: 2018-03-01

Status: Closed
Project: MariaDB MaxScale
Component/s: schemarouter
Affects Version/s: 2.1.13
Fix Version/s: 2.1.14, 2.2.3

Type: Bug Priority: Critical
Reporter: Valerii Kravchuk Assignee: Johan Wikman
Resolution: Fixed Votes: 0
Labels: None


 Description   

When non-default database is referred in statements using the following datetime-related functions with INTERVAL clauses:

DATE_SUB(('date1',INTERVAL x DAY)
 
SUBDATE('date1',INTERVAL x DAY)
 
DATE_ADD('date1',INTERVAL x DAY)
 
ADDDATE('date1',INTERVAL x DAY)

for example:

select id from db2.t1 where DATE_ADD("2017-06-15", INTERVAL 10 DAY) < "2017-06-15";

schemarouter can not parse them properly and produces table dioesn't exist error (even if the table exists only in the referred database db2.

In the log we see:

2018-02-26 23:34:10   warning: (7) [qc_sqlite] Statement was classified only based on keywords (Sqlite3 error: SQL logic error or missing database, near "INTERVAL": syntax error): "select id from db2.t1 where DATE_ADD("2017-06-15", INTERVAL 10 DAY) < "2017-06-15""

So, it's a parser error.
Generated at Thu Feb 08 04:08:40 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.