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(dot)gashi(at)city(dot)ac(dot)uk
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
____________________________________________