Thread: Possible bug?
Hello,
I saw this 'bug' reported in the Firebird SourceForge website for the
Firebird 1.0 server. They confirmed it as a bug! I tried it in PostgreSQL
7.2 (I know its an old release but I do not have a newer version installed,
and in any case I am only using the server for reserach purposes).
PostgreSQL returns no rows for either of the queries. Of course this is the
case since the attributes F1 and F2 of Table T1 are of type Char whereas
the select query is making the test with an integer value. Is this the
intended behaviour?
Firebird returns 0 rows for the first query and 1 row (values 3,3) for the
second query. I also tried it in Oracle 8.0.5 which returns 1 row for each
of the two queries. Therefore I have different results being returned from
all three servers.
Reproducible script:
CREATE TABLE T1 (F1 CHAR(4), F2 CHAR(4));
CREATE INDEX T1_F1 ON T1 (F1);
INSERT INTO T1 (F1, F2) VALUES ('001', '001');
INSERT INTO T1 (F1, F2) VALUES ('002', '002');
INSERT INTO T1 (F1, F2) VALUES ('003', '003');
INSERT INTO T1 (F1, F2) VALUES ('004', '004');
SELECT * FROM T1 WHERE F1 = 3;
F1 F2
---- ----
SELECT * FROM T1 WHERE F2 = 3;
F1 F2
---- ----
Best regards,
Ilir
--
____________________________________________
Ilir Gashi
PhD Student
Centre for Software Reliability
City University
Northampton Square, London EC1V 0HB
Tel: +44 20 7040 0273
Fax: +44 20 7040 8585
email: i.gashi@city.ac.uk
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
____________________________________________
On Thu, 1 Jul 2004, Ilir Gashi wrote: > I saw this 'bug' reported in the Firebird SourceForge website for the > Firebird 1.0 server. They confirmed it as a bug! I tried it in PostgreSQL > 7.2 (I know its an old release but I do not have a newer version installed, > and in any case I am only using the server for reserach purposes). > PostgreSQL returns no rows for either of the queries. Of course this is the > case since the attributes F1 and F2 of Table T1 are of type Char whereas > the select query is making the test with an integer value. Is this the > intended behaviour? Technically speaking, I believe the queries are simply invalid without the presence of casts from the spec's standpoint. Theoretically, I'd either expect both to give 0 rows (convert 3 into a string and compare) or both to give 1 row (convert '003' to an integer and compare) if it didn't error. What does Oracle do if you insert a value like 'XXX' into the column?
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> Technically speaking, I believe the queries are simply invalid without the
> presence of casts from the spec's standpoint.
Right. The reason PG doesn't error is that we have an implicit cast
from integer to text. I've opined before that we should mark most
cross-type-category casts as explicit-only ... they are just too prone
to give unexpected answers, as in this case.
It's interesting that Oracle seems to be converting text to integer
instead of vice versa. Or maybe they have some truly bizarre string
comparison operation that ignores leading '0'? Whatever they are
doing, there's no support for it in the spec.
regards, tom lane
On Jul 1 2004, Stephan Szabo wrote:
> Technically speaking, I believe the queries are simply invalid without the
> presence of casts from the spec's standpoint.
>
> Theoretically, I'd either expect both to give 0 rows (convert 3 into a
> string and compare) or both to give 1 row (convert '003' to an integer and
> compare) if it didn't error.
>
> What does Oracle do if you insert a value like 'XXX' into the column?
>
Below is the behaviour of Oracle 8.0.5 for the value 'XXX'
CREATE TABLE T1 (F1 CHAR(4), F2 CHAR(4));
CREATE INDEX T1_F1 ON T1 (F1);
INSERT INTO T1 (F1, F2) VALUES ('001', '001');
INSERT INTO T1 (F1, F2) VALUES ('002', '002');
INSERT INTO T1 (F1, F2) VALUES ('XXX', '003');
INSERT INTO T1 (F1, F2) VALUES ('004', '004');
SELECT * FROM T1 WHERE F1 = 3;
ORA-01722: invalid number
SELECT * FROM T1 WHERE F2 = 3;
F1 F2
---- ----
XXX 003
1 row selected.
So, yes Oracle is doing a char to integer implicit conversion and
performing an integer with integer comparison.
Thanks for the quick response.
(BTW I appologise for my last report being posted twice. I was not a member
of PGSQL-bugs list when I posted the first one, and since it was taking a
while for it to be approved, I decied to join the list and then post it
again.)
Best regards,
Ilir
--
____________________________________________
Ilir Gashi
PhD Student
Centre for Software Reliability
City University
Northampton Square, London EC1V 0HB
email: i.gashi@city.ac.uk
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
____________________________________________
Am Donnerstag, 1. Juli 2004 23:10 schrieb Tom Lane: > Right. The reason PG doesn't error is that we have an implicit cast > from integer to text. I've opined before that we should mark most > cross-type-category casts as explicit-only ... they are just too prone > to give unexpected answers, as in this case. It seems we're just opining in circles. Why not do it? Is anyone objecting?
Peter Eisentraut <peter_e@gmx.net> writes:
> Am Donnerstag, 1. Juli 2004 23:10 schrieb Tom Lane:
>> Right. The reason PG doesn't error is that we have an implicit cast
>> from integer to text. I've opined before that we should mark most
>> cross-type-category casts as explicit-only ... they are just too prone
>> to give unexpected answers, as in this case.
> It seems we're just opining in circles. Why not do it? Is anyone objecting?
IIRC there were objections the last time it was seriously proposed,
basically of the form "but that will break my application which relies
on writing so-and-so without a cast". We did get as far as removing all
the implicit cross-category coercions except these casts to text:
bigint | text
smallint | text
integer | text
real | text
double precision | text
numeric | text
oid | text
date | text
time without time zone | text
time with time zone | text
timestamp without time zone | text
timestamp with time zone | text
interval | text
but these seem to have a constituency :-(
regards, tom lane
On Fri, Jul 02, 2004 at 10:27:35 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > IIRC there were objections the last time it was seriously proposed, > basically of the form "but that will break my application which relies > on writing so-and-so without a cast". We did get as far as removing all > the implicit cross-category coercions except these casts to text: > > bigint | text > smallint | text > integer | text > real | text > double precision | text > numeric | text > oid | text > date | text > time without time zone | text > time with time zone | text > timestamp without time zone | text > timestamp with time zone | text > interval | text > > but these seem to have a constituency :-( I think it is reasoable to expect people to use an explicit cast when doing these conversions. These are not conversions that you would normally want to do. And even if you do, you need to be very concerned about how the converted text looks for different ranges of source data values. Allowing these makes it harder to notice mistakes. And I expect that overall the hiding mistakes case occurs a lot more than the I meant to do that but didn't want to type ::text case.
Bruno Wolff III <bruno@wolff.to> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> but these seem to have a constituency :-(
> I think it is reasoable to expect people to use an explicit cast when
> doing these conversions.
That's what I think, but I lost the argument last time round...
I think it would be easier to sell making these changes as part of
a move that creates non-implicit casts to/from text for *all* datatypes
(implemented on top of their I/O routines). So I don't plan on making
the proposal again until I or somebody else have time to write some
infrastructure for that.
regards, tom lane