Hi,
I think I might have stumbled upon a bug, but I thought it best to ask
here first.
I just upgraded to version 1.6.8 from 1.5.5.2., and now one of my
queries which contains a nested select that uses the 'extract'
function no longer works. I'm getting the following exception:
org.jooq.SQLDialectNotSupportedException: extract not supported
at org.jooq.impl.Extract.getFunction0(Extract.java:153)
at org.jooq.impl.AbstractFunction.getFunction(AbstractFunction.java:
89)
at org.jooq.impl.AbstractFunction.toSQL(AbstractFunction.java:75)
at org.jooq.impl.DefaultRenderContext.sql(DefaultRenderContext.java:
160)
at org.jooq.impl.AliasProviderImpl.toSQL(AliasProviderImpl.java:85)
at org.jooq.impl.DefaultRenderContext.sql(DefaultRenderContext.java:
173)
at org.jooq.impl.FieldAlias.toSQL(FieldAlias.java:69)
at org.jooq.impl.DefaultRenderContext.sql(DefaultRenderContext.java:
173)
at org.jooq.impl.QueryPartList.toSQL(QueryPartList.java:89)
at org.jooq.impl.DefaultRenderContext.sql(DefaultRenderContext.java:
173)
at
org.jooq.impl.AbstractSubSelect.toSQLReference0(AbstractSubSelect.java:
456)
at org.jooq.impl.AbstractSubSelect.toSQL(AbstractSubSelect.java:229)
at org.jooq.impl.DefaultRenderContext.sql(DefaultRenderContext.java:
173)
at org.jooq.impl.SelectQueryAsTable.toSQL(SelectQueryAsTable.java:
106)
at org.jooq.impl.DefaultRenderContext.sql(DefaultRenderContext.java:
173)
at
org.jooq.impl.DefaultRenderContext.render(DefaultRenderContext.java:
92)
at org.jooq.impl.Factory.renderInlined(Factory.java:257)
at org.jooq.impl.AbstractQueryPart.hashCode(AbstractQueryPart.java:
239)
at org.jooq.impl.AbstractSelect.asTable(AbstractSelect.java:129)
at org.jooq.impl.AbstractSelect.asTable(AbstractSelect.java:134)
at
org.jooq.impl.AbstractDelegatingSelect.asTable(AbstractDelegatingSelect.java:
245)
at be.pensionarchitects.dashboard.JooqTest$1.execute(JooqTest.java:
44)
at org.hibernate.impl.SessionImpl.doWork(SessionImpl.java:1997)
at $Session_13340728974.doWork($Session_13340728974.java)
at $Session_13340728970.doWork($Session_13340728970.java)
at be.pensionarchitects.dashboard.JooqTest.test(JooqTest.java:53)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:
39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:
25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.junit.runners.model.FrameworkMethod
$1.runReflectiveCall(FrameworkMethod.java:44)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:
15)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:
41)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:
20)
at
org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:
28)
at
org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:
31)
at org.junit.rules.TestWatchman$1.evaluate(TestWatchman.java:48)
at
org.junit.runners.BlockJUnit4ClassRunner.runNotIgnored(BlockJUnit4ClassRunner.java:
79)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:
71)
at
org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:
49)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:193)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:52)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:191)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:42)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:184)
at
org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:
28)
at
org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:
31)
at org.junit.runners.ParentRunner.run(ParentRunner.java:236)
at
org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:
49)
at
org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:
38)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:
467)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:
683)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:
390)
at
org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:
197)
Now, I found the cause of this exception. The
AbstractSelect.asTable(String alias) method delegates to the no-
arg .asTable() method in the same class, which calls the hashCode
method on a newly created SelectQueryAsTable instance to generate an
alias. The hashCode method calls the renderInlined method on a new
factory to render the subselect's SQL. However, all this happens in a
disconnected fashion with a default configuration and the default
dialect which does not support the extract function. Hence the
exception.
As a workaround, I am using a patched version of the AbstractSelect
class where the asTable(String alias) method does not delegate to the
no-arg variant but creates the SelectQueryAsTable directly. This
avoids the call to hashCode() and the exception. I think it also makes
more sense this way because the current code generates an alias
unnecessarily.
But I was wondering: is there a newly imposed restriction on nested
selects? Am I doing it wrong? Here is a code sample that illustrates
the problem:
SchemaMapping mapping = new SchemaMapping();
mapping.add(Pfadmin.PFADMIN, "pfadmin_fuel");
Factory create = new Factory(connection, SQLDialect.MYSQL, mapping);
Table<Record> subselect = getSubselect(create,
19).asTable("subselect");
@SuppressWarnings("unchecked")
Field<Integer> txYear = (Field<Integer>)subselect.getField("txYear");
create.select(txYear).from(subselect).fetch();
And the subselect method:
private Select<Record> getSubselect(Factory create, Integer policyId)
{
Field<Integer> txYear =
TransactionTable.T_DATE_VALUTA.extract(DatePart.YEAR).as("txYear");
return create
.select(txYear)
.from(TBL_TRANSACTION)
.join(TBL_ACCOUNT).on(AccountTable.A_ACCOUNT_ID.equal(TransactionTable.T_ACCOUNT_ID))
.join(TBL_POLICY_SUB_POLICY).on(PolicySubPolicyTable.POLICY_SUB_POLICY_ID.equal(AccountTable.A_SPOL_ID))
.join(TBL_POLICY).on(PolicyTable.POL_POLICY_ID.equal(PolicySubPolicyTable.SPOL_POLICY_ID))
.where(PolicyTable.POL_POLICY_ID.equal(policyId));
}
Thanks in advance.
Regards,
Gunther
