pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
14+ messages / 7 participants
[nested] [flat]
* BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2024-08-28 16:17 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 14+ messages in thread
From: PG Bug reporting form @ 2024-08-28 16:17 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: fco.j.ossandon@gmail.com
The following bug has been logged on the website:
Bug reference: 18594
Logged by: Francisco Javier Ossandon
Email address: fco.j.ossandon@gmail.com
PostgreSQL version: 15.5
Operating system: Linux
Description:
Dear developers:
I have been using Postgres for some years now, and I just found what looks
like a bug, or at least I did not see anything in the documentation that
could explain it.
* version():
PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712
(Red Hat 7.3.1-12), 64-bit
The main issue is that "CASE WHEN THEN ELSE" is giving an expected result in
what seems like simple logic. I have a series of WHENs with conditions over
the value of a column and an ELSE where I return the same column value if
the CASEs above don't apply, and the query always returns the value of the
ELSE condition like if the WHENs above would not exist. The following query
exemplifies this, where the first CASE column gives an unexpected result
when using the same column in the ELSE, while the other CASE columns provide
the expected result when removing the ELSE or using a different column in
the ELSE or a constant string:
```
SELECT oid,
typname,
typcategory,
CASE
WHEN typcategory = 'N' THEN 'Numeric types'
WHEN typcategory = 'S' THEN 'String types'
WHEN typcategory = 'E' THEN 'Enum types'
ELSE typcategory
END AS test_case_fails,
CASE
WHEN typcategory = 'N' THEN 'Numeric types'
WHEN typcategory = 'S' THEN 'String types'
WHEN typcategory = 'E' THEN 'Enum types'
END AS test_case_works_no_else,
CASE
WHEN typcategory = 'N' THEN 'Numeric types'
WHEN typcategory = 'S' THEN 'String types'
WHEN typcategory = 'E' THEN 'Enum types'
ELSE typname
END AS test_case_works_other_col,
CASE
WHEN typcategory = 'N' THEN 'Numeric types'
WHEN typcategory = 'S' THEN 'String types'
WHEN typcategory = 'E' THEN 'Enum types'
ELSE 'ELSE'
END AS test_case_works_constant
FROM pg_type
ORDER BY oid
LIMIT 10
;
```
This returns the following:
```
oid|typname
|typcategory|test_case_fails|test_case_works_no_else|test_case_works_other_col|test_case_works_constant|
---+----------+-----------+---------------+-----------------------+-------------------------+------------------------+
16|bool |B |B | |bool
|ELSE |
17|bytea |U |U | |bytea
|ELSE |
18|char |Z |Z | |char
|ELSE |
19|name |S |S |String types |String
types |String types |
20|int8 |N |N |Numeric types |Numeric
types |Numeric types |
21|int2 |N |N |Numeric types |Numeric
types |Numeric types |
22|int2vector|A |A |
|int2vector |ELSE |
23|int4 |N |N |Numeric types |Numeric
types |Numeric types |
24|regproc |N |N |Numeric types |Numeric
types |Numeric types |
25|text |S |S |String types |String
types |String types |
```
It looks weird, so I'm reporting this to you for review.
Best regards,
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2024-08-28 17:56 Andrew Dunstan <andrew@dunslane.net>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 14+ messages in thread
From: Andrew Dunstan @ 2024-08-28 17:56 UTC (permalink / raw)
To: fco.j.ossandon@gmail.com; pgsql-bugs@lists.postgresql.org
On 2024-08-28 We 12:17 PM, PG Bug reporting form wrote:
> The following bug has been logged on the website:
>
> Bug reference: 18594
> Logged by: Francisco Javier Ossandon
> Email address: fco.j.ossandon@gmail.com
> PostgreSQL version: 15.5
> Operating system: Linux
> Description:
>
> Dear developers:
> I have been using Postgres for some years now, and I just found what looks
> like a bug, or at least I did not see anything in the documentation that
> could explain it.
>
> * version():
> PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712
> (Red Hat 7.3.1-12), 64-bit
>
> The main issue is that "CASE WHEN THEN ELSE" is giving an expected result in
> what seems like simple logic. I have a series of WHENs with conditions over
> the value of a column and an ELSE where I return the same column value if
> the CASEs above don't apply, and the query always returns the value of the
> ELSE condition like if the WHENs above would not exist. The following query
> exemplifies this, where the first CASE column gives an unexpected result
> when using the same column in the ELSE, while the other CASE columns provide
> the expected result when removing the ELSE or using a different column in
> the ELSE or a constant string:
> ```
> SELECT oid,
> typname,
> typcategory,
> CASE
> WHEN typcategory = 'N' THEN 'Numeric types'
> WHEN typcategory = 'S' THEN 'String types'
> WHEN typcategory = 'E' THEN 'Enum types'
> ELSE typcategory
> END AS test_case_fails,
> CASE
> WHEN typcategory = 'N' THEN 'Numeric types'
> WHEN typcategory = 'S' THEN 'String types'
> WHEN typcategory = 'E' THEN 'Enum types'
> END AS test_case_works_no_else,
> CASE
> WHEN typcategory = 'N' THEN 'Numeric types'
> WHEN typcategory = 'S' THEN 'String types'
> WHEN typcategory = 'E' THEN 'Enum types'
> ELSE typname
> END AS test_case_works_other_col,
> CASE
> WHEN typcategory = 'N' THEN 'Numeric types'
> WHEN typcategory = 'S' THEN 'String types'
> WHEN typcategory = 'E' THEN 'Enum types'
> ELSE 'ELSE'
> END AS test_case_works_constant
> FROM pg_type
> ORDER BY oid
> LIMIT 10
> ;
> ```
> This returns the following:
> ```
> oid|typname
> |typcategory|test_case_fails|test_case_works_no_else|test_case_works_other_col|test_case_works_constant|
> ---+----------+-----------+---------------+-----------------------+-------------------------+------------------------+
> 16|bool |B |B | |bool
> |ELSE |
> 17|bytea |U |U | |bytea
> |ELSE |
> 18|char |Z |Z | |char
> |ELSE |
> 19|name |S |S |String types |String
> types |String types |
> 20|int8 |N |N |Numeric types |Numeric
> types |Numeric types |
> 21|int2 |N |N |Numeric types |Numeric
> types |Numeric types |
> 22|int2vector|A |A |
> |int2vector |ELSE |
> 23|int4 |N |N |Numeric types |Numeric
> types |Numeric types |
> 24|regproc |N |N |Numeric types |Numeric
> types |Numeric types |
> 25|text |S |S |String types |String
> types |String types |
> ```
> It looks weird, so I'm reporting this to you for review.
>
The bug is in the query, not in Postgres. Try casting typcategory to
text in your failing case to see the difference. As it is the literals
are being cast to "char" because that's what typcategory is.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2024-08-29 00:02 Francisco J. Ossandón <fco.j.ossandon@gmail.com>
parent: Andrew Dunstan <andrew@dunslane.net>
0 siblings, 1 reply; 14+ messages in thread
From: Francisco J. Ossandón @ 2024-08-29 00:02 UTC (permalink / raw)
To: Andrew Dunstan <andrew@dunslane.net>; +Cc: pgsql-bugs@lists.postgresql.org
Hello Andrew,
I thought about that before writing and tried casting to TEXT in the WHEN
comparison bits but it still failed.
CASE
WHEN typcategory::TEXT = 'N' THEN 'Numeric types'
WHEN typcategory::TEXT = 'S' THEN 'String types'
WHEN typcategory::TEXT = 'E' THEN 'Enum types'
ELSE typcategory
END AS test_case_fails,
After your answer, I tried again but this time adding the casting to the
ELSE too, and then it worked:
CASE
WHEN typcategory::TEXT = 'N' THEN 'Numeric types'
WHEN typcategory::TEXT = 'S' THEN 'String types'
WHEN typcategory::TEXT = 'E' THEN 'Enum types'
ELSE typcategory::TEXT
So is the ELSE column hijacking the data type of the whole expression?
Cheers,
Francisco
El mié, 28 ago 2024 a las 13:57, Andrew Dunstan (<andrew@dunslane.net>)
escribió:
>
> On 2024-08-28 We 12:17 PM, PG Bug reporting form wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 18594
> > Logged by: Francisco Javier Ossandon
> > Email address: fco.j.ossandon@gmail.com
> > PostgreSQL version: 15.5
> > Operating system: Linux
> > Description:
> >
> > Dear developers:
> > I have been using Postgres for some years now, and I just found what
> looks
> > like a bug, or at least I did not see anything in the documentation that
> > could explain it.
> >
> > * version():
> > PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1
> 20180712
> > (Red Hat 7.3.1-12), 64-bit
> >
> > The main issue is that "CASE WHEN THEN ELSE" is giving an expected
> result in
> > what seems like simple logic. I have a series of WHENs with conditions
> over
> > the value of a column and an ELSE where I return the same column value if
> > the CASEs above don't apply, and the query always returns the value of
> the
> > ELSE condition like if the WHENs above would not exist. The following
> query
> > exemplifies this, where the first CASE column gives an unexpected result
> > when using the same column in the ELSE, while the other CASE columns
> provide
> > the expected result when removing the ELSE or using a different column in
> > the ELSE or a constant string:
> > ```
> > SELECT oid,
> > typname,
> > typcategory,
> > CASE
> > WHEN typcategory = 'N' THEN 'Numeric types'
> > WHEN typcategory = 'S' THEN 'String types'
> > WHEN typcategory = 'E' THEN 'Enum types'
> > ELSE typcategory
> > END AS test_case_fails,
> > CASE
> > WHEN typcategory = 'N' THEN 'Numeric types'
> > WHEN typcategory = 'S' THEN 'String types'
> > WHEN typcategory = 'E' THEN 'Enum types'
> > END AS test_case_works_no_else,
> > CASE
> > WHEN typcategory = 'N' THEN 'Numeric types'
> > WHEN typcategory = 'S' THEN 'String types'
> > WHEN typcategory = 'E' THEN 'Enum types'
> > ELSE typname
> > END AS test_case_works_other_col,
> > CASE
> > WHEN typcategory = 'N' THEN 'Numeric types'
> > WHEN typcategory = 'S' THEN 'String types'
> > WHEN typcategory = 'E' THEN 'Enum types'
> > ELSE 'ELSE'
> > END AS test_case_works_constant
> > FROM pg_type
> > ORDER BY oid
> > LIMIT 10
> > ;
> > ```
> > This returns the following:
> > ```
> > oid|typname
> >
> |typcategory|test_case_fails|test_case_works_no_else|test_case_works_other_col|test_case_works_constant|
> >
> ---+----------+-----------+---------------+-----------------------+-------------------------+------------------------+
> > 16|bool |B |B | |bool
> > |ELSE |
> > 17|bytea |U |U | |bytea
> > |ELSE |
> > 18|char |Z |Z | |char
> > |ELSE |
> > 19|name |S |S |String types
> |String
> > types |String types |
> > 20|int8 |N |N |Numeric types
> |Numeric
> > types |Numeric types |
> > 21|int2 |N |N |Numeric types
> |Numeric
> > types |Numeric types |
> > 22|int2vector|A |A |
> > |int2vector |ELSE |
> > 23|int4 |N |N |Numeric types
> |Numeric
> > types |Numeric types |
> > 24|regproc |N |N |Numeric types
> |Numeric
> > types |Numeric types |
> > 25|text |S |S |String types
> |String
> > types |String types |
> > ```
> > It looks weird, so I'm reporting this to you for review.
> >
>
> The bug is in the query, not in Postgres. Try casting typcategory to
> text in your failing case to see the difference. As it is the literals
> are being cast to "char" because that's what typcategory is.
>
>
>
> cheers
>
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
>
--
Francisco J. Ossandon
Bioinformatician
Ph.D. in Biotechnology
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2024-08-29 00:11 Tom Lane <tgl@sss.pgh.pa.us>
parent: Francisco J. Ossandón <fco.j.ossandon@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: Tom Lane @ 2024-08-29 00:11 UTC (permalink / raw)
To: Francisco J. Ossandón <fco.j.ossandon@gmail.com>; +Cc: Andrew Dunstan <andrew@dunslane.net>; pgsql-bugs@lists.postgresql.org
=?UTF-8?Q?Francisco_J=2E_Ossand=C3=B3n?= <fco.j.ossandon@gmail.com> writes:
> So is the ELSE column hijacking the data type of the whole expression?
It's the only CASE result that is supplying a definite type at all.
But see
https://www.postgresql.org/docs/current/typeconv-union-case.html
particularly the footnote to the bit about "Select the first
non-unknown input type as the candidate type, then consider
each other non-unknown input type, left to right."
The WHEN clauses have exactly nothing to do with the result type
of the CASE: it's the THEN and ELSE clauses that supply the result.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2024-08-29 00:33 Francisco J. Ossandón <fco.j.ossandon@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 14+ messages in thread
From: Francisco J. Ossandón @ 2024-08-29 00:33 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Dunstan <andrew@dunslane.net>; pgsql-bugs@lists.postgresql.org
Hello Tom,
Thanks for the explanation and the link to the documentation. I understand
now what happened.
So it was a mistake on my side.
Thanks again for the patience and replies.
Best regards,
Francisco
El mié, 28 ago 2024 a las 20:11, Tom Lane (<tgl@sss.pgh.pa.us>) escribió:
> =?UTF-8?Q?Francisco_J=2E_Ossand=C3=B3n?= <fco.j.ossandon@gmail.com>
> writes:
> > So is the ELSE column hijacking the data type of the whole expression?
>
> It's the only CASE result that is supplying a definite type at all.
> But see
>
> https://www.postgresql.org/docs/current/typeconv-union-case.html
>
> particularly the footnote to the bit about "Select the first
> non-unknown input type as the candidate type, then consider
> each other non-unknown input type, left to right."
>
> The WHEN clauses have exactly nothing to do with the result type
> of the CASE: it's the THEN and ELSE clauses that supply the result.
>
> regards, tom lane
>
--
Francisco J. Ossandon
Bioinformatician
Ph.D. in Biotechnology
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2025-01-22 10:00 Chris BSomething <xpusostomos@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 14+ messages in thread
From: Chris BSomething @ 2025-01-22 10:00 UTC (permalink / raw)
To: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
In the 6 rules for type resolution, rule 5 says "select the first
non-unknown input type as candidate, and consider if other non-unknown
types can be implicitly converted." ... should there not be a new rule,
something like considering if the unknown types can be converted, then
notice that a long string can't be converted to a char, because it won't
fit? And then fail with an interesting error?
On Thu, 29 Aug 2024 at 08:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?UTF-8?Q?Francisco_J=2E_Ossand=C3=B3n?= <fco.j.ossandon@gmail.com>
> writes:
> > So is the ELSE column hijacking the data type of the whole expression?
>
> It's the only CASE result that is supplying a definite type at all.
> But see
>
> https://www.postgresql.org/docs/current/typeconv-union-case.html
>
> particularly the footnote to the bit about "Select the first
> non-unknown input type as the candidate type, then consider
> each other non-unknown input type, left to right."
>
> The WHEN clauses have exactly nothing to do with the result type
> of the CASE: it's the THEN and ELSE clauses that supply the result.
>
> regards, tom lane
>
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2025-02-17 22:31 David G. Johnston <david.g.johnston@gmail.com>
parent: Chris BSomething <xpusostomos@gmail.com>
0 siblings, 2 replies; 14+ messages in thread
From: David G. Johnston @ 2025-02-17 22:31 UTC (permalink / raw)
To: Chris BSomething <xpusostomos@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Wed, Jan 22, 2025 at 3:00 AM Chris BSomething <xpusostomos@gmail.com>
wrote:
> In the 6 rules for type resolution, rule 5 says "select the first
> non-unknown input type as candidate, and consider if other non-unknown
> types can be implicitly converted." ... should there not be a new rule,
> something like considering if the unknown types can be converted, then
> notice that a long string can't be converted to a char, because it won't
> fit? And then fail with an interesting error?
>
Do you have an example demonstrating this new rule? I'm doubting this
resolution logic goes into that much depth regarding the semantics of each
type. It is implied that even if an implicit cast exists from type A to
type B that actual values of type A may fail during the casting process to
make them type B. That would be the interesting error you'd end up seeing
- but from the type casting/input system, not the type resolution one.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2025-02-17 22:49 Chris BSomething <xpusostomos@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Chris BSomething @ 2025-02-17 22:49 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Tue, Feb 18, 2025, 6:31 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Wed, Jan 22, 2025 at 3:00 AM Chris BSomething <xpusostomos@gmail.com>
> wrote:
>
>> In the 6 rules for type resolution, rule 5 says "select the first
>> non-unknown input type as candidate, and consider if other non-unknown
>> types can be implicitly converted." ... should there not be a new rule,
>> something like considering if the unknown types can be converted, then
>> notice that a long string can't be converted to a char, because it won't
>> fit? And then fail with an interesting error?
>>
>
> Do you have an example demonstrating this new rule? I'm doubting this
> resolution logic goes into that much depth regarding the semantics of each
> type. It is implied that even if an implicit cast exists from type A to
> type B that actual values of type A may fail during the casting process to
> make them type B. That would be the interesting error you'd end up seeing
> - but from the type casting/input system, not the type resolution one.
>
> David J.
>
That sounds like a lot of words to justify an error that could be caught at
parse time, that is actually never caught at all. It seems to me that
whatever type is inferred from a long constant string, it won't fit into a
char... At least not without casting it. And therefore an error should come
out.
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2025-02-17 23:12 David G. Johnston <david.g.johnston@gmail.com>
parent: Chris BSomething <xpusostomos@gmail.com>
0 siblings, 1 reply; 14+ messages in thread
From: David G. Johnston @ 2025-02-17 23:12 UTC (permalink / raw)
To: Chris BSomething <xpusostomos@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Mon, Feb 17, 2025 at 3:49 PM Chris BSomething <xpusostomos@gmail.com>
wrote:
> That sounds like a lot of words to justify an error that could be caught
> at parse time, that is actually never caught at all. It seems to me that
> whatever type is inferred from a long constant string, it won't fit into a
> char... At least not without casting it. And therefore an error should come
> out.
>
I thought you were trying to convince people to modify the documentation?
This reads more like wanting to hack the parser to teach it forbidden
knowledge.
In any case I think this email report is closed and burying any discussion
about this material here just makes it harder for people to find.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2025-02-17 23:39 Chris BSomething <xpusostomos@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 14+ messages in thread
From: Chris BSomething @ 2025-02-17 23:39 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Tue, Feb 18, 2025, 7:13 AM David G. Johnston <david.g.johnston@gmail.com>
wrote:
> On Mon, Feb 17, 2025 at 3:49 PM Chris BSomething <xpusostomos@gmail.com>
> wrote:
>
>> That sounds like a lot of words to justify an error that could be caught
>> at parse time, that is actually never caught at all. It seems to me that
>> whatever type is inferred from a long constant string, it won't fit into a
>> char... At least not without casting it. And therefore an error should come
>> out.
>>
>
> I thought you were trying to convince people to modify the documentation?
> This reads more like wanting to hack the parser to teach it forbidden
> knowledge.
>
Hack? Forbidden knowledge? What an odd comment.
>
>
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2025-02-18 02:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 14+ messages in thread
From: Tom Lane @ 2025-02-18 02:12 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Chris BSomething <xpusostomos@gmail.com>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Jan 22, 2025 at 3:00 AM Chris BSomething <xpusostomos@gmail.com>
> wrote:
>> ... should there not be a new rule,
>> something like considering if the unknown types can be converted, then
>> notice that a long string can't be converted to a char, because it won't
>> fit? And then fail with an interesting error?
> Do you have an example demonstrating this new rule? I'm doubting this
> resolution logic goes into that much depth regarding the semantics of each
> type. It is implied that even if an implicit cast exists from type A to
> type B that actual values of type A may fail during the casting process to
> make them type B. That would be the interesting error you'd end up seeing
> - but from the type casting/input system, not the type resolution one.
Yeah. We document somewhere that the contents of a literal string are
*not* considered while deciding what type it should be taken as; thus,
the possibility of a casting failure is not part of the rules.
I think such a rule would be pretty dangerous, even if we could
implement it easily. As an example, if you entered something you
meant as a timestamp, but you fat-fingered the punctuation or
something, it'd likely fall back to being considered just "text",
potentially silently changing the semantics of the expression. We had
a lot of trouble with that sort of behavior back when the system had a
lot of implicit casts to text. We got rid of (most of) those in 8.3
or thereabouts, and it made the semantics generally a lot safer.
regards, tom lane
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2025-02-19 12:50 Chris BSomething <xpusostomos@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 14+ messages in thread
From: Chris BSomething @ 2025-02-19 12:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Tue, 18 Feb 2025 at 10:12, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yeah. We document somewhere that the contents of a literal string are
> *not* considered while deciding what type it should be taken as; thus,
> the possibility of a casting failure is not part of the rules.
>
The length of a literal is not strictly speaking its "contents". One could
for example, have its type as varchar(x) where x is its length. And then
not have implicit typecasts from varchar(x) to varchar( < x).
> I think such a rule would be pretty dangerous, even if we could
> implement it easily. As an example, if you entered something you
> meant as a timestamp, but you fat-fingered the punctuation or
> something, it'd likely fall back to being considered just "text",
>
I don't get that, I thought timestamps have to be preceded by "TIMESTAMP".
potentially silently changing the semantics of the expression. We had
> a lot of trouble with that sort of behavior back when the system had a
> lot of implicit casts to text. We got rid of (most of) those in 8.3
> or thereabouts, and it made the semantics generally a lot safer.
So... you solved that, it sounds like.
And yet this super dangerous behavior which could sit dormant and
undiscovered in a view in a large computer system, doesn't bother anyone?
OK, whatever.
Chris
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2025-02-19 13:46 David G. Johnston <david.g.johnston@gmail.com>
parent: Chris BSomething <xpusostomos@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2025-02-19 13:46 UTC (permalink / raw)
To: Chris BSomething <xpusostomos@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On Wednesday, February 19, 2025, Chris BSomething <xpusostomos@gmail.com>
wrote:
>
>
> And yet this super dangerous behavior which could sit dormant and
> undiscovered in a view in a large computer system, doesn't bother anyone?
> OK, whatever.
>
>
There a quite a few of those in SQL…testing queries is a requirement. And
being explicit with types is strongly suggested.
To be clear, the complaint is that we allow input functions such as
varchar(n) and char to truncate their inputs as opposed to erroring out if
the input length is too long. Right? If not I’m still confused as to what
exact behavior you believe is dangerous.
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE
@ 2025-02-19 19:02 Vik Fearing <vik@postgresfriends.org>
parent: Chris BSomething <xpusostomos@gmail.com>
1 sibling, 0 replies; 14+ messages in thread
From: Vik Fearing @ 2025-02-19 19:02 UTC (permalink / raw)
To: Chris BSomething <xpusostomos@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David G. Johnston <david.g.johnston@gmail.com>; PostgreSQL Bug List <pgsql-bugs@lists.postgresql.org>
On 19/02/2025 13:50, Chris BSomething wrote:
>
> On Tue, 18 Feb 2025 at 10:12, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I think such a rule would be pretty dangerous, even if we could
> implement it easily. As an example, if you entered something you
> meant as a timestamp, but you fat-fingered the punctuation or
> something, it'd likely fall back to being considered just "text",
>
>
> I don't get that, I thought timestamps have to be preceded by "TIMESTAMP".
The SQL standard requires you to do that, but PostgreSQL is a bit more
lax about it (and in my humble opinion, thankfully so). If you did
prefix your literals with their type, you wouldn't be having these
problems in the first place. The problem stems from postgres treating
your unmarked literal as "unknown" and then trying to figure out what
the heck it is.
--
Vik Fearing
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2025-02-19 19:02 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-08-28 16:17 BUG #18594: CASE WHEN ELSE failing to return the expected output when the same colum is used in WHEN and ELSE PG Bug reporting form <noreply@postgresql.org>
2024-08-28 17:56 ` Andrew Dunstan <andrew@dunslane.net>
2024-08-29 00:02 ` Francisco J. Ossandón <fco.j.ossandon@gmail.com>
2024-08-29 00:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-08-29 00:33 ` Francisco J. Ossandón <fco.j.ossandon@gmail.com>
2025-01-22 10:00 ` Chris BSomething <xpusostomos@gmail.com>
2025-02-17 22:31 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-17 22:49 ` Chris BSomething <xpusostomos@gmail.com>
2025-02-17 23:12 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-17 23:39 ` Chris BSomething <xpusostomos@gmail.com>
2025-02-18 02:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-02-19 12:50 ` Chris BSomething <xpusostomos@gmail.com>
2025-02-19 13:46 ` David G. Johnston <david.g.johnston@gmail.com>
2025-02-19 19:02 ` Vik Fearing <vik@postgresfriends.org>
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