agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: David Johnston <polobo@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: Postgres behavior - Conditional statements
Date: Mon, 24 Feb 2014 08:47:59 -0800 (PST)
Message-ID: <1393260479026-5793317.post@n5.nabble.com> (raw)
In-Reply-To: <1905.1393257750@sss.pgh.pa.us>
References: <CAA5_7crQLLN=Wr7gp4ykhmTZxrbtSfXjucQeoi2uyec79N1Z7Q@mail.gmail.com>
	<1905.1393257750@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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=5ec6b7f1b87f0fa006b8e08a11...
> 
> 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.h...
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

view thread (5+ messages)  latest in thread

Message-ID: <1393260479026-5793317.post@n5.nabble.com>
Permalink:  ../1393260479026-5793317.post@n5.nabble.com/
Also on:    postgresql.org/message-id/1393260479026-5793317.post@n5.nabble.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: polobo@yahoo.com
  Subject: Re: Postgres behavior - Conditional statements
  In-Reply-To: <1393260479026-5793317.post@n5.nabble.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox