Issue 89039 - Support MySQL datetime arithmetic like DATE_ADD and DATE_SUB with INTERVAL
Summary: Support MySQL datetime arithmetic like DATE_ADD and DATE_SUB with INTERVAL
Alias: None
Product: Base
Classification: Application
Component: code (show other issues)
Version: OOo 2.4.0
Hardware: All All
: P3 Trivial with 1 vote (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2008-05-05 20:41 UTC by vgagernm
Modified: 2013-08-07 15:45 UTC (History)
1 user (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---


Note You need to log in before you can comment on or make changes to this issue.
Description vgagernm 2008-05-05 20:41:25 UTC
I get error messages when I try to do MySQL date arithmetic in an SQL query.

Streps to reproduce:
1. Connect to some mysql database using the JDBC MySQL Connector (5.0.8 here)
2. Create table:
   2.1. Tools / SQL
   2.2. CREATE TABLE test1 (dateCol DATE NOT NULL)
   2.3. Execute
   2.4. Close
3. View / Refresh Tables
4. Enter some date:
   4.1. Tools / SQL
   4.2. INSERT INTO test1 VALUES ('2008-05-05')
   4.3. Execute
   4.4. Close
5. Create a new Query:
   5.1. Queries
   5.2. Create Query in SQL View
   5.3. SELECT dateCol FROM test1
   5.4. Save as test2
6. Introduce date arithmetic
   6.1. SELECT dateCol, DATE_ADD(dateCol, INTERVAL 1 DAY) FROM test1
   6.2. Save
        => Syntax error in SQL expression
   6.3. More lists three errors:

Error 1:
SQL Status: HY000
Error code: 1000
Syntax error in SQL expression

Error 2:
SQL Status HY000
Error code: 1000

Error 3:
SQL Status: HY000
Error code: 1000
parse error, expecting `BETWEEN' or `IN' or `SQL_TOKEN_LIKE'

Executung the same command in the MySQL command line client works all right.
Executing the same command in the Java BeanShell console using the same JDBC
connector works all right.
Therefore this is neither a syntax problem nor a problem with MySQL or JDBC,
therefore it's a problem with

Looking at the sources, it would seem that datetime_value_exp in
OOH680_m12/connectivity/source/parse/sqlbison.y should be modified to accept
this kind of expression, either the whole DATE_ADD etc., or three words, the
first of them being INTERVAL. There are some comments about this, but for some
reason no active code.
Comment 1 marc.neumann 2008-05-06 12:11:10 UTC
reassign to oj
Comment 2 ocke.janssen 2008-12-10 07:49:30 UTC
Set target.