[sqlite] The Simplest SQLite Common Table Expression Tutorial

[Gert Van Assche]

You're a great teacher, David. Thanks for sharing this!

gert

2015-09-25 22:17 GMT+02:00 David Barrett :

> Hey all, just wanted to share this in case anybody is also looking for a
> very simple tutorial for CTE's in sqlite:
>
>
> http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/
>
> The Simplest SQLite Common Table Expression Tutorial
>
> I?ve been trying to wrap my head aroundCommon Table Expressions
>  for a while, and all the tutorials
> I?ve read started out with ?simple? examples that were way too advanced for
> me to follow. Here?s my attempt to write a tutorial that starts as simple
> as possible.
>
> First, let?s start with the simplest query:
>
> sqlite> SELECT 1;
> 1
> sqlite>
>
> All this does is return a result set containing a row. Next, consider the
> simplest subquery:
>
> sqlite> SELECT * FROM ( SELECT 1 );
> 1
> sqlite>
>
> This just selects all the results from the subquery ? which in this case,
> is just a single row. A ?Common Table Expression? is basically the same as
> a subquery, except assigned a name and defined prior to the query in which
> it?s referenced. Accordingly, the simplest CTE version of the above query
> would be like:
>
> sqlite> WITH one AS ( SELECT 1 )
> SELECT * FROM one;
> 1
> sqlite>
>
> Breaking that down a bit further:
>
>- We?ve defined a common table expression named ?one?
>- We?ve ?filled? it with the output of SELECT 1, which is just 1 row
>- Then we selected everything from ?one?
>- Such that the final result is a single value: 1
>
> But a CTE can have multiple columns, too, and those columns can be assigned
> names:
>
> sqlite> WITH twoCol( a, b ) AS ( SELECT 1, 2 )
> SELECT a, b FROM twoCol;
> 1|2
> sqlite>
>
> Similarly, a CTE can query other tables:
>
> sqlite> CREATE TABLE foo ( bar INTEGER );
> sqlite> INSERT INTO foo VALUES(1);
> sqlite> INSERT INTO foo VALUES(2);
> sqlite> SELECT * FROM foo;
> 1
> 2
> sqlite> WITH fooCTE AS (SELECT * FROM foo)
> SELECT * FROM fooCTE;
> 1
> 2
> sqlite>
>
> Additionally, you can define as many CTEs as you want in a single query:
>
> sqlite> WITH aCTE AS (SELECT 'a'),
>  bCTE AS (SELECT 'b')
> SELECT * FROM aCTE, bCTE;
> a|b
> sqlite>
>
> So, common table expressions can be used to restructure a query to make it
> more readable, by moving the subqueries out in front. But the real power of
> common table expressions is when you define an expression that recursively
> selects itself. They key to this is using a ?Compound Select Statements?,
> such as the UNION ALL operator. This just combines two result sets into one
> (so long as they have the same number of columns):
>
> sqlite> SELECT 1, 2
> UNION ALL
> SELECT 3, 4;
> 1|2
> 3|4
> sqlite>
>
> Take this example:
>
> sqlite> WITH RECURSIVE infinite AS (
> SELECT 1
> UNION ALL
> SELECT * FROM infinite
> )
> SELECT * FROM infinite;
> ^CError: interrupted
> sqlite>
>
> Let?s break down why that query will never finish:
>
>- ?WITH RECURSIVE infinite? defines a common table expression named
>?infinite?
>- ?SELECT 1? seeds that CTE?s output with a single row ? containing ?1?
>- Next the ?UNION ALL? says ?combine the output of what?s on the left,
>with the output of what?s on the right
>- And on the right we do ?SELECT * FROM infinite? ? meaning, select
>everything currently in the table.
>- The result is we?re defining a common table expression named
>?infinite? to be the union of ?a single row? and ?all other rows?.
>- Because no ?cap? has been placed on this (via a WHERE or LIMIT), this
>means we?ve defined an infinitely recurring CTE. Fun!
>
> So we can ?cap? that CTE by writing a query like:
>
> sqlite> WITH RECURSIVE finite AS (
> SELECT 1
> UNION ALL
> SELECT * FROM finite LIMIT 2
> )
> SELECT * FROM finite;
> 1
> 1
> sqlite>
>
> This does the same basic thing, but we?ve limited the number of possible
> results to only be 2. Ok, so that?s all well and good, but what is this
> good for? It turns out, a lot. Say you wanted to generate a table on the
> fly containing the numbers one through ten:
>
> sqlite> WITH RECURSIVE ten(x) AS (
> SELECT 1
> UNION ALL
> SELECT x+1 FROM ten WHERE x<10
> )
> SELECT * FROM ten;
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
> sqlite>
>
> To do this, we?ve defined a CTE named ?ten?, with a single column named ?x?
> (the column name is optional, but in this case we need it to refer to
> later). Then in the recursive UNION ALL, we keep adding one more row to the
> result set ? each one larger than the row before ? until we reach a limit
> of 10.
>
> So CTEs can be used to generate a wide array of different types of data ?
> such as date ranges, perhaps to join against when doing 

[sqlite] The Simplest SQLite Common Table Expression Tutorial

[Swithun Crowe]

Hello

DB> Hey all, just wanted to share this in case anybody is also looking for
DB> a very simple tutorial for CTE's in sqlite:

DB> 
http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-expression-tutorial/

Yes, that was a great introduction to recursive SQL. Last week I wrote a
CTE to generate Lindenmayer (L) Systems drawings, and it helped to
understand why my code worked!

This cuts out dozens of lines of procedural code.

Thanks.

WITH RECURSIVE follow_rule(instruction_id, depth, position, pattern_id) AS
(
SELECT instruction_id, 0, 0, pattern_id
  FROM patterns
INNER JOIN (SELECT instruction_id, pattern_id
  FROM seeds
  ORDER BY position) AS seeds USING (pattern_id)
 UNION ALL
SELECT r.expansion_instruction_id, follow_rule.depth + 1, r.position, 
follow_rule.pattern_id
  FROM (SELECT instruction_id, expansion_instruction_id, position
  FROM instructions
INNER JOIN expansions USING (instruction_id)
  ORDER BY expansions.position) AS r
INNER JOIN follow_rule USING (instruction_id)
INNER JOIN patterns USING (pattern_id)
 WHERE follow_rule.depth < patterns.depth
  ORDER BY 2 DESC, 3 ASC
)
SELECT pattern_id, type, value, class FROM follow_rule
INNER JOIN instructions USING (instruction_id);

DROP TABLE IF EXISTS instructions;
CREATE TABLE instructions (
  instruction_id INTEGER PRIMARY KEY AUTOINCREMENT,
  type INTEGER, -- draw, turn, push/pop, expand etc.
  value REAL DEFAULT 0, -- rotation, push or pop etc.
  class INTEGER DEFAULT 0 -- class for draw instructions
);

DROP TABLE IF EXISTS expansions;
CREATE TABLE expansions (
  instruction_id INTEGER, -- parent instruction ID
  expansion_instruction_id INTEGER,
  position INTEGER
);

DROP TABLE IF EXISTS patterns;
CREATE TABLE patterns (
  pattern_id INTEGER PRIMARY KEY AUTOINCREMENT,
  depth INTEGER
);

DROP TABLE IF EXISTS seeds;
CREATE TABLE seeds (
  pattern_id INTEGER,
  instruction_id INTEGER,
  position INTEGER
);

CREATE INDEX expansionIdx ON expansions(instruction_id);
CREATE INDEX seedsIdx ON seeds(pattern_id);

[sqlite] SQLITE_BUSY_SNAPSHOT from BEGIN IMMEDIATE

