words: 482

We have some runnable SQL examples with assertion under the sequelize/raw directory.

These examples are written in the Sequelize library using raw queries.

Sequelize is used minimally, just to feed raw queries in transparently to any underlying database, and get minimally parsed results out for us, which we then assert with standard JavaScript. The queries themselves are all written by hand.

By default the examples run on SQLite. Just like the examples from sequelize example, you can set the database at runtime as:

./index.js or ./index.js l: SQLite
./index.js p: PostgreSQL. You must manually create a database called tmp and ensure that peer authentication works for it

Here we list only examples which we believe are standard SQL, and should therefore work across different SQL implementations:

nodejs/sequelize/raw/index.js: basic hello world to demonstrate the setup and very simple functionality
nodejs/sequelize/raw/many_to_many.js: illustrates many-to-many relations with JOIN. Contains:
SQL transaction examples:
- nodejs/sequelize/raw/commit_error.js: stackoverflow.com/questions/27245101/why-should-we-use-rollback-in-sql-explicitly/27245234#27245234 and stackoverflow.com/questions/48277519/how-to-use-commit-and-rollback-in-a-postgresql-function/48277708#48277708 suggest that on PostgreSQL, once something fails inside a transaction, all queries in the current transaction are ignored, and COMMIT simply does a ROLLBACK. Let's check. Yup, true for Postgres, but false for SQLite, SQLite just happily runs anything it can, you really need ROLLBACK for it.
- SQL isolation level example
GROUP BY and SQL aggregate functions:
- nodejs/sequelize/raw/group_by_extra_column.js: let's see if it blows up or not on different DB systems, sqlite3 Node.js package allows it:
  - github.com/sequelize/sequelize/issues/5481#issuecomment-964387232
  - dba.stackexchange.com/questions/141594/how-select-column-does-not-list-in-group-by-clause/141600 says that it was allowed in SQL:1999 when there are no ambiguities due to constraints, e.g. when grouping by unique columns
  - github.com/postgres/postgres/blob/REL_13_5/src/test/regress/sql/functional_deps.sql#L27 shows that PostgreSQL wants it to work for UNIQUE NOT NULL, but they just haven't implemented it as of 13.5, where it only works if you group by PRIMARY KEY
  - dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b also says that UNIQUE NOT NULL doesn't work. Dan Lenski then points to rationale mailing list thread:
- nodejs/sequelize/raw/group_by_max_full_row.js: here we try to get the full row of each group at which a given column reaches the max of the group
  - Postgres: has SELECT DISCINTCT ON which works perfectly if you only want one row in case of multiple rows attaining the max. ON is an extension to the standard unfortunately: www.postgresql.org/docs/9.3/sql-select.html#SQL-DISTINCT Docs specify that it always respects ORDER BY when selecting the row.
    stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column asks it without the multiple matches use case
    stackoverflow.com/questions/586781/postgresql-fetch-the-rows-which-have-the-max-value-for-a-column-in-each-group/587209#587209 also present in simpler form at stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of/123481#123481 gives a very nice OUTER JOIN only solution! Incredible, very elegant.
    dba.stackexchange.com/questions/171938/get-only-rows-with-max-group-value asks specifically the case of multiple matches to the max
  - SQLite:
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/48328243#48328243 teaches us that in SQLite min and max are magic and guarantee that the matching row is returned
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/72996649#72996649 Ciro Santilli uses the magic of ROW_NUMBER
    stackoverflow.com/questions/17277152/sqlite-select-distinct-of-one-column-and-get-the-others/71924314#71924314 get any full row without specifying which, we teach how to specify
    code.djangoproject.com/ticket/22696 WONTFIXed DISTINCT ON
    stackoverflow.com/questions/50846722/what-is-the-difference-between-postgres-distinct-vs-distinct-on/72997494#72997494 DISTINCT vs DISTINCT ON, somewhat related question
  - stackoverflow.com/questions/5803032/group-by-to-return-entire-row asks how to take the top N with distinct after order limit. I don't know how to do it in Postgres
- nodejs/sequelize/raw/most_frequent.js: illustrates a few variants of findind the mode, including across GROUP
  - stackoverflow.com/questions/12235595/find-most-frequent-value-in-sql-column/72979899#72979899
- nodejs/sequelize/raw/group_by_max_n.js: get the top N in each group
order results in the same order as IN:
- MysQL: stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause
- PostgreSQL:
  - stackoverflow.com/questions/866465/order-by-the-in-value-list
  - dba.stackexchange.com/questions/59394/order-of-returned-rows-with-in-statement
LIMIT by a running total: TODO links

SQL implementation

words: 1k articles: 23

IBM Db2

MySQL

words: 183 articles: 3

sudo mysql

works on Ubuntu 20.10.

Create user for further logins without sudo askubuntu.com/questions/915585/how-to-login-mysql-shell-when-mysql-have-no-password/1325689#1325689:

sudo mysql -e "CREATE USER $USER"

Run command from CLI stackoverflow.com/questions/1602904/how-do-you-run-a-single-query-through-mysql-from-the-command-line

mysql -e 'SHOW DATABASES'

Create test user with password:

sudo mysql -e 'CREATE USER user0 IDENTIFIED WITH mysql_native_password BY "a"'
sudo mysql -e 'GRANT ALL PRIVILEGES ON database_name.* TO "user0"'

and login as that user:

mysql -u user0 -p

mysql -u user0 -pmypassword

The IDENTIFIED WITH mysql_native_password part is to overcome "Client does not support authentication protocol requested by server" when connecting from Node.js.

List users:

sudo mysql -e 'SELECT * FROM mysql.user'

View permissions for each user on each DB: serverfault.com/questions/263868/how-to-know-all-the-users-that-can-access-a-database-mysql

sudo mysql -e 'SELECT * FROM mysql.db'

List databases:

sudo mysql -e 'SHOW DATABASES'

Create database:

sudo mysql -e 'CREATE DATABASE mydb0'

Destroy database:

sudo mysql -e 'DROP DATABASE mydb0'

Show tables in database:

sudo mysql -e 'SHOW TABLES' mydb0

or:

sudo mysql -e 'SHOW TABLES FROM mydb0'

mysqldump

words: 1 articles: 1

dev.mysql.com/doc/refman/8.0/en/mysqldump.html

mysqldump to CSV

words: 1

MariaDB

words: 19

Dude's a legend. Sells company for a few million. Then forks the open source project next year. Love it.

PostgreSQL (Postgres)

words: 931 articles: 6

PostgreSQL feels good.

Its feature set is insanely large! Just look at stuff like: stackoverflow.com/questions/1986491/sql-split-string-by-space-into-table-in-postgresql/1993058#1993058

Had a look at the source tree, and also felt good.

If Oracle is the Microsoft of database, Postgres is the Linux, and MySQL (or more precisely MariaDB) is the FreeBSD (i.e. the one that got delayed by legal issues). Except that their software licenses were accidentally swapped.

The only problem with Postgres is its name. PostgreSQL is so unpronounceable and so untypeable that you should just call it "Postgres" like everyone else.

PostgreSQL getting started

words: 496 articles: 1

On Ubuntu 20.10 PostgreSQL 12.6, login with psql on my default username without sudo fails with: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist

This is the one that worked on Ubuntu 21.04: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist/38444152#38444152

sudo -u postgres createuser -s $(whoami)
createdb $(whoami)

Explanation:

sudo -u postgres uses the postgres user via peer authentication
-s in createuser -s: make it a superuser
createdb: TODO why do we have to create a table with the same name as the user? Otherwise login fails.

You can now run psql without any password. This works without password due to peer authentication,

sudo cat /etc/postgresql/12/main/pg_hba.conf

shows that peer authentication is available to all users apparently:

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer

List users:

psql -c '\du'

output:

                                    List of roles
  Role name  |                         Attributes                         | Member of 
-------------+------------------------------------------------------------+-----------
 ciro        | Superuser, Create role, Create DB                          | {}
 owning_user |                                                            | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Delete user later on:

psql -c 'DROP USER username;'

Create a database:

createdb testdb0

Help toplevel:

help

Get help for Postgres commands such as \h and so on:

\?

List supported SQL commands:

\h

Show syntax for one type of command:

\h SELECT

List all databases:

psql -c '\l'

which shows:

    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-------------+----------+----------+-------------+-------------+-----------------------
 ciro        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 testdb0     | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)

Delete a database:

psql -c 'DROP DATABASE "testdb0";'

If you didn't give a database from the command line e.g.:

psql

you can do that afterwards with:

\c testdb0

Let's create a table and test that it is working:

psql testdb0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'

List tables, no special tables:

psql testdb0 -c '\dt'

gives:

        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | table0 | table | ciro
(1 row)

View table schema: stackoverflow.com/questions/109325/postgresql-describe-table

psql testdb0 -c '\d+ table0'

output:

                                      Table "public.table0"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 int0   | integer       |           |          |         | plain    |              | 
 char0  | character(16) |           |          |         | extended |              |

Insert some data into it and get the data out:

psql testdb0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
psql testdb0 -c 'SELECT * FROM table0;'

output:

 int0 |      char0
------+------------------
    2 | two
    3 | three
    5 | five
    7 | seven
(4 rows)

Delete the table:

psql testdb0 -c 'DROP TABLE table0;'

PostgreSQL tips

words: 12

output one column per line: stackoverflow.com/questions/9604723/alternate-output-format-for-psql-showing-one-column-per-line-with-column-name
PostgreSQL does not automatically index foreign keys! stackoverflow.com/questions/970562/postgres-and-indexes-on-foreign-keys-and-primary-keys

Create a test user in PostgreSQL

words: 252

In order to create a test user with password instead of peer authentication, let's create test user:

createuser -P user0
createdb user0

-P makes it prompt for the users password.

Alternatively, to create the password non-interactively stackoverflow.com/questions/42419559/postgres-createuser-with-password-from-terminal:

psql -c "create role NewRole with login password 'secret'"

Can't find a way using the createuser helper.

We can then login with that password with:

psql -U user0 -h localhost

which asks for the password we've just set, because the -h option turns off peer authentication, and turns off password authentication.

The password can be given non-interactively as shown at stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively with the PGPASSWORD environment variable:

PGPASSWORD=a psql -U user0 -h localhost

Now let's create a test database which user0 can access with an existing superuser account:

createdb user0db0
psql -c 'GRANT ALL PRIVILEGES ON DATABASE user0db0 TO user0'

We can check this permission with:

psql -c '\l'

which now contains:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 user0db0  | ciro     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/ciro             +
           |          |          |             |             | ciro=CTc/ciro        +
           |          |          |             |             | user0=CTc/ciro

The permission letters are explained at:

user0 can now do the usual table operations on that table:

PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'SELECT * FROM table0;'

Peer authentication

words: 12

www.postgresql.org/docs/13/auth-peer.html

Uses the name of the current Linux user to login without a password.

PostgreSQL logging

words: 44

stackoverflow.com/questions/722221/how-to-log-postgresql-queries

Ubuntu 21.10 has a certain default level of logging by default to:

/var/log/postgresql/postgresql-13-main.log

but it does not log everything, only/mostly errors it seems.

Setting:

log_statement = 'all'

under:

/etc/postgresql/13/main/postgresql.conf

and then restarting the server:

sudo service restart postgresql

just works.

Realtime monitoring for long queries instead: stackoverflow.com/questions/8597516/app-to-monitor-postgresql-queries-in-real-time

PostgreSQL serialization failure

words: 46

