Hi, i found LATERAL in reserved words list https://docs.dremio.com/sql-reference/reserved-keywords.html but was not able to execute query.
LATERAL is one of the new JOIN types supported in PostgreSQL that can be used in Dremio too. For more information and use cases of LATERAL see Postgresql-LATERAL
postgres=# create table foo(a int); CREATE TABLE postgres=# create table bar(b int); CREATE TABLE postgres=# insert into foo values(generate_series(1,10)); INSERT 0 10 postgres=# insert into bar values(generate_series(1,10)); INSERT 0 10 postgres=# SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.b = foo.a) ss; a | b ----+---- 1 | 1 2 | 2 3 | 3 4 | 4 5 | 5 6 | 6 7 | 7 8 | 8 9 | 9 10 | 10 (10 rows)
In the Dremio UI you can use the same SQL,
SELECT * FROM PostgreSQL.public.foo, LATERAL (SELECT * FROM PostgreSQL.public.bar WHERE bar.b = foo.a)
Will get back to you on “CROSS” shortly,
is LATERAL available only in PostgreSQL? i.e. it is not executed by dremio engine by delegated to postgresql engine?
When i executed query with LATERAL on xlsx files i got: “Internal error: todo: Cycle detected during type-checking.”
Sorry, it was my mistake in query. It looks like it works.