xRowid and read only virtual tables....

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

xRowid and read only virtual tables....

dave
Hi, I am building a system which involves a number of virtual table
implementations.  They are all read-only, but will be involved in a bunch of
joins amongst themselves.  My question is this:
 
the documentation
  http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid
seems (to my reading) to be always required to be implemented.  But does it
really?  Is it ever used for read-only tables?  I have never seen it
invoked, and I have been blithely ignoring implementing it, but I wonder if
there is a case where it would be invoked for a read-only query and so I am
tempting fate.
 
I ask in particular because implementing it will be quite awkward for the
underlying implementation in my case, and I'd very much prefer to skip it.
Even a 'without rowid' table would imply specifying some primary key, which
in a few cases would also be awkward.
 
Thanks in advance,
 
-dave
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] xRowid and read only virtual tables....

Hick Gunter
In our virtual table implementations, we are using the rowid to return the location of the record in the backing store (e.g. record offset in the file used as a backing store, offset within a shared memory section or maybe even the memory address of the record image) and also implement fast lookup by rowid.

If you don't require such ability, you may as well return a constant, a global counter value or a counter that is reset in the xFilter function.

So, YES you always have to implement the xRowid method.

It will only get called if your SELECT statement explicitly mentions it. No "INTEGER PRIMARY KEY" magic is performed for virtual tables.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von dave
Gesendet: Montag, 16. Oktober 2017 21:23
An: 'SQLite mailing list' <[hidden email]>
Betreff: [EXTERNAL] [sqlite] xRowid and read only virtual tables....

Hi, I am building a system which involves a number of virtual table implementations.  They are all read-only, but will be involved in a bunch of joins amongst themselves.  My question is this:

the documentation
  http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid seems (to my reading) to be always required to be implemented.  But does it really?  Is it ever used for read-only tables?  I have never seen it invoked, and I have been blithely ignoring implementing it, but I wonder if there is a case where it would be invoked for a read-only query and so I am tempting fate.

I ask in particular because implementing it will be quite awkward for the underlying implementation in my case, and I'd very much prefer to skip it.
Even a 'without rowid' table would imply specifying some primary key, which in a few cases would also be awkward.

Thanks in advance,

-dave
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: xRowid and read only virtual tables....

Richard Hipp-3
In reply to this post by dave
On 10/16/17, dave <[hidden email]> wrote:

> Hi, I am building a system which involves a number of virtual table
> implementations.  They are all read-only, but will be involved in a bunch of
> joins amongst themselves.  My question is this:
>
> the documentation
>   http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid
> seems (to my reading) to be always required to be implemented.  But does it
> really?  Is it ever used for read-only tables?  I have never seen it
> invoked, and I have been blithely ignoring implementing it, but I wonder if
> there is a case where it would be invoked for a read-only query and so I am
> tempting fate.

