using DSL.any in a condition field throws ClassCastException

[Gokul Krishna Paravasthu]

Hi,

I am trying to model a recursive sql query with jooq 3.6.2 and postgres 9.4

The query is

with recursive "all_managers"("rid", "path", "cycle") as ((select
       "reportee_user_id",
       ARRAY["reportee_user_id"], false from
       "tempusers" where
      ("user_id" = 100 and "org_id" = 1)) union all (select
       "dr"."reportee_user_id",
       array_append("path", "dr"."reportee_user_id"),
       "dr"."reportee_user_id" = any ("path") from
       "all_managers" as "am", "public"."direct_reportees_values" as
       "dr" where ("dr"."user_id" =
       "am"."rid" and not("cycle"))))
select distinct "rid" from "all_managers" join "tempusers" on rid=user_id

The select part is coded as

final Field<Integer> drReporteeUserId = DSL.field(DSL.name("dr", "reportee_user_id"), Integer.class);
final Field<Integer> drUserId = DSL.field(DSL.name("dr", "reportee_user_id"), Integer.class);
final Field<Integer[]> ctePathField = DSL.field(DSL.name("path"), Integer[].class);

final SelectConditionStep<Record3<Integer, Integer[], Boolean>> unionSelect =     dslContext.select(
    drReporteeUserId,
    PostgresDSL.arrayAppend(ctePathField,drReporteeUserId),
    DSL.field(drReporteeUserId.equal(DSL.any(ctePathField)))
    ).from(amTable, dr)
.where(drUserId.eq(amTableReporteeId).andNot(cycleField));

This results in an exception with the stack trace

java.lang.ClassCastException: org.jooq.impl.QualifiedField cannot be cast to org.jooq.Param
    at org.jooq.impl.ArrayTable.simulate(ArrayTable.java:310) ~[jooq.jar:na]
    at org.jooq.impl.ArrayTable.table(ArrayTable.java:214) ~[jooq.jar:na]
    at org.jooq.impl.ArrayTable.accept(ArrayTable.java:172) ~[jooq.jar:na]
    at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:368) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:353) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:133) ~[jooq.jar:na]
    at org.jooq.impl.QueryPartList.accept(QueryPartList.java:104) ~[jooq.jar:na]
    at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:368) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:353) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:133) ~[jooq.jar:na]
    at org.jooq.impl.DefaultRenderContext.render(DefaultRenderContext.java:154) ~[jooq.jar:na]
    at org.jooq.impl.SelectQueryImpl.toSQLReference0(SelectQueryImpl.java:987) ~[jooq.jar:na]
    at org.jooq.impl.SelectQueryImpl.toSQLReference0(SelectQueryImpl.java:796) ~[jooq.jar:na]
    at org.jooq.impl.SelectQueryImpl.toSQLReferenceLimitDefault(SelectQueryImpl.java:621) ~[jooq.jar:na]
    at org.jooq.impl.SelectQueryImpl.accept(SelectQueryImpl.java:460) ~[jooq.jar:na]
    at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:368) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:353) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:133) ~[jooq.jar:na]
    at org.jooq.impl.AbstractDelegatingQuery.accept(AbstractDelegatingQuery.java:95) ~[jooq.jar:na]
    at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:368) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:353) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:133) ~[jooq.jar:na]
    at org.jooq.impl.QuantifiedSelectImpl.accept(QuantifiedSelectImpl.java:109) ~[jooq.jar:na]
    at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:368) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:353) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:133) ~[jooq.jar:na]
    at org.jooq.impl.QuantifiedComparisonCondition.accept(QuantifiedComparisonCondition.java:78) ~[jooq.jar:na]
    at org.jooq.impl.DefaultRenderContext.visit0(DefaultRenderContext.java:368) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit0(AbstractContext.java:353) ~[jooq.jar:na]
    at org.jooq.impl.AbstractContext.visit(AbstractContext.java:133) ~[jooq.jar:na]
    at org.jooq.impl.DefaultRenderContext.render(DefaultRenderContext.java:154) ~[jooq.jar:na]
    at org.jooq.impl.DefaultDSLContext.renderInlined(DefaultDSLContext.java:408) ~[jooq.jar:na]
    at org.jooq.impl.AbstractQueryPart.toString(AbstractQueryPart.java:161) ~[jooq.jar:na]
    at org.jooq.impl.ConditionAsField.<init>(ConditionAsField.java:62) ~[jooq.jar:na]
    at org.jooq.impl.DSL.field(DSL.java:6923) ~[jooq.jar:na]
...........
.............

Thanks,
Gokul

[Gokul Krishna Paravasthu]

Hi,

I tried it with the latest jooq 3.7.1 and the issue is still reproducible.
I have changed the code to use an sql string. However is there a better way to do this than using an sql string?

final SelectConditionStep<Record3<Integer, Integer[], Boolean>> unionSelect = dslContext.select(drReporteeUserId,
                                                                                                      PostgresDSL.arrayAppend(ctePathField,
                                                                                                                              drReporteeUserId),

                                                                                                      DSL.field("dr.reportee_user_id = any (\"path\")",
                                                                                                                Boolean.class))
                                                                                              .from(amTable, dr)
                                                                                              .where(drUserId.eq(amTableReporteeId)
                                                                                                             .andNot(cycleField));

Thanks,
Gokul

...

[Lukas Eder]

Thank you very much for your detailed bug report.

As far as I can tell, there seems to be a bug caused by ConditionAsField's constructor which calls toString on the argument Condition. I'll have to investigate the details of this, but we should probably prevent calling toString() here.

As a workaround, you can either resort to using all plain SQL as you suggested, or at least partially use plain SQL as follows:

DSL.field("{0}", Boolean.class, drReporteeUserId.equal(DSL.any(ctePathField)))

... instead of 

DSL.field(drReporteeUserId.equal(DSL.any(ctePathField)))

Hope this helps,
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.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Tracking this issue as:

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

[Gokul Krishna Paravasthu]

Thanks Lukas for confirming the bug. I have switched to the partial sql construct which is more in line with jooq's spirit than the plain sql.

Cheers,
Gokul