Presto: Add support for LEFT/RIGHT/INNER lateral joins

[martint]

It should be possible to write queries like:

SELECT *
FROM t LEFT JOIN LATERAL (...) u ON ...

SELECT *
FROM t LEFT JOIN UNNEST(t.a) u ON ...

[kokosing]

What do you mean by RIGHT LATERAL? Can you explain its semantic?

24.06.2017 21:28 "Martin Traverso" [e-mail] napisał(a):

It should be possible to write queries like:

SELECT *FROM t LEFT JOIN LATERAL (...) u ON ...

SELECT *FROM t LEFT JOIN UNNEST(t.a) u ON ...

—
You are receiving this because you are subscribed to this thread.
Reply to this email directly, view it on GitHub
https://github.com/prestodb/presto/issues/8360, or mute the thread
https://github.com/notifications/unsubscribe-auth/AHN_-29c6O4UqsLDKdZzzxOYsebrjuFlks5sHWNTgaJpZM4OEarI
.

[martint]

Think of lateral as just producing a table on the fly for the row to be joined. In the case of RIGHT JOIN, the condition is evaluated, and if it doesn't match, each row on the right side are output with nulls for the columns on the left side. Similar rules apply for other types of joins. Just standard semantics, nothing magical.

[martint]

Also, to be precise, LEFT LATERAL or RIGHT LATERAL are not special constructs. They are just other types of "table primaries" ( "collection derived tables" and "lateral derived tables") that can appear in a join clause, just like a table reference or subquery can.

[sopel39]

Think of lateral as just producing a table on the fly for the row to be joined

Isn't it the case that LATERAL join has natural left side "bias" because of correlations and scope semantics.
Given your sentence, I think it's intuitive to think that when there is no row to be joined then table for that particular row cannot be produced. This also implies that there is no single right table, but rather different instances of the table for each row. Therefore one cannot simply emit unjoined right side rows since there is no such thing as right side table instance. Because of that RIGHT/FULL LATERAL doesn't make much sense in correlated case. Uncorrelated LATERAL can be easily rewritten to plain JOIN, but then should we allow for uncorrelated RIGHT LATERALs in syntax?

What is RIGHT LATERAL in this case:

SELECT * FROM nation n RIGHT LATERAL (SELECT s.nationkey + n.nationkey FROM supplier s)

[sopel39]

SELECT * FROM nation n RIGHT LATERAL (SELECT s.nationkey + n.nationkey FROM supplier s)

@martint by your description this will produce no rows, right? This is a bit confusing (this behaves exactly like normal LATERAL). It it worth having such syntax?

[kokosing]

@sopel39
According to the way I understand RIGHT LATERAL does not have much sense if you do not have a join condition in ON clause.

Think about:

SELECT n.name, r.name FROM nation n, RIGHT LATERAL (SELECT n.name AS name) r ON false

Output of this will be

n.name | r.name
-----------------
null   | ALGIERIA
null   | POLAND
...

For each row from left table the right table is calculated, then these tables (row and table) are joined when the join conditions is meet, when condition is not satisfied only RIGHT rows are returned (completed with NULL values for columns from left relation).

[martint]

Yeah, as I said above, "left/right/inner lateral" don't exist as a concept in SQL. What's being computed is a derived table (lateral) that gets joined using regular join syntax: left join lateral (...) on ...

From a logical standpoint, first, the the lateral relation is resolved using whatever correlations exist. Then, the join is resolved using regular semantics.