DB2 Date Arithmetic

[s961212]

Actual Behavior

execute the following SQL caused exception below
SELECT DOB + 1 YEARS as NewDOB FROM TABLE

Caused by:
net.sf.jsqlparser.parser.ParseException: Encountered " "AS" "AS "" at line 1, column 36.
Was expecting one of: ....

it is unable to use db2 Date arithmetic

Expected Behavior

Steps to Reproduce the Problem

Specifications

• Version:
• Platform:
• Subsystem:

[thebiguno]

Hello,

I am running into this issue (or at least something related to it) as well. Date math seems to work in JSQLParser for simple cases, but there are some more which do not.

See https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/db2z_datearithmetic.html for some documentation on this.

Some simplified examples that we have from our existing set of SQL reports, which fail in JSQLParser but work in DB2, are as follows:

1. Duration in brackets:

SELECT CURRENT_DATE + (1 DAY) FROM SYSIBM.SYSDUMMY1

The workaround here is to remove the brackets from around '1 DAY'.

2. Naming an output column (as reported above):

SELECT CURRENT_DATE + 1 DAY AS NEXT_DATE FROM SYSIBM.SYSDUMMY1

This also fails if you leave out the 'AS':

SELECT CURRENT_DATE + 1 DAY NEXT_DATE FROM SYSIBM.SYSDUMMY1

Not sure of a workaround for this issue... it seems as though you cannot name a column that contains date math.

3. Multiple operations on a single field:

SELECT CURRENT_DATE - 1 DAY + 1 YEAR - 1 MONTH FROM SYSIBM.SYSDUMMY1

Not aware of a workaround for this issue either.

4. Not sure exactly how to classify this one... This fails:

SELECT CASE WHEN CURRENT_DATE BETWEEN (CURRENT_DATE - 1 DAY) AND ('2019-01-01') THEN 1 ELSE 0 END FROM SYSIBM.SYSDUMMY1

but if you replace (CURRENT_DATE - 1 DAY) with ('2018-01-01') it works.
Not aware of a workaround for this issue.

5. Another issue with case statements:

SELECT
CASE
WHEN FOO IS NULL THEN NULL
ELSE A_DATE_COLUMN + A_NUMBER_COLUMN MONTH
END
FROM A_TABLE

This appears to be an issue only when the date math is in a case statement; the statement:

SELECT A_DATE_COLUMN + A_NUMBER_COLUMN MONTH FROM A_TABLE

parses correctly.
I am not aware of a workaround for this issue.

Please let me know if you need any other samples or information.

Thank you very much for your support!

Cheers

[wumpz]

There is a JSqlParser misparsing between date literals (month, year) and aliases. This would be very difficult to implement (I think).

select 5 years or select 5 as years is for many dbms the same and years is the alias.

[thebiguno]

Hmm, good point. According to https://www.petefreitag.com/tools/sql_reserved_words_checker/, YEAR, MONTH, DAY, etc (singular) are reserved words in many DBs including DB2, Postgres, ODBC, and future versions of SQL Server. The plural forms YEARS, MONTHS, DAYS, etc are only reserved on DB2. Would it be easier to implement this feature using the singular form as a reserved word?

This is a show stopper for any DB2 queries involving date math; speaking at least for our reports, this is a very common use case. Furthermore, from what I have found there is no workaround that works in JSqpParser. If the parser supported date math using the singular forms, that could be a way to make it work without some of the difficulties that you point out above.

I realize that adding reserved words this late in the game is dangerous, and may affect other users' queries. If the changes cannot be put into your branch, I may look at forking a copy and doing modifications like this for better DB2 support. Would doing it this way be an easy fix? I am researching EBNF grammars, but do not yet understood the .jjt file. While I would obviously prefer using the official version, this parser is so close to doing exactly what we need that I don't want to move to a different product if possible.

Thanks!

[wumpz]

Adding keywords could be done without fork. There are so many tests, that one should be warned quite fast.
Using these keywords as identifiers is possible by adding to RelObjectName.

[thebiguno]

Would adding those keywords YEAR, MONTH, DAY, etc make parsing easier / possible?

[wumpz]

It would be a first step. One should decide how to handle those alias conflicts. Maybe integrate some semantic lookaheads to make it configurable.

[thebiguno]

It's been a while... I have been working on other areas of the product not directly related to SQL Parsing.

I am now looking back at this. I have been unable to figure out how to modify the code to support the date math. Unfortunately this is a major holdup for our software, as we use date math frequently, and without either support for the date math or a workaround, JSQLParser would not work for us.

