SQLite, DataFrame and Missing


Julia is wonderful, but this got me stymied

I am reading a sqlite table into a dataframe, where one column is
r real
and all values are missing. I use
df=DBInterface.execute(db, sql) |> DataFrame;
and get df.r to be of type missing (think it should be Union{Missing,Float64} )
and then I am unable to change any values in df.r as it is missing
when I try to use the tricks suggested here (DataFrames: convert column data type - #50 by Skoffer) to convert the column, it always just gives me missing back, like

I can bypass this by making a new column like Vector{Union{Missing,Float64}}(undef,size(df)[1]) if sum(ismissing.(df.r))==size(df)[1], but that is horribly clumcy.

SQLite.jl returns the correct type, but it fails when converted to a dataframe.
So, I was wondering if there was a way to make SQLite.jl honor the sql types when read into a dataframe, or convert a dataframe with all missing to a union of missing and float?

all the best, Jack

This should be fixable. Though I’m not a maintainer of SQLite.jl, it can probably be fixed there. Please file an issue so the maintainers see this.

Do convert(Vector{Union{Missing, Float64}, df.r) instead.

But I agree with @pdeffebach that something seems to be wrong in SQLite.jl or DBInterface.jl.


thank you both, @pdeffebach I filed an issue in SQlite, and @nalimilan , makes sense. To my untrained eye, both should do the same, but I can appreciate the subtlety in the difference.

best, wishes, Jack.