BUG #4478: = operator in connection with CASE looks like loose some functionality (bug or feature?) - Mailing list pgsql-bugs

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