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 tab
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);
INSE
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
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.
S
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 decr
>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 sub
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 wi
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 sor
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.
_
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
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
___
sql
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
> 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
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 p
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.
Know
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 (
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
O
17 matches
Mail list logo
