Thread: Fwd: int type problem in 7.3
Ok, I checked this again. Up until 7.2, it was possible to compare an empty string to a number, and it worked:: e.g.: select * from mytable where int4id='' worked fine, but delivered no result. This is exactly what Oracle did here, a comparison like this does not work: SQL> select * from re_eintraege where id='foobar'; select * from re_eintraege where id='foobar' * ERROR at line 1: ORA-01722: invalid number But oracle accepts this one: SQL> select * from re_eintraege where id=''; no rows selected because oracle treats the empty string as NULL and effectivly checks: select * from re_eintraege where id is null; I think 7.3 is doing right here and I've to fix all queries (*sigh*), but oracle compatibilty is lost here. The bad news for me is, rewriting the queries won't help here, because I'll use indexing when I rewrite my queries to: select 1 from mytable where id::text='' Regards,Mario Weilguni ---------- Weitergeleitete Nachricht ---------- Subject: [HACKERS] int type problem in 7.3 Date: Wed, 2 Oct 2002 08:31:45 +0200 From: Mario Weilguni <mweilguni@sime.com> To: pgsql-hackers@postgresql.org It seems queries like: select ... from table where id='' (an empty string) do not work anymore, itworked up to 7.2. This will make migration to7.3 quite difficult for someapplication, especially for oracle applications. Would'nt it be better toevaluate such expressionsto false. Regards,Mario Weilguni ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -------------------------------------------------------
Mario Weilguni <mweilguni@sime.com> writes:
> Ok, I checked this again. Up until 7.2, it was possible to compare an empty string to a number, and it worked::
> e.g.: select * from mytable where int4id=''
> worked fine, but delivered no result.
No, that was not what it did: in reality, the '' was silently taken as
zero, and would match rows containing 0. That seems a very error-prone
behavior (not to say a flat-out bug) to me.
> But oracle accepts this one:
> SQL> select * from re_eintraege where id='';
> no rows selected
> because oracle treats the empty string as NULL
Oracle does that for string data, but it doesn't do it for numerics
does it? In any case, that behavior is surely non-compliant with
the SQL spec.
We were not compatible with Oracle on this behavior before, and I'm
not very inclined to become so now.
regards, tom lane
>> But oracle accepts this one: >> SQL> select * from re_eintraege where id=''; >> no rows selected >> because oracle treats the empty string as NULL > >Oracle does that for string data, but it doesn't do it for numerics >does it? In any case, that behavior is surely non-compliant with >the SQL spec. No, oracle accepts this and works correctly with number() datatype. However I did not know that in postgres '' was treated as '0'. Regards,Mario Weilguni
On Wed, 2 Oct 2002, Mario Weilguni wrote: > >> But oracle accepts this one: > >> SQL> select * from re_eintraege where id=''; > >> no rows selected > >> because oracle treats the empty string as NULL > > > >Oracle does that for string data, but it doesn't do it for numerics > >does it? In any case, that behavior is surely non-compliant with > >the SQL spec. > > No, oracle accepts this and works correctly with number() datatype. > However I did not know that in postgres '' was treated as '0'. So what would I be selecting in Oracle if I did: SELECT * FROM mytable WHERE myfield = '' where myfield is of VARCHAR type? If you want to select on NULL, whether or not you think the database is more intelligent than you in determining what you really want, then write your query to select on NULL. The chances are your database is not actually a mind reader. -- Nigel J. Andrews
This document: http://developer.postgresql.org/docs/postgres/release-7-2-3.html mentions a release date of 2002-10-01 for version 7.2.3. It isn't on the main website, tough, is it? Regards, Michael
On Wed, 2 Oct 2002, Michael Paesold wrote: > This document: > http://developer.postgresql.org/docs/postgres/release-7-2-3.html > > mentions a release date of 2002-10-01 for version 7.2.3. > > It isn't on the main website, tough, is it? The documentation on the developers website is not necessarily accurate - especially when it comes to dates. Documentation is typically one of the last things finalized and is in a constant state of change. That's one of the reasons why the developer site is separated from the main website - people read things on the developer site and think they're 100% accurate. Nothing is final until it's announced on the announce mailing list and/or the main website. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 56K Nationwide Dialup from $16.00/mo atPop4 Networking http://www.camping-usa.com http://www.cloudninegifts.com http://www.meanstreamradio.com http://www.unknown-artists.com ==========================================================================
Have you looked at transform_null_equals in the postgresql.conf file to see if turning that on makes this work like oracle? On Wed, 2 Oct 2002, Mario Weilguni wrote: > Ok, I checked this again. Up until 7.2, it was possible to compare an empty string to a number, and it worked:: > e.g.: select * from mytable where int4id='' > worked fine, but delivered no result. This is exactly what Oracle did here, > a comparison like this does not work: > > SQL> select * from re_eintraege where id='foobar'; > select * from re_eintraege where id='foobar' > * > ERROR at line 1: > ORA-01722: invalid number > > But oracle accepts this one: > SQL> select * from re_eintraege where id=''; > > no rows selected > > because oracle treats the empty string as NULL and effectivly checks: > select * from re_eintraege where id is null; > > I think 7.3 is doing right here and I've to fix all queries (*sigh*), but oracle compatibilty is lost here. > > The bad news for me is, rewriting the queries won't help here, because I'll use indexing when I rewrite my queries to: > select 1 from mytable where id::text='' > > Regards, > Mario Weilguni > > ---------- Weitergeleitete Nachricht ---------- > > Subject: [HACKERS] int type problem in 7.3 > Date: Wed, 2 Oct 2002 08:31:45 +0200 > From: Mario Weilguni <mweilguni@sime.com> > To: pgsql-hackers@postgresql.org > > It seems queries like: > select ... from table where id='' (an empty string) do not work anymore, it > worked up to 7.2. This will make migration to 7.3 quite difficult for some > application, especially for oracle applications. Would'nt it be better to > evaluate such expressions to false. > > Regards, > Mario Weilguni > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > ------------------------------------------------------- > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >