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:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Deon Brewis Gesendet: Montag, 03. April 2017 15:52 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> 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 sqlite-users@mailinglists.sqlite.org 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: h...@scigames.at 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
