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