When using SQL REPEATABLE READ isolation level and SQL SERIALIZABLE isolation level, concurrent transactions may fail with a serialization failure, and then you might need to retry them. You server code or your ORM must always account for that.

A good way to explore when it happens is to use the example

Microsoft SQL Server

Oracle Database

words: 36

Often known simply as SQL Server, a terrible thing that makes it impossible to find portable SQL answers on Google! You just have to Google by specific SQL implementation unfortunately to find anything about the open source ones.

SQLite

words: 320 articles: 8

The minimalism, serverlessness/lack of temporary caches/lack of permission management, Hipp's religious obsession with efficiency, the use of their own pure Fossil version control ^[ref]. Wait, scrap that last one. Pure beauty!

Official Git mirror: github.com/sqlite/sqlite

Create a table

sqlite3 db.sqlite3 "
CREATE TABLE 'IntegerNames' (int0 INT, char0 CHAR(16));
INSERT INTO 'IntegerNames' (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');
"

List tables:

sqlite3 db.sqlite3 '.tables'

output:

IntegerNames

Show schema of a table:

sqlite3 db.sqlite3 '.schema IntegerNames'

outputs the query that would generate that table:

CREATE TABLE IF NOT EXISTS 'IntegerNames' (int0 INT, char0 CHAR(16));

Show all data in a table:

sqlite3 db.sqlite3 'SELECT * FROM IntegerNames'

output:

2|two
3|three
5|five
7|seven

 Tagged

D. Richard Hipp

SQLite import CSV

articles: 1

SQLite import CSV from stdin

SQLite benchmark

words: 5

Python sequence test data generation: stackoverflow.com/questions/18219779/bulk-insert-huge-data-into-sqlite-using-python/76659706#76659706

SQLite C extension

words: 18

Example: sqlite/ip.c, adapted from www.sqlite.org/loadext.html, also mentioned explained at: stackoverflow.com/questions/7638238/sqlite-ip-address-storage/76520885#76520885.

Sample usage in the test program: sqlite/test.sh.

Docs: www.sqlite.org/loadext.html

SQLite isolation levels

words: 27

www.sqlite.org/pragma.html#pragma_read_uncommitted mentions:

____
The default isolation level for SQLite is SERIALIZABLE
____

It does not appear possible to achieve the other two levels besides SERIALIZABLE and READ UNCOMMITED

www.sqlite.org/isolation.html

Node.js SQLite bindings

words: 160 articles: 2

`sqlite3` Node.js package

words: 111

Includes its own copy of sqlite3, you don't use the system one, which is good to ensure compatibility. The version is shown at: github.com/mapbox/node-sqlite3/blob/918052b538b0effe6c4a44c74a16b2749c08a0d2/deps/common-sqlite.gypi#L3 SQLite source is tracked compressed in-tree: github.com/mapbox/node-sqlite3/blob/918052b538b0effe6c4a44c74a16b2749c08a0d2/deps/sqlite-autoconf-3360000.tar.gz horrendous. This explains why it takes forever to clone that repository. People who don't believe in git submodules, there's even an official Git mirror at: github.com/sqlite/sqlite

It appears to spawn its own threads via its C extension (since JavaScript is single threaded and and SQLite is not server-based), which allows for parallel queries using multiple threads: github.com/mapbox/node-sqlite3/blob/v5.0.2/src/threading.h

Hello world example: nodejs/node-sqlite3/index.js.

As of 2021, this had slumped back a bit, as maintainers got tired. Unmerged pull requests started piling more, and better-sqlite3 Node.js package started pulling ahead a little.

github.com/mapbox/node-sqlite3/issues/1381 FATAL ERROR: Error::ThrowAsJavaScriptException napi_throw with Node.js worker_threads vs better-sqlite3 Node.js package github.com/JoshuaWise/better-sqlite3/issues/237

`better-sqlite3` Node.js package

words: 49

As claimed on their README, their operation truly appears to be 10x faster than the node-sqlite package!! It is insane!! How can that other package still exist at all?

The only big problem was the lack of ORM, but people are looking into that by adding it to Sequelize:

SQL function

words: 10 articles: 4

SQL set returning function

words: 1 articles: 1

PostgreSQL: www.postgresql.org/docs/current/functions-srf.html

SQL `genenerate_series`

SQL aggregate function

words: 9 articles: 1

SQL `COUNT` function

words: 9

Have a look at some interesting examples under nodejs/sequelize/raw/many_to_many.js.

SQL keyword

words: 497 articles: 26

SQL CASCADE

stackoverflow.com/questions/59297/when-why-to-use-cascading-in-sql-server

DELETE (SQL)

words: 13 articles: 1

 Tagged

DELETE with JOIN

Delete all duplicate rows in SQL

words: 13

SQLite with rowid: stackoverflow.com/questions/8190541/deleting-duplicate-rows-from-sqlite-database
- PostgreSQL is ctid: stackoverflow.com/questions/14626481/rowid-equivalent-in-postgres-9-2
SQL Server has crazy "CTEs" change backing table extension: stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server

GROUP BY (SQL)

articles: 1

HAVING (SQL)

INSERT (SQL)

words: 68 articles: 2

Upsert (ON CONFLICT UPDATE)

words: 68 articles: 1

UPSERT is extremely handy, and reduces the number of find, check on server, update loops. But RETURNING is a fundamental part of that (to get the updated/existing) ID. Can't believe SQL hasn't standardized it yet as of 2022. But both SQLite and Postgres support it with similar syntax thankfully.

nodejs/sequelize/raw/upsert.js

Upsert with `NOT NULL` column

words: 21

Attempt at nodejs/sequelize/raw/upsert.js:

stackoverflow.com/questions/48816629/on-conflict-do-nothing-in-postgres-with-a-not-null-constraint OP unable to provide a minimal exampe, but it is likely the problem
dba.stackexchange.com/questions/292428/postgresql-upsert-issue-with-not-null-columns

Related on more complex constraints:

JOIN (SQL)

words: 28 articles: 3

SQL prefix column names with the table they came from

words: 28

It is mind blowing that this is not possible... the only way to avoid ambiguity in JOINs with column name conflicts is to give aliases to each column...

INNER JOIN

OUTER JOIN

LIKE (SQL)

SELECT (SQL)

words: 29 articles: 1

SELECT FOR UPDATE

words: 29

An example where SELECT FOR UPDATE is a good solution to an use case can be seen at: nodejs/sequelize/raw/parallel_select_and_update.js.

SELECT FOR UPDATE vs/together with the SQL transaction isolation level is commented at: stackoverflow.com/questions/10935850/when-to-use-select-for-update.

SQL stored procedure

articles: 2

SQL FUNCTION keyword

SQL PROCEDURE

SQL TRIGGER

words: 60

SQL's implementation of database triggers.

This feature is really cool, as it allows you to keep caches up to date!

In particular, everything that happens in a trigger happens as if it were in a transaction. This way, you can do less explicit transactions when you use triggers. It is a bit like the advantages of SQL CASCADE.

DBMS:

ORM:

Sequelize: SQL TRIGGER in Sequelize

ISO SQL TRIGGER syntax

words: 99 articles: 1

TODO what is the standard compliant syntax?

PostgreSQL requires you to define a SQL stored procedure: stackoverflow.com/questions/28149494/is-it-possible-to-create-trigger-without-execute-procedure-in-postgresql Their syntax may be standard compliant, not sure about the EXECUTE part. Their docs: www.postgresql.org/docs/current/sql-createtrigger.html

SQLite does not support SQL stored procedures at all, so maybe that's why they can't be standard compliant here: stackoverflow.com/questions/3335162/creating-stored-procedure-in-sqlite

SQL:1999 11.38 covers "Trigger definition". The Abstract syntax tree starts with the CREATE TRIGGER and ends in:

<triggered SQL statement> ::=
  <SQL procedure statement>

This is defined at 13.5 "SQL procedure statement", but that is humongous and I'm not sure what it is at all.

nodejs/sequelize/raw/trigger_count.js

words: 16

In this example we cache track the number of posts per user on a cache column.

nodejs/sequelize/raw/trigger_count.js was not rendered because it is too large (> 2000 bytes)

UNION (SQL)

words: 84

Basic example tested on SQLite 3.40.1, Ubuntu 23.04:

sqlite3 :memory: 'select 1 union select 2'

output:

1
2

Two columns two rows:

sqlite3 :memory: <<EOF
select * from (values (1, 2), (2, 3))
union
select * from (values (2, 3), (3, 4))
EOF

output:

1|2
2|3
3|4

Note how duplicates are removed, to keep them we UNION ALL instead:

sqlite3 :memory: <<EOF
select * from (values (1, 2), (2, 3))
union all
select * from (values (2, 3), (3, 4))
EOF

output:

1|2
2|3
2|3
3|4

UPDATE (SQL)

words: 116 articles: 3

Update multiple rows with different values in a single SQL query

words: 34

This happens when you calculate a bunch of values on your program, and then want to save the to SQL.

Upsert is an option, but it fails if you have a NOT NULL column: Upsert with NOT NULL column

Bibliography:

UPDATE with JOIN (SQL)

words: 82 articles: 1

Dumping examples under nodejs/sequelize/raw/many_to_many.js.

Not possible without subqueries in the standard syntax, a huge shame: stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server

The UPDATE + FROM extension exists in a few DBMSs:

ORM:

Sequelize: UPDATE with JOIN in Sequelize

 Tagged

UPDATE with JOIN in Sequelize

DELETE with JOIN (SQL)

words: 53

Demo under: nodejs/sequelize/raw/many_to_many.js.

NO way in the SQL standard apparently, but you'd hope that implementation status would be similar to UPDATE with JOIN, but not even!

PostgreSQL: possible with DELETE FROM USING: stackoverflow.com/questions/11753904/postgresql-delete-with-inner-join
SQLite: not possible without subqueries as of 3.35 far: stackoverflow.com/questions/24511153/how-delete-table-inner-join-with-other-table-in-sqlite, Does not appear to have any relevant features at: www.sqlite.org/lang_delete.html

ORM

Sequelize: no support of course: stackoverflow.com/questions/40890131/sequelize-destroy-record-with-join

SQL standard

words: 28 articles: 2

A quick look at PostgreSQL's compliance notes: www.postgresql.org/docs/13/features.html shows the complete utter mess that this standard is. Multiple compliance levels that no one fully implements and optional features everywhere.

 Tagged

ISO SQL TRIGGER syntax

SQL standard version

articles: 1

SQL:1999

SQL application

words: 528 articles: 5

SQL histogram

words: 439 articles: 1

OK, there's a billion questions:

SQL Server
- stackoverflow.com/questions/485409/generating-a-histogram-from-column-values-in-a-database OP did not know the difference between count and histogram :-) But it's the number one Google result.
- stackoverflow.com/questions/19103991/create-range-bins-from-sql-server-table-for-histograms has a minor extra group by twist, but otherwise fine
- stackoverflow.com/questions/16268441/generate-histogram-in-sql-server
SQLite
- stackoverflow.com/questions/67514208/how-to-optimise-creating-histogram-bins-in-sqlite perf only, benchmarking would be needed. SQLite.
- stackoverflow.com/questions/32155449/create-a-histogram-with-a-dynamic-number-of-partitions-in-sqlite variable bin size, same number of entries per bin
- stackoverflow.com/questions/60348109/histogram-for-time-periods-using-sqlite-regular-buckets-1h-wide time
MySQL: stackoverflow.com/questions/1764881/getting-data-for-histogram-plot MySQL appears to extend ROUND to also round by integers: ROUND(numeric_value, -2), but this is not widely portable which is a shame
stackoverflow.com/questions/72367652/populating-empty-bins-in-a-histogram-generated-using-sql specifically asks about empty bins, which is amazing. Amazon Redshift dialect unfortunately, but answer provided works widely, and Redshift was forked from PostgreSQL, so there's hope. Those newb open source server focused projects that don't use AGPL!

