Date: Mon, 24 Feb 2014 08:47:59 -0800 (PST)
From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Message-ID: <1393260479026-5793317.post@n5.nabble.com>
In-Reply-To: <1905.1393257750@sss.pgh.pa.us>
References: 
 <CAA5_7crQLLN=Wr7gp4ykhmTZxrbtSfXjucQeoi2uyec79N1Z7Q@mail.gmail.com>
 <1905.1393257750@sss.pgh.pa.us>
Subject: Re: Postgres behavior - Conditional statements
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

Tom Lane-2 wrote
> Kumar Babu P G &lt;

> kumar.pulakunta@

> &gt; 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.


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