[sqlite] Optimizer limitation with partial indexes
I'm running into a problem with partial indexes; apparently the query optimizer isn't smart enough. I currently have indexes of the form CREATE INDEX Index1 ON Table (expr1) CREATE INDEX Index2 ON Table (expr2) where expr1 and expr2 are expressions involving table columns. The problematic queries are of the form SELECT * FROM Table WHERE (expr1 > val1 OR expr2 > val2) AND expr3 Such a query correctly uses the above indexes — the EXPLAIN command shows it's using a multi-index OR combining two 'search table using index' loops. If, however, I try to make the indexes smaller by changing them to CREATE INDEX Index1 ON Table (expr1) WHERE expr3 CREATE INDEX Index2 ON Table (expr2) WHERE expr3 the query stops using the indexes effectively. It's reduced to doing 'scan table using index', i.e. O(n). It looks like what happens is that the optimizer doesn't associate the "AND expr3" clause with the "expr1" and "expr2" comparisons. In other words, it doesn't realize that (A OR B) AND C is equivalent to (A AND C) OR (B AND C). If this were a hand-written SELECT statement it would be easy to work around this, but it's not. It's the output of a query translator that generates SQL, and it can generate arbitrary queries with arbitrary combinations of operators. I know the SQLite optimizer isn't a Mathematica-grade symbolic logic analyzer! But I'm wondering if in this case there's a way around this limitation? —Jens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On February 11, 2020 1:43:30 p.m. EST, Jens Alfke wrote:
>I ran into this a few months ago. I ended up just biting the bullet and
>constructing a SQL statement by hand, concatenating comma-separated
>values inside an "IN (…)" expression.
>
>Yes, SQL injection is a danger. But if you're being bad in just one
>place, and you review that code, you can do this safely. SQLite's C API
>even has a function that escapes strings for you, and if you're not
>coding in C/C++, it's easy to write your own; basically
> str ⟶ "'" + str.replace("'", "''") + "'"
Same here, for what it's worth. Since SQLite also has a 1M byte statement
length limit I had my application embed terms once an IN() expression exceeded
a certain number of terms, but used parameters always for string terms longer
than a couple hundred bytes.
--
J. King
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On 2/11/20, J. King wrote: > SQLite also has a 1M byte statement > length limit ... The statement length limit is yet another defense against mischief caused by SQL injections. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
> On Feb 11, 2020, at 2:10 AM, Digital Dog wrote:
>
> Thanks for enlighening again. It was just a thought. It seems it would be a
> lot of design and code to maintain the performance while preventing the
> original problem from happening. Not worth the trouble. But maybe
> increasing the parameter count to 10.000 would not hurt?
I ran into this a few months ago. I ended up just biting the bullet and
constructing a SQL statement by hand, concatenating comma-separated values
inside an "IN (…)" expression.
Yes, SQL injection is a danger. But if you're being bad in just one place, and
you review that code, you can do this safely. SQLite's C API even has a
function that escapes strings for you, and if you're not coding in C/C++, it's
easy to write your own; basically
str ⟶ "'" + str.replace("'", "''") + "'"
—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Make sqlite3 database searchable on Mac OS X
> On Feb 10, 2020, at 8:10 PM, Peng Yu wrote: > > It seems that sqlite3 databases are not searchable by Spotlight on Mac > OS X. Is there a way to make them searchable? Thanks. How would Spotlight know what tables or columns to index? It doesn't understand what database schema mean, and it can't tell a column with user-visible text apart from one with internal info. You can write a Spotlight indexer plugin for your application that recognizes your file type and scans your SQLite database to generate indexing data. The downside is that Spotlight is fundamentally file-based: it considers a file to be a single entity. So when the user searches for something that's in your database, it will just show the database file as a hit. It won't show separate results for every row that matches. (Some apps work around this by creating stub files in a hidden directory, one file per entity. All these files need to contain is a record identifier so your indexer can index the file by looking at the corresponding row in the database. Spotlight will return the stub file as a match; when your app is told to open it, it looks at its record ID and displays the corresponding database row in its UI. Apple Mail is an example; there's a directory somewhere in ~/Library/Mail that contains an empty file for every email message.) —Jens PS: Disclaimer: My knowledge of Spotlight is years out of date; it may be that it's advanced since then. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to group this?
Solved this now, nil to do with SQL, but just running a different search (other value code and then you can ask for a secondary value and no need anymore to find the matching pair). RBS On Mon, Feb 10, 2020 at 8:22 AM Bart Smissaert wrote: > I fully agree with you, but I sofar I have no control over this data, I > have it like I showed. > As far as I can see there always will be a secondary value, but as you say > I can't be sure. > All this has to do with changing our clinical coding system from Read > codes to Snomed. > In the old setup there was the concept of a secondary value (systolic >> > diastolic), but it > seems in this particular case that is missing. > I get the data by running searches (not SQL) on a clinical database and I > have no control > over this database. > I will see if I can get better data with a different search, to do with > blood pressure values. > > RBS > > On Mon, Feb 10, 2020 at 3:12 AM Richard Damon > wrote: > >> On 2/9/20 7:24 PM, Bart Smissaert wrote: >> > ID ENTRY_DATE TERM NUMERIC_VALUE ROWID >> > >> > 1308 15/Mar/2013 Systolic 127 701559 >> > 1308 15/Mar/2013 Diastolic 81 701568 >> > 1308 27/Jun/2013 Systolic 132 701562 >> > 1308 27/Jun/2013 Systolic 141 701563 >> > 1308 27/Jun/2013 Systolic 143 701564 >> > 1308 27/Jun/2013 Diastolic 82 701571 >> > 1308 27/Jun/2013 Diastolic 85 701572 >> > 1308 27/Jun/2013 Diastolic 94 701573 >> > 278975701 08/Mar/2018 Systolic 136 1583551 >> > 278975701 08/Mar/2018 Diastolic 99 1583591 >> > 278975701 04/Apr/2018 Systolic 119 1583552 >> > 278975701 04/Apr/2018 Systolic 124 1583553 >> > 278975701 04/Apr/2018 Systolic 130 1583554 >> > 278975701 04/Apr/2018 Diastolic 74 1583592 >> > 278975701 04/Apr/2018 Diastolic 75 1583593 >> > 278975701 04/Apr/2018 Diastolic 85 1583594 >> > >> > These are systolic and diastolic blood pressures for 2 people with the >> ID's >> > 1308 and 278975701, ordered by ID asc, ENTRY_DATE asc, ROWID asc. >> > Systolic and diastolic values are a pair and should be grouped in one >> row. >> > This is no problem if there is only one pair for one date, but sometimes >> > there multiple pairs per date. >> > The pairing should be based on the rowed if there are multiple pairs by >> > date, so for ID 1308 >> > I should get: >> > >> > 127/81 >> > 132/82 >> > 141/85 >> > 143/94 >> > >> > What should be the SQL to group like this? >> > >> > RBS >> >> To be honest, I think the problem is fundamentally badly designed. You >> say pair the two readings by ROWID, but they of course don't have the >> same ROWID, but you seem to be saying to pair them sorted by ROWID (1st >> to 1st, 2nd to 2nd, etc). The fundamental problem is what if there isn't >> the same number of each? You may say that you know that there will >> always be the same number, but there is no constraint that forces this, >> so any general program is going to have to deal with the possibility >> (and at least throw out an error when it sees that). >> >> >> -- >> Richard Damon >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "INSERT INTO table AS alias" is invalid inside triggers
On Mon Feb 10, 2020 at 01:34:12AM +0100, no...@null.net wrote: > I suspect I have found a parsing error in SQLite 3.30.1. Given the > ... > However if I wrap it inside a trigger: > > CREATE TABLE t2(b INTEGER); > > CREATE TRIGGER t2_ai > AFTER INSERT ON t2 > FOR EACH ROW BEGIN > > INSERT INTO t1 AS original (a) VALUES(NEW.b) > ON CONFLICT DO NOTHING; > > END; > > Then SQLite fails to prepare: near "AS": syntax error [for Statement > "CREATE TRIGGER"] I see the same behaviour with 3.31.1. Could I ask the devs if this is likely to be fixed at some point or will remain as is? On a related note and perhaps more generally interesting, I find the UPSERT mechanism quite useful for updating specific multiple rows in a specific order: INSERT INTO table SELECT columns FROM table LEFT JOIN other_table ON join_condition WHERE where_condition ORDER BY order_condition ON CONFLICT DO UPDATE SET x,y,z = (values or some other query) Previously I have used temporary tables and complicated recursive triggers to achieve what the above appears to do. I would be very interested in hearing from those that know better if the above is reliable and functions the way I think it does. It is certainly much easier to understand and I assume more efficient. Some systems apparently support an UPDATE ... JOIN syntax but I find the SQLite UPSERT implementation more powerful because of the ORDER BY possibility. -- Mark Lawrence ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER
On Mon, Feb 10, 2020 at 9:03 PM Richard Hipp wrote: > On 2/10/20, Digital Dog wrote: > > Maybe they should be treated as a > > dictionary/hashtable/linked list or similar? > > > > > Parameter look-ups are on the critical path. How much performance are > you willing to give up in order to have parameters with larger > numbers? > > Thanks for enlighening again. It was just a thought. It seems it would be a lot of design and code to maintain the performance while preventing the original problem from happening. Not worth the trouble. But maybe increasing the parameter count to 10.000 would not hurt? > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to group this?
On 11/02/2020 01:35, Simon Slavin wrote: I don't think that creating an index on a view actually works, does it? You're right. What was I thinking ? Maybe I've used another implementation of SQL that it does work on. Thanks for picking me up on it. You are right, SQL Server allows you to create an index on a view. J-L Hainaut ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
