We are still using sqlite 3.7.14.1 here, so I can't verify what the current code does.
Support for virtual tables has been much extended, adding (OTTOMH): - conflict resolution algorithm - support for unchanged columns in UPDATE statements - more detailed xBestIndex return values (# of estimated rows, UNIQUE flag) - collation sequence support VT implementations written before collation sequence support should be seen as supporting only BINARY. Since VT are by definition user defined, the same user has control over the queries. Changing the query to use a different collation sequence than the default BINARY requires changing the VT implementation to support that. Or at least check for BINARY and just not return that index number if a different sequence is required. I cannot check what current SQLite fills into the p_idx structure in the case of "SELECT * FROM vt ORDER BY field(s)". Adding fields from the ORDER BY but leaving the "usable" bit unset would solve the problem -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:[email protected]] Im Auftrag von Dominique Devienne Gesendet: Donnerstag, 28. Juni 2018 12:56 An: General Discussion of SQLite Database <[email protected]> Betreff: Re: [sqlite] [EXTERNAL] order by column_name collate custom_collation, with virtual table index On Thu, Jun 28, 2018 at 11:48 AM Dominique Devienne <[email protected]> wrote: > > On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter <[email protected]> wrote: >> >> The xBestIndex function needs to call the sqlite_vtab_collation() function to query the collation name required for each constraint and return the appropriate index number. >> >> Subs: yes, yes, see above > > > Oh, great! Thanks Gunther!!! > > Richard, may I suggest > https://www.sqlite.org/c3ref/vtab_collation.html to be mentioned or linked somewhere in https://www.sqlite.org/vtab.html? TIA. --DD Hmmm, on second thought, https://www.sqlite.org/c3ref/vtab_collation.html seems to work with constraints only (i.e. WHERE clause), and not the sqlite3_index_info.aOrderBy[]... The second argument must be an index into the aConstraint[] array belonging > to the sqlite3_index_info structure passed to xBestIndex So SQLite cannot reliably use a vindex to optimize an Order By if a custom collation is used in the query? Is that a latent bug? And apparently this returns the collation of the query, instead of providing the vindex's "own" collation. Which implies that it's the xBestIndex impl that supposed to rule out the vindex, not SQLite itself? In other words, any xBestIndex impl which does *NOT* call sqlite3_vtab_collation is necessarily buggy, if one day a query using a different collation (than the vindex) somehow uses a plan using that vindex? There seems to be a lot of subtle things to consider here. Can more light be shed on this? In the vtab.html doc? Thanks, --DD _______________________________________________ sqlite-users mailing list [email protected] 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 [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
