pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18327: Column naming inconsistency for boolean literals in ELSE clauses of CASE expressions.
3+ messages / 3 participants
[nested] [flat]
* BUG #18327: Column naming inconsistency for boolean literals in ELSE clauses of CASE expressions.
@ 2024-02-03 20:29 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2024-02-03 20:29 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: work.michael.2956@gmail.com
The following bug has been logged on the website:
Bug reference: 18327
Logged by: Michael Bondarenko
Email address: work.michael.2956@gmail.com
PostgreSQL version: 14.10
Operating system: MacOS Sonoma 14.1.1 (23B81)
Description:
I'm running my queries on the TPC-H schema, which can be found at:
https://github.com/dimitri/tpch-citus/blob/master/schema/tpch-schema.sql
I found the following inconsistency:
```
tpch=# select case when true then true else true end;
case
------
t
(1 row)
tpch=# select case when true then true else (select true) end;
bool
------
t
(1 row)
```
As you can see, the auto-generated aliases do not correspond. The expected
behaviour was for the first query to also name the column 'bool'.
I base my expectations on:
1) This query naming the result 'bool':
```
tpch=# select true;
bool
------
t
(1 row)
```
2) These queries propagating the column name from the else clause of the
case expression:
2.1) Propagation of the aggregate function name
```
tpch=# select case when true then true else bool_or(true) end;
bool_or
---------
t
(1 row)
```
2.2) Propagation of the [compound] column identifier:
```
tpch=# select case when true then 1 else t1.n_nationkey end from nation as
t1 limit 1;
n_nationkey
-------------
1
(1 row)
```
2.3) Propagation of the simple column identifier:
```
tpch=# select case when true then 1 else n_nationkey end from nation limit
1;
n_nationkey
-------------
1
(1 row)
```
2.4) And even nested case expressions:
```
tpch=# select case when true then 1 else (
case when true then 1 else t1.n_nationkey end
) end from nation as t1 limit 1;
n_nationkey
-------------
1
(1 row)
```
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18327: Column naming inconsistency for boolean literals in ELSE clauses of CASE expressions.
@ 2024-02-04 19:01 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2024-02-04 19:01 UTC (permalink / raw)
To: work.michael.2956@gmail.com, PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Sun, Feb 4, 2024, 11:19 PG Bug reporting form <noreply@postgresql.org>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18327
> Logged by: Michael Bondarenko
> Email address: work.michael.2956@gmail.com
> PostgreSQL version: 14.10
> Operating system: MacOS Sonoma 14.1.1 (23B81)
> Description:
>
> I'm running my queries on the TPC-H schema, which can be found at:
> https://github.com/dimitri/tpch-citus/blob/master/schema/tpch-schema.sql
>
> I found the following inconsistency:
This one doesn't have a name to use. Using case is better than picking one
of the branches to make up a name from.
> ```
> tpch=# select case when true then true else true end;
> case
> ------
> t
> (1 row)
>
All of these do. And in any case we never publish how we choose our
aliases in this circumstance so it cannot be a bug.
The subquery forces a name to be chosen for the output relation. Then
since only one branch has a name to provide it is used.
> tpch=# select case when true then true else (select true) end;
> bool
> ------
> t
> (1 row)
> ```
>
> As you can see, the auto-generated aliases do not correspond. The expected
> behaviour was for the first query to also name the column 'bool'.
>
> I base my expectations on:
>
> 1) This query naming the result 'bool':
> ```
> tpch=# select true;
> bool
> ------
> t
> (1 row)
> ```
>
> 2) These queries propagating the column name from the else clause of the
> case expression:
2.1) Propagation of the aggregate function name
> ```
> tpch=# select case when true then true else bool_or(true) end;
> bool_or
> ---------
> t
> (1 row)
> ```
>
This is indeed all consistent in that literals don't have names while other
expression tend to.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18327: Column naming inconsistency for boolean literals in ELSE clauses of CASE expressions.
@ 2024-02-04 19:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2024-02-04 19:57 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: work.michael.2956@gmail.com, PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> This is indeed all consistent in that literals don't have names while other
> expression tend to.
Actually, pre-v15 the literal constants "true" and "false" do have
names for this purpose. For historical reasons they were parsed
into the equivalent of 't'::bool and 'f'::bool, and then the name
selection rule for a typecast took effect.
Starting in v15 they're treated like other sorts of literals,
meaning they have no assigned name:
regression=# select true;
?column?
----------
t
(1 row)
Many of these other examples change as a consequence.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-02-04 19:57 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-03 20:29 BUG #18327: Column naming inconsistency for boolean literals in ELSE clauses of CASE expressions. PG Bug reporting form <noreply@postgresql.org>
2024-02-04 19:01 ` David G. Johnston <david.g.johnston@gmail.com>
2024-02-04 19:57 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox