On Wednesday, 5 February, 2020 18:10, Jens Alfke <j...@mooseyard.com>:

>> On Feb 5, 2020, at 9:58 AM, Keith Medcalf <kmedc...@dessus.com> wrote:

>> It seems that "column affinities" are not respected in Virtual Table
>> implementations -- that is the value that is returned is the datatype
>> provided by the the vtab_cursor sqlite3_result_* function and the "column
>> affinity" from the vtab declaration is not applied.

> The vtab implementation is responsible for generating the CREATE TABLE
> statement and passing it to sqlite3_declare_vtab(). It’s also responsible
> for returning column values. So I think the assumption is that it’s up to
> the implementation to be self-consistent, i.e. returning column values
> that match the declaration.

That would make perfect sense except that the documentation for the 
sqlite_vtab_declare function specifically states that only the column name and 
type affinity are used, and that other things (ie, constraints, defaults, etc) 
that may be supplied in a vtab declaration are ignored.  Why go to all the 
bother of parsing the affinity and allowing it to be supplied if it is not used?

> I haven’t used the CSV vtable. It looks as though its CREATE VIRTUAL
> TABLE statement takes a ‘schema’ parameter containing the SQL table
> declaration, that it then passes straight through to
> sqlite3_declare_vtab(). It probably doesn’t parse that declaration or
> figure out from it what the declared types of the columns are.

Actually it does, and this is documented for the sqlite3_declare_vtab function 
-- and in fact the column names and affinities are parsed and stored in the 
internal Table schema.  The column names are used but the executed VDBE program 
does not "apply affinity".

>In other words this looks like a limitation of the CSV implementation,
>which is perhaps unusual in that it is not in control of the table schema
>it declares.

Yes it is, but easily fixed.  As far as I can tell it should not affect vtabs 
that are part of the SQLite3 distribution, but I do not know if there are 
third-party virtual tables that specify column affinities in the vtab 
declarations rather than just use blob (or no affinity).

I would expect that most vtab writers write consistent code and this is would 
not be an issue at all.  The CSV vtab is kind of a special case where it is 
returning arbitrary external data over which it has no control so the 
application of affinity is probably worthwhile.  In cases where it does not 
matter what the actual prefered value affinity is the vtab can still be 
declared as blob affinity (ie, no affinity applied).

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume. 



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to