[Florian Weimer]

Relatively rarely, while starting a transaction on a concurrently
modified SQLite database in WAL mode, I get a SQLITE_BUSY_SNAPSHOT
error for just-prepared "BEGIN IMMEDIATE" statement.  When I look at
the list of statements with sqlite3_stmt_next, I cannot find any busy
statement.  Is it possible that a "BEGIN IMMEDIATE" statement by
itself fails with this error code?  This is a bit suprising.  I would
have expected a regular SQLITE_BUSY because this is not exactly a
snapshot upgrade failure as far as the application is concerned (it is
possible to try again immediately and succeed).

This is with SQLite 3.8.7.1 from Debian jessie.

[sqlite] Query containing correlated subquery gets "stuck"

[gunnar]

Hi Clemens et all,

I found no difference when executing the query while inserting and not 
inserting records at the same time. For one of the identical databases 
the query still doesn't return.

But I think the reason is in the correlated subquery together with how 
the database is filled (they're only identical in structure, not in 
contents.) In the problematic database there are many more modfications 
per order:

sqlite> select distinct(server_order_id), count(*) as count from 
ordercallback cb where 
sessionuuid='eebf348d-9b03-4e85-a54f-ff92ab5e998f' and endstate=0 and 
working=1 group by server_order_id order by count desc limit 10;
4f3fbb2e-b83e-4c37-8ec2-137105365ac9|22174
7a6f54b5-05b9-4685-8181-3e55a6b5f573|22066
6ab4cad4-d0ed-46a0-aaac-72e35f17bbe6|10388
faec0f1a-cd49-4629-8e35-fbc00ca5cae9|9976
dae76b8c-743a-47fd-8201-014cb70e307b|6783
bf070394-8d4b-47da-9adb-ce016f48b550|6310
aaf06a7b-ab21-45c4-a202-954d5c9301ba|6280
ed9eb05f-03c8-41aa-b727-3557cc6513a6|5162
a344b519-e13d-4b09-b28c-ce36d78bbcfb|5120
61deaa60-cca6-4bd4-a249-7f7e12273abc|4386


sqlite> select distinct(server_order_id), count(*) as count from 
ordercallback cb where 
sessionuuid='92712e97-14a6-4292-9ed4-0c93eb92ce29' and endstate=0 and 
working=1 group by server_order_id order by count desc limit 10;
1931780b-cd5c-4b38-a99c-1a4fa6625b01|331
9d3cc94f-ce44-43f4-a95b-ef3a11587ad5|188
96ffa303-2086-4c94-9b53-ed2f35b11cbc|164
d73cb810-f5f6-4361-8eaf-cf26e2436901|160
7c1b11a6-5452-4e62-a446-bd47a781c30e|153
fcf6a84d-da53-439f-a570-2b664e70c0e9|151
33537f7b-f5b6-4c33-a4b7-654d5733830f|112
3669e572-33dd-4026-9e5e-5ec1ebff5bfa|101
2d9fad5e-8664-487b-8c87-bc5842c6e72b|91
ce2c0ff7-141d-43ee-aebe-b8fea1931e57|87



So i suspect the correlated query in:

SELECT count(*) FROM ordercallback cb WHERE sessionuuid=(select uuid 
from session where date = (select max(date) from session)) AND 
endstate=0 AND working=1 AND cb_seq_num = (SELECT max(cb_seq_num) FROM 
ordercallback WHERE server_order_id=cb.server_order_id AND  
sessionuuid=cb.sessionuuid AND working=1);

has some difficulties.


But I am not sure yet, so will keep investigating.
Perhaps someone knows a way how I can speed up the original query (I 
prefer not to make a temp table and make a join after that, although I 
know that works).

Regards,
Gunnar




