Binding signed/unsigned integer causes different results

[rongcuid]

For instance a query like so:

SELECT * FROM table WHERE x = ?

Binding 0x1000 (which is UInt16) and Int(0x1000) yield different results.

[metab0t]

Please give a reproducible example.

[rongcuid]

db = SQLite.DB()
DBInterface.execute(db, "CREATE TABLE t(x INTEGER)")
DBInterface.execute(db, "INSERT INTO t(x) VALUES (1000)")

Then:

julia> using DataFrames

julia> DBInterface.execute(db, "SELECT * FROM t WHERE x  = 1000") |> DataFrame
1×1 DataFrame
 Row │ x     
     │ Int64 
─────┼───────
   1 │  1000

julia> DBInterface.execute(db, "SELECT * FROM t WHERE x  = ?", (1000,)) |> DataFrame
1×1 DataFrame
 Row │ x     
     │ Int64 
─────┼───────
   1 │  1000

julia> DBInterface.execute(db, "SELECT * FROM t WHERE x  = ?", (UInt16(1000),)) |> DataFrame
0×1 DataFrame
 Row │ x      
     │ Int64? 
─────┴────────
julia> DBInterface.execute(db, "SELECT * FROM t WHERE x  = ?", (UInt32(1000),)) |> DataFrame
0×1 DataFrame
 Row │ x      
     │ Int64? 
─────┴────────

julia> DBInterface.execute(db, "SELECT * FROM t WHERE x  = ?", (UInt64(1000),)) |> DataFrame
0×1 DataFrame
 Row │ x      
     │ Int64? 
─────┴────────

[quinnj]

I think this is because we don't have a direct dispatch for UInt types, so they go through the fallback which does the full serialize(x) and bound as blobs. We should probably have a fallback for Integer that converts to Int64 and does the bind, though that may mean we get a weird truncation issue for some UInt64. Or maybe we explicitly error and say you can only bind signed integers since sqlite doesn't ahve explicit bind functions for unsigned.

[Zentrik]

This also seems to be an issue if you load a DataFrame with UInt64s in it into a database. Everything works fine on Julia, but sqlite3 and other libraries see a string.