Hello!
I find some incorrect types casting for constants in all SQLite versions.
================= 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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