Let's try it on SQLite 3.40.1, Ubuntu 23.04. Data setup:

sqlite3 tmp.sqlite 'create table t(x integer)'
sqlite3 tmp.sqlite <<EOF
insert into t values (
  0,
  2,
  2,
  3,

  5,
  6,
  6,
  8,
  9,

  17,
)
EOF
sqlite3 tmp.sqlite 'create index tx on t(x)'

For a bin size of 5 ignoring empty ranges we can:

sqlite3 tmp.sqlite <<EOF
select floor(x/5)*5 as x,
       count(*) as cnt
from t
group by 1
order by 1
EOF

which produces the desired:

0|4
5|5
15|1

And to consider empty ranges we can use SQL genenerate_series + as per stackoverflow.com/questions/72367652/populating-empty-bins-in-a-histogram-generated-using-sql:

sqlite3 tmp.sqlite <<EOF
select x, sum(cnt) from (
  select floor(x/5)*5 as x,
         count(*) as cnt
    from t
    group by 1
  union
  select *, 0 as cnt from generate_series(0, 15, 5)
)
group by x
EOF

which outputs the desired:

0|4
5|5
10|0
15|1

SQL 2D histogram

words: 199

Let's try it on SQLite 3.40.1, Ubuntu 23.04. Data setup:

sqlite3 tmp.sqlite 'create table t(x integer, y integer)'
sqlite3 tmp.sqlite <<EOF
insert into t values
  (0, 0),
  (1, 1),
  (2, 2),
  (3, 3),
  (4, 4),
  (5, 5),
  (6, 6),
  (7, 7),
  (8, 8),
  (9, 9),
  (10, 10),
  (11, 11),
  (12, 12),
  (13, 13),
  (14, 14),
  (15, 15),
  (16, 16),
  (17, 17),
  (18, 18),
  (19, 19),

  (2, 18)
EOF
sqlite3 tmp.sqlite 'create index txy on t(x, y)'

For a bin size of 5 ignoring empty ranges we can:

sqlite3 tmp.sqlite <<EOF
select
  floor(x/5)*5 as x,
  floor(y/5)*5 as y,
  count(*) as cnt
from t
group by 1, 2
order by 1, 2
EOF

which produces the desired:

0|0|5
0|15|1
5|5|5
10|10|5
15|15|5

And to consider empty ranges we can use SQL genenerate_series + as per stackoverflow.com/questions/72367652/populating-empty-bins-in-a-histogram-generated-using-sql:

sqlite3 tmp.sqlite <<EOF
select x, y, sum(cnt) from (
  select
      floor(x/5)*5 as x,
      floor(y/5)*5 as y,
      count(*) as cnt
    from t
    group by 1, 2
  union
  select *, 0 as cnt from generate_series(0, 15, 5) inner join (select * from generate_series(0, 15, 5))
)
group by x, y
EOF

which outputs the desired:

0|0|5
0|5|0
0|10|0
0|15|1
5|0|0
5|5|5
5|10|0
5|15|0
10|0|0
10|5|0
10|10|5
10|15|0
15|0|0
15|5|0
15|10|0
15|15|5

SQL tree traversal

words: 89 articles: 2

Example: nodejs/sequelize/raw/tree.js

Closure table

Nested set model in SQL (Nested set index)

words: 59

How to implement Nested set model in SQL:

stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/42781302#42781302 contains the correct left/size representation and update queries, which makes it much easier to maintain the tree without having to worry about the sizes of siblings which are constant
stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/194031#194031 amazing ASCII art representations of the structure. Unfortunatly uses a wonky left/right representation, rather than the much more natural left/size representation from the other post

SQL feature

words: 2k articles: 25

SQL RECURSIVE query

words: 10 articles: 1

Minimal example: nodejs/sequelize/raw/recursive.js

More advanced SQL tree traversal examples: nodejs/sequelize/raw/tree.js

PostgreSQL docs: www.postgresql.org/docs/16/queries-with.html#QUERIES-WITH-RECURSIVE

SQL RECURSIVE prevent infinite recursion

words: 4

Example under: nodejs/sequelize/raw/tree.js

SQL spatial index

words: 151 articles: 3

PostgreSQL spatial index

words: 151 articles: 2

PostgreSQL GIST

words: 136

www.postgresql.org/docs/15/gist.html
www.postgresql.org/docs/15/datatype-geometric.html
medium.com/postgres-professional/indexes-in-postgresql-5-gist-86e19781b5db the only example on the net!

The highly underdocumented built-in module, that supports SQL spatial index and a lot more.

Quite horrendous as it only seems to work on geometric types and not existing columns. But why.

And it uses custom operatores, where standard operators would have been just fine for points...

Minimal runnable example with points:

set -x
time psql -c 'drop table if exists t'
time psql -c 'create table t(p point)'
time psql -c "insert into t select (point ('(' || generate_series || ',' || generate_series || ')')) from generate_series(1, 10000000)"
time psql -c 'create index on t using gist(p)'
time psql -c "select count(*) from t where p <@ box '(1000000,1000000),(9000000,2000000)'"

The index creation unfortunately took 100s, so it will not scale to 1B points very well whic his a shame.

Some sources about it:

stackoverflow.com/questions/28292198/how-to-port-simple-spatial-index-using-sqlite-r-trees-to-postgres

PostGIS

words: 15

postgis.net/

The third part module, which clutters up any serches you make for the built-in one.

SQL subquery (CTE)

words: 67 articles: 2

Common Table Expression (CTE)

words: 67 articles: 1

Similar to SQL subquery, but with some differences: stackoverflow.com/questions/706972/difference-between-cte-and-subquery

rm -f tmp.sqlite
sqlite3 tmp.sqlite 'create table t(i integer)'
sqlite3 tmp.sqlite 'insert into t values (1), (2)'
sqlite3 tmp.sqlite 'with mycte as ( select * from t ) delete from mycte where i = 1'
sqlite3 tmp.sqlite 'select * from t'

CTE insert values

words: 19

Useful for testing: stackoverflow.com/questions/21819183/how-to-use-ctes-with-update-delete-on-sqlite

sqlite3 :memory: 'WITH t (i, j) AS (VALUES (1, -1), (2, -2)) SELECT * FROM t'

SQL transaction

words: 2k articles: 11

SQL transaction isolation level

words: 2k articles: 10

Each transaction isolation level specifies what can or cannot happen when two queries are being run in parallel, i.e.: the memory semantics of the system.

Remember that queries can affects thousands of rows, and database systems like PostgreSQL can run multiple such queries at the same time.

Good summary on the PostgreSQL page: www.postgresql.org/docs/14/transaction-iso.html

Implementation specifics:

SQLite isolation levels

 Tagged

SQLite isolation levels

SQL READ UNCOMMITTED isolation level

SQL READ COMMITTED isolation level

words: 7

Example where this level is sufficient: nodejs/sequelize/raw/parallel_update_async.js.

SQL REPEATABLE READ isolation level

words: 70

Vs SQL SERIALIZABLE isolation level on PostgreSQL: dba.stackexchange.com/questions/284744/postgres-repeatable-read-vs-serializable

nodejs/sequelize/raw/parallel_create_delete_empty_tag.js is an example which experimentally seems to be solved by REAPEATABLE READ, although we are not sure that this is truly the case and why. What is clear is that that example is not solved by the SQL READ COMMITTED isolation level.

In PostgreSQL, this is the first isolation level which can lead to postgreSQL serialization failures, this does not happen to SQL READ COMMITTED isolation level in that DBMS. You then have to retry the transaction.

SQL SERIALIZABLE isolation level

SQL isolation level example

words: 1k articles: 5

SQL parallel update example

words: 1k articles: 4

nodejs/sequelize/raw/parallel_update_async.js

words: 227

nodejs/sequelize/raw/parallel_update_worker_threads.js contains a base example that can be used to test what can happen when queries are being run in parallel. But it is broken due to a sqlite3 Node.js package bug: github.com/mapbox/node-sqlite3/issues/1381...

nodejs/sequelize/raw/parallel_update_async.js is an async version of it. It should be just parallel enough to allow observing the same effects.

This is an example of a transaction where the SQL READ COMMITTED isolation level if sufficient.

These examples run queries of type:

UPDATE "MyInt" SET i = i + 1

Sample execution:

node --unhandled-rejections=strict ./parallel_update_async.js p 10 100

which does:

PostgreSQL, see other databases options at SQL example
10 threads
100 increments on each thread

The fear then is that of a classic read-modify-write failure.

But as www.postgresql.org/docs/14/transaction-iso.html page makes very clear, including with an explicit example of type UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;, that the default isolation level, SQL READ COMMITTED isolation level, already prevents any problems with this, as the update always re-reads selected rows in case they were previously modified.

If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row

Since in PostgreSQL "Read uncommitted" appears to be effectively the same as "Read committed", we won't be able to observe any failures on that database system for this example.

nodejs/sequelize/raw/parallel_create_delete_empty_tag.js contains an example where things can actually blow up in read committed.

nodejs/sequelize/raw/parallel_update_async.js

#!/usr/bin/env node

// https://cirosantilli.com/file/sequelize/raw/parallel_update_async.js

const assert = require('assert');
const common = require('../common');

async function inc(sequelize, n) {
  for (let i = 0; i < n; i++) {
    await sequelize.query(`UPDATE "MyInt" SET i = i + 1`)
  }
}

// CLI arguments.
let nthreads;
if (process.argv.length > 3) {
  nthreads = parseInt(process.argv[3], 10)
} else {
  nthreads = 2;
}
let n;
if (process.argv.length > 4) {
  n = parseInt(process.argv[4], 10)
} else {
  n = 10;
}

const sequelizes = common.sequelizes(nthreads, __filename, process.argv[2])
const sequelize = sequelizes[0]
;(async () => {
await common.drop(sequelize, 'MyInt')
await sequelize.query(`CREATE TABLE "MyInt" ( i INTEGER NOT NULL)`)
await sequelize.query(`INSERT INTO "MyInt" VALUES (0)`)
const arr = []
for (let i = 0; i < nthreads; i++) {
  arr.push(inc(sequelizes[i], n))
}
await Promise.all(arr)
;[rows, meta] = await sequelize.query(`SELECT * FROM "MyInt"`)
assert.strictEqual(rows[0].i, nthreads * n)
})().finally(() => {
  return Promise.all(sequelizes.map(s => s.close()))
});

nodejs/sequelize/raw/parallel_select_and_update.js

words: 422

This example is similar to nodejs/sequelize/raw/parallel_update_async.js, but now we are doing a separate SELECT, later followed by an update:

SELECT FROM to get i
update on Js code newI = i + 1
UPDATE SET the newI

Although this specific example is useless in itself, as we could just use UPDATE "MyInt" SET i = i + 1 as in nodejs/sequelize/raw/parallel_update_async.js, which automatically solves any concurrency issue, this kind of code could be required for example if the update was a complex function not suitably implemented in SQL, or if the update depends on some external data source.

Sample execution:

node --unhandled-rejections=strict ./parallel_select_and_update.js p 2 10 'READ COMMITTED'

