Search code examples
postgresqllateral-join

lateral join with a right join

so I have two tables, A and B.

first I do a lateral join of A with one column (items)

select A.id, item->>'id' as item_id, b.id 
from a, jsonb_array_elements(items) rx(item)

enter image description here

then I do a right join with table b (has only 2 rows, with id 1 and 2)

select A.id, item->>'id' as item_id, b.id 
from a, jsonb_array_elements(items) rx(item) 
right join b on b.id::varchar = item->>'id'

enter image description here

my main question is, why didn't the right join take out the rows where item_id = 3 and 4? why still null there. On the other hand, if I change the cross lateral join to left lateral join, it solves my problem. But I want to understand the evaluation order in this particular query.

Solution

  • On the other hand, if I change the cross lateral join to left lateral join, it solves my problem.

    You didn't have a cross join to start with, you had a comma join. Those are not quite identical, as the comma join has lower precedence. If you change it from a comma join to a true cross join, it also solves the problem.

    I think this is probably a bug. The comma join should throw an error because the "items" column should be unavailable at the point where it is needed.