From: "Regina Obe" <lr@pcorp.us>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>,
	"'Mark Dilger'" <hornschnorter@gmail.com>
Cc: "'Andres Freund'" <andres@anarazel.de>,
	"'PostgreSQL-development'" <pgsql-hackers@postgresql.org>
References: <000001d2d5de$d8d66170$8a832450$@pcorp.us>
 <29994.1495809534@sss.pgh.pa.us> <000c01d2d654$518f2be0$f4ad83a0$@pcorp.us>
 <21809.1495994606@sss.pgh.pa.us>
 <20170603024503.yv6gxvkyx6mtc63m@alap3.anarazel.de>
 <7181.1496458380@sss.pgh.pa.us>
 <20170603031127.fqg7zjhfgbcbcank@alap3.anarazel.de>
 <4125B63A-886A-442D-9932-34F0D4F76D41@gmail.com>
 <20170604193521.ha6d5kpkdswmissm@alap3.anarazel.de>
 <63850C96-9570-4969-8074-7033DA4C334F@gmail.com>
 <20170604211942.aiavcbagsho5owcz@alap3.anarazel.de>
 <FB5BA655-95A0-4EC5-900D-2582D3E98A74@gmail.com>
 <13450.1496861842@sss.pgh.pa.us>
In-Reply-To: <13450.1496861842@sss.pgh.pa.us>
Subject: Re: PostgreSQL 10 changes in exclusion constraints - did something
 change? CASE WHEN behavior oddity
Date: Wed, 7 Jun 2017 17:07:10 -0400
Message-ID: <000201d2dfd2$07fad150$17f073f0$@pcorp.us>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: 
 AQGSyGid0WOb7K33IPoK2k+QsEQaEAKr4LhqAj7/u9sCYz3ehQI8CMhgAnAUar8CAekolwFXzi3+Apc1MG8B7cswggJEIFHfAhiT9OsBmaJDn6HLlrTg
Content-Language: en-us
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org


> After chewing on this for awhile, I'm starting to come to the conclusion
that we'd be best off to throw an error for SRF-inside-CASE (or COALESCE).
Mark is correct that the simplest case of

>	SELECT x, CASE WHEN y THEN generate_series(1,z) ELSE 5 END
>	FROM table_with_columns_x_and_y_and_z;

> behaves just intuitively enough that people might be using it.  The new
implementation method cannot reasonably duplicate the old semantics for
that, which means that if we let it stand as-is we will be 

> silently breaking queries, even if we fix up some of the weirder corner
cases like what happens when the CASE can be const-simplified.  So I think
we'd be better off to make this throw an error, and force any 
> affected users to rewrite in a way that will work in both v10 and older
releases.

> As to *how* to throw an error, I think it should be possible to teach
parse analysis to detect such cases, with something like the ParseExprKind
mechanism that could be checked to see if we're inside a 
> subexpression that restricts what's allowed.  There are some other checks
like no-nested-aggregates that perhaps could be folded in as well.  Checking
at parse analysis ought to be sufficient because 
> rule rewriting could not introduce such a case where it wasn't before, and
planner subquery flattening won't introduce one either because we don't
flatten subqueries with SRFs in their tlists.

> If people are on board with throwing an error, I'll go see about writing a
patch.

>			regards, tom lane

+1

I'm not a fan of either solution, but I think what Tom proposes of throwing
an error sounds like least invasive and confusing.

I'd much prefer an error thrown than silent behavior change. Given that we
ran into this in 3 places in PostGIS code, I'm not convinced the issue is
all that rare.

Make sure to point out the breaking change in the release notes though and
syntax to remedy it.

Thanks,
Regina 


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers