Обсуждение: BUG #4478: = operator in connection with CASE looks like loose some functionality (bug or feature?)
BUG #4478: = operator in connection with CASE looks like loose some functionality (bug or feature?)
От
"Robert 'BoBsoN' Partyka"
Дата:
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
"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