dateDiff support for Firebird

[Mark Rotteveel]

I ran into a problem with dateDiff on Firebird with jOOQ 3.12.1, and I'm
not sure if it is not supported on Firebird, or if I'm missing some option.

I'm trying to generate a query with a condition that is the equivalent of:

ABS(DATEDIFF(DAY FROM a.PREVIOUS_POST_DATE TO a.NEXT_POST_DATE)) <= 1

However using

var oneDay = DayToSecond.valueOf(Duration.ofDays(1))
var previousPostDate =
linkInfoDates.field("PREVIOUS_POST_DATE", SQLDataType.LOCALDATETIME);
var nextPostDate =
linkInfoDates.field("NEXT_POST_DATE", SQLDataType.LOCALDATETIME);

and (as fragment of a larger query):

abs(localDateTimeDiff(previousPostDate,
nextPostDate)).lessOrEqual(oneDay)

produces SQL like:

abs(datediff(millisecond, "a"."NEXT_POST_DATE",
"a"."PREVIOUS_POST_DATE")) <= '+1 00:00:00.000000000'

The problem is that Firebird has no interval literal, so the right hand
side of the comparison ('+1 00:00:00.000000000') is not valid and
produces an error:

java.sql.SQLException: conversion error from string "+1
00:00:00.000000000" [SQLState:22018, ISC error code:335544334]

Is there an option I'm missing, or should I resort to using an escape to
plain SQL:

abs(field("DATEDIFF(DAY FROM " + previousPostDate + " TO " +
nextPostDate + ")", SQLDataType.INTEGER)).lessOrEqual(1)

Mark
--
Mark Rotteveel

[Lukas Eder]

Hi Mark,

Thank you very much for your thorough bug report. I do think this is a bug, although I'm not sure if we should:

- Generate an integer value instead of an interval

- Change the API entirely, as other dialects will probably have a similar problem with this particular usage.

A workaround is, as always, to use plain SQL. I would not resort to using string concatenation, but the templating functionality instead:

abs(field("DATEDIFF(DAY FROM {0} TO {1})", SQLDataType.INTEGER, previousPostDate, nextPostDate)).lessOrEqual(1)  

Another option is to use Field.coerce(SQLDataType.INTEGER) on your datediff expression:

abs(localDateTimeDiff(previousPostDate, nextPostDate).coerce(INTEGER)).lessOrEqual(oneDay)  

Coerce re-assigns a new jOOQ DataType to a Field expression, without any impact on the resulting SQL statement.

We'll look into how to fix this thoroughly, but you should be able to continue working with one of the above workarounds.

Thanks,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/f91c1cfd-e015-76b4-fe9e-b461d91ea712%40lawinegevaar.nl.

[Mark Rotteveel]

On 2019-10-21 13:17, Lukas Eder wrote:
> Hi Mark,
>
> Thank you very much for your thorough bug report. I do think this is a
> bug, although I'm not sure if we should:
>
> - Generate an integer value instead of an interval
> - Change the API entirely, as other dialects will probably have a
> similar problem with this particular usage.
>
> A workaround is, as always, to use plain SQL. I would not resort to
> using string concatenation, but the templating functionality instead:
>
>> abs(field("DATEDIFF(DAY FROM {0} TO {1})", SQLDataType.INTEGER,
>> previousPostDate, nextPostDate)).lessOrEqual(1)

Thanks. This was the first time I actually used this particular feature,
and I hadn't seen the templating option.

> Another option is to use Field.coerce(SQLDataType.INTEGER) on your
> datediff expression:
>
>> abs(localDateTimeDiff(previousPostDate,
>> nextPostDate).coerce(INTEGER)).lessOrEqual(oneDay)
>
> Coerce re-assigns a new jOOQ DataType to a Field expression, without
> any impact on the resulting SQL statement.

I'll try that as well. Given I'm checking bad data, and the range of
dates I'm working is occasionally off by years, coercing to BIGINT is
probably better to avoid overflow issues.

> We'll look into how to fix this thoroughly, but you should be able to
> continue working with one of the above workarounds.

Thanks, I'll try and see which option works best for me.

