Обсуждение: BUG #15291: Lateral join has different/suprising semantics (withimpure function)

The following bug has been logged on the website:

Bug reference:      15291
Logged by:          Marek K.
Email address:      kadek.marek@gmail.com
PostgreSQL version: 11beta2
Operating system:   Ubuntu, OSX
Description:

Following SQL:

CREATE EXTENSION "uuid-ossp";

CREATE TABLE fruits (name VARCHAR);
INSERT INTO fruits VALUES ('banana'), ('apple');

-- 1:
-- banana | uuid1
-- apple  | uuid1
SELECT f.name, t.uuid FROM fruits f 
CROSS JOIN LATERAL (
  SELECT uuid_generate_v4() AS uuid FROM (VALUES(1)) v
) t;

-- 2:
-- banana | uuid1
-- apple  | uuid2
SELECT f.name, t.uuid FROM fruits f 
CROSS JOIN LATERAL (
  SELECT f.name, uuid_generate_v4() AS uuid FROM (VALUES(1)) v
) t

I can understand why it's happening what is happening, yet I still find it
to be problematic. In my opinion those two selects should yield same
results. Hence for impure functions lateral semantics should be preserved
and not optimized away.
I am not 100% sure it can be classified as a bug, but looking into what your
opinion is.

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> [ LATERAL is ignored when sub-select has no actual lateral references ]

> I can understand why it's happening what is happening, yet I still find it
> to be problematic. In my opinion those two selects should yield same
> results. Hence for impure functions lateral semantics should be preserved
> and not optimized away.

I believe this behavior is effectively required by the SQL spec.
In any case, removing it would pessimize many queries in which writing
an unnecessary LATERAL has no bad side-effects today, so I'm disinclined
to change a behavior that's stood for half a dozen releases now.

            regards, tom lane