which does:

PostgreSQL, see other databases options at SQL example
2 threads
10 increments on each thread

Another one:

node --unhandled-rejections=strict ./parallel_select_and_update.js p 2 10 'READ COMMITTED' 'FOR UPDATE'

this will run SELECT FOR UPDATE rather than just SELECT

Observed behaviour under different SQL transaction isolation levels:

READ COMMITTED: fails. Nothing in this case prevents:
- thread 1: SELECT, obtains i = 0
- thread 2: SELECT, obtains i = 0
- thread 2: newI = 1
- thread 2: UPDATE i = 1
- thread 1: newI = 1
- thread 1: UPDATE i = 1
REPEATABLE READ: works. the manual mentions that if multiple concurrent updates would happen, only the first commit succeeds, and the following ones fail and rollback and retry, therefore preventing the loss of an update.
READ COMMITTED + SELECT FOR UPDATE: works. And does not do rollbacks, which probably makes it faster. With p 10 100, REPEATABLE READ was about 4.2s and READ COMMITTED + SELECT FOR UPDATE 3.2s on Lenovo ThinkPad P51 (2017).
SELECT FOR UPDATE should be enough as mentioned at: www.postgresql.org/docs/13/explicit-locking.html#LOCKING-ROWS
FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Section 13.4.

A non-raw version of this example can be seen at: nodejs/sequelize/parallel_select_and_update.js.

nodejs/sequelize/raw/parallel_select_and_update.js

#!/usr/bin/env node

// https://cirosantilli.com/file/sequelize/raw/parallel_select_and_update.js

const assert = require('assert');
const common = require('../common');

async function inc(sequelize, n, isolation, for_update) {
  for (let i = 0; i < n; i++) {
    await common.transaction(sequelize, isolation, async sequelize => {
      ;[rows, meta] = await sequelize.query(`SELECT * FROM "MyInt" ${for_update}`)
      const newI = rows[0].i + 1
      await sequelize.query(`UPDATE "MyInt" SET i = ${newI}`)
    })
  }
}

// CLI arguments.
let nthreads;
if (process.argv.length > 3) {
  nthreads = parseInt(process.argv[3], 10)
} else {
  nthreads = 2;
}
let n;
if (process.argv.length > 4) {
  n = parseInt(process.argv[4], 10)
} else {
  n = 10;
}
const isolation = process.argv[5]
let for_update = process.argv[6]
if (for_update === undefined) {
  for_update = ''
}

const sequelizes = common.sequelizes(nthreads, __filename, process.argv[2])
const sequelize = sequelizes[0]
;(async () => {
await common.drop(sequelize, 'MyInt')
await sequelize.query(`CREATE TABLE "MyInt" ( i INTEGER NOT NULL )`)
await sequelize.query(`INSERT INTO "MyInt" VALUES (0)`)
const arr = []
for (let i = 0; i < nthreads; i++) {
  arr.push(inc(sequelizes[i], n, isolation, for_update))
}
await Promise.all(arr)
let [rows, meta] = await sequelize.query(`SELECT * FROM "MyInt"`)
assert.strictEqual(rows[0].i, nthreads * n)
})().finally(() => {
  return Promise.all(sequelizes.map(s => s.close()))
});

nodejs/sequelize/raw/parallel_select_and_update_deterministic.js

words: 13

This example contains a deterministic demo of when postgreSQL serialization failures may happen.

Tested on PostgreSQL 13.5.

nodejs/sequelize/raw/parallel_select_and_update_deterministic.js was not rendered because it is too large (> 2000 bytes)

nodejs/sequelize/raw/parallel_create_delete_empty_tag.js

words: 758

In this example, posts have tags. When a post is deleted, we check to see if there are now any empty tags, and now we want to delete any empty tags that the post deletion may have created.

If we are creating and deleting posts concurrently, a naive implementation might wrongly delete the tags of a newly created post.

This could be due to a concurrency issue of the following types.

Failure case 1:

thread 2: delete old post
thread 2: find all tags with 0 posts. Finds tag0 from the deleted old post which is now empty.
thread 1: create new post, which we want to have tag tag0
thread 1: try to create a new tag tag0, but don't because it already exists, this is done using SQLite's INSERT OR IGNORE INTO or PostgreSQL's INSERT ... ON CONFLICT DO NOTHING
thread 1: assign tag0 to the new post by adding an entry to the join table
thread 2: delete all tags with 0 posts. It still sees from its previous search that tag0 is empty, and deletes it, which then cascades into the join table

which would result in the new post incorrectly not having the tag0.

Failure case 2:

thread 2: delete old post
thread 2: find all tags with 0 posts
thread 1: create new post
thread 1: try to create a new tag tag0, but don't because it already exists
thread 2: delete all tags with 0 posts. It still sees from its previous search that tag0 is empty, and deletes it
thread 1: assign tag0 to the new post

which leads to a foreign key failure, because the tag does not exist anymore when the assignment happens.

Failure case 3:

thread 2: delete old post
thread 1: create new post, which we want to have tag tag0
thread 1: try to create a new tag tag0, and succeed because it wasn't present
thread 2: find all tags with 0 posts, finds the tag that was just created
thread 2: delete all tags with 0 posts, deleting the new tag
thread 1: assign tag0 to the new post

which leads to a foreign key failure, because the tag does not exist anymore when the assignment happens.

Sample executions:

