Réécrivez le titre comme suit : Comment convertir AVEC `cte` AS (VALUES ROW(1), ROW(2), ROW(n)) dans la syntaxe MySQL en jOOQ DSL

Question

J'ai une requête SQL pour 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`;

Après la conversion vers 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);

Le résultat est :

+------------+----------------+
| 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)

Mais l'explication montre une UNION redondante :

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

J'ai réécrit un SQL en utilisant une autre syntaxe MySQL - 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`;

Même résultat :

+------------+----------------+
| 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)

Mais le planificateur de requêtes n'utilise pas 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 : Comment convertir le script final en jOOQ DSL ? Je ne trouve aucun moyen de le faire

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

Answer 1

Depuis la version 3.15 de jOOQ, il n'y a aucun moyen de convertir le SQL standard VALUES构造器表示为org.jooq.Select。它只能表示为org.jooq.Table, voir : https://github.com/jOOQ/jOOQ/issues/5871.

Vous ne pouvez donc pas encore exprimer la syntaxe entièrement conforme en utilisant jOOQ DSL, mais vous pouvez le faire sur FROM子句中使用VALUES :

selectFrom(values(...))

Cela devrait produire un plan presque identique à ce à quoi vous vous attendiez. Une autre solution consiste à utiliser des Modèles Pure SQL.