You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
Binding signed/unsigned integer causes different results #313
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?
─────┴────────
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.
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.
Fixed on master by #356: integer parameters of any width now bind as SQLite integers, so WHERE x = ? with UInt16(1000) matches, and load! stores UInt64 columns as integers. Values SQLite can't represent, like typemax(UInt64), throw InexactError instead of being stored as blobs. This ships in the next release (1.9.0).
Activity
metab0t commentedon Nov 30, 2022
Please give a reproducible example.
rongcuid commentedon Nov 30, 2022
Then:
quinnj commentedon Nov 30, 2022
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 forIntegerthat converts toInt64and 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 commentedon Jun 21, 2024
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.quinnj commentedon Sep 30, 2026
Fixed on master by #356: integer parameters of any width now bind as SQLite integers, so
WHERE x = ?withUInt16(1000)matches, andload!storesUInt64columns as integers. Values SQLite can't represent, liketypemax(UInt64), throwInexactErrorinstead of being stored as blobs. This ships in the next release (1.9.0).Bump version to 1.9.0 (#365)