On Wed, Aug 6, 2014 at 5:29 PM, Martin Engelschalk <
engelsch...@codeswift.com> wrote:
> Hello List,
>
> I have a strange problem with a statement like
>
> select id from some_table where coalesce(some_col, 1) = :1
>
> where :1 is a bind variable which i bind in my program using
> sqlite3_bind_text.
>
fwiw, i cannot reproduce this using:
#define SQLITE_VERSION "3.8.5"
#define SQLITE_VERSION_NUMBER 3008005
#define SQLITE_SOURCE_ID "2014-06-04 14:06:34
b1ed4f2a34ba66c29b130f8d13e9092758019212"
so long as the value i bind is of the same type being returned. If, e.g.,
some_col is an integer and i bind '3' instead of integer 3, then i am
seeing what you describe. If i bind an integer to the integer column, or
bind a string to a coalesce-returned string value, then i see what one
would expect.
For completeness:
s2> var d = Fossil.Db.open(':memory:')
s2> d.exec("Create table t(a)")
s2> d.exec("insert into t(a) values(1),(2),(NULL),(3)")
s2> d.each({sql:'select * from t',callback:'print(this)'})
[1]
[2]
[null]
[3]
s2> d.each({sql:'select * from t where
coalesce(a,9)=$a',callback:'print(this)',bind:{$a:'9'}})
<NO RESULT HERE>
s2> d.each({sql:"select * from t where
coalesce(a,'9')=$a",callback:'print(this)',bind:{$a:'9'}})
[null]
<coalesce result of string type>
s2> d.each({sql:"select * from t where
coalesce(a,'9')=$a",callback:'print(this)',bind:{$a:'1'}})
result: Db@0x1eb4d20[scope=#1@0x7fffd16b3068 ref#=1] ==> Db@0x1eb4d20
<integer column but string binding>
s2> d.each({sql:"select * from t where
coalesce(a,'9')=$a",callback:'print(this)',bind:{$a:1}})
[1]
<integer column and integer binding>
s2> print(d.selectValue("select 1='1'"))
0
--
----- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
