Re: [sqlite] Bug due to left join strength reduction optimization?

[Keith Medcalf]

Fascinating.  From the same source table see also:

sqlite> select tab2.id is not null as c from tab left join tab as tab2 on 0 
where c = 0;
QUERY PLAN
|--SCAN TABLE tab AS tab2 (~983040 rows)
`--SCAN TABLE tab (~1048576 rows)

sqlite> select tab2.id is not null as c from tab left join tab as tab2 on 0 
where c is 0;
QUERY PLAN
|--SCAN TABLE tab (~1048576 rows)
`--SCAN TABLE tab AS tab2 (~917504 rows)
0

---
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 [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Danny
>Sent: Monday, 4 February, 2019 23:24
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Bug due to left join strength reduction
>optimization?
>
>The result of the query described below changed (became incorrect, I
>believe) with the addition of the left join strength reduction
>optimization in revision dd568, and remains that way in trunk (2c876,
>at
>the time of writing).
>
>Consider the following statements:
>
>```
>CREATE TABLE tab (id INT);
>INSERT INTO tab VALUES (1);
>SELECT tab2.id IS NOT NULL AS c
>  FROM tab LEFT JOIN tab AS tab2 ON 0
>  WHERE c = 0;
>```
>
>As of revision a8dfe (parent of dd568), the SELECT outputs one row
>with
>one column containing 0, as I would expect. At dd568 (and at trunk),
>however, it outputs no rows.
>
>This looks similar in spirit to an existing, fixed bug [1], but the
>output for the test case there has gone back to its pre-LJSRO value
>at
>some point since dd568, while this one has not.
>
>Thanks,
>Danny
>
>[1] https://www.sqlite.org/src/tktview/1e39b966ae9ee7394334
>___
>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

[sqlite] Bug due to left join strength reduction optimization?

[Danny]

The result of the query described below changed (became incorrect, I
believe) with the addition of the left join strength reduction
optimization in revision dd568, and remains that way in trunk (2c876, at
the time of writing).

Consider the following statements:

```
CREATE TABLE tab (id INT);
INSERT INTO tab VALUES (1);
SELECT tab2.id IS NOT NULL AS c
  FROM tab LEFT JOIN tab AS tab2 ON 0
  WHERE c = 0;
```

As of revision a8dfe (parent of dd568), the SELECT outputs one row with
one column containing 0, as I would expect. At dd568 (and at trunk),
however, it outputs no rows.

This looks similar in spirit to an existing, fixed bug [1], but the
output for the test case there has gone back to its pre-LJSRO value at
some point since dd568, while this one has not.

Thanks,
Danny

[1] https://www.sqlite.org/src/tktview/1e39b966ae9ee7394334
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Displaying hierarchical structure

[Bart Smissaert]

Looking at this approach of a hierarchical system:
https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql

Given a table like this:

ID PARENT_ID FOLDER RANK
---
1   0   Main1
2   1   CC   1-02
3   1   BB   1-03
4   1   AA   1-04
5   2   B 1-02-05
6   2   A 1-02-06

What SQL should I use to update the field RANK if the first row is known to
be 01, but all the
next rows are null? I tried with a non-recursive query, but couldn't work
it out.

RBS



On Thu, Jan 31, 2019 at 8:02 AM Bart Smissaert 
wrote:

> This looks a nice and simple way to display the tree in the right order
> without recursive SQL:
>
> https://coderwall.com/p/lixing/closure-tables-for-browsing-trees-in-sql
>
> Will do some testing on large numbers to see how the 2 methods compare
> speed-wise.
>
> RBS
>
> On Tue, Jan 29, 2019 at 8:33 PM Keith Medcalf  wrote:
>
>>
>> See https://sqlite.org/lang_with.html
>>
>> which includes how to traverse the recursive tree in either depth-first
>> or breadth-first order.
>>
>> Why do you need the closure table at all?
>>
>>
>> create table folders
>> (
>>idinteger primary key,
>>parent_id integer references folders,
>>name  text not null collate nocase,
>>check (not (parent_id is null and id != 1))
>> );
>>
>> insert into folders values (1, null, 'Folder1'),
>>(2, 1, 'Folder2'),
>>(3, 1, 'Folder3'),
>>(4, 1, 'Folder4'),
>>(5, 2, 'Folder5'),
>>(6, 2, 'Folder6');
>> .head on
>> .mode column
>> .width 30 9 38
>>
>> -- depth first
>>
>> with foo (id, parent_id, name, level, path)
>>   as (select folders.*, 0, folders.name
>> from folders
>>where parent_id is null
>>union all
>>   select folders.*, level + 1, foo.path || '\' || folders.name
>> from foo, folders
>>where folders.parent_id = foo.id
>> order by 4
>>  )
>> select substr('', 1, (level - 1) * 4) || name as
>> Folder,
>>coalesce(parent_id, 0) as PARENT_ID,
>>path as FullPath
>>   from foo;
>>
>>
>> -- breadth first
>>
>> with foo (id, parent_id, name, level, path)
>>   as (select folders.*, 0, folders.name
>> from folders
>>where parent_id is null
>>union all
>>   select folders.*, level + 1, foo.path || '\' || folders.name
>> from foo, folders
>>where folders.parent_id = foo.id
>> order by 4 desc
>>  )
>> select substr('', 1, (level - 1) * 4) || name as
>> Folder,
>>coalesce(parent_id, 0) as PARENT_ID,
>>path as FullPath
>>   from foo;
>>
>>
>>
>> SQLite version 3.27.0 2019-01-28 00:42:06
>> Enter ".help" for usage hints.
>> Connected to a transient in-memory database.
>> Use ".open FILENAME" to reopen on a persistent database.
>> sqlite> create table folders
>>...> (
>>...>idinteger primary key,
>>...>parent_id integer references folders,
>>...>name  text not null collate nocase,
>>...>check (not (parent_id is null and id != 1))
>>...> );
>> sqlite>
>> sqlite> insert into folders values (1, null, 'Folder1'),
>>...>(2, 1, 'Folder2'),
>>...>(3, 1, 'Folder3'),
>>...>(4, 1, 'Folder4'),
>>...>(5, 2, 'Folder5'),
>>...>(6, 2, 'Folder6');
>> sqlite> .head on
>> sqlite> .mode column
>> sqlite> .width 30 9 38
>> sqlite>
>> sqlite> -- depth first
>> sqlite>
>> sqlite> with foo (id, parent_id, name, level, path)
>>...>   as (select folders.*, 0, folders.name
>>...> from folders
>>...>where parent_id is null
>>...>union all
>>...>   select folders.*, level + 1, foo.path || '\' ||
>> folders.name
>>...> from foo, folders
>>...>where folders.parent_id = foo.id
>>...> order by 4
>>...>  )
>>...> select substr('', 1, (level - 1) * 4) || name
>> as Folder,
>>...>coalesce(parent_id, 0) as PARENT_ID,
>>...>path as FullPath
>>...>   from foo;
>> Folder  PARENT_ID  FullPath
>> --  -
>> --
>> Folder1 0  Folder1
>> Folder2 1  Folder1\Folder2
>> Folder3 1  Folder1\Folder3

Re: [sqlite] Min/Max and skip-scan optimizations

[Simon Slavin]

On 4 Feb 2019, at 9:14pm, James K. Lowden  wrote:

> As Keith said, SQLite allows ORDER BY in subqueries.  The SQL standard does 
> not.  

True.  But SQLite does not guarantee that the outer query will preserve the 
inner query's ORDER BY, even if the outer query doesn't have its own ORDER BY.  
So be careful.

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

Re: [sqlite] Min/Max and skip-scan optimizations

[James K. Lowden]

On Mon, 4 Feb 2019 18:55:33 +0100
Gerlando Falauto  wrote:

> I remember reading ORDER BY is only allowed in
> the outer query

As Keith said, SQLite allows ORDER BY in subqueries.  The SQL standard
does not.  

Logically, ORDER BY makes sense only for the outer query.  An SQL
SELECT statement decribes a set of results, possibly presented in a
particular order. An "internal ORDER BY" describes neither selection
criteria nor presentation order.  

Technically, ORDER BY takes a tabular result as input and produces a
cursor as output.  It need not sort the results; all that's required is
that the cursor return successive rows in the prescribed order.  

SQLite extends ORDER BY with LIMIT.  Because the combination affects
more than just the order, it can be useful to use ORDER BY in a
subquery.  Now that window functions provide a (more convenient)
standard way to produce row numbers, LIMIT is a bit of anachronism but, 
for reasons of backwards compatibility, is unlikely to be removed.  

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

Re: [sqlite] Min/Max and skip-scan optimizations

[Keith Medcalf]

>I wonder if I'd be allowed to add an ORDER BY in the subquery and if
>that would make any difference -- I remember reading ORDER BY is only
>allowed in the outer query (which makes perfect sense).

Yes, you can use an order by in a subquery (either a correlated subquery or a 
table generating subquery).  And you can use one in the outer query which will 
be used to order the results.  Whether the order-by clause in a non-correlated 
subquery has any meaning depends on how the query planner decides to perform 
the query.  The order-by in a table generating subquery (that is not 
correlated) may be "pushed" to the outer query just as a where clause in the 
subquery may also get pushed to the outer query as well if the query is 
flattened or if doing so results in a more optimum plan, or so I have observed 
...

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



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

Re: [sqlite] Min/Max and skip-scan optimizations

[Gerlando Falauto]

On Mon, Feb 4, 2019 at 4:52 PM Simon Slavin  wrote:

> On 4 Feb 2019, at 1:55pm, Gerlando Falauto 
> wrote:
>
> > Or (most likely) my understanding of how data is retrieved is plain
> wrong...
>
> Or your understanding how the current version of SQLite is correct, but a
> later version of SQLite will have different optimizations and do things
> differently.  So at some point you'll update your libraries and suddenly
> things won't work any more.  So if you depend on sorting, future-proof your
> code by asking for sorting.
>

As I just wrote in replty to Luuk's comment, I'm not questioning the usage
of ORDER BY. It should be there. I just would expect it to add ZERO
overhead.
But again, perhaps I'm making the wrong assumption that using the index
data would be "already sorted", perhaps it isn't.
I wonder if I'd be allowed to add an ORDER BY in the subquery and if that
would make any difference -- I remember reading ORDER BY is only allowed in
the outer query (which makes perfect sense).


> By the way, here's an example of a SQL engine (not SQLite) not using an
> index when you though it would.  Suppose you have a short table …just 40
> rows:
>
> CREATE TABLE MyTable (a INTEGER, b TEXT);
> CREATE UNIQUE INDEX MT_a ON MyTable (a);
> INSERT <40 rows of data into MyTable>
>
> SELECT a,b FROM MyTable ORDER BY a;
>
> The assumed plan would be to use the index to retrieve the row order, then
> to look up each retrieved row in the table to retrieve the value for b.
> This requires one index walk plus 40 table lookups.
>
> But the engine knows that 40 table lookups takes a long time.  It would be
> faster to read the table, then sort it internally.  It's a table with only
> 40 rows, so sorting it would be fast and take only a little memory.  That
> saves 40 lookups.
>
> So even though there's an index, it's not a covering index (it doesn't
> contain all the data needed) so it won't be used.
>

That's understandable and I was expecting that. That's why I populated the
test dataset with *muuuch* more data -- to avoid corner cases like this.

Thanks again for your patience ;-)
Gerlando
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Min/Max and skip-scan optimizations