I don't think xRowid is ever called if you create a WITHOUT ROWID
virtual table (https://sqlite.org//vtab.html#worid).  But, just to be
safe, I think I would include a stub function that always returned 0.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: xRowid and read only virtual tables....

dave
> On 10/16/17, dave <[hidden email]> wrote:
> > Hi, I am building a system which involves a number of virtual table
> > implementations.  They are all read-only, but will be
> involved in a bunch of
> > joins amongst themselves.  My question is this:
> >
> > the documentation
> >   http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid
> > seems (to my reading) to be always required to be
> implemented.  But does it
> > really?  Is it ever used for read-only tables?  I have never seen it
> > invoked, and I have been blithely ignoring implementing it,
> but I wonder if
> > there is a case where it would be invoked for a read-only
> query and so I am
> > tempting fate.
>
> I don't think xRowid is ever called if you create a WITHOUT ROWID
> virtual table (https://sqlite.org//vtab.html#worid).  But, just to be
> safe, I think I would include a stub function that always returned 0.
> --
> D. Richard Hipp

Thanks. OK, I am interpreting that to mean:
*  you could use WITHOUT ROWID, which will surely obviate the need for a
valid xRowid implementation. However that does incur the need for defining
PRIMARY KEY, etc.  (I have verified this. It is problemattic with at least a
few of my vtables)
*  for a read-only vtable, you do not need a valid imlementation of xRowid,
you can just stub it.  However it is required to be present (i.e. the xRowid
member must not be NULL).

-dave


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] xRowid and read only virtual tables....

Dan Kennedy-4
In reply to this post by Hick Gunter
On 10/17/2017 01:22 PM, Hick Gunter wrote:
> In our virtual table implementations, we are using the rowid to return the location of the record in the backing store (e.g. record offset in the file used as a backing store, offset within a shared memory section or maybe even the memory address of the record image) and also implement fast lookup by rowid.
>
> If you don't require such ability, you may as well return a constant, a global counter value or a counter that is reset in the xFilter function.
>
> So, YES you always have to implement the xRowid method.
>
> It will only get called if your SELECT statement explicitly mentions it. No "INTEGER PRIMARY KEY" magic is performed for virtual tables.

I think the exception is queries with OR terms. With FTS[345], if you do
something like:

   CREATE VIRTUAL TABLE t1 USING fts5(x);
   EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def';

You can see the Rowid opcodes.

SQLite runs two separate queries on the virtual table - one with "MATCH
'abc'" and the other with "MATCH 'def'". It uses the rowids for each
matched row to avoid returning duplicates. If the xRowid method always
returned 0, then only the first set of matches would be returned
(because SQLite would deem the second set to be duplicates of the
first). Or if xRowid returned arbitrary values your results might
include duplicates. etc.

Same applies to other virtual table types.

Dan.













>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von dave
> Gesendet: Montag, 16. Oktober 2017 21:23
> An: 'SQLite mailing list' <[hidden email]>
> Betreff: [EXTERNAL] [sqlite] xRowid and read only virtual tables....
>
> Hi, I am building a system which involves a number of virtual table implementations.  They are all read-only, but will be involved in a bunch of joins amongst themselves.  My question is this:
>
> the documentation
>    http://sqlite.org/vtab.html#tabfunc2 at 2.12 xRowid seems (to my reading) to be always required to be implemented.  But does it really?  Is it ever used for read-only tables?  I have never seen it invoked, and I have been blithely ignoring implementing it, but I wonder if there is a case where it would be invoked for a read-only query and so I am tempting fate.
>
> I ask in particular because implementing it will be quite awkward for the underlying implementation in my case, and I'd very much prefer to skip it.
> Even a 'without rowid' table would imply specifying some primary key, which in a few cases would also be awkward.
>
> Thanks in advance,
>
> -dave
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] xRowid and read only virtual tables....

dave
> Behalf Of Dan Kennedy
> Sent: Tuesday, October 17, 2017 11:58 AM
>
> On 10/17/2017 01:22 PM, Hick Gunter wrote:
> > In our virtual table implementations, we are using the
> rowid to return the location of the record in the backing
> store (e.g. record offset in the file used as a backing
> store, offset within a shared memory section or maybe even
> the memory address of the record image) and also implement
> fast lookup by rowid.
> >
> > If you don't require such ability, you may as well return a
> constant, a global counter value or a counter that is reset
> in the xFilter function.
> >
> > So, YES you always have to implement the xRowid method.
> >
> > It will only get called if your SELECT statement explicitly
> mentions it. No "INTEGER PRIMARY KEY" magic is performed for
> virtual tables.
>
> I think the exception is queries with OR terms. With
> FTS[345], if you do
> something like:
>
>    CREATE VIRTUAL TABLE t1 USING fts5(x);
>    EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def';
>
> You can see the Rowid opcodes.
>
> SQLite runs two separate queries on the virtual table - one
> with "MATCH
> 'abc'" and the other with "MATCH 'def'". It uses the rowids for each
> matched row to avoid returning duplicates. If the xRowid
> method always
> returned 0, then only the first set of matches would be returned
> (because SQLite would deem the second set to be duplicates of the
> first). Or if xRowid returned arbitrary values your results might
> include duplicates. etc.
>
> Same applies to other virtual table types.
>
> Dan.

Yikes, thanks for the insight on that OR use-case; I'll have to do some
analysis to see what is my exposure.

The counter trick is an interesting suggestion, but I guess I am still
at-risk because I have to make it deterministic/repeatable at least in the
context of a statement, which can still a challenge.  E.g. in your OR clause
example, if the query engine does indeed do two table scans (as opposed to
one table scan, and computing all the predicates) then I have some risk that
the two scans return different results (since my data is coming from APIs,
and dynamic, rather that persisted collections).

In other projects I've definitely used the counter trick before, caching the
underlying data (to support updates and transactions) but those were known
to be small datasets.  This stuff coming from APIs could be big, so I wanted
to avoid caching it all.  But one does what one must....

Thanks for all the feedback!

-dave


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] xRowid and read only virtual tables....

dave
In reply to this post by Hick Gunter
> -----Original Message-----
> From: sqlite-users
> [mailto:[hidden email]] On
> Behalf Of Hick Gunter
>
> In our virtual table implementations, we are using the rowid
> to return the location of the record in the backing store
> (e.g. record offset in the file used as a backing store,
> offset within a shared memory section or maybe even the
> memory address of the record image) and also implement fast
> lookup by rowid.
>
> If you don't require such ability, you may as well return a
> constant, a global counter value or a counter that is reset
> in the xFilter function.
>
> So, YES you always have to implement the xRowid method.
>
> It will only get called if your SELECT statement explicitly
> mentions it. No "INTEGER PRIMARY KEY" magic is performed for
> virtual tables.
>
Thanks for your input as well; I somehow missed it until just now.
Cheers!
-dave


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] xRowid and read only virtual tables....

dave
In reply to this post by Dan Kennedy-4
> Behalf Of Dan Kennedy
> Sent: Tuesday, October 17, 2017 11:58 AM
...

>
> I think the exception is queries with OR terms. With
> FTS[345], if you do
> something like:
>
>    CREATE VIRTUAL TABLE t1 USING fts5(x);
>    EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def';
>
> You can see the Rowid opcodes.
>
> SQLite runs two separate queries on the virtual table - one
> with "MATCH
> 'abc'" and the other with "MATCH 'def'". It uses the rowids for each
> matched row to avoid returning duplicates. If the xRowid
> method always
> returned 0, then only the first set of matches would be returned
> (because SQLite would deem the second set to be duplicates of the
> first). Or if xRowid returned arbitrary values your results might
> include duplicates. etc.
>
> Same applies to other virtual table types.
...

FYI FWIW, I had a moment to play with this a little.  I was able to
reproduce Dan's case, however I'm not so sure that it is due to the OR (or
at least not only that).  I think it maybe has more to do with the OR of
MATCH's.

I tried with one of my vtables using an 'OR' clause, and I got no rowid
opcodes.  I was using equality, however.  There were two scenarios:
1)  OR clause on a column that is indexed
  this generated two table scans, with different filter values
2)  OR clause on a column that was /not/ indexed
  this generated one table scan, with both conditionals evaluated on the
same row
Those seemed like sane plans.  For fun I also tried 'IN' with the exact same
results.

I'm less familiar with MATCH, but I understand what Dan is saying about
de-duping.  I don't understand why the planner would have chosen to realize
OR as a set union, but I'm sure it has it's reasons.  I should study the
query planner implementation one day when I have some time....

Cheers!
-dave


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] xRowid and read only virtual tables....

Hick Gunter
If your xBestIndex function indicates that your virtual table supports an index on the constraint with cost x and you have a single OR clause, the QP will assign a cost of 2*x to performing 2 keyed lookups/partial table scans

If your XbestIndex function indicates that your virtual tabel does no support an index on the constraint, the QP will revert to a full table scan with a subsequent check of the constraints. The option of creating an index "on the fly" does not exist for virtual tables.

An IN clause is always implemented as an ephemeral table (the ONCE opcode ensures that the values given are inserted before the first query), so this forces SQLite to use a full table scan of the virtual table.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von dave
Gesendet: Samstag, 21. Oktober 2017 02:07
An: 'SQLite mailing list' <[hidden email]>
Betreff: Re: [sqlite] [EXTERNAL] xRowid and read only virtual tables....

> Behalf Of Dan Kennedy
> Sent: Tuesday, October 17, 2017 11:58 AM
...

>
> I think the exception is queries with OR terms. With FTS[345], if you
> do something like:
>
>    CREATE VIRTUAL TABLE t1 USING fts5(x);
>    EXPLAIN SELECT x FROM t1 WHERE t1 MATCH 'abc' OR t1 MATCH 'def';
>
> You can see the Rowid opcodes.
>
> SQLite runs two separate queries on the virtual table - one with
> "MATCH 'abc'" and the other with "MATCH 'def'". It uses the rowids for
> each matched row to avoid returning duplicates. If the xRowid method
> always returned 0, then only the first set of matches would be
> returned (because SQLite would deem the second set to be duplicates of
> the first). Or if xRowid returned arbitrary values your results might
> include duplicates. etc.
>
> Same applies to other virtual table types.
...

FYI FWIW, I had a moment to play with this a little.  I was able to reproduce Dan's case, however I'm not so sure that it is due to the OR (or at least not only that).  I think it maybe has more to do with the OR of MATCH's.

I tried with one of my vtables using an 'OR' clause, and I got no rowid opcodes.  I was using equality, however.  There were two scenarios:
1)  OR clause on a column that is indexed
  this generated two table scans, with different filter values
2)  OR clause on a column that was /not/ indexed
  this generated one table scan, with both conditionals evaluated on the same row Those seemed like sane plans.  For fun I also tried 'IN' with the exact same results.

I'm less familiar with MATCH, but I understand what Dan is saying about de-duping.  I don't understand why the planner would have chosen to realize OR as a set union, but I'm sure it has it's reasons.  I should study the query planner implementation one day when I have some time....

Cheers!
-dave


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users