Repeat calls to load!(...) fail

[NicholasWMRitchie]

If I create a database, load a data frame in using the load!(..) function it creates a table in the database. However, if I call load!(...) a second time with additional similarly structured data, it fails with the error shown below.

using SQLite
using DataFrames

db=SQLite.DB(joinpath(@__DIR__,"crap.db")
df=DataFrame(A=collect(1:10), B=collect(map(i->i*i,1:10)), C=collect(map(i->sin(i),1:10)))
df |> SQLite.load!(db, "Table")
df2=DataFrame(A=collect(11:20), B=collect(map(i->i*i,11:20)), C=collect(map(i->sin(i),11:20)))
df2 |> SQLite.load!(db, "Table")

ERROR: MethodError: promote_type(::Type{Union{}}, ::Type{String}) is ambiguous. Candidates:
promote_type(::Type{Union{}}, ::Type{T}) where T in Base at promotion.jl:224
promote_type(::Type{T}, ::Type{String}) where T<:WeakRefStrings.InlineString in WeakRefStrings at C:\Users\nritchie\.julia\packages\WeakRefStrings\a3jYm\src\inlinestrings.jl:44
Possible fix, define
promote_type(::Type{Union{}}, ::Type{String})
Stacktrace:
[1] add_or_widen!(val::String, col::Int64, nm::Symbol, dest::Tables.EmptyVector, row::Int64, updated::Base.RefValue{Any}, L::Base.SizeUnknown)
  @ Tables C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:150
[2] eachcolumns
  @ C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\utils.jl:127 [inlined]
[3] _buildcolumns(rowitr::SQLite.Query, row::SQLite.Row, st::Int64, sch::Tables.Schema{(:cid, :name, :type, :notnull, :dflt_value, :pk), nothing}, columns::NTuple{6, Tables.EmptyVector}, updated::Base.RefValue{Any})
  @ Tables C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:187
[4] buildcolumns
  @ C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:217 [inlined]
[5] columns
  @ C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:253 [inlined]
[6] columntable(itr::SQLite.Query)
  @ Tables C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\namedtuples.jl:171
[7] (::SQLite.var"#23#24")(qry::SQLite.Query)
  @ SQLite C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\tables.jl:169
[8] execute(f::SQLite.var"#23#24", stmt::SQLite.Stmt, params::NamedTuple{(), Tuple{}})
  @ DBInterface C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:135
[9] execute(f::Function, conn::SQLite.DB, sql::String, params::NamedTuple{(), Tuple{}})
  @ DBInterface C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:144
[10] #execute#3
  @ C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:153 [inlined]
[11] execute
  @ C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:153 [inlined]
[12] tableinfo
  @ C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\tables.jl:165 [inlined]
[13] load!(itr::DataFrame, db::SQLite.DB, name::String; kwargs::Base.Iterators.Pairs{Union{}, Union{}, Tuple{}, NamedTuple{(), Tuple{}}})
  @ SQLite C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\tables.jl:195
[14] load!
  @ C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\tables.jl:195 [inlined]
[15] #26
  @ C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\tables.jl:191 [inlined]
[16] |>(x::DataFrame, f::SQLite.var"#26#27"{Base.Iterators.Pairs{Union{}, Union{}, Tuple{}, NamedTuple{(), Tuple{}}}, SQLite.DB, String})
  @ Base .\operators.jl:858
[17] top-level scope
  @ REPL[8]:1``

[jeremiahpslewis]

Hey! @NicholasWMRitchie can you post the SQLite.jl and DataFrames.jl version you are using? (versioninfo() and using Pkg; Pkg.status()? I ran your code on my machine and didn’t encounter the error, would be helpful to know version info. :)

(I think you are missing a parentheses here db=SQLite.DB(joinpath(@__DIR__,"crap.db")), but this is certainly unrelated to the error.)

[NicholasWMRitchie]

Julia Version 1.6.3
Commit ae8452a9e0 (2021-09-23 17:34 UTC)
Platform Info:
OS: Windows (x86_64-w64-mingw32)
CPU: Intel(R) Xeon(R) CPU E3-1545M v5 @ 2.90GHz
WORD_SIZE: 64
LIBM: libopenlibm
LLVM: libLLVM-11.0.1 (ORCJIT, skylake)

[0aa819cd] SQLite v1.3.0
[a93c6f00] DataFrames v1.2.2
[bd369af6] Tables v1.6.0

Re: parenthesis, yes, I changed the path as I uploaded it and omitted a )

