Re: Possible bug?

From: Ilir Gashi <I(dot)Gashi(at)city(dot)ac(dot)uk>
To: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Possible bug?
Date: 2004-07-02 10:38:28
Message-ID: E1BgLR6-0005z0-00@ms2.city.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Browse pgsql-bugs by date

  From Date Subject
Next Message Achilleus Mantzios 2004-07-02 10:46:46 Re: timestamp arithmetic (a possible bug?)
Previous Message vertigo 2004-07-02 10:24:24 7.4: serial not working ?