On 09/25/2015 05:04 PM, Clemens Ladisch wrote:
> gunnar wrote:
>> What do you exactly mean with "But in any case, as others have already
>> said, it is not possible for a write transaction to lock out a read
>> transaction _in the middle_."? I do see that records are being inserted
>> while I made those stack traces.
> The inserted records are appended to the write-ahead-log.  Any read-only
> transaction started previously ignores them, and continues to read the
> old data.
>
> Once a transaction has started, it is guaranteed to be able to read
> everything in the database.
>
>> I have a fifteen minute window / 24hours, is it enough for VACUUM?
> Try it with a copy.  (Anyway, VACUUM uses a proper transaction, so the
> worst that can happen is that the other processes run into their
> timeouts.)
>
>
> Another possibility for the slowdown might be a huge WAL file.
> Try "PRAGMA wal_checkpoint(TRUNCATE)", but this might be as slow as
> a VACUUM.
>
>
> Regards,
> Clemens
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

[sqlite] The Simplest SQLite Common Table Expression Tutorial

[Drago, William @ CSG - NARDA-MITEQ]

David,

Thank you for sharing that. I found it very helpful.

Regards,

--
Bill Drago
Staff Engineer
L3 Narda-MITEQ
435 Moreland Road
Hauppauge, NY 11788
631-272-5947 / William.Drago at L-3COM.com


> -Original Message-
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-
> users-bounces at mailinglists.sqlite.org] On Behalf Of David Barrett
> Sent: Friday, September 25, 2015 4:17 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] The Simplest SQLite Common Table Expression Tutorial
>
> Hey all, just wanted to share this in case anybody is also looking for
> a very simple tutorial for CTE's in sqlite:
>
> http://blog.expensify.com/2015/09/25/the-simplest-sqlite-common-table-
> expression-tutorial/
>
> The Simplest SQLite Common Table Expression Tutorial
>
> I?ve been trying to wrap my head aroundCommon Table Expressions
>  for a while, and all the
> tutorials I?ve read started out with ?simple? examples that were way
> too advanced for me to follow. Here?s my attempt to write a tutorial
> that starts as simple as possible.
>
> First, let?s start with the simplest query:
>
> sqlite> SELECT 1;
> 1
> sqlite>
>
> All this does is return a result set containing a row. Next, consider
> the simplest subquery:
>
> sqlite> SELECT * FROM ( SELECT 1 );
> 1
> sqlite>
>
> This just selects all the results from the subquery ? which in this
> case, is just a single row. A ?Common Table Expression? is basically
> the same as a subquery, except assigned a name and defined prior to the
> query in which it?s referenced. Accordingly, the simplest CTE version
> of the above query would be like:
>
> sqlite> WITH one AS ( SELECT 1 )
> SELECT * FROM one;
> 1
> sqlite>
>
> Breaking that down a bit further:
>
>- We?ve defined a common table expression named ?one?
>- We?ve ?filled? it with the output of SELECT 1, which is just 1 row
>- Then we selected everything from ?one?
>- Such that the final result is a single value: 1
>
> But a CTE can have multiple columns, too, and those columns can be
> assigned
> names:
>
> sqlite> WITH twoCol( a, b ) AS ( SELECT 1, 2 )
> SELECT a, b FROM twoCol;
> 1|2
> sqlite>
>
> Similarly, a CTE can query other tables:
>
> sqlite> CREATE TABLE foo ( bar INTEGER ); INSERT INTO foo VALUES(1);
> sqlite> INSERT INTO foo VALUES(2); SELECT * FROM foo;
> 1
> 2
> sqlite> WITH fooCTE AS (SELECT * FROM foo)
> SELECT * FROM fooCTE;
> 1
> 2
> sqlite>
>
> Additionally, you can define as many CTEs as you want in a single
> query:
>
> sqlite> WITH aCTE AS (SELECT 'a'),
>  bCTE AS (SELECT 'b')
> SELECT * FROM aCTE, bCTE;
> a|b
> sqlite>
>
> So, common table expressions can be used to restructure a query to make
> it more readable, by moving the subqueries out in front. But the real
> power of common table expressions is when you define an expression that
> recursively selects itself. They key to this is using a ?Compound
> Select Statements?, such as the UNION ALL operator. This just combines
> two result sets into one (so long as they have the same number of
> columns):
>
> sqlite> SELECT 1, 2
> UNION ALL
> SELECT 3, 4;
> 1|2
> 3|4
> sqlite>
>
> Take this example:
>
> sqlite> WITH RECURSIVE infinite AS (
> SELECT 1
> UNION ALL
> SELECT * FROM infinite
> )
> SELECT * FROM infinite;
> ^CError: interrupted
> sqlite>
>
> Let?s break down why that query will never finish:
>
>- ?WITH RECURSIVE infinite? defines a common table expression named
>?infinite?
>- ?SELECT 1? seeds that CTE?s output with a single row ? containing
> ?1?
>- Next the ?UNION ALL? says ?combine the output of what?s on the
> left,
>with the output of what?s on the right
>- And on the right we do ?SELECT * FROM infinite? ? meaning, select
>everything currently in the table.
>- The result is we?re defining a common table expression named
>?infinite? to be the union of ?a single row? and ?all other rows?.
>- Because no ?cap? has been placed on this (via a WHERE or LIMIT),
> this
>means we?ve defined an infinitely recurring CTE. Fun!
>
> So we can ?cap? that CTE by writing a query like:
>
> sqlite> WITH RECURSIVE finite AS (
> SELECT 1
> UNION ALL
> SELECT * FROM finite LIMIT 2
> )
> SELECT * FROM finite;
> 1
> 1
> sqlite>
>
> This does the same basic thing, but we?ve limited the number of
> possible results to only be 2. Ok, so that?s all well and good, but
> what is this good for? It turns out, a lot. Say you wanted to generate
> a table on the fly containing the numbers one through ten:
>
> sqlite> WITH RECURSIVE ten(x) AS (
> SELECT 1
> UNION ALL
> SELECT x+1 FROM ten WHERE x<10
> )
> SELECT * FROM ten;
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
> sqlite>
>
> To do this, we?ve defined a 

