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: [EMAIL PROTECTED]
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
____________________________________________
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
