On Oct 7, 2009, at 10:41 PM, Alexey Pechnikov wrote: > Hello! > > I find some incorrect types casting for constants in all SQLite > versions.
The behaviour is actually correct, assuming that SQLite is using sqlite3_bind_text() to bind the value of $i to the SQL statement executed by [db eval]. See the rules to do with affinity and comparisons here: http://www.sqlite.org/datatype3.html#comparisons for why the expressions {1=$i}, {a=$i} and {a=1} appear to give inconsistent results. Dan. > > ================= test script ================= > #!/usr/bin/tclsh8.5 > > package require sqlite3 > sqlite3 db :memory: > > db eval { > create table test(a int); > insert into test values (1); > } > proc test {label sql} { > global i > puts -nonewline $label\t > puts [db eval $sql] > } > > set i 1 > test 1.1 {select * from test where a=$i} > test 1.2 {select * from test where 1=$i} ;# it doesn't work > test 1.3 {select a from test where a IN (cast($i AS INT), 160)} > test 1.4 {select a from test where 1 IN (cast($i AS INT), 160)} > > set i [db onecolumn {select quote($i)}] > test 2.1 "select * from test where a=$i" > test 2.2 "select * from test where 1=$i" ;# it doesn't work > test 2.3 "select a from test where a IN ($i, 160)" > test 2.4 "select a from test where 1 IN ($i, 160)" ;# it doesn't work > > test 3.1 "create view view_test1 as select * from test where a= > $i;select * from view_test1" > test 3.2 "create view view_test2 as select * from test where 1= > $i;select * from view_test2" ;# it doesn't work > test 3.3 "create view view_test3 as select * from test where a IN > ($i);select * from view_test3" > test 3.4 "create view view_test4 as select * from test where 1 IN > ($i);select * from view_test4" ;# it doesn't work > ====================================== > > ================= result ================= > 1.1 1 > 1.2 > 1.3 1 > 1.4 1 > 2.1 1 > 2.2 > 2.3 1 > 2.4 > 3.1 1 > 3.2 > 3.3 1 > 3.4 > ====================================== > > Of cource results of tests 1.1 and 1.2, 2.1 and 2.2, etc. must be > equal. > I sqlite3 shell all work correct. > > ============== sqlite3 shell ================ > sqlite> select * from test where 1=1; > 1 > sqlite> select * from test where 1=quote(1); > 1 > sqlite> select a from test where a IN (1, 160); > 1 > sqlite> select a from test where 1 IN (1, 160); > 1 > sqlite> select a from test where 1 IN (quote(1), 160); > 1 > sqlite> select a from test where a IN (quote(1), 160); > 1 > ====================================== > > P.S. I did report about this problem some times ago... > > Best regards, Alexey Pechnikov. > http://pechnikov.tel/ > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