[Gerlando Falauto]

Hi Luuk,

It says:
>
> SQLite *attempts* to use an index to satisfy the ORDER BY clause of a
> query when possible
>
>
> To be (abolutely!) SURE results are in the correct order, you need an
> ORDER BY.
>

No questioning about that. ORDER BY *must* be there in order to get the
results correctly sorted.

What I'm saying is it shouldn't add any extra overhead, which apparently
does in my case.

Notice how ORDER BY does not add any in all the trivial cases:

sqlite> explain query plan select * from rolling order by
source1,source2,ts;
QUERY PLAN
`--SCAN TABLE rolling USING INDEX sources
sqlite> explain query plan select * from rolling order by
source1,source2;
QUERY PLAN
`--SCAN TABLE rolling USING INDEX sources
sqlite> explain query plan select * from rolling order by source1;
QUERY PLAN
`--SCAN TABLE rolling USING INDEX sources
sqlite> explain query plan select source1,source2,ts from rolling order by
source1,source2,ts;
QUERY PLAN
`--SCAN TABLE rolling USING COVERING INDEX sources
sqlite> explain query plan select source1,source2,ts from rolling order by
source1,source2;
QUERY PLAN
`--SCAN TABLE rolling USING COVERING INDEX sources
sqlite> explain query plan select source1,source2,ts from rolling order by
source1;
QUERY PLAN
`--SCAN TABLE rolling USING COVERING INDEX sources

It's just that when things get a bit more complicated, I start getting
surprising results (for my use case -- which, I admit, is unusual at least).

Any suggestion welcome.
Thanks,
Gerlando
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite error (5): database is locked

[Simon Slavin]

On 4 Feb 2019, at 3:15pm, Urs Wagner  wrote:

> SQLite error (5): database is locked occurs?

Can't answer your question, but …

If you're getting unexpected locks, have you set a timeout on every connection 
to that database ?  That gets rid of most locks.

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

Re: [sqlite] Min/Max and skip-scan optimizations

[Simon Slavin]

On 4 Feb 2019, at 1:55pm, Gerlando Falauto  wrote:

> Or (most likely) my understanding of how data is retrieved is plain wrong...

Or your understanding how the current version of SQLite is correct, but a later 
version of SQLite will have different optimizations and do things differently.  
So at some point you'll update your libraries and suddenly things won't work 
any more.  So if you depend on sorting, future-proof your code by asking for 
sorting.

By the way, here's an example of a SQL engine (not SQLite) not using an index 
when you though it would.  Suppose you have a short table …just 40 rows:

CREATE TABLE MyTable (a INTEGER, b TEXT);
CREATE UNIQUE INDEX MT_a ON MyTable (a);
INSERT <40 rows of data into MyTable>

