From: | "Robert 'BoBsoN' Partyka" <bobson(at)bobson(dot)pl> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4478: = operator in connection with CASE looks like loose some functionality (bug or feature?) |
Date: | 2008-10-14 14:10:38 |
Message-ID: | 200810141410.m9EEAc0i015335@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4478
Logged by: Robert 'BoBsoN' Partyka
Email address: bobson(at)bobson(dot)pl
PostgreSQL version: 8.3.4
Operating system: OpenSuSE Linux
Description: = operator in connection with CASE looks like loose some
functionality (bug or feature?)
Details:
Hi,
I just migrate one application from 8.2.7 to 8.3.1, and I see some strange
change of way the CASE works (case study tested also on 8.3.4).
I have template system for sql queries which generates such sql:
select * from foo where ind = case when '0'<>'' then '0' else null end;
it works "slightly" different in 8.2.7 and 8.3.4:
test=# select * from foo;
ind | inf
-----+-----------
0 | Test info
(1 row)
============ version 8.2.7 ============
test=# select version();
version
----------------------------------------------------------------------------
----------------------------------
PostgreSQL 8.2.7 on i686-pc-linux-gnu, compiled by GCC
i686-pc-linux-gnu-gcc (GCC) 4.2.3 (Gentoo 4.2.3 p1.0)
(1 row)
test=# select * from foo where ind = case when '0'<>'' then '0' else null
end;
ind | inf
-----+-----------
0 | Test info
(1 row)
test=# select * from foo where ind = (case when '0'<>'' then '0' else null
end)::integer;
ind | inf
-----+-----------
0 | Test info
(1 row)
test=# select * from foo where ind = '0';
ind | inf
-----+-----------
0 | Test info
(1 row)
As we see - all SQL are parsed ok, and executed without even notice or
warning - but...
============ version 8.3.4 ============
test=# select version();
version
----------------------------------------------------------------------------
-----------------------------------------------------------------
PostgreSQL 8.3.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.3.1 20080507 (prerelease) [gcc-4_3-branch revision 135036]
(1 row)
test=# select * from foo where ind = case when '0'<>'' then '0' else null
end;
ERROR: operator does not exist: integer = text at character 29
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
STATEMENT: select * from foo where ind = case when '0'<>'' then '0' else
null end;
ERROR: operator does not exist: integer = text
LINE 1: select * from foo where ind = case when '0'<>'' then '0' els...
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
test=# select * from foo where ind = (case when '0'<>'' then '0' else null
end)::integer;
ind | inf
-----+-----------
0 | Test info
(1 row)
test=# select * from foo where ind = '0';
ind | inf
-----+-----------
0 | Test info
(1 row)
In construction "... ind = case ..." automagic conversion from text to
integer is not done anymore in 8.3.* - you must cast it manually.
Is this bug or feature? (for me it looks like bug).
If this is feature then I think it should be documented in manual and
probably in some migration documentation.
Regards - you do great job with PgSQL :)
BoBsoN
From | Date | Subject | |
---|---|---|---|
Next Message | Eric Haszlakiewicz | 2008-10-14 15:49:47 | Re: Re: BUG #4078: ERROR: operator does not exist: numeric = character varying |
Previous Message | Tom Lane | 2008-10-14 01:10:41 | Re: Re: BUG #4078: ERROR: operator does not exist: numeric = character varying |