[sqlite] Creating a stable database

[Aaron Digulla]

Am Montag, 28. September 2015 15:55 CEST, "R.Smith"  
schrieb: 

> 
> 
> On 2015-09-28 11:30 AM, Aaron Digulla wrote:
> >   
> > Am Freitag, 25. September 2015 20:46 CEST, Warren Young  > etr-usa.com> schrieb:
> >   
> >>
> >> That suggests a good test for Aaron, then:
> >>
> >>  sqlite3 my.db .dump > dump.sql
> >>  sqlite3 a.db < dump.sql
> >>  sqlite3 b.db < dump.sql
> > Thank you very much. This set of commands has allowed me to narrow down the 
> > root cause to the Java JDBC driver by xerial 
> > (https://github.com/xerial/sqlite-jdbc):
> >
> > When I dump the database and recreate it two times with the command line 
> > sqlite3 tool, I get the exact same files. Looking at the source code of the 
> > JDBC driver, I can't see anything obviously wrong, either. There is one 
> > malloc() where it doesn't clear the memory but
> > I'm not creating functions, so the code is never used.
> >
> > The other malloc() when I read metadata from the database.
> >
> > That leaves me with a bug in my code or some really odd behavior because of 
> > the Java VM.
> 
> Also note that if the Java VM builds the DB in any way other than a 

> straight dump-the-sql-to-SQLite-Execute method, it might for instance 
> add comments to the Table definitions, often if this is done a date / 
> time is included, which by itself will cause different byte images for 
> the DB file.

I wrote the client side of the code (which executes CREATE TABLE and INSERT 
statements); there is nothing there.

Also, the JDBC driver is only a thin wrapper around the C API, so I don't 
expect oddities there. But I think I found it, now:

There is code which optimizes imports from CSV files by remembering the 
timestamp, size, etc. The API was a bit stubborn, which means I couldn't 
disable this code - instead I fed the code fake input files (table name = file 
name, size=0, timestamp=now) ... The result was a series of small changes in 
several places (= a new place every time the last page for the timestamp table 
was full).

sqlite works :-)

