Thread: Problem with LATERAL

Good Morning all,

I am having a problem understanding a simple LATERAL join - I'm working on grasping them.

All tables and data are at the bottom of this question and on the fiddles, SQL Server (working) and Postgres (not
working).

SQL Server fiddle - https://dbfiddle.uk/hjBBd87B

Postgres fiddle - https://dbfiddle.uk/PihnqTwG

I have the following  - create table scripts and sample data are at end of this question and on the fiddles.

The query in question which works on SQL Server is the following:

SELECT  o.order_id,
  o.total_price - COALESCE(p.paid, 0) AS remaining
FROM _order o
CROSS APPLY (
    SELECT SUM(p.amount) AS paid
    FROM payment p
    WHERE p.order_id = o.order_id
) AS p
WHERE o.total_price > ISNULL(p.paid, 0);

relatively simple one would have thought! I tried to convert this into a Postgres query as follows:

SELECT  o.order_id,
  o.total_price - COALESCE(sub.paid, 0)
FROM _order o
LEFT JOIN LATERAL (
    SELECT SUM(p.amount) AS paid
    FROM payment p
    WHERE p.order_id = o.order_id
) AS sub
WHERE o.total_price > ISNULL(sub.paid, 0);  -- << line 10 - Error occurs!

but I receive the error:

    ERROR:  syntax error at or near "WHERE"
    LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0);


I would be grateful if I could get a working query and also for an explanation as to what it is I'm doing incorrectly?

The payment table has no primary key. Any input on the suitability of creating a surrogate one would also be
appreciated.


Please let me know if there's any important information missing.

Rgs and TIA,


E.


CREATE TABLE _order
(
  order_id    INT NOT NULL PRIMARY KEY,
  total_price INT NOT NULL
);

INSERT INTO _order VALUES
(1, 1000), (2, 2000), (3, 3000), (4, 4000);


CREATE TABLE payment
(
  order_id INT NOT NULL,
  amount   INT NOT NULL,
  CONSTRAINT payment_order_id_fk FOREIGN KEY (order_id) REFERENCES _order (order_id)
);

CREATE INDEX pt_order_id_ix ON payment (order_id);   -- normal indexing of foreign key field

INSERT INTO payment VALUES
(1, 500), (2, 2000), (3, 1000), (3, 500), (3, 750);  -- note - no payment for order_id = 4

Hi,

On Thu, Oct 13, 2022 at 07:05:48AM +0000, Eagna wrote:
>
> relatively simple one would have thought! I tried to convert this into a Postgres query as follows:
>
> SELECT  o.order_id,
>   o.total_price - COALESCE(sub.paid, 0)
> FROM _order o
> LEFT JOIN LATERAL (
>     SELECT SUM(p.amount) AS paid
>     FROM payment p
>     WHERE p.order_id = o.order_id
> ) AS sub
> WHERE o.total_price > ISNULL(sub.paid, 0);  -- << line 10 - Error occurs!
>
> but I receive the error:
>
>     ERROR:  syntax error at or near "WHERE"
>     LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0);

There error here is because a JOIN clause requires a join condition.  Adding an
"ON true" is probably what you want.  You would also need to change isnull()
with coalesce().

The final query should be:

SELECT  o.order_id,
  o.total_price - COALESCE(sub.paid, 0)
FROM _order o
LEFT JOIN LATERAL (
    SELECT SUM(p.amount) AS paid
    FROM payment p
    WHERE p.order_id = o.order_id
) AS sub ON true
WHERE o.total_price > coalesce(sub.paid, 0);

> > ERROR: syntax error at or near "WHERE"
> > LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0);


> There error here is because a JOIN clause requires a join condition. Adding an
> "ON true" is probably what you want. You would also need to change isnull()
> with coalesce().

> The final query should be:

...
...
...
> ) AS sub ON true
...
...

OK - I see that it works now - which is great!

However, it's unclear to me what, exactly, it is that is "TRUE"?

What am I joining to what?

The syntax is unclear to me - if I'm joining, I should be joining on tab_A.field_x = tab_B.field_y - no?

Why does SQL Server's OUTER APPLY not require this?

Thanks for any input - Merci, À+

Rgs,

E.

On Thu, Oct 13, 2022 at 08:04:03AM +0000, Eagna wrote:
>
> > > ERROR: syntax error at or near "WHERE"
> > > LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0);
>
>
> > There error here is because a JOIN clause requires a join condition. Adding an
> > "ON true" is probably what you want. You would also need to change isnull()
> > with coalesce().
>
> > The final query should be:
>
> ...
> ...
> ...
> > ) AS sub ON true
> ...
> ...
>
> OK - I see that it works now - which is great!
>
> However, it's unclear to me what, exactly, it is that is "TRUE"?
>
> What am I joining to what?
>
> The syntax is unclear to me - if I'm joining, I should be joining on tab_A.field_x = tab_B.field_y - no?

Well, yes but the join condition in that case is already in the WHERE clause in
the sub select, so trying to put an actual join clause would be unnecessary and
add extra cost.

But I'm not sure why you want a LATERAL clause in the first place, wouldn't
this query have the same meaning?

SELECT  o.order_id,
  o.total_price - coalesce(sum(p.amount), 0)
FROM _order o
LEFT JOIN payment p ON p.order_id = o.order_id
GROUP BY o.order_id, o.total_price
HAVING o.total_price > coalesce(sum(p.amount), 0);

It should perform better if you have a lot of orders, as it can be executed
with something better than a nested loop.

> Why does SQL Server's OUTER APPLY not require this?

I don't know much about sql server, I'm assuming CROSS APPLY is an alias for
LEFT JOIN LATERAL () ON TRUE.