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

Started by Robert 'BoBsoN' Partykaover 17 years ago2 messagesbugs
Jump to latest

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Robert 'BoBsoN' Partyka (#1)
Re: BUG #4478: = operator in connection with CASE looks like loose some functionality (bug or feature?)

"Robert 'BoBsoN' Partyka" <bobson@bobson.pl> writes:

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).

It's intentional.

If this is feature then I think it should be documented in manual and
probably in some migration documentation.

See the first item under the 8.3 migration caveats:
"Non-character data types are no longer automatically cast to TEXT"
http://www.postgresql.org/docs/8.3/static/release-8-3.html

regards, tom lane