The following bug has been logged online:
Bug reference: 4478
Logged by: Robert 'BoBsoN' Partyka
Email address: bobson@bobson.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
