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
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
