Thread: No rows returned from the SQL query (possible bug)

Hello,

I saw this 'bug' reported in the Firebird SourceForge website for the
Firebird 1.0 server. They confirmed it as a bug! I tried it in PostgreSQL
7.2 (I know its an old release but I do not have a newer version installed,
and in any case I am only using the server for reserach purposes).
PostgreSQL returns no rows for either of the queries. Of course this is the
case since the attributes F1 and F2 of Table T1 are of type Char whereas
the select query is making the test with an integer value. Is this the
intended behaviour?

Firebird returns 0 rows for the first query and 1 row (values 3,3) for the
second query. I also tried it in Oracle 8.0.5 which returns 1 row for each
of the two queries. Therefore I have different results being returned from
all three servers.

Reproducible script:

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 ('003', '003');
INSERT INTO T1 (F1, F2) VALUES ('004', '004');


SELECT * FROM T1 WHERE F1 = 3;

F1   F2
---- ----

SELECT * FROM T1 WHERE F2 = 3;

F1   F2
---- ----

Best regards,

Ilir

--
____________________________________________

Ilir Gashi
PhD Student
Centre for Software Reliability
City University
Northampton Square, London, UK EC1V 0HB
email: i.gashi@city.ac.uk
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
____________________________________________

3 and '003 ' are not the samething!!!
Do not forget that F1 and F2 are paded with spaces also.
Maybe you want to use varchar instead.

Ilir Gashi wrote:

> Hello,
>
> I saw this 'bug' reported in the Firebird SourceForge website for the
> Firebird 1.0 server. They confirmed it as a bug! I tried it in
> PostgreSQL 7.2 (I know its an old release but I do not have a newer
> version installed, and in any case I am only using the server for
> reserach purposes). PostgreSQL returns no rows for either of the
> queries. Of course this is the case since the attributes F1 and F2 of
> Table T1 are of type Char whereas the select query is making the test
> with an integer value. Is this the intended behaviour?
>
> Firebird returns 0 rows for the first query and 1 row (values 3,3) for
> the second query. I also tried it in Oracle 8.0.5 which returns 1 row
> for each of the two queries. Therefore I have different results being
> returned from all three servers.
>
> Reproducible script:
>
> 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 ('003', '003');
> INSERT INTO T1 (F1, F2) VALUES ('004', '004');
>
>
> SELECT * FROM T1 WHERE F1 = 3;
>
> F1   F2  ---- ----
>
> SELECT * FROM T1 WHERE F2 = 3;
>
> F1   F2  ---- ----
>
> Best regards,
>
> Ilir
>