Joining a Table on VALUES() using JOOQ DSL API

Question

NOTE:

I had a similar question open, how to generate the following SQL in JOOQ but in the last two days i worked out 99% of it and this brought me to another problem - which gets described here - as my current solution did not answer my initial question i deleted the other question and created this new one - rather than replacing the original question.

UPDATE: Changed this Question to a Solution, as Lukas Eder pointed out, that the resulting SQL is valid. Thanks.

I have the following SQL query - as an example and transformed this into JOOQ API to generate the SQL for different tables:

SELECT c.*
FROM contacts c
   JOIN ( 
      VALUES
        (0, 13259), 
        (1, 12472),  
        (2, 12422)
  ) AS il(listindex, id) ON c.id = il.id
ORDER BY il.listindex;

I created the following code based on the JOOQ manual of VALUES() and JOIN

NOTE:

this is a standalone example, which creates Tables, Fields and Names from Strings. none of this code is actually executed in database, it just generates the SQL

import static org.jooq.impl.DSL.row;
import static org.jooq.impl.DSL.values;
import static org.jooq.impl.DSL.table;
import static org.jooq.impl.DSL.field;
import static org.jooq.impl.DSL.name;

import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Name;
import org.jooq.Record;
import org.jooq.Row2;
import org.jooq.SQLDialect;
import org.jooq.SelectJoinStep;
import org.jooq.Table;
import org.jooq.impl.DSL;

public class StackoverflowJOOQQuestion {
    public static void main(String[] args) {
        DSLContext ctx = DSL.using(SQLDialect.H2);

        // create test data (as in the sql statement on top)
        Long[] lon = new Long[] { 13259l, 12472l, 12422l };
        Row2<Integer, Long>[] rows = new Row2[lon.length];

        for (int i = 0; i < rows.length; i++) {
            rows[i] = row(i, lon[i]);
        }

        // create Names, Fields and Tables
        // contacts table
        Name contactIdName = name("tContacts", "conIdContact");
        Field contactIdField = field(contactIdName, Long.class);
        Name contactNameName = name("tContacts", "conContactName");
        Field contactNameField = DSL.field(contactNameName, String.class);
        // values table
        Name nameTableValues = name("il");
        Table valuesTable = table(nameTableValues);
        Name nameTableValuesIndex = name("il", "listindex");
        Field valuesIndexField = field(nameTableValuesIndex, Integer.class);
        Name nameTableValuesId = name("il", "id");
        Field valuesIdField = field(nameTableValuesId, Long.class);

        // build the SQL query
        SelectJoinStep<Record> step = ctx.select(contactNameField).from("tContacts")
                .join(//
                        values(rows)//
                                // NOTE: also works with Strings, Names or Table and
                                // Fields as shown here
//                              .as("il", "listindex", "id")//
//                              .as(nameTableValues, nameTableValuesIndex, nameTableValuesId)//
                                .as(valuesTable, valuesIndexField, valuesIdField)//
                ).on(contactIdField.eq(valuesIdField));

        // print the query
        System.out.println(step.getSQL());
    }
}

but this produces the following output:

select "tContacts"."conContactName" from tContacts join (
    (select null "listindex", null "id" where 1 = 0) union all 
    (select * from (
            values (cast(? as int), cast(? as bigint))
                    , (cast(? as int), cast(? as bigint))
                    , (cast(? as int), cast(? as bigint))
            ) "il")
    ) "il" on "tContacts"."conIdContact" = "il"."id"

Answer 1

This isn't a bug and there's no problem with your generated SQL. The derived table that you're joining does have the required column names. Observe:

(select null "listindex", null "id" where 1 = 0) union all ...

An emulation is applied when using the derived column lists feature, for some databases. See:

• https://github.com/jOOQ/jOOQ/issues/1801
• How to apply a derived column list (table and column aliases) to a table valued function call in SQL Server
• https://blog.jooq.org/2013/01/07/simulating-the-sql-standard

Only recently, the H2 database added support for the derived column list feature, so jOOQ's generated SQL might be outdated:

• http://h2database.com/html/grammar.html#table_expression
• https://github.com/h2database/h2database/pull/909
• https://github.com/h2database/h2database/commit/db1612f4241317e70f3da3930457d936eb24a67b

But for backwards compatibility reasons, it would be unwise to move to the newer syntax already.

Given that H2 doesn't have a really well-defined major release versioning scheme, jOOQ also doesn't distinguish between H2 dialects.