Support CROSS JOIN and CROSS JOIN LATERAL

[mertalev]

Kysely supports LEFT JOIN LATERAL and INNER JOIN LATERAL, but doesn't support lateral subqueries of the syntax SELECT * FROM a, LATERAL ( ) l, or the equivalent SELECT * FROM a CROSS JOIN LATERAL ( ) l. #140 discussed the reference of multiple tables in selectFrom as an alternative to cross joins, but this doesn't work for subqueries as far as I can tell.

This is the kind of query I'd like to use this feature for:

WITH RECURSIVE cte AS (
  (
    SELECT city, "assetId"
    FROM exif
    ORDER BY city
    LIMIT 1
  )

  UNION ALL

  SELECT l.city, l."assetId"
  FROM cte c
  CROSS JOIN LATERAL (
    SELECT city, "assetId"
    FROM exif
    WHERE city > c.city
    ORDER BY city
    LIMIT 1
  )  l
)
select "assets".*, jsonb_strip_nulls(to_jsonb(exif)) as "exifInfo"
from "assets"
inner join "exif" on "assets"."id" = "exif"."assetId"
inner join "cte" on "assets"."id" = "cte"."assetId";

[koskimas]

Doesn't leftJoinLateral work just as well in this case since you're only joining max 1 row (limit 1)?

[mertalev]

In the example I shared with the exif and assets tables both having 2 million rows (one-one), using LEFT JOIN LATERAL ... ON true took at least 60s - I canceled it at that point. CROSS JOIN LATERAL took 7ms.

[koskimas]

How about inner join on true? Isn't that 100% equivalent to cross join?

[koskimas]

But I guess we should add cross join and cross join lateral to Kysely.

[mertalev]

How about inner join on true? Isn't that 100% equivalent to cross join?

Nice suggestion! This one does have the same query plan and performance as cross join.

[bakasmarius]

How about inner join on true? Isn't that 100% equivalent to cross join?

Probably no because on MS SQL Server 2016 I get this error when I try to run inner join MyTable as mt on true: SQL Error [4145] [S0001]: An expression of non-boolean type specified in a context where a condition is expected

It works fine though if I replace on true with on 1 = 1.

Looks like .crossJoin('MyTable') would be a more elegant solution than .innerJoin('MyTable as mt', (join) => join.on(sql.val(1), '=', 1))).

[drew-marsh]

MS SQL's cross join does not have an on clause. This PR addresses on-less joins as a solution for mssql apply statements #1074