node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'READ COMMITTED': PostgreSQL, 9 tags, DELETE/CREATE the tag0 test tag 1000 times, use READ COMMITTED
Execution often fails, although not always. The failure is always:
```
error: insert or update on table "PostTag" violates foreign key constraint "PostTag_tagId_fkey"
```
because the:
```
INSERT INTO "PostTag"
```
tries to insert a tag that was deleted in the other thread, as it didn't have any corresponding posts, so this is the foreign key failure.
TODO: we've never managed to observe the failure case in which tag0 is deleted. Is it truly possible? And if not, by which guarantee?
node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'READ COMMITTED' 'FOR UPDATE': do a SELECT ... FOR UPDATE before trying to INSERT.
This is likely correct and the fastest correct method according to our quick benchmarking, about 20% faster than REPEATABLE READ.
We are just now 100% sure it is corret becase we can't find out if the SELECT in the DELETE subquery could first select some rows, which are then locked by the tag creator, and only then locked by DELETE after selection. Or does it re-evaludate the SELECT even though it is in a subquery?
node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'REPEATABLE READ': repeatable read
We've never observed any failures with this level. This should likely fix the foreign key issue according to the PostgreSQL docs, since:
- the DELETE "Post" commit cannot start to be seen only in the middle of the thread 1 transaction
- and then if DELETE happened, the thread 1 transaction will detect it, ROLLBACK, and re-run. TODO how does it detect the need rollback? Is it because of the foreign key? It is very hard to be sure about this kind of thing, just can't find the information. Related: postgreSQL serialization failure.
node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'SERIALIZABLE': serializable
node --unhandled-rejections=strict ./parallel_create_delete_empty_tag.js p 9 1000 'NONE': magic value, don't use any transaction. Can blow up of course, since even less restrictions than READ COMMITTED

All executions use 2 threads.

Some theoretical notes:

Failure case 3 is averted by a READ COMMITTED transaction, because thread 2 won't see the uncommitted tag that thread 1 created, and therefore won't be able to delete it

stackoverflow.com/questions/10935850/when-to-use-select-for-update from SELECT FOR UPDATE also talks about a similar example, and has relevant answers.

nodejs/sequelize/raw/parallel_create_delete_empty_tag.js was not rendered because it is too large (> 2000 bytes)

Window function (SQL)

words: 452 articles: 3

`ROW_NUMBER`

words: 57

sqlite3 ':memory:'  'WITH t (i) AS (VALUES (-1), (-1), (-2)) SELECT *, row_number() over () FROM t'

Possible output:

-1|1
-1|2
-2|3

Gives them unique IDs.

With a partition by:

sqlite3 ':memory:'  'WITH t (i) AS (VALUES (-1), (-1), (-2)) SELECT *, row_number() over ( partition by i ) FROM t'

possible output:

-2|1
-1|1
-1|2

SQL window `RANGE`

words: 214

SQLite: www.sqlite.org/windowfunctions.html#exprrange

rm -f tmp.sqlite
sqlite3 tmp.sqlite "create table t (id integer, val integer)"
sqlite3 tmp.sqlite <<EOF
insert into t values
  (0, 0),
  (1, 5),
  (2, 10),
  (3, 14),
  (4, 15),
  (5, 16),
  (6, 20),
  (7, 25),
  (8, 29),
  (9, 30),
  (10, 30),
  (11, 31),
  (12, 35),
  (13, 40)
EOF

Show how many neighbours each column has with val between val - 2 and val + 2 inclusive:

sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
  ORDER BY val RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
) FROM t;
EOF

Output:

0|0|1
1|5|1
2|10|1
3|14|3
4|15|3
5|16|3
6|20|1
7|25|1
8|29|4
9|30|4
10|30|4
11|31|4
12|35|1
13|40|1

val - 1 and val + 1 inclusive instead:

sqlite3 tmp.sqlite <<EOF
SELECT id, val, COUNT(*) OVER (
  ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t;
EOF

Output:

0|0|1
1|5|1
2|10|1
3|14|2
4|15|3
5|16|2
6|20|1
7|25|1
8|29|3
9|30|4
10|30|4
11|31|3
12|35|1
13|40|1

There seems to be no analogue to HAVING for window functions, so we can just settle for a subquery for once, e.g.:

sqlite3 tmp.sqlite <<EOF
SELECT * FROM (
  SELECT id, val, COUNT(*) OVER (
    ORDER BY val RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) as c FROM t
) WHERE c > 2
EOF

which outputs:

4|15|3
8|29|3
9|30|4
10|30|4
11|31|3

SQL contiguous ranges

words: 181

stackoverflow.com/questions/17046204/how-to-find-the-boundaries-of-groups-of-contiguous-sequential-numbers/17046749#17046749 just works, even in SQLite which supports all quoting types known to man including [] for compatibility with insane RDBMSs!

Here's a slightly saner version:

rm -f tmp.sqlite
sqlite3 tmp.sqlite "create table mytable (id integer primary key autoincrement, number integer, status integer)"
sqlite3 tmp.sqlite <<EOF
insert into mytable(number, status) values
  (100,0),
  (101,0),
  (102,0),
  (103,0),
  (104,1),
  (105,1),
  (106,0),
  (107,0),
  (1014,0),
  (1015,0),
  (1016,1),
  (1017,0)
EOF
sqlite3 tmp.sqlite <<EOF
SELECT
  MIN(id) AS "id",
  MIN(number) AS "from",
  MAX(number) AS "to"
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY number) - number AS grp, id, number
  FROM mytable
  WHERE status = 0
)
GROUP BY grp
ORDER BY MIN(number)
EOF

output:

1|100|103
7|106|107
9|1014|1015
12|1017|1017

To get only groups of length greater than 1:

sqlite3 tmp.sqlite <<EOF
SELECT "id", "from", "to", "to" - "from" + 1 as "len" FROM (
  SELECT
    MIN("id") AS "id",
    MIN(number) AS "from",
    MAX(number) AS "to"
  FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY "number") - "number" AS "grp", "id", "number"
    FROM "mytable"
    WHERE "status" = 0
  )
  GROUP BY "grp"
  ORDER BY MIN("number")
) WHERE "len" > 1
EOF

Output:

1|100|103|4
7|106|107|2
9|1014|1015|2