2010-06-09 Tomash Brechko <tomash.brec...@gmail.com>: > With SQLite 3.6.23.1 I see the following: > > $ ./sqlite3 /tmp/a.sqlite > SQLite version 3.6.23.1 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> CREATE TABLE t (c1 INTEGER, c2 INTEGER); > sqlite> INSERT INTO t (c1, c2) VALUES (5, 5); > sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2'; > sqlite> CREATE INDEX t_c1_c2 ON t (c1, c2); > sqlite> SELECT * FROM t WHERE c1 = 5 AND c2 > 0 AND c2 <= '2'; > 5|5 > > Note that the same query may or may not return the result based on > whether there's an index present. Apparently on second invocation the > comparison is 5 <= '2', and numbers compare before strings. > > It's not clear to me which result should be considered correct though: > > sqlite> select 11 > 2; > 1 > sqlite> select '11' > '2'; > 0 > sqlite> select '11' > 2; > 1 > sqlite> select 11 > '2'; > 0 > > It seems that last two cases should compare the same way, no matter > what the actual affinity rules are.
I just tested MySQL 5.1.47 : mysql> select 11 > 2; +--------+ | 11 > 2 | +--------+ | 1 | +--------+ 1 row in set (0.40 sec) mysql> select '11' > '2'; +------------+ | '11' > '2' | +------------+ | 0 | +------------+ 1 row in set (0.00 sec) mysql> select '11' > 2; +----------+ | '11' > 2 | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) mysql> select 11 > '2'; +----------+ | 11 > '2' | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) And PostgreSQL 8.4.4 : Type "help" for help. postgres=# SELECT 11 > 2; ?column? ---------- t (1 row) postgres=# SELECT '11' > '2'; ?column? ---------- f (1 row) postgres=# SELECT '11' > 2; ?column? ---------- t (1 row) postgres=# SELECT 11 > '2'; ?column? ---------- t (1 row) I consider MySQL and PostgreSQL are doing the correct thing so I consider this a bug in SQLite. Could you open a bug at the tracker? (If there is a bug open, what id it is?) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