[jeremiahpslewis]

Ok, maybe this is a windows specific issue? Can you tell me if you get the error with this snippet? Just want to try to get to a minimal working example, then try and figure out why this doesn’t get picked up by the existing unit tests.

using SQLite
using DataFrames

db = SQLite.DB()
df=DataFrame(A=[1,2,3], B=[1,2,3], C=[1,2,3])
df |> SQLite.load!(db, "table1")
df |> SQLite.load!(db, "table1")

[NicholasWMRitchie]

Same error

julia> using SQLite

julia> using DataFrames

julia>

julia> db = SQLite.DB()
SQLite.DB(":memory:")

julia> df=DataFrame(A=[1,2,3], B=[1,2,3], C=[1,2,3])
3×3 DataFrame
 Row │ A      B      C
     │ Int64  Int64  Int64
─────┼─────────────────────
   1 │     1      1      1
   2 │     2      2      2
   3 │     3      3      3

julia> df |> SQLite.load!(db, "table1")
"table1"

julia> df |> SQLite.load!(db, "table1")
ERROR: MethodError: promote_type(::Type{Union{}}, ::Type{String}) is ambiguous. Candidates:
  promote_type(::Type{Union{}}, ::Type{T}) where T in Base at promotion.jl:224
  promote_type(::Type{T}, ::Type{String}) where T<:WeakRefStrings.InlineString in WeakRefStrings at C:\Users\nritchie\.julia\packages\WeakRefStrings\a3jYm\src\inlinestrings.jl:44
Possible fix, define
  promote_type(::Type{Union{}}, ::Type{String})
Stacktrace:
  [1] add_or_widen!(val::String, col::Int64, nm::Symbol, dest::Tables.EmptyVector, row::Int64, updated::Base.RefValue{Any}, L::Base.SizeUnknown)
    @ Tables C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:150
  [2] eachcolumns
    @ C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\utils.jl:127 [inlined]
  [3] _buildcolumns(rowitr::SQLite.Query, row::SQLite.Row, st::Int64, sch::Tables.Schema{(:cid, :name, :type, :notnull, :dflt_value, :pk), nothing}, columns::NTuple{6, Tables.EmptyVector}, updated::Base.RefValue{Any})
    @ Tables C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:187
  [4] buildcolumns
    @ C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:217 [inlined]
  [5] columns
    @ C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:253 [inlined]
  [6] columntable(itr::SQLite.Query)
    @ Tables C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\namedtuples.jl:171
  [7] (::SQLite.var"#23#24")(qry::SQLite.Query)
    @ SQLite C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\tables.jl:169
  [8] execute(f::SQLite.var"#23#24", stmt::SQLite.Stmt, params::NamedTuple{(), Tuple{}})
    @ DBInterface C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:135
  [9] execute(f::Function, conn::SQLite.DB, sql::String, params::NamedTuple{(), Tuple{}})
    @ DBInterface C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:144
 [10] #execute#3
    @ C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:153 [inlined]
 [11] execute
    @ C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:153 [inlined]
 [12] tableinfo
    @ C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\tables.jl:165 [inlined]
 [13] load!(itr::DataFrame, db::SQLite.DB, name::String; kwargs::Base.Iterators.Pairs{Union{}, Union{}, Tuple{}, NamedTuple{(), Tuple{}}})
    @ SQLite C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\tables.jl:195
 [14] load!
    @ C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\tables.jl:195 [inlined]
 [15] #26
    @ C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\tables.jl:191 [inlined]
 [16] |>(x::DataFrame, f::SQLite.var"#26#27"{Base.Iterators.Pairs{Union{}, Union{}, Tuple{}, NamedTuple{(), Tuple{}}}, SQLite.DB, String})
    @ Base .\operators.jl:858
 [17] top-level scope
    @ REPL[5]:1

[jeremiahpslewis]

Ok, one more try to strip away DataFrames.jl:

using SQLite

db = SQLite.DB()
tbl = (a = [1, 2, 3], b = ["a", "b", "c"])
tbl |> SQLite.load!(db, "table1")
tbl |> SQLite.load!(db, "table1")

[NicholasWMRitchie]

