Обсуждение: Postgres behavior - Conditional statements

Kumar Babu P G <kumar.pulakunta@gmail.com> writes:
> Found strange behavior of postgres between 9.1 and 9.2. Can some one
> pointout the reason for the difference in column name of the output between
> the versions?
> postgres=> select case when exists (select 1 from test where a=1) then 0
> else (select b from test where a=2) end;

http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=5ec6b7f1b87f0fa006b8e08a11cd4e99bcb67358

The behavior of CASE didn't change, but the behavior of the sub-select
did.  More simply, 9.1 gives this

regression=# select (select b from test where a=2);    ?column? 
----------        
(1 row)

while 9.2 and up give

regression=# select (select b from test where a=2);b 
--- 
(1 row)

        regards, tom lane

Tom Lane-2 wrote
> Kumar Babu P G <

> kumar.pulakunta@

> > writes:
>> Found strange behavior of postgres between 9.1 and 9.2. Can some one
>> pointout the reason for the difference in column name of the output
>> between
>> the versions?
>> postgres=> select case when exists (select 1 from test where a=1) then 0
>> else (select b from test where a=2) end;
> 
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=5ec6b7f1b87f0fa006b8e08a11cd4e99bcb67358
> 
> The behavior of CASE didn't change, but the behavior of the sub-select
> did.  More simply, 9.1 gives this
> 
> regression=# select (select b from test where a=2);    
>  ?column? 
> ----------
>          
> (1 row)
> 
> while 9.2 and up give
> 
> regression=# select (select b from test where a=2);
>  b 
> ---
>   
> (1 row)

While the behavior of CASE was not intentionally changed the end result is
the same:

select case when true then (select 1 as one) else (select 2 as two) end;

returns a column header of "two" having a value of 1.

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.  Now, I'll admit that if you
actually care about the name you should provide an alias on all expressions
but reasonable default behavior is nice to have.

Also note in:

select case when true then (select 1 as one) else (select 2 as two) end,
(select 'two' as two);

both output columns have the same name.  Ideally, and I thought by intent,
auto-generated names would try to be made unique.

Given that CASE was not touched I have to believe this is best considered an
unintended side-effect and not something explicitly desired.  At the least
that is my opinion and - though whether this can/should now be fixed in
back-branches is another matter - it should at least be corrected in 9.4 to
output "case" in any and all circumstances.

The very nature of a branching construct means that neither/none of the
branch data can reasonably be said to be an accurate representation of the
final output and so they should not be considered when generating a name for
the output column.  A generic name, like "case", is needed and then it is up
to the user to disambiguate when they feel it is necessary.

David J.




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

David Johnston <polobo@yahoo.com> 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.
        regards, tom lane

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.