Bad date time arithmetic rendered for DuckDB DATE + INTERVAL expressions #19527
Open
1 of 1 issue completed
Description
One integration test fails as we're currently rendering this:
select
cast(date_add(date '1970-01-01', interval 1 day * (2 * interval '0 years 9 mons 0 days 0 hours 0 mins 0.0 secs')) as date)This produces an error:
org.jooq.exception.DataAccessException: SQL [select cast(date_add(cast(? as date), interval 1 day * (cast(? as int) * cast(? as interval year to month))) as date) "d3", cast(date_add(cast(? as date), interval 1 day * (cast(? as int) * cast(? as interval year to month))) as date) "d3a", cast(date_add(cast(date_add(cast(? as date), interval 1 day * (cast(? as int) * cast(? as interval year to month))) as date), interval 1 day * (cast(? as int) * cast(? as interval year to month))) as date) "d3b" -- SQL rendered with a free trial version of jOOQ 3.21.0-SNAPSHOT]; java.sql.SQLException: Binder Error: No function matches the given name and argument types '*(INTERVAL, INTERVAL)'. You might need to add explicit type casts.
Candidate functions:
*(TINYINT, TINYINT) -> TINYINT
*(SMALLINT, SMALLINT) -> SMALLINT
*(INTEGER, INTEGER) -> INTEGER
*(BIGINT, BIGINT) -> BIGINT
*(HUGEINT, HUGEINT) -> HUGEINT
*(FLOAT, FLOAT) -> FLOAT
*(DOUBLE, DOUBLE) -> DOUBLE
*(DECIMAL, DECIMAL) -> DECIMAL
*(UTINYINT, UTINYINT) -> UTINYINT
*(USMALLINT, USMALLINT) -> USMALLINT
*(UINTEGER, UINTEGER) -> UINTEGER
*(UBIGINT, UBIGINT) -> UBIGINT
*(UHUGEINT, UHUGEINT) -> UHUGEINT
*(INTERVAL, BIGINT) -> INTERVAL
*(BIGINT, INTERVAL) -> INTERVAL
LINE 1: select cast(date_add(cast(? as date), interval 1 day * (cast(? as int) * cast(? as interval year to month)))...
^
at org.jooq_3.21.0-SNAPSHOT.DUCKDB.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:3736)
at org.jooq.impl.Tools.translate(Tools.java:3712)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:912)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:370)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:267)
at org.jooq.impl.AbstractResultQuery.fetchLazy(AbstractResultQuery.java:296)
at org.jooq.impl.AbstractResultQuery.fetchLazyNonAutoClosing(AbstractResultQuery.java:317)
at org.jooq.impl.SelectImpl.fetchLazyNonAutoClosing(SelectImpl.java:3243)
at org.jooq.impl.ResultQueryTrait.fetchOne(ResultQueryTrait.java:508)
at org.jooq.test.all.testcases.DataTypeTests.lambda$86(DataTypeTests.java:3759)
at org.jooq.test.jOOQAbstractTest.withDSLContext(jOOQAbstractTest.java:2973)
at org.jooq.test.jOOQAbstractTest.bindAndInline(jOOQAbstractTest.java:2956)
at org.jooq.test.BaseTest.bindAndInline(BaseTest.java:2566)
at org.jooq.test.all.testcases.DataTypeTests.testDateTimeArithmetic_IntervalNumberMultiplication_Date_YearToMonth(DataTypeTests.java:3753)
at org.jooq.test.jOOQAbstractTest.testDateTimeArithmetic_IntervalNumberMultiplication_Date_YearToMonth(jOOQAbstractTest.java:8910)
at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61)
at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:93)
at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:520)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:748)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:443)
at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:211)
Caused by: java.sql.SQLException: java.sql.SQLException: Binder Error: No function matches the given name and argument types '*(INTERVAL, INTERVAL)'. You might need to add explicit type casts.
Candidate functions:
*(TINYINT, TINYINT) -> TINYINT
*(SMALLINT, SMALLINT) -> SMALLINT
*(INTEGER, INTEGER) -> INTEGER
*(BIGINT, BIGINT) -> BIGINT
*(HUGEINT, HUGEINT) -> HUGEINT
*(FLOAT, FLOAT) -> FLOAT
*(DOUBLE, DOUBLE) -> DOUBLE
*(DECIMAL, DECIMAL) -> DECIMAL
*(UTINYINT, UTINYINT) -> UTINYINT
*(USMALLINT, USMALLINT) -> USMALLINT
*(UINTEGER, UINTEGER) -> UINTEGER
*(UBIGINT, UBIGINT) -> UBIGINT
*(UHUGEINT, UHUGEINT) -> UHUGEINT
*(INTERVAL, BIGINT) -> INTERVAL
*(BIGINT, INTERVAL) -> INTERVAL
LINE 1: select cast(date_add(cast(? as date), interval 1 day * (cast(? as int) * cast(? as interval year to month)))...
^
at org.duckdb.DuckDBPreparedStatement.prepare(DuckDBPreparedStatement.java:121)
at org.duckdb.DuckDBPreparedStatement.<init>(DuckDBPreparedStatement.java:75)
at org.duckdb.DuckDBConnection.prepareStatement(DuckDBConnection.java:80)
at org.duckdb.DuckDBConnection.prepareStatement(DuckDBConnection.java:192)
at org.jooq.tools.jdbc.DefaultConnection.prepareStatement(DefaultConnection.java:95)
at org.jooq.test.utils.LoggingConnection.prepareStatement(LoggingConnection.java:68)
at org.jooq.tools.jdbc.DefaultConnection.prepareStatement(DefaultConnection.java:95)
at org.jooq.impl.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:109)
at org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:91)
at org.jooq.impl.AbstractResultQuery.prepare(AbstractResultQuery.java:196)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:323)
... 41 more
Caused by: java.sql.SQLException: Binder Error: No function matches the given name and argument types '*(INTERVAL, INTERVAL)'. You might need to add explicit type casts.
Candidate functions:
*(TINYINT, TINYINT) -> TINYINT
*(SMALLINT, SMALLINT) -> SMALLINT
*(INTEGER, INTEGER) -> INTEGER
*(BIGINT, BIGINT) -> BIGINT
*(HUGEINT, HUGEINT) -> HUGEINT
*(FLOAT, FLOAT) -> FLOAT
*(DOUBLE, DOUBLE) -> DOUBLE
*(DECIMAL, DECIMAL) -> DECIMAL
*(UTINYINT, UTINYINT) -> UTINYINT
*(USMALLINT, USMALLINT) -> USMALLINT
*(UINTEGER, UINTEGER) -> UINTEGER
*(UBIGINT, UBIGINT) -> UBIGINT
*(UHUGEINT, UHUGEINT) -> UHUGEINT
*(INTERVAL, BIGINT) -> INTERVAL
*(BIGINT, INTERVAL) -> INTERVAL
LINE 1: select cast(date_add(cast(? as date), interval 1 day * (cast(? as int) * cast(? as interval year to month)))...
^
at org.duckdb.DuckDBNative.duckdb_jdbc_prepare(Native Method)
at org.duckdb.DuckDBPreparedStatement.prepare(DuckDBPreparedStatement.java:115)
... 51 more
I don't know anymore what the motivation was to generate an interval 1 day expression like that, but clearly, this doesn't work. Perhaps, the error didn't manifest like this before, prior to fixing interval bind values, though, so it might have been the correct syntax for integer "intervals" (e.g. DATE + INTEGER):