As a related remark, being able to specify the part parameter would be a
great feature as well. The results of DATEDIFF(DAY, ..., ...) and
DATEDIFF(MILLISECOND, ..., ...) are not entirely equivalent as DATEDIFF
only uses the specified part and 'higher' parts for calculating the
difference. For example, DATEDIFF(DAY, timestamp '2019-10-21
23:59:59.999', timestamp '2019-10-22 00:00:00') is 1, but
DATEDIFF(MILLISECOND, timestamp '2019-10-21 23:59:59.999', timestamp
'2019-10-22 00:00:00') is also 1. In my specific use case, this doesn't
really matter, but it might be a relevant difference for example when
using a greaterThan comparison.

Mark

[Lukas Eder]

As a related remark, being able to specify the part parameter would be a
great feature as well. The results of DATEDIFF(DAY, ..., ...) and
DATEDIFF(MILLISECOND, ..., ...) are not entirely equivalent as DATEDIFF
only uses the specified part and 'higher' parts for calculating the
difference. For example, DATEDIFF(DAY, timestamp '2019-10-21
23:59:59.999', timestamp '2019-10-22 00:00:00') is 1, but 
DATEDIFF(MILLISECOND, timestamp '2019-10-21 23:59:59.999', timestamp
'2019-10-22 00:00:00') is also 1. In my specific use case, this doesn't
really matter, but it might be a relevant difference for example when
using a greaterThan comparison.

Absolutely!

https://github.com/jOOQ/jOOQ/issues/7900

There are a variety of such feature requests on our roadmap. While each one of them is very useful on its own, they're all very difficult to implement, as all dialects are completely different when it comes to date time arithmetic. This is why some of these features are still not available in jOOQ.

Thanks,

Lukas 

[Mark Rotteveel]

I entirely understand that, and with the available workarounds this is
not a pressing matter.

Have you considered exposing or using the JDBC escapes like
TIMESTAMPDIFF and then leave it up to the driver to provide the
translation? That does assume that drivers implement those JDBC escapes,
but it is required for the Java EE support level of a driver.

Mark

[Lukas Eder]

Have you considered exposing or using the JDBC escapes like
TIMESTAMPDIFF and then leave it up to the driver to provide the
translation? That does assume that drivers implement those JDBC escapes,
but it is required for the Java EE support level of a driver.

I really don't like those escapes.

1) They're very poorly supported

2) They're very incomplete (why are there escapes for 1-2 features, but not the gazillion other ones?)

3) They produce SQL that relies on JDBC, which we don't want. jOOQ can be used in other, non-JDBC based tooling, e.g. https://www.jooq.org/translate

We're only using JDBC escapes if there is really no other way. E.g. in HSQLDB, some functions are *only* implemented using this escape syntax. 

[Mark Rotteveel]

On 2019-10-22 11:13, Lukas Eder wrote:
>> Have you considered exposing or using the JDBC escapes like
>> TIMESTAMPDIFF and then leave it up to the driver to provide the
>> translation? That does assume that drivers implement those JDBC
>> escapes,
>> but it is required for the Java EE support level of a driver.
>
> I really don't like those escapes.

To be honest, I never use them. I only know about them for the fun (not
entirely ironic) I had (re)implementing them in Jaybird.

> 1) They're very poorly supported
> 2) They're very incomplete (why are there escapes for 1-2 features,
> but not the gazillion other ones?)

As I understand it, historically they are derived from ODBC (like a lot
of other things in JDBC). I guess that when writing the first ODBC
specification they took a cross-section of what most databases at that
time had in some form or another, and came up with escapes for those
features to allow a unified access. JDBC just copied that. See
https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/odbc-escape-sequences?view=sql-server-ver15
and
https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/appendix-e-scalar-functions?view=sql-server-ver15
and compare that against the JDBC specification.

From the perspective of JDBC, there probably won't be new JDBC escapes,
except something in the next JDBC revision (whenever that arrives) to
mark sections of SQL as 'pass-through' without processing to address
parser problems for drivers of databases where the native parameter
marker is not a question mark, where parts of their syntax also uses
question marks.

The primary opinion from the JDBC EG is that databases should use SQL
standard syntax.

Mark