Re: Possible bug?

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/
____________________________________________