SELECT a,b FROM MyTable ORDER BY a;

The assumed plan would be to use the index to retrieve the row order, then to 
look up each retrieved row in the table to retrieve the value for b.  This 
requires one index walk plus 40 table lookups.

But the engine knows that 40 table lookups takes a long time.  It would be 
faster to read the table, then sort it internally.  It's a table with only 40 
rows, so sorting it would be fast and take only a little memory.  That saves 40 
lookups.

So even though there's an index, it's not a covering index (it doesn't contain 
all the data needed) so it won't be used.

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

[sqlite] SQLite error (5): database is locked

[Urs Wagner]

Hello

Is it possible to get a C# exception when the error

SQLite error (5): database is locked occurs?

I am using the entity framework with multiple thread and a global mutex.
I would like to know which call generates the locking error.

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

Re: [sqlite] Min/Max and skip-scan optimizations

[Luuk]

On 4-2-2019 14:55, Gerlando Falauto wrote:

Thank you Luuk, I understand your point.
However, the query plan already takes advantage of the index and should be
retrieving data in that order.
Reading the docs
https://www.sqlite.org/optoverview.html#order_by_optimizations my
understanding was that
SQLite would be taking advantage of that.
So perhaps my use case it's too complicated (many columns -- some filtered,
some not -- skip/scan, all together) to make it obvious to the query
planner that data *is* already sorted.
Or maybe it never occurred to anyone that someone might be trying to do
something like that.
Or (most likely) my understanding of how data is retrieved is plain wrong...

Thank you!
Gerlando



It says:

SQLite *attempts* to use an index to satisfy the ORDER BY clause of a 
query when possible



To be (abolutely!) SURE results are in the correct order, you need an 
ORDER BY.


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

Re: [sqlite] Inconsistent behavior in sqlite3_set_authorizer() and error messages

[Joshua Thomas Wise]

> On Feb 4, 2019, at 7:00 AM, sqlite-users-requ...@mailinglists.sqlite.org 
> wrote:
> 
> For the last point, using the SQLITE_OMIT_TEMPDB option, did you compile from 
> the amalgamation or the full cannonical sources? According to 
> https://www.sqlite.org/compile.html#_options_to_omit_features 
> , many "OMIT" 
> options only work when the cannonical sources are used (my guess if compiling 
> the amalgamation is the parser still recognises TEMP as a keyword but there's 
> no code to implement it, hence the "logic error").


Rest assured, I compiled from the full canonical source. When using 
SQLITE_OMIT_TEMPDB, the “TEMP” keywords do result in syntax errors (as 
expected), but “temp.foo” schema names result in those cryptic error messages, 
rather than the expected "unknown database temp”.

I should also note that when using SQLITE_OMIT_TEMPDB, all four SQL statements 
previously mentioned will report the their normal variants to the 
sqlite3_set_authorizer() callback (i.e., not their TEMP_* variants), which is 
good and desired.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Min/Max and skip-scan optimizations

[Gerlando Falauto]

Thank you Luuk, I understand your point.
However, the query plan already takes advantage of the index and should be
retrieving data in that order.
Reading the docs
https://www.sqlite.org/optoverview.html#order_by_optimizations my
understanding was that
SQLite would be taking advantage of that.
So perhaps my use case it's too complicated (many columns -- some filtered,
some not -- skip/scan, all together) to make it obvious to the query
planner that data *is* already sorted.
Or maybe it never occurred to anyone that someone might be trying to do
something like that.
Or (most likely) my understanding of how data is retrieved is plain wrong...

Thank you!
Gerlando


On Mon, Feb 4, 2019 at 1:26 PM Luuk  wrote:

>
> On 3-2-2019 23:29, Gerlando Falauto wrote:
> > IMHO, adding the ORDER BY clause to query 1) above (i.e. query 2) should
> > ideally yield the exact same query plan.
> > In the end adding an ORDER BY clause on the exact same columns of the
> index
> > used to traverse the table, should be easily recognizable.
> > Knowing absolutely nothing about the internals though, I have no idea
> > whether this particular use case has been overlooked, or it would just be
> > unfeasible to handle it.
>
>
> In SQL, when doing a SELECT, the order of the results is undetermined
> (by definition).
>
> If you want/need to results to be ORDERed, you need to add 'ORDER BY'.
> This will always show as an extra step in your QUERY PLAN.
>
> One can never know (for sure) if the output of this is in the correct
> order:
>
> CREATE TABLE test(i primary key);
> INSERT INTO test values(4);
> INSERT INTO test values(2);
> INSERT INTO test values(3);
> INSERT INTO test values(1);
> SELECT i FROM test;
>
> 4
> 2
> 3
> 1
>
> To 'know' it is in the correct order one has to define an ORDER BY to
> specify in which order the data should be returned
>
> SELECT i FROM test order by 2*i+i%2;
>
> 1
> 2
> 3
> 4
>
>
>
> ___
> 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] Min/Max and skip-scan optimizations

[Luuk]

On 3-2-2019 23:29, Gerlando Falauto wrote:

IMHO, adding the ORDER BY clause to query 1) above (i.e. query 2) should
ideally yield the exact same query plan.
In the end adding an ORDER BY clause on the exact same columns of the index
used to traverse the table, should be easily recognizable.
Knowing absolutely nothing about the internals though, I have no idea
whether this particular use case has been overlooked, or it would just be
unfeasible to handle it.



In SQL, when doing a SELECT, the order of the results is undetermined 
(by definition).


If you want/need to results to be ORDERed, you need to add 'ORDER BY'. 
This will always show as an extra step in your QUERY PLAN.


One can never know (for sure) if the output of this is in the correct order:

CREATE TABLE test(i primary key);
INSERT INTO test values(4);
INSERT INTO test values(2);
INSERT INTO test values(3);
INSERT INTO test values(1);
SELECT i FROM test;

4
2
3
1

To 'know' it is in the correct order one has to define an ORDER BY to 
specify in which order the data should be returned


SELECT i FROM test order by 2*i+i%2;

1
2
3
4



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

Re: [sqlite] Min/Max and skip-scan optimizations

[Gerlando Falauto]

Thanks Keith, I'll give it a go and let you know!
I still don't get how that differs from
2) or 4) below, though.

Thanks again!
Gerlando

Il dom 3 feb 2019, 00:27 Keith Medcalf  ha scritto:

>
> Like this?
>
> SELECT rolling.source1,
>rolling.source2,
>ts,
>value
>   FROM (
> select distinct source1,
> source2
>   from rolling
>  where source1 = 'aaa'
>) as x
>   JOIN rolling
> ON rolling.source1 = x.source1
>AND rolling.source2 = x.source2
>  WHERE ts > 1
>AND ts < 10
> ORDER BY 1,2,3;
>
>
> ---
> 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 [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Gerlando Falauto
> >Sent: Saturday, 2 February, 2019 15:20
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Min/Max and skip-scan optimizations
> >
> >Hi,
> >it's me again, struggling with indices once again.
> >What I'm now trying to do is filter on source1 and by range of
> >timestamp.
> >Results should be naturally ordered by source1, source2,ts.
> >
> >1)
> >
> >SELECT source1, source2, ts, value
> >FROM rolling
> >WHERE source1 = 'aaa'
> >AND ts > 1 AND ts < 10;
> >
> >QUERY PLAN
> >`--SEARCH TABLE rolling USING INDEX sources (ANY(source1) AND
> >ANY(source2)
> >AND ts>? AND ts >
> >This looks pretty OK on the sample dataset.
> >For some reason though this doesn't really work like that with the
> >real
> >dataset (which has an extra index on ts only),
> >and that seems to kick in instead of the real index.
> >So the query plan comes out completely different, and rows are not
> >naturally sorted by source1, source2, as I'd like.
> >[I know I can use "+ts" and/or drop the "ts" index altogether, but
> >I'm
> >trying to make a point here...]
> >So I add an extra ORDER BY clause:
> >
> >2)
> >
> >SELECT source1, source2, ts, value
> >FROM rolling
> >WHERE source1 = 'aaa'
> >  AND ts > 1 AND ts < 1
> >ORDER BY source1, source2, ts;
> >
> >QUERY PLAN
> >|--SEARCH TABLE rolling USING INDEX sources (ANY(source1) AND
> >ANY(source2)
> >AND ts>? AND ts >`--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
> >
> >Here I don't really understand why a TEMP B-TREE is required at all.
> >Apparently, this adds extra processing at the end so I don't start
> >pulling
> >any rows until much later.
> >If the index is used, data would be *naturally* sorted by its key, so
> >I
> >don't really understand.
> >So I recur to a subquery:
> >
> >3)
> >
> >SELECT source1, source2, ts, value
> >FROM rolling
> >WHERE ((source1, source2) in
> >(SELECT DISTINCT source1, source2 from rolling where
> >source1='aaa')
> >  AND ts > 1 AND ts < 10)
> >
> >QUERY PLAN
> >|--SEARCH TABLE rolling USING INDEX sources (source1=? AND source2=?
> >AND
> >ts>? AND ts >`--LIST SUBQUERY
> >   `--SEARCH TABLE rolling USING COVERING INDEX sources (source1=?)
> >
> >This also seems to yield the right results, in the right order.
> >But the order looks pretty much like a coincidence to me.
> >So I'd rather explicitly state the sorting I'd like:
> >
> >4)
> >
> >SELECT source1, source2, ts, value
> >FROM rolling
> >WHERE ((source1, source2) in
> >(SELECT DISTINCT source1, source2 from rolling where
> >source1='aaa')
> >  AND ts > 1 AND ts < 10)
> >ORDER BY source1, source2, ts
> >
> >QUERY PLAN
> >|--SEARCH TABLE rolling USING INDEX sources (source1=? AND source2=?
> >AND
> >ts>? AND ts >|--LIST SUBQUERY
> >|  `--SEARCH TABLE rolling USING COVERING INDEX sources (source1=?)
> >`--USE TEMP B-TREE FOR ORDER BY
> >
> >Here the b-tree is *NOT* for "RIGHT PART OF" ORDER BY, it seems like
> >some
> >global sorting
> >which looks even worse than case 2.
> >
> >What am I doing wrong? Is this expected?
> >I just don't seem to be able to get what would have been a pretty
> >trivial
> >task with
> >last-century technologies (thinking of Paradox, dBase, CA-Clipper)...
> >
> >Thanks in advance!
> >Gerlando
> >
> >
> >On Mon, Jan 28, 2019 at 9:11 AM Gerlando Falauto
> >
> >wrote:
> >
> >> YES! Thank you!
> >> Many thanks for the ".eqp full" tip also, that really explains a
> >lot
> >> (though I don't really understand any of it yet).
> >>
> >> Have a great day!
> >> Gerlando
> >>
> >>
> >> On Mon, Jan 28, 2019 at 6:50 AM Keith Medcalf 
> >wrote:
> >>
> >>>
> >>> Do you perhaps want this:
> >>>
> >>> select source1,
> >>>source2,
> >>>(
> >>> select min(ts)
> >>>   from rolling
> >>>  where source1 = x.source1
> >>>and source2 = x.source2
> >>>)
> >>>   from (
> >>> select distinct source1,
> >>> source2
> >>>   from rolling
> >>>) as x;
> >>>
> >>> SQLite version 3.27.0 2019-01-28 00:42:06
> >>> Enter ".help" for usage hints.
> >>> Connected to a transient in-memory database.
> >>> Use ".open FILENAME" to reopen on a 

Re: [sqlite] Min/Max and skip-scan optimizations

[Gerlando Falauto]

Hi Keith,

here's what I get as a query plan for your query:

5)
SELECT rolling.source1,
   rolling.source2,
   ts,
   value
  FROM (
select distinct source1,
source2
  from rolling
 where source1 = 'aaa'
   ) as x
  JOIN rolling
ON rolling.source1 = x.source1
   AND rolling.source2 = x.source2
 WHERE ts > 1
   AND ts < 1
ORDER BY 1,2,3;

QUERY PLAN
|--MATERIALIZE 1
|  `--SEARCH TABLE rolling USING COVERING INDEX sources (source1=?)
|--SEARCH TABLE rolling USING INDEX sources (ANY(source1) AND ANY(source2)
AND ts>? AND ts 1 AND ts < 1;

QUERY PLAN
`--SEARCH TABLE rolling USING INDEX sources (ANY(source1) AND ANY(source2)
AND ts>? AND ts wrote:

>
> Like this?
>
> SELECT rolling.source1,
>rolling.source2,
>ts,
>value
>   FROM (
> select distinct source1,
> source2
>   from rolling
>  where source1 = 'aaa'
>) as x
>   JOIN rolling
> ON rolling.source1 = x.source1
>AND rolling.source2 = x.source2
>  WHERE ts > 1
>AND ts < 10
> ORDER BY 1,2,3;
>
>
> ---
> 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 [mailto:sqlite-users-
> >boun...@mailinglists.sqlite.org] On Behalf Of Gerlando Falauto
> >Sent: Saturday, 2 February, 2019 15:20
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Min/Max and skip-scan optimizations
> >
> >Hi,
> >it's me again, struggling with indices once again.
> >What I'm now trying to do is filter on source1 and by range of
> >timestamp.
> >Results should be naturally ordered by source1, source2,ts.
> >
> >1)
> >
> >SELECT source1, source2, ts, value
> >FROM rolling
> >WHERE source1 = 'aaa'
> >AND ts > 1 AND ts < 10;
> >
> >QUERY PLAN
> >`--SEARCH TABLE rolling USING INDEX sources (ANY(source1) AND
> >ANY(source2)
> >AND ts>? AND ts >
> >This looks pretty OK on the sample dataset.
> >For some reason though this doesn't really work like that with the
> >real
> >dataset (which has an extra index on ts only),
> >and that seems to kick in instead of the real index.
> >So the query plan comes out completely different, and rows are not
> >naturally sorted by source1, source2, as I'd like.
> >[I know I can use "+ts" and/or drop the "ts" index altogether, but
> >I'm
> >trying to make a point here...]
> >So I add an extra ORDER BY clause:
> >
> >2)
> >
> >SELECT source1, source2, ts, value
> >FROM rolling
> >WHERE source1 = 'aaa'
> >  AND ts > 1 AND ts < 1
> >ORDER BY source1, source2, ts;
> >
> >QUERY PLAN
> >|--SEARCH TABLE rolling USING INDEX sources (ANY(source1) AND
> >ANY(source2)
> >AND ts>? AND ts >`--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
> >
> >Here I don't really understand why a TEMP B-TREE is required at all.
> >Apparently, this adds extra processing at the end so I don't start
> >pulling
> >any rows until much later.
> >If the index is used, data would be *naturally* sorted by its key, so
> >I
> >don't really understand.
> >So I recur to a subquery:
> >
> >3)
> >
> >SELECT source1, source2, ts, value
> >FROM rolling
> >WHERE ((source1, source2) in
> >(SELECT DISTINCT source1, source2 from rolling where
> >source1='aaa')
> >  AND ts > 1 AND ts < 10)
> >
> >QUERY PLAN
> >|--SEARCH TABLE rolling USING INDEX sources (source1=? AND source2=?
> >AND
> >ts>? AND ts >`--LIST SUBQUERY
> >   `--SEARCH TABLE rolling USING COVERING INDEX sources (source1=?)
> >
> >This also seems to yield the right results, in the right order.
> >But the order looks pretty much like a coincidence to me.
> >So I'd rather explicitly state the sorting I'd like:
> >
> >4)
> >
> >SELECT source1, source2, ts, value
> >FROM rolling
> >WHERE ((source1, source2) in
> >(SELECT DISTINCT source1, source2 from rolling where
> >source1='aaa')
> >  AND ts > 1 AND ts < 10)
> >ORDER BY source1, source2, ts
> >
> >QUERY PLAN
> >|--SEARCH TABLE rolling USING INDEX sources (source1=? AND source2=?
> >AND
> >ts>? AND ts >|--LIST SUBQUERY
> >|  `--SEARCH TABLE rolling USING COVERING INDEX sources (source1=?)
> >`--USE TEMP B-TREE FOR ORDER BY
> >
> >Here the b-tree is *NOT* for "RIGHT PART OF" ORDER BY, it seems like
> >some
> >global sorting
> >which looks even worse than case 2.
> >
> >What am I doing wrong? Is this expected?
> >I just don't seem to be able to get what would have been a pretty
> >trivial
> >task with
> >last-century technologies (thinking of Paradox, dBase, CA-Clipper)...
> >
> >Thanks in advance!
> >Gerlando
> >
> >
> >On Mon, Jan 28, 2019 at 9:11 AM Gerlando Falauto
> >
> >wrote:
> >
> >> YES! Thank you!
> >> Many thanks for the ".eqp full" tip also, that really explains a
> >lot
> >> (though I don't really understand any of it yet).
> >>
> >> Have a great day!
> >> Gerlando
> >>
> >>
> >> On Mon, Jan 28, 2019 at 6:50 AM Keith Medcalf 
> >wrote:
> >>
> >>>
> >>> Do you perhaps want this:
>