Does Dremio support sql LATERAL/CROSS APPLY statements?

1

Hi, i found LATERAL in reserved words list https://docs.dremio.com/sql-reference/reserved-keywords.html but was not able to execute query.

2

Hi @jasiek

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,

Thanks,
@balaji.ramaswamy

3

Hi,
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.”

4

Hi @jasiek,

Can you please share the query you tried on Dremio,

Thanks,
@balaji.ramaswamy

5

Sorry, it was my mistake in query. It looks like it works.