Regards,

-- 
Aaron "Optimizer" Digulla a.k.a. Philmann Dark
"It's not the universe that's limited, it's our imagination.
Follow me and I'll show you something beyond the limits." 
http://blog.pdark.de/ 

[sqlite] Creating a stable database

[R.Smith]

On 2015-09-28 11:30 AM, Aaron Digulla wrote:
>   
> Am Freitag, 25. September 2015 20:46 CEST, Warren Young  
> schrieb:
>   
>>
>> That suggests a good test for Aaron, then:
>>
>>  sqlite3 my.db .dump > dump.sql
>>  sqlite3 a.db < dump.sql
>>  sqlite3 b.db < dump.sql
> Thank you very much. This set of commands has allowed me to narrow down the 
> root cause to the Java JDBC driver by xerial 
> (https://github.com/xerial/sqlite-jdbc):
>
> When I dump the database and recreate it two times with the command line 
> sqlite3 tool, I get the exact same files. Looking at the source code of the 
> JDBC driver, I can't see anything obviously wrong, either. There is one 
> malloc() where it doesn't clear the memory but
> I'm not creating functions, so the code is never used.
>
> The other malloc() when I read metadata from the database.
>
> That leaves me with a bug in my code or some really odd behavior because of 
> the Java VM.

Also note that if the Java VM builds the DB in any way other than a 
straight dump-the-sql-to-SQLite-Execute method, it might for instance 
add comments to the Table definitions, often if this is done a date / 
time is included, which by itself will cause different byte images for 
the DB file.

[sqlite] Creating a stable database

[Simon Slavin]

On 28 Sep 2015, at 10:30am, Aaron Digulla  wrote:

> That leaves me with a bug in my code or some really odd behavior because of 
> the Java VM.

That's a nice bit of detective work.  Is there anyone on the Java VM team you 
can contact and explain the bug to ?

Simon.

[sqlite] Creating a stable database

[Aaron Digulla]

Am Freitag, 25. September 2015 20:46 CEST, Warren Young  
schrieb: 

> On Sep 25, 2015, at 11:59 AM, Richard Hipp  wrote:
> > 
> > On 9/25/15, Aaron Digulla  wrote:
> >> 
> >> I was wondering if it was possible to create the same database (= no binary
> >> difference) twice with sqlite.
> > 
> > It works fine to create identical database files when I try it:
> > 
> >   drh at bella:~/sqlite/bld$ ./sqlite3 db1  >   drh at bella:~/sqlite/bld$ ./sqlite3 db2  
> That suggests a good test for Aaron, then:
> 
> sqlite3 my.db .dump > dump.sql
> sqlite3 a.db < dump.sql
> sqlite3 b.db < dump.sql

Thank you very much. This set of commands has allowed me to narrow down the 
root cause to the Java JDBC driver by xerial 
(https://github.com/xerial/sqlite-jdbc):

When I dump the database and recreate it two times with the command line 
sqlite3 tool, I get the exact same files. Looking at the source code of the 
JDBC driver, I can't see anything obviously wrong, either. There is one 
malloc() where it doesn't clear the memory but 
I'm not creating functions, so the code is never used.

The other malloc() when I read metadata from the database.

That leaves me with a bug in my code or some really odd behavior because of the 
Java VM.

Regards, 

-- 
Aaron "Optimizer" Digulla a.k.a. Philmann Dark
"It's not the universe that's limited, it's our imagination.
Follow me and I'll show you something beyond the limits." 
http://blog.pdark.de/