Re: Postgres behavior - Conditional statements - Mailing list pgsql-sql

Tom Lane-2 wrote
> David Johnston <

> polobo@

> > writes:
>> Tom Lane-2 wrote
>>> The behavior of CASE didn't change, but the behavior of the sub-select
>>> did.
> 
>> CASE should be treated just like a function and the name of the function
>> should be returned.  Arbitrarily picking the "else" branch to obtain the
>> name of the result column seems somehow wrong.
> 
> [ shrug... ]  It's acted like that since roughly the late bronze age;
> or to be more specific, since commit 2b189aa9537f7b4a of 1998-12-13.
> Given the lack of previous complaints, it's going to take a lot more
> than one man's opinion to change it.

If we are going to leave this broken for a subset of users ISTM that more of
them are going to use the constructs fixed in the first commit you pointed
to rather than the "CASE WHEN ... THEN col1 ELSE col2 END" form - and if
they do use the later form they are already likely creating an alias.  I can
see the motivation for this behavior in the following template:

SELECT CASE WHEN ... THEN col + 1 ELSE col END FROM (SELECT 1 as col) src;

But that seems narrow enough a use-case, especially if the impact is only
felt if no alias for the CASE is provided, that leaving the current "case"
label in place for the OP and causing a break for others is the correct
solution.

Who would you rather impact?  Long-time users relying on the fact the any
sub-query (or similarly patched expression) used to - since the late bronze
age - return "?column?" and thus the case expression label ended up being
"case"; or those who have either recently migrated to and/or started
projects using 9.2 and thus in all likelihood are much less reliant on the
behavior and also likely more able to easily modify any affected queries to
use explicit aliases.

The very nature of the bug means that the overall reported impact is likely
to be small and most of them will probably just affix aliases and be done
with it.  It is also not an issue for fresh adopters - though "being
correct" is of importance to them - so the ultimate concern we should have
is estimating how much silent frustration is being caused by this unintended
side-effect and likewise how much we would cause by correcting it.

I'm still of the mind of only correcting this as-of 9.4; the number of
additional affect between now and when 9.4 is released and generally adopted
should be small and anyone jumping into 9.2 or 9.3 will have already
silently overcome the problem by simply assigning an alias.

It would be nice to ready the original impetus to apply the patch back in
1998 but I'm not sure where to search, if material from that long ago is
online, or whether there was even any discussion on the topic.

Sorry for the diatribe but I tend to get prickly at "it has been that way
forever" - especially since there are recent changes that are only now
bringing to light flaws in the long-existing code - in this specific case
the idea that getting a label from the components of a CASE is a generally
acceptable solution.  in practice most branches are expressions so the
default "case" label was being chosen in almost all cases anyway so few
people would have noticed any difference between smart and constant.  I
myself was not aware that a case expression could have an auto-generated
non-"case" label until this thread - mostly because for live code I always
uses aliases and for interactive code I seldom look at the label or because
I too mostly use expressions I have always seen the output name "case".

David J.






--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Postgres-behavior-Conditional-statements-tp5793264p5793347.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.