Search semantics with a virtual table?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Search semantics with a virtual table?

Deon Brewis
How do I convince SQLITE to give me SEARCH semantics over a virtual table instead of SCAN semantics?

e.g. I have:

explain query plan SELECT * FROM vtable WHERE value = 12345

0 | 0 | 0 | SCAN TABLE vtable VIRTUAL TABLE INDEX 1:

And I'm returning in xBestIndex:
   pIdxInfo->idxNum = 1;
   pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;
   pIdxInfo->aConstraintUsage[0].argvIndex = 1;
   pIdxInfo->aConstraintUsage[0].omit = true;
   pIdxInfo->estimatedCost = 1;
   pIdxInfo->estimatedRows = 1;


So obviously the explain might just be iffy, but I'm actually getting SCAN semantics. Well, somewhere between SEARCH & SCAN.

I can perform a true search for the '12345' item between xBestIndex & xFilter, but after I returned the first item, SQLITE keeps calling back my xNext/xEof to ask for more and more items. However... I'm specifying SQLITE_INDEX_SCAN_UNIQUE so why doesn't it stop after I returned the first one?

Lacking convincing it to call me more than once... I then tried to tell it that the results I return are ordered and maybe it will notice once it runs beyond the range. Since there's no way to return an unsolicited order in xBestIndex, I've tried:

SELECT * FROM vtable WHERE value = 12345 ORDER BY value

And then responded:
  pIdxInfo->orderByConsumed = 1;


to the request. But nada - it still keeps calling me over and over.

I know I can hack around that by mucking around in my xEof call, but is that the only way? It really does seem like SQLITE_INDEX_SCAN_UNIQUE (or something similar) should achieve SEARCH semantics directly?

- Deon

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

Re: Search semantics with a virtual table?

Hick Gunter
The SQLITE_INDEX_SCAN_UNIQUE flag is a hint for the query planner. It does not affect query execution mechanics. You should be returning TRUE from xEOF after the first call to your xNext function (provided indeed that there is only 1 row that matches the value). Or you need to add a LIMIT 1 clause, so that SQLIte will not attempt to read more than 1 record.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Deon Brewis
Gesendet: Montag, 03. April 2017 15:52
An: SQLite mailing list <[hidden email]>
Betreff: [sqlite] Search semantics with a virtual table?

How do I convince SQLITE to give me SEARCH semantics over a virtual table instead of SCAN semantics?

e.g. I have:

explain query plan SELECT * FROM vtable WHERE value = 12345

0 | 0 | 0 | SCAN TABLE vtable VIRTUAL TABLE INDEX 1:

And I'm returning in xBestIndex:
   pIdxInfo->idxNum = 1;
   pIdxInfo->idxFlags = SQLITE_INDEX_SCAN_UNIQUE;
   pIdxInfo->aConstraintUsage[0].argvIndex = 1;
   pIdxInfo->aConstraintUsage[0].omit = true;
   pIdxInfo->estimatedCost = 1;
   pIdxInfo->estimatedRows = 1;


So obviously the explain might just be iffy, but I'm actually getting SCAN semantics. Well, somewhere between SEARCH & SCAN.

I can perform a true search for the '12345' item between xBestIndex & xFilter, but after I returned the first item, SQLITE keeps calling back my xNext/xEof to ask for more and more items. However... I'm specifying SQLITE_INDEX_SCAN_UNIQUE so why doesn't it stop after I returned the first one?

Lacking convincing it to call me more than once... I then tried to tell it that the results I return are ordered and maybe it will notice once it runs beyond the range. Since there's no way to return an unsolicited order in xBestIndex, I've tried:

SELECT * FROM vtable WHERE value = 12345 ORDER BY value

And then responded:
  pIdxInfo->orderByConsumed = 1;


to the request. But nada - it still keeps calling me over and over.

I know I can hack around that by mucking around in my xEof call, but is that the only way? It really does seem like SQLITE_INDEX_SCAN_UNIQUE (or something similar) should achieve SEARCH semantics directly?

- Deon

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


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


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