Re: [sqlite] [EXTERNAL] Optimizer limitation with partial indexes

2020-02-12 Thread Hick Gunter 
>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
>Auftrag von Jens Alfke
>> On Feb 12, 2020, at 5:30 AM, Hick Gunter  wrote:
>>
>> This is documented here https://sqlite.org/partialindex.html
>>  and here
>> https://sqlite.org/queryplanner.html
>> 
>>
>> Specifically, SQLIte does not prove theorems in first-order logic.
>
>Thanks — I hadn't seen the section "Queries Using Partial Indexes" before, and 
>it gives more detail about how the matching is done. >However, it seems that 
>my query does match one of the rules:
>
>   "If W [the query's WHERE clause] is AND-connected terms
>and X [the index's WHERE clause]  is OR-connected terms
>and if any term of W appears as a term of X,
>then the partial index is usable."
>
>Here W = (expr1 > val1 OR expr2 > val2) AND expr3  and X = expr3, which is a 
>degenerate case of one OR-connected term.
>
>So I'm not sure why the indexes aren't useable, unless there are limitations 
>of the actual rule that aren't described in that English text.

My guess ist hat SQLite is looking at the "expr1>val1" and "expr2>val2" terms 
respectively, which don't have a reference to expr3, and thus concludes that 
the indices are not usable.

However, "expr1>val1 AND expr3" clearly matches the rule and thus should use 
the index.


___
 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON_EACH + recursive query = unexpected performance degradation

2020-02-12 Thread Warren Young 
On Feb 12, 2020, at 10:53 AM, Jens Alfke  wrote:
> 
> You should be able to speed this up by creating temporary tables from the 
> JSON first, and then changing the CTE to use those tables.

Do you not get the same effect by using the new generated columns feature, only 
without the manual work of maintaining the temporary table?

https://www.sqlite.org/gencol.html

sqlite> create table a (
  json text,
  b text generated always as (json_extract(json, '$.field')) stored
);
sqlite> insert into a values('{"field": "hello"}'); 
sqlite> select b from a;
hello


It’s probably critical to the success of this that you use the STORED attribute 
rather than VIRTUAL, which means you can’t ALTER TABLE your way to success, but 
you’d be looking at table copies with the temporary table idea anyway.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Issue with sqlite3 / import / strictly numerical table names

2020-02-12 Thread Alan Kinder 
It would appear to me that sqlite and sqlite3 are out-of-sync in respect of
the handling of tables with strictly numerical table names - please see the
following sqlite3  log demonstrating the problem:

-- Loading resources from C:\Users\Kinder/.sqliterc
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .sys type C:\\Users\\Kinder\\.sqliterc
.mode columns
.headers on
sqlite> create table "_666" (c1 TEXT, c2 TEXT);
sqlite> create table "666" (c1 TEXT, c2 TEXT);
sqlite> .tables
666   _666
sqlite> .mode csv
sqlite> .sys type file.csv
"A","B"
sqlite> .import "file.csv" "_666"
sqlite> select * from "_666";
c1,c2
A,B
sqlite> .import "file.csv" "666"
Error: near "666": syntax error
sqlite>

Please feel free to contact me if you require additional information.
Regards,
Alan Kinder
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation inconsistency

2020-02-12 Thread Tony Papadimitriou 
From here: https://www.sqlite.org/faq.html#q5

> (16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round 
> up?

This example seems to be no longer valid.  Although the explanation is still 
valid in general, the particular example “SELECT ROUND(9.95,1)” actually 
returns 10.0

___
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

2020-02-12 Thread Eric Grange 
If the trouble comes from a big "IN()", an approach can be to pass all the
values in a JSON array (one parameter) and use json_each in the query.
This is completely safe vs SQL injection, and IME quite efficient.

IME using JSON + json_each is also very efficient to fill temporary tables
(indexed if appropriate), in case the filter is reused in multiple queries.

Le mar. 11 févr. 2020 à 20:39, J. King  a écrit :

> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON_EACH + recursive query = unexpected performance degradation

2020-02-12 Thread Keith Medcalf 

The easiest way is to phrase the query such that the table is internally 
materialized.  In query1.sql the easiest way to do that is to change the:

WITH
build a in memory table with parent-child relations 
from 3 json arrays _which have the same size_
"objects_in_memory" AS (
   SELECT "id"."value" AS "id",

to read like this:

WITH
build a in memory table with parent-child relations 
from 3 json arrays _which have the same size_
"objects_in_memory" AS (
   SELECT DISTINCT "id"."value" AS "id",

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-Original Message-
>From: sqlite-users  On
>Behalf Of  ?
>Sent: Monday, 10 February, 2020 23:28
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] JSON_EACH + recursive query = unexpected performance
>degradation
>
>I have a dataset of about 300 rows which have parent-child relations.
>Due to factors unrelated to the issue I build the rows by zipping JSON
>arrays with values from each column.
>Then I run a simplest recursive query on it to get the whole tree(ends
>up being ~4 levels).
>
>Problem: the query takes 3000 ms (3 seconds) on my machine to complete.
>If I create a real table with the SAME structure, insert the SAME data
>into it, and run the SAME query, get the SAME result back, it takes
>10-15 ms (200-300 TIMES faster).
>
>
>I attached both queries(don't require schema to run)




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON_EACH + recursive query = unexpected performance degradation

2020-02-12 Thread Jens Alfke 


> On Feb 10, 2020, at 10:27 PM, Хусаинов Динар  
> wrote:
> 
> Problem: the query takes 3000 ms (3 seconds) on my machine to complete. If I 
> create a real table with the SAME structure, insert the SAME data into it, 
> and run the SAME query, get the SAME result back, it takes 10-15 ms (200-300 
> TIMES faster).

It sounds like SQLite is much faster at traversing real tables than it is at 
traversing JSON arrays. Which I would expect. This is exacerbated by the fact 
that the recursive CTE must be doing a lot of traversals of the JSON.

You should be able to speed this up by creating temporary tables from the JSON 
first, and then changing the CTE to use those tables.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Optimizer limitation with partial indexes

2020-02-12 Thread Jens Alfke 


> On Feb 12, 2020, at 5:30 AM, Hick Gunter  wrote:
> 
> This is documented here https://sqlite.org/partialindex.html 
>  and here 
> https://sqlite.org/queryplanner.html 
> 
> Specifically, SQLIte does not prove theorems in first-order logic.

Thanks — I hadn't seen the section "Queries Using Partial Indexes" before, and 
it gives more detail about how the matching is done. However, it seems that my 
query does match one of the rules:

"If W [the query's WHERE clause] is AND-connected terms
 and X [the index's WHERE clause]  is OR-connected terms
 and if any term of W appears as a term of X,
 then the partial index is usable."

Here W = (expr1 > val1 OR expr2 > val2) AND expr3
 and X = expr3, which is a degenerate case of one OR-connected term.

So I'm not sure why the indexes aren't useable, unless there are limitations of 
the actual rule that aren't described in that English text.

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] JSON_EACH + recursive query = unexpected performance degradation

2020-02-12 Thread Richard Hipp 
On 2/12/20, David Raymond  wrote:
> Not necessarily related to the question itself, but how did the attachments
> actually come through with this mail? Every single other person to try and
> attach something to this list has had it stripped off.

Sometimes Mailman asks me for approval for messages containing
attachments.  I think this depends on the size and mimetype of the
attachments.  In this case, I was asked and I approved.

-- 
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] JSON_EACH + recursive query = unexpected performance degradation

2020-02-12 Thread David Raymond 
Not necessarily related to the question itself, but how did the attachments 
actually come through with this mail? Every single other person to try and 
attach something to this list has had it stripped off.


-Original Message-
From: sqlite-users  On Behalf Of 
 ?
Sent: Tuesday, February 11, 2020 1:28 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] JSON_EACH + recursive query = unexpected performance 
degradation

I have a dataset of about 300 rows which have parent-child relations. 
Due to factors unrelated to the issue I build the rows by zipping JSON 
arrays with values from each column.
Then I run a simplest recursive query on it to get the whole tree(ends 
up being ~4 levels).

Problem: the query takes 3000 ms (3 seconds) on my machine to complete. 
If I create a real table with the SAME structure, insert the SAME data 
into it, and run the SAME query, get the SAME result back, it takes 
10-15 ms (200-300 TIMES faster).


I attached both queries(don't require schema to run)

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible caching issue between connections with specific inserts

2020-02-12 Thread Alexey Podogov 
Joe Mistachkin wrote:
> 
> Thanks for the report.  I'm looking into it now.
> 

Hi Joe,

I hope you are doing well.

Did you have a chance to get any feedback about this case? Can you confirm that 
it is a bug or kind of expected behavior? If it is a bug, is there any chance 
that it will be fixed soon?

The problem is that I see it sporadically in real application (due to 
application's complexity, it was not easy task to isolate such simple steps). 
And due to strangeness of the issue, I can't know for sure what else is 
affected. My next actions just depend on your answer... If it can't be 
processed now, I'll have to search some workarounds/change application's 
architecture.

Regards, Alexey Podogov
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] JSON_EACH + recursive query = unexpected performance degradation

2020-02-12 Thread Хусаинов Динар 
I have a dataset of about 300 rows which have parent-child relations. 
Due to factors unrelated to the issue I build the rows by zipping JSON 
arrays with values from each column.
Then I run a simplest recursive query on it to get the whole tree(ends 
up being ~4 levels).


Problem: the query takes 3000 ms (3 seconds) on my machine to complete. 
If I create a real table with the SAME structure, insert the SAME data 
into it, and run the SAME query, get the SAME result back, it takes 
10-15 ms (200-300 TIMES faster).



I attached both queries(don't require schema to run)

/*query1*/

WITH 
build a in memory table with parent-child relations 
from 3 json arrays _which have the same size_
"objects_in_memory" AS (
   SELECT "id"."value" AS "id",
  "parent"."value" AS "parent",
  "code"."value"   AS "code"
   FROM

Re: [sqlite] [EXTERNAL] Optimizer limitation with partial indexes

2020-02-12 Thread Hick Gunter 
This is documented here https://sqlite.org/partialindex.html and here 
https://sqlite.org/queryplanner.html

Specifically, SQLIte does not prove theorems in first-order logic.

To have a chance of using the partial indices, you would need to have your 
query translator formulate (expr1>val1 AND expr 3) OR (expr2>val2 AND expr3)

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Jens Alfke
Gesendet: Mittwoch, 12. Februar 2020 00:09
An: SQLite mailing list 
Betreff: [EXTERNAL] [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


___
 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Optimizer limitation with partial indexes

2020-02-12 Thread Wout Mertens 
Does moving the expr3 work?

SELECT * FROM Table WHERE ((expr1 > val1 AND AND expr3) OR (expr2 > val2
AND expr3))

Wout.


On Wed, Feb 12, 2020 at 12:09 AM Jens Alfke  wrote:

> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users