POC

CREATE TABLE def AS SELECT * FROM (SELECT * FROM (SELECT *, * FROM sqlite_dbpage ORDER BY 1), (SELECT *, * FROM sqlite_dbpage)), (SELECT *, * FROM sqlite_dbpage);

This will lead to 100%CPU and 100%MEM, then killed by OS. Please check it.

Regards.

SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> CREATE TABLE def AS SELECT * FROM (SELECT * FROM (SELECT *, * FROM sqlite_dbpage ORDER BY 1), (SELECT *, * FROM sqlite_dbpage)), (SELECT *, * FROM sqlite_dbpage);
zsh: killed     sqlite3
simon@184 Desktop % 

Verified on my computer. Well, my OS doesn't allow 100% CPU or boundless memory use but yes, it crashes. Don't know whether the bug is in the SQLite library or the SQLIte CLI.

Don't know whether the bug is in the SQLite library or the SQLIte CLI.

I'm not convinced it is a bug at all. If it is to be deemed "a bug", it would have to be attributed to library.

The sqlite_dbpage virtual table is exposed for purposes of low-level access to the database file. Doing an insert (such as the OP's SQL does) from that source is a runaway process, as the database can never become large enough to contain both its underlying storage AND the book-keeping data necessary to permit efficient queries and manipulation of that data.

I think it is the same sort of "bug" as complaining that "while (1) malloc(100);" never finishes and slurps all available memory.

The multi-way join is mere icing on that runaway process. It makes an O(N^3) operation out of an O(N) operation having N=infinity.

I had never seen this mechanism before:

.progress 10000000 --limit 1

Is that covered in the documentation - and do you know if it's available to the Python standard library module?

I guess I can replicate what it's doing in Python like this:

conn.set_progress_handler(lambda: 1, 10000000)

Since returning 1 from a progress handler cancels the query.

The keyword index leads to the sqlite3_progress_handler() API. The shell's response to .help mentions the .progress dot command. I highly recommend using those resources.

I would have to go read some Python docs to answer your question about Python. However, my guess from your use of the set_progress_handler() method is that SQLite's sqlite3_progress_handler() API is usefully exposed.

If you are asking whether the SQLite shell's functionality (such as the .progress dot command) is available from Python -- I doubt it.

Point taken. Not a bug. The program is doing exactly what you asked it to do: try to use all available memory, then get aborted by the OS.

Yes, that worked:

sqlite> .help .progress
.progress N              Invoke progress handler after every N opcodes
   --limit N                 Interrupt after N progress callbacks
   --once                    Do no more than one progress interrupt
   --quiet|-q                No output except at interrupts
   --reset                   Reset the count for each input and interrupt

It looks like the only place that documentation is available right now is inside the interactive prompt - my attempts to find it through Google searches didn't turn up anything useful.

A trick I've been using recently for my own command line projects is to have a page of the documentation which automatically shows the output of the --help commands - for example: https://sqlite-utils.datasette.io/en/stable/cli-reference.html

This is useful for search, and also as a development tool because it gives me somewhere I can review the current --help output of everything in one place.

Maybe the sqlite3 command would benefit from something similar!

I built a page that does that here: https://github.com/simonw/help-scraper/blob/main/sqlite3-help.md - using the "help scraping" technique I described in https://simonwillison.net/2022/Feb/2/help-scraping/