Rewrite the title as: How to convert WITH `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) in MySQL syntax to jOOQ DSL
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)) ... ?