Are there any plans to implement this? It looks like we are not the only ones encountering the issue, see for instance #666 .

Alternatively, if you would be able to give me some pointers on what would need to be done to implement this, I can try again. I have not worked on parser code before, and especially not JavaCC, and so am not understanding what needs to go in the .jjt file and what should go into the classes. I thought I could figure it out by jumping in, but the learning curve is too steep for me.

Thanks!

[wumpz]

There is a good start at the IntervalExpression. This is what you want. But JSqlParser only supports it with a leading "INTERVAL" keyword.

Look e.g. at SelectTest.testInterval4.

The interval keyword has to be optional, therefore the production should look something like:

IntervalExpression IntervalExpression() : {
    IntervalExpression interval = new IntervalExpression();
    Token token;
    boolean signed = false;
}
{
    [ <K_INTERVAL> ] ["-" {signed=true;}] (token=<S_LONG> | token=<S_DOUBLE> | token=<S_CHAR_LITERAL>) 
    { interval.setParameter((signed?"-":"") + token.image); }
    [ LOOKAHEAD(2) token = <S_IDENTIFIER> { interval.setIntervalType(token.image); } ]
    {
        return interval;
    }
}

Now comes the hard part. I guess there are multiple problems in the grammar. JavaCC cannot decide, which production to go. JavaCC gives you, while building the parser, warnings where a collision appears.

Hope that helps for a first start.

[thebiguno]

OK thanks, I will take a look and let you know if (or more likely when ;-) ) I have more questions.

[thebiguno]

Looking good, thanks very much! There are some places in our set of SQL statements that are still not quite working, as my simplified test cases above didn't explicitly address them.

1. Date offset coming from an expression instead of a integer constant:
   select offset, CURRENT_DATE + offset MONTH as new_date from mytable
   Any integer expression should be fine here: constant, function, case statement, etc. It looks like integer constants are working but the others are not. Other examples:
   select CURRENT_DATE + (RAND() * 12 MONTH) as new_date from mytable
select CURRENT_DATE + CASE WHEN CAST(RAND() * 3 AS INTEGER) = 1 THEN 100 ELSE 0 END DAY AS NEW_DATE from mytable
select CURRENT_DATE + (dayofweek(MY_DUE_DATE) + 5) DAY FROM mytable
select CASE WHEN CAST(RAND() * 3 AS INTEGER) = 1 THEN NULL ELSE CURRENT_DATE + (month_offset MONTH) END FROM mytable (Within a case statement; it looks like this should Just Work when the first example is working, as if I change month_offset to 1 it works fine.)

Note that these will generally parse (incorrectly) if you don't supply the alias, but they are parsing as the expression being X + Y with an alias of MONTH, not X + (Y MONTH).

2. The spec also allows for time intervals such as HOUR, MINUTE, SECOND. For instance:

select CURRENT_DATE - 1 HOUR AS FOO from SYSIBM.SYSDUMMY1
select CURRENT_DATE - 2 MINUTE AS FOO from SYSIBM.SYSDUMMY1
select CURRENT_DATE - 3 SECOND AS FOO from SYSIBM.SYSDUMMY1

This is easily changed by modifying line 143 of JSQLParserCC.jit to be:
| <K_DATE_LITERAL : ( "YEAR" | "MONTH" | "DAY" | "HOUR" | "MINUTE" | "SECOND" ) >

All tests still pass, after compiling this works for the above test case. I can supply a pull request if you want, but I imagine it would be simpler to just copy the above line.

[thebiguno]

Doing some more investigation into point 1...

If you add <S_IDENTIFIER> to the line ( token=<S_LONG> | token=<S_DOUBLE> | token=<S_CHAR_LITERAL> | token = <S_IDENTIFIER>) it fixes many of the issues we are seeing (most of the time we use a single identifier as the offset value; it is a less frequent case that we use an expression).

A test case for this scenario is:

select current_date + foo day as next_day from foo

It still does not work for expressions. I tried adding support for an expression directly there, but it gives a left parse error when reading the .jj file. I tried wrapping the expression in brackets (accepted but not required in DB2), and everything compiled but tests started to fail - it looks like the parser was getting confused.

Anyway, the S_IDENTIFIER change works for most of our reports. There are now only 11 out of 1553 reports which fail to parse.

I have created a pull request #756 which adds the identifier, as well as the hour / minute / second date literals from point 2 above, as well as some new test cases which test these scenarios.

Cheers