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

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

Responses

Browse pgsql-bugs by date

  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