[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On 2015-03-06 03:19 PM, Richard Hipp wrote: > Yikes! Actually (1) cannot be determined for normal (non-virtual) > tables either because the value could be a BLOB even if the column > affinity is TEXT. And so the current LIKE optimization is not valid > *ever*. See ticket https://www.sqlite.org/src/tktview/05f43be8fdda9f I > don't yet know how we will fix this... True, but who uses LIKE on a BLOB? Any such usage has to be by accident - though granted, this can happen. Maybe a simple documentation note stating that LIKE operator on BLOB values will have undefined results?
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
Using LIKE on a BLOB is not the problem. It is the LIKE optimization that is broken, because it requires a BLOB to sort AFTER a text, which is never the case, while the LIKE function compares an expression that may contain wildcards to the raw data, which may be the case. -Urspr?ngliche Nachricht- Von: R.Smith [mailto:rsmith at rsweb.co.za] Gesendet: Freitag, 06. M?rz 2015 14:25 An: sqlite-users at mailinglists.sqlite.org Betreff: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On 2015-03-06 03:19 PM, Richard Hipp wrote: > Yikes! Actually (1) cannot be determined for normal (non-virtual) > tables either because the value could be a BLOB even if the column > affinity is TEXT. And so the current LIKE optimization is not valid > *ever*. See ticket https://www.sqlite.org/src/tktview/05f43be8fdda9f I > don't yet know how we will fix this... True, but who uses LIKE on a BLOB? Any such usage has to be by accident - though granted, this can happen. Maybe a simple documentation note stating that LIKE operator on BLOB values will have undefined results? ___ sqlite-users mailing list sqlite-users at 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: hick at 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] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On Fri, Mar 6, 2015 at 1:21 PM, Hick Gunter wrote: > And then there remain to be considered the effects of the pragma > CASE_SENSITIVE_LIKE > Good point. But that's no different from the case when an application overrides the LIKE behavior via a custom function, and the vtable can similarly decide to ignore it or not; Or can even decide changing like the semantic of LIKE, again just like an application overrides. Which is way I think the point you raise is tangential to SQLite not giving vtables the opportunity to optimize LIKE. --DD
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
And then there remain to be considered the effects of the pragma CASE_SENSITIVE_LIKE -Urspr?ngliche Nachricht- Von: Dominique Devienne [mailto:ddevienne at gmail.com] Gesendet: Freitag, 06. M?rz 2015 10:30 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On Fri, Mar 6, 2015 at 10:12 AM, R.Smith wrote: > On 2015-03-06 09:42 AM, Dominique Devienne wrote: > >> On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: >> >> >>> The LIKE operator can be overridden by the application to mean >>> anything the application wants - it is not compelled to follow >>> standard SQL semantics. For that reason, virtual tables are unable >>> to optimize using LIKE since they have no way of knowing what it will do. >>> >>> Works as designed. >>> >>> Sure. But SQLite knows whether LIKE is overriden by the application >>> or >> not, >> so shouldn't it only hide LIKE from the virtual table when it detects >> it, instead of wholesale preventing the vtable from optimizing the >> "normal semantic" LIKE case? My $0.02. --DD >> > > I'm sure this is possible, but how would it be feasible? If this was > implemented, how would you know as a programmer designing a virtual > table (or many other interface things) whether or not the target you > operate on will/will not be able to run some bit of code? Even if you > could query the API to find out whether it is possible (i.e. the > normal LIKE is used), you still need to use that result as a specifier > to decide which code block to implement. And if you are going to have > to do two code blocks... might as well do the one where LIKE isn't supported > right from the start. > > Now if there was a setting where you could /force/ the standard LIKE > to be used along with supporting the v-table interface, that might be > something, but that might break a whole other universe of > possibilities for v-table users and end-user customization. That's not what I was saying. DRH is saying the vtable *never* sees the LIKE and SQLite itself a-posteriori filters out the rows based on LIKE. The vtable is not aware it's been denied seeing the LIKE. I'm saying that (in the vast majority of) cases when LIKE is not overriden by the application (globally or for that vtable), something SQLite can know, SQLite could pass the LIKE constraint to the xBestIndex, and let the vtable decide whether it can handle (i.e. optimize) LIKE or not, and whether SQLite should double-check it or not (as usual for vtable indexing). When it is overriden, it behaves as now. But that way the vtable has at least the opportunity to optimize LIKE, unlike now. Whether that's a priority, obviously not given what we've read so far, but whether that's technically possible, from 30,000 ft I don't see why not given the above. My $0.02. --DD ___ sqlite-users mailing list sqlite-users at 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: hick at 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] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On 2015-03-06 09:42 AM, Dominique Devienne wrote: > On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: > >> >> The LIKE operator can be overridden by the application to mean >> anything the application wants - it is not compelled to follow >> standard SQL semantics. For that reason, virtual tables are unable to >> optimize using LIKE since they have no way of knowing what it will do. >> >> Works as designed. >> > Sure. But SQLite knows whether LIKE is overriden by the application or not, > so shouldn't it only hide LIKE from the virtual table when it detects it, > instead of wholesale preventing the vtable from optimizing the "normal > semantic" LIKE case? My $0.02. --DD I'm sure this is possible, but how would it be feasible? If this was implemented, how would you know as a programmer designing a virtual table (or many other interface things) whether or not the target you operate on will/will not be able to run some bit of code? Even if you could query the API to find out whether it is possible (i.e. the normal LIKE is used), you still need to use that result as a specifier to decide which code block to implement. And if you are going to have to do two code blocks... might as well do the one where LIKE isn't supported right from the start. Now if there was a setting where you could /force/ the standard LIKE to be used along with supporting the v-table interface, that might be something, but that might break a whole other universe of possibilities for v-table users and end-user customization.
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On Fri, Mar 6, 2015 at 10:12 AM, R.Smith wrote: > On 2015-03-06 09:42 AM, Dominique Devienne wrote: > >> On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: >> >> >>> The LIKE operator can be overridden by the application to mean >>> anything the application wants - it is not compelled to follow >>> standard SQL semantics. For that reason, virtual tables are unable to >>> optimize using LIKE since they have no way of knowing what it will do. >>> >>> Works as designed. >>> >>> Sure. But SQLite knows whether LIKE is overriden by the application or >> not, >> so shouldn't it only hide LIKE from the virtual table when it detects it, >> instead of wholesale preventing the vtable from optimizing the "normal >> semantic" LIKE case? My $0.02. --DD >> > > I'm sure this is possible, but how would it be feasible? If this was > implemented, how would you know as a programmer designing a virtual table > (or many other interface things) whether or not the target you operate on > will/will not be able to run some bit of code? Even if you could query the > API to find out whether it is possible (i.e. the normal LIKE is used), you > still need to use that result as a specifier to decide which code block to > implement. And if you are going to have to do two code blocks... might as > well do the one where LIKE isn't supported right from the start. > > Now if there was a setting where you could /force/ the standard LIKE to be > used along with supporting the v-table interface, that might be something, > but that might break a whole other universe of possibilities for v-table > users and end-user customization. That's not what I was saying. DRH is saying the vtable *never* sees the LIKE and SQLite itself a-posteriori filters out the rows based on LIKE. The vtable is not aware it's been denied seeing the LIKE. I'm saying that (in the vast majority of) cases when LIKE is not overriden by the application (globally or for that vtable), something SQLite can know, SQLite could pass the LIKE constraint to the xBestIndex, and let the vtable decide whether it can handle (i.e. optimize) LIKE or not, and whether SQLite should double-check it or not (as usual for vtable indexing). When it is overriden, it behaves as now. But that way the vtable has at least the opportunity to optimize LIKE, unlike now. Whether that's a priority, obviously not given what we've read so far, but whether that's technically possible, from 30,000 ft I don't see why not given the above. My $0.02. --DD
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On Thu, Mar 5, 2015 at 7:45 PM, Richard Hipp wrote: > On 3/5/15, Mike Nicolino wrote: > > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to > be a > > bug with Virtual Tables. Queries using 'like' in the where clause are > not > > getting the like clause passed to BestIndex as a query constraint. > > Specifically: > > > > > > - Simple query: select * from foo where name like 'a%' > > The LIKE operator can be overridden by the application to mean > anything the application wants - it is not compelled to follow > standard SQL semantics. For that reason, virtual tables are unable to > optimize using LIKE since they have no way of knowing what it will do. > > Works as designed. > Sure. But SQLite knows whether LIKE is overriden by the application or not, so shouldn't it only hide LIKE from the virtual table when it detects it, instead of wholesale preventing the vtable from optimizing the "normal semantic" LIKE case? My $0.02. --DD
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On 3/6/15, Dominique Devienne wrote: > > Sure. But SQLite knows whether LIKE is overriden by the application or not, > so shouldn't it only hide LIKE from the virtual table when it detects it, > instead of wholesale preventing the vtable from optimizing the "normal > semantic" LIKE case? It's more complicated than that. The optimization in question is to convert WHERE clause terms of the form: xyz LIKE 'abc%' Into: xyz>='abc' AND xyz<'abd' But this optimization is only valid if (1) xyz is a text value, not a numeric value and (2) xyz has the "nocase" collating sequence. We typically do not know either for a virtual table. You might know (2) if the expression has an explicit COLLATE clause: xyz LIKE 'abc%' COLLATE nocase But SQLite does not have a mechanism whereby a virtual table can tell the query planner that the value of a column will never be numeric. Yikes! Actually (1) cannot be determined for normal (non-virtual) tables either because the value could be a BLOB even if the column affinity is TEXT. And so the current LIKE optimization is not valid *ever*. See ticket https://www.sqlite.org/src/tktview/05f43be8fdda9f I don't yet know how we will fix this... -- D. Richard Hipp drh at sqlite.org
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
Hmm, yes this will work. Though re-educating users writing their own queries to avoid like in this case will be an ongoing challenge. :) But I do understand that generic 'like' support for Virtual Tables given the ability to override would be very challenging to implement generically. MikeN -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jay Kreibich Sent: Thursday, March 05, 2015 10:55 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On Mar 5, 2015, at 12:30 PM, Mike Nicolino wrote: > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a > bug with Virtual Tables. Queries using 'like' in the where clause are not > getting the like clause passed to BestIndex as a query constraint. > Specifically: > "LIKE" is not a supported virtual table index constraint. See: http://www.sqlite.org/c3ref/c_index_constraint_eq.html As such, it cannot be passed to a virtual table implementation, and the constraint is handled by the SQLite engine above the virtual table implementation. Given that the LIKE expression is translated into an SQL function, which the application can override, it would be difficult for a virtual table to correctly implement a LIKE operation internally, while matching the exact functionality of the current LIKE function. Consider a statement like this: SELECT * FROM myVirtualTable AS vt WHERE returnThisRow_CustomFunction( vt.col1 ); If returnThisRow_CustomFunction() is a function returns a true or false based on... well, who knows what... there is no way for a virtual table implementation to understand the inter-workings of that function and pre-filter the rows. LIKE is no different. It should be noted that MATCH is a supported virtual table index constraint supported. Along with the virtual table xFindFunction() function allows a virtual table to implement a table specific filter function. This is how the FTS extensions implement searches. Consider providing a virtual table specific MATCH function, over-ride use on your table with xFindFunction(), and rewrite statements using MATCH rather than LIKE. See the FTS modules as examples. You might want to start here: https://www.sqlite.org/fts3.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
> I'm using System.Data.SQLite version 1.0.95.0 and have what appears to > be a bug with Virtual Tables. Queries using 'like' in the where > clause are not getting the like clause passed to BestIndex as a query > constraint. > Specifically: > > > - Simple query: select * from foo where name like 'a%' The LIKE operator can be overridden by the application to mean anything the application wants - it is not compelled to follow standard SQL semantics. For that reason, virtual tables are unable to optimize using LIKE since they have no way of knowing what it will do. Works as designed. Not communicating the like constraint to virtual tables make it impossible for a virtual table to do query optimization to be done in that case. I realize like behavior can be overridden, but the resulting 'query' still needs to filter down to virtual table in some way to avoid full table scans. Otherwise any queries using 'like' against a virtual table of a substantial size become potentially unusable depending on 'time' required for a full table scan. Incidentally, this used to 'work' in a much older version of SQLite, though the semantics may not have been correct in all cases. Version 3.7.7.1, ended up transforming like to of pair of constraints in the "like 'a%'" case which were passed to BestIndex. Thanks, MikeN -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, March 05, 2015 10:46 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like' On 3/5/15, Mike Nicolino wrote: > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to > be a bug with Virtual Tables. Queries using 'like' in the where > clause are not getting the like clause passed to BestIndex as a query > constraint. > Specifically: > > > - Simple query: select * from foo where name like 'a%' The LIKE operator can be overridden by the application to mean anything the application wants - it is not compelled to follow standard SQL semantics. For that reason, virtual tables are unable to optimize using LIKE since they have no way of knowing what it will do. Works as designed. > > - Break inside module BestIndex > > - SQLiteIndex.Inputs.Constraints has 0 length (no constraints) > > The above causes a full table scan of the virtual table for queries > using 'like', which is very bad for any virtual table of a substantial size. > Virtual tables need to be able to use 'like' clauses to restrict > result set size. > > Before I bug this issue, is anyone aware of it and have any workaround? > Currently, the only workaround I've got, is telling users don't use 'like' > in their queries (obviously not a good thing). > > Thanks, > MikeN > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a bug with Virtual Tables. Queries using 'like' in the where clause are not getting the like clause passed to BestIndex as a query constraint. Specifically: - Simple query: select * from foo where name like 'a%' - Break inside module BestIndex - SQLiteIndex.Inputs.Constraints has 0 length (no constraints) The above causes a full table scan of the virtual table for queries using 'like', which is very bad for any virtual table of a substantial size. Virtual tables need to be able to use 'like' clauses to restrict result set size. Before I bug this issue, is anyone aware of it and have any workaround? Currently, the only workaround I've got, is telling users don't use 'like' in their queries (obviously not a good thing). Thanks, MikeN
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On 3/5/15, Mike Nicolino wrote: > Incidentally, this used to 'work' in a much older version of SQLite, though > the semantics may not have been correct in all cases. Version 3.7.7.1, > ended up transforming like to of pair of constraints in the "like 'a%'" case > which were passed to BestIndex. > That was a bug - it could lead to incorrect answers depending on the virtual table and its content. The bug was fixed on 2012-03-29. https://www.sqlite.org/src/timeline?c=2012-03-29+14:29:07 -- D. Richard Hipp drh at sqlite.org
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On 3/5/15, Mike Nicolino wrote: > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a > bug with Virtual Tables. Queries using 'like' in the where clause are not > getting the like clause passed to BestIndex as a query constraint. > Specifically: > > > - Simple query: select * from foo where name like 'a%' The LIKE operator can be overridden by the application to mean anything the application wants - it is not compelled to follow standard SQL semantics. For that reason, virtual tables are unable to optimize using LIKE since they have no way of knowing what it will do. Works as designed. > > - Break inside module BestIndex > > - SQLiteIndex.Inputs.Constraints has 0 length (no constraints) > > The above causes a full table scan of the virtual table for queries using > 'like', which is very bad for any virtual table of a substantial size. > Virtual tables need to be able to use 'like' clauses to restrict result set > size. > > Before I bug this issue, is anyone aware of it and have any workaround? > Currently, the only workaround I've got, is telling users don't use 'like' > in their queries (obviously not a good thing). > > Thanks, > MikeN > > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
[sqlite] Virtual Table BestIndex Bug in system.data.sqlite/sqlite with 'like'
On Mar 5, 2015, at 12:30 PM, Mike Nicolino wrote: > I'm using System.Data.SQLite version 1.0.95.0 and have what appears to be a > bug with Virtual Tables. Queries using 'like' in the where clause are not > getting the like clause passed to BestIndex as a query constraint. > Specifically: > ?LIKE? is not a supported virtual table index constraint. See: http://www.sqlite.org/c3ref/c_index_constraint_eq.html As such, it cannot be passed to a virtual table implementation, and the constraint is handled by the SQLite engine above the virtual table implementation. Given that the LIKE expression is translated into an SQL function, which the application can override, it would be difficult for a virtual table to correctly implement a LIKE operation internally, while matching the exact functionality of the current LIKE function. Consider a statement like this: SELECT * FROM myVirtualTable AS vt WHERE returnThisRow_CustomFunction( vt.col1 ); If returnThisRow_CustomFunction() is a function returns a true or false based on? well, who knows what? there is no way for a virtual table implementation to understand the inter-workings of that function and pre-filter the rows. LIKE is no different. It should be noted that MATCH is a supported virtual table index constraint supported. Along with the virtual table xFindFunction() function allows a virtual table to implement a table specific filter function. This is how the FTS extensions implement searches. Consider providing a virtual table specific MATCH function, over-ride use on your table with xFindFunction(), and rewrite statements using MATCH rather than LIKE. See the FTS modules as examples. You might want to start here: https://www.sqlite.org/fts3.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson
