order by column_name collate custom_collation, with virtual table index

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

order by column_name collate custom_collation, with virtual table index

Dominique Devienne
From reading this list, I've learned that for an index to have a change to
be used to consume an order by, the collation of the query and the index
must match.

But in many instances, that index is one from a virtual table we implement.
So is there a way to tell SQLite that vindex is of a given custom collation,
to open the possibility of the index being used?

FWIW, the collation is a "natural order" one, i.e. a1, a2, ..., a10, ...,
a19, a20, ..., a100.
Right now the vindex is lexicographic, not "natural order", but of I can
have SQLite use
it somehow, I can easily change my vindex to  "natural order" too.

Subsidiary questions:
Can one have two indexes on the same column with different collations?
And thus have SQLite consider these alternate indexes depending on queries
collations?
Which goes back to my question about how telling SQLite about a vindex's
collation.

Thanks, --DD
_______________________________________________
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: order by column_name collate custom_collation, with virtual table index

Dominique Devienne
On Thu, Jun 28, 2018 at 10:59 AM Dominique Devienne <[hidden email]>
wrote:

> So is there a way to tell SQLite that vindex is of a given custom
> collation,
> to open the possibility of the index being used?
>

Note that there's no mention at all of "collation" or "collate" in
https://www.sqlite.org/vtab.html
so this might once again be one of these corners of SQLite vtables where
functionality in not
on-par with "real" tables and indexes. I hope I'm wrong though, and hope to
hear about it here. --DD
_______________________________________________
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] order by column_name collate custom_collation, with virtual table index

Hick Gunter
In reply to this post by Dominique Devienne
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


-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Donnerstag, 28. Juni 2018 11:00
An: General Discussion of SQLite Database <[hidden email]>
Betreff: [EXTERNAL] [sqlite] order by column_name collate custom_collation, with virtual table index

From reading this list, I've learned that for an index to have a change to be used to consume an order by, the collation of the query and the index must match.

But in many instances, that index is one from a virtual table we implement.
So is there a way to tell SQLite that vindex is of a given custom collation, to open the possibility of the index being used?

FWIW, the collation is a "natural order" one, i.e. a1, a2, ..., a10, ..., a19, a20, ..., a100.
Right now the vindex is lexicographic, not "natural order", but of I can have SQLite use it somehow, I can easily change my vindex to  "natural order" too.

Subsidiary questions:
Can one have two indexes on the same column with different collations?
And thus have SQLite consider these alternate indexes depending on queries collations?
Which goes back to my question about how telling SQLite about a vindex's collation.

Thanks, --DD
_______________________________________________
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: [EXTERNAL] order by column_name collate custom_collation, with virtual table index

Dominique Devienne
On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter <[hidden email]> 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
_______________________________________________
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] order by column_name collate custom_collation, with virtual table index

Dominique Devienne
On Thu, Jun 28, 2018 at 11:48 AM Dominique Devienne <[hidden email]>
wrote:
>
> On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: [EXTERNAL] order by column_name collate custom_collation, with virtual table index

Hick Gunter
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:[hidden email]] Im Auftrag von Dominique Devienne
Gesendet: Donnerstag, 28. Juni 2018 12:56
An: General Discussion of SQLite Database <[hidden email]>
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 <[hidden email]>
wrote:
>
> On Thu, Jun 28, 2018 at 11:18 AM Hick Gunter <[hidden email]> 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
[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: order by column_name collate custom_collation, with virtual table index

Richard Hipp-3
In reply to this post by Dominique Devienne
On 6/28/18, Dominique Devienne <[hidden email]> wrote:
> From reading this list, I've learned that for an index to have a change to
> be used to consume an order by, the collation of the query and the index
> must match.
>
> But in many instances, that index is one from a virtual table we implement.
> So is there a way to tell SQLite that vindex is of a given custom collation,
> to open the possibility of the index being used?

The only way to avoid sorting the output of a virtual table is for the
xBestIndex routine to set the sqlite3_index_info.orderByConsumed
boolean.

--
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: order by column_name collate custom_collation, with virtual table index

Dominique Devienne
On Thu, Jun 28, 2018 at 2:03 PM Richard Hipp <[hidden email]> wrote:

> On 6/28/18, Dominique Devienne <[hidden email]> wrote:
> > From reading this list, I've learned that for an index to have a change
> to
> > be used to consume an order by, the collation of the query and the index
> > must match.
> >
> > But in many instances, that index is one from a virtual table we
> implement.
> > So is there a way to tell SQLite that vindex is of a given custom
> collation,
> > to open the possibility of the index being used?
>
> The only way to avoid sorting the output of a virtual table is for the
> xBestIndex routine to set the sqlite3_index_info.orderByConsumed boolean.
>

My point is more than when doing so, i.e. setting  orderByConsumed to
1/true,
there's no way that I can see for the vtable to know the collation used by
the query,
to validate whether it matches the vindex's own ordering. Or am I missing
something?

sqlite3_vtab_collation that Gunther pointed me to, works for  aConstraint[]
only,
according to the doc, so there's no way to know the aOrderBy[]
collation(s). --DD
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users