Eduardo, You should never check correctness of what SQLite is doing in postgresql or mysql. They are different database engines with different principles. SQLite does exactly the right thing in this case and exactly how it's documented. (I don't mean dependence of query result on index existence of course.)
Tomash, >> 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. You can see that these 2 cases compare the same way. They both show that string is always greater than number and thus '11' > 2 and '2' > 11. And no affinity rules are applicable here because you use constants which don't have any affinity. Pavel 2010/6/9 Eduardo Pérez Ureta <edp...@gmail.com>: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