The tables(db) method seems to be the culprit.

using SQLite
db = SQLite.DB()
tbl = (a = [1, 2, 3], b = ["a", "b", "c"])
tbl |> SQLite.load!(db, "table1")
SQLite.tables(db)

ERROR: MethodError: promote_type(::Type{Union{}}, ::Type{String}) is ambiguous. Candidates:
  promote_type(::Type{Union{}}, ::Type{T}) where T in Base at promotion.jl:224
  promote_type(::Type{T}, ::Type{String}) where T<:WeakRefStrings.InlineString in WeakRefStrings at C:\Users\nritchie\.julia\packages\WeakRefStrings\a3jYm\src\inlinestrings.jl:44
Possible fix, define
  promote_type(::Type{Union{}}, ::Type{String})
Stacktrace:
  [1] add_or_widen!(val::String, col::Int64, nm::Symbol, dest::Tables.EmptyVector, row::Int64, updated::Base.RefValue{Any}, L::Base.SizeUnknown)
    @ Tables C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:150
  [2] eachcolumns
    @ C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\utils.jl:127 [inlined]
  [3] _buildcolumns(rowitr::SQLite.Query, row::SQLite.Row, st::Int64, sch::Tables.Schema{(:name,), nothing}, columns::Tuple{Tables.EmptyVector}, updated::Base.RefValue{Any})
    @ Tables C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:187
  [4] buildcolumns
    @ C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:217 [inlined]
  [5] columns
    @ C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\fallbacks.jl:253 [inlined]
  [6] columntable(itr::SQLite.Query)
    @ Tables C:\Users\nritchie\.julia\packages\Tables\OWzlh\src\namedtuples.jl:171
  [7] execute(f::typeof(Tables.columntable), stmt::SQLite.Stmt, params::NamedTuple{(), Tuple{}})
    @ DBInterface C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:135
  [8] execute(f::Function, conn::SQLite.DB, sql::String, params::NamedTuple{(), Tuple{}})
    @ DBInterface C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:144
  [9] #execute#3
    @ C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:153 [inlined]
 [10] execute
    @ C:\Users\nritchie\.julia\packages\DBInterface\1Gmxx\src\DBInterface.jl:153 [inlined]
 [11] tables (repeats 2 times)
    @ C:\Users\nritchie\.julia\packages\SQLite\5DdLp\src\SQLite.jl:665 [inlined]
 [12] top-level scope
    @ REPL[5]:1

[jeremiahpslewis]

Seems like we're getting closer. But it also seems less likely to be an issue with the Tables function alone, given that it works on other platforms. Could it be the sqlite_jll binary which is used on windows returning something SQLite package can't parse?

Two things one could try:

1. See what sqllite_jll version you have installed

using Pkg; Pkg.status(mode= PKGMODE_MANIFEST)

2. Create a new environment and try (re)installing SQLite again?

[NicholasWMRitchie]

Two things:
First, [76ed43ae] SQLite_jll v3.36.1+0
Second, installing only DataFrames and SQLite into a new environments works without error.

Now, its time for me to figure out which package in my project.toml is causing the issue.

[NicholasWMRitchie]

So I start to add some packages. I get the failures again. So I remove all the extra packages leaving only SQLite and DataFrames. It fails again (even after restarting.) Very confusing!!

[quinnj]

This is an error from the InlineStrings package that has been fixed, so if you do ]up InlineStrings that should fix it

[NicholasWMRitchie]

I'm confused. My fully loaded manifest.toml doesn't even mention InlineStrings. SQLite doesn't use InlineStrings but it does use WeakRefStrings.

[quinnj]

CSV uses them when reading into a DataFrame. I would think you just need to do ] up then to get InlineString to update.

[NicholasWMRitchie]

I've actually been pegged at CSV 0.8.5 (something I need to resolve) which doesn't use InlineString.

[NicholasWMRitchie]

Removing the block that was prohibiting the update of CSV beyond 0.8.5 and updating CSV to 0.9.10 seems resolve the original issue. This also caused InlineStrings to be installed whereas it hadn't previously.
I have run a number of different tests that had previously failed. They now work.
I suspect that an old version of CSV was the culprit and that the issue can be closed.

[NicholasWMRitchie]

Resolved - Not a bug in SQLite but seemingly the result of a adverse interaction with another library.