BUG #15291: Lateral join has different/suprising semantics (with impure function)

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: kadek(dot)marek(at)gmail(dot)com
Subject: BUG #15291: Lateral join has different/suprising semantics (with impure function)
Date: 2018-07-24 07:10:26
Message-ID: 153241622629.1395.7281368493830784815@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15291
Logged by: Marek K.
Email address: kadek(dot)marek(at)gmail(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2018-07-24 09:19:44 BUG #15292: Multiple Messages "2018-07-24 10:46:12.566 CEST [32029] LOG: invalid length of startup" in logfile
Previous Message Michael Paquier 2018-07-24 05:52:35 Re: BUG #15182: Canceling authentication due to timeout aka Denial of Service Attack