Rewrite the title as: How to convert WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) in MySQL syntax to jOOQ DSL

Question

I have a SQL query for MySQL:

WITH `cte` AS (
    ( SELECT 1431655747 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 1431655733 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827794 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827865 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 1073741809 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 1073741759 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827800 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 1431655693 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827789 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827838 AS `n` FROM DUAL ) UNION ALL
    ( SELECT 715827823 AS `n` FROM DUAL ) UNION ALL
( SELECT 858993391 AS `n` FROM DUAL )) SELECT
`cte`.`n`,
`maxmind_country`.`country`.`name_en` 
FROM
    `cte`
    JOIN `maxmind_country`.`ipv4` ON `cte`.`n` BETWEEN `maxmind_country`.`ipv4`.`start_int` 
    AND `maxmind_country`.`ipv4`.`last_int`
    JOIN `maxmind_country`.`country` ON `maxmind_country`.`country`.`geoname_id` = `maxmind_country`.`ipv4`.`v_geoname_id`;

After conversion to jOOQ DSL:

String ipAlias = "n";
SelectSelectStep<Record1<UInteger>> unionIps = ips
    .stream()
    .distinct()
    .map(value -> DSL.select(DSL.val(value).as(ipAlias)))
    .reduce((r1, r2) -> (SelectSelectStep<Record1<UInteger>>) r1.unionAll(r2))
    .orElse(null);

if(unionIps == null)
    return null;

CommonTableExpression<Record1<UInteger>> cte = DSL.name("cte").as(unionIps);
Field<UInteger> ipField = cte.field(ipAlias, UInteger.class);
return dslContext
    .with(cte)
    .select(ipField, COUNTRY.NAME_EN)
    .from(cte)
    .join(IPV4).on(ipField.between(IPV4.START_INT, IPV4.LAST_INT))
    .join(COUNTRY).on(COUNTRY.GEONAME_ID.eq(IPV4.V_GEONAME_ID))
    .fetchMap(ipField, Country.class);

The result is:

+------------+----------------+
| n          | name_en        |
+------------+----------------+
| 1431655747 | Spain          |
| 1431655733 | Spain          |
|  715827794 | China          |
|  715827865 | China          |
| 1073741809 | United States  |
| 1073741759 | United States  |
|  715827800 | China          |
| 1431655693 | Spain          |
|  715827789 | China          |
|  715827838 | China          |
|  715827823 | China          |
|  858993391 | United Kingdom |
+------------+----------------+
12 rows in set (0.16 sec)

But the explanation shows that there are redundant UNIONs:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1   PRIMARY <derived2>      ALL                 12  100,00  
1   PRIMARY ipv4        ALL ipv4_start_int_UNIQUE,ipv4_last_int_UNIQUE,ipv4_v_geoname_id_fk_idx             325934  11,11   Range checked for each record (index map: 0x7)
1   PRIMARY country     eq_ref  PRIMARY PRIMARY 4   maxmind_country.ipv4.v_geoname_id   1   100,00  
2   DERIVED                                     No tables used
3   UNION                                       No tables used
4   UNION                                       No tables used
5   UNION                                       No tables used
6   UNION                                       No tables used
7   UNION                                       No tables used
8   UNION                                       No tables used
9   UNION                                       No tables used
10  UNION                                       No tables used
11  UNION                                       No tables used
12  UNION                                       No tables used
13  UNION                                       No tables used

I rewrote a SQL using another MySQL syntax - VALUES ROW(1), ROW(2), ROW(n):

WITH `cte` AS (
VALUES ROW(1431655747),
    ROW(1431655733),
    ROW(715827794),
    ROW(715827865),
    ROW(1073741809),
    ROW(1073741759),
    ROW(715827800),
    ROW(1431655693),
    ROW(715827789),
    ROW(715827838),
    ROW(715827823),
    ROW(858993391)) SELECT
`cte`.`column_0`,
`maxmind_country`.`country`.`name_en` 
FROM
    `cte`
    JOIN `maxmind_country`.`ipv4` ON `cte`.`column_0` BETWEEN `maxmind_country`.`ipv4`.`start_int` 
    AND `maxmind_country`.`ipv4`.`last_int`
    JOIN `maxmind_country`.`country` ON `maxmind_country`.`country`.`geoname_id` = `maxmind_country`.`ipv4`.`v_geoname_id`;

The result is the same:

+------------+----------------+
| column_0   | name_en        |
+------------+----------------+
| 1431655747 | Spain          |
| 1431655733 | Spain          |
|  715827794 | China          |
|  715827865 | China          |
| 1073741809 | United States  |
| 1073741759 | United States  |
|  715827800 | China          |
| 1431655693 | Spain          |
|  715827789 | China          |
|  715827838 | China          |
|  715827823 | China          |
|  858993391 | United Kingdom |
+------------+----------------+
12 rows in set (0.16 sec)

But the query planner does not use UNION:

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1   PRIMARY <derived2>      ALL                 12  100,00  
1   PRIMARY ipv4        ALL ipv4_start_int_UNIQUE,ipv4_last_int_UNIQUE,ipv4_v_geoname_id_fk_idx             325934  11,11   Range checked for each record (index map: 0x7)
1   PRIMARY country     eq_ref  PRIMARY PRIMARY 4   maxmind_country.ipv4.v_geoname_id   1   100,00  
2   DERIVED                                     No tables used

Question: How to convert the final script to jOOQ DSL? I can't find any way to achieve

WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) ... ?

Answer 1

As of jOOQ version 3.15, there is no way to represent the standard SQL VALUES constructor as org.jooq.Select. It can only be represented as org.jooq.Table, see: https://github.com/jOOQ/jOOQ/issues/5871.

Therefore, you cannot yet use the jOOQ DSL to express the fully conforming syntax, but you can use VALUES in the FROM clause:

selectFrom(values(...))

This should produce a plan almost identical to what you would expect. Another solution is to use Pure SQL Template.