SQLite Forum


50 most recent forum posts by user drh

12:26 Reply: ANALYZE Documentation addition request (artifact: d9082f1aab user: drh)

Undo ANALYZE for all historical versions of SQLite using:

DROP TABLE IF EXISTS sqlite_stat1;
DROP TABLE IF EXISTS sqlite_stat2;
DROP TABLE IF EXISTS sqlite_stat3;
DROP TABLE IF EXISTS sqlite_stat4;

There are no plans to add "sqlite_stat5" anytime soon. But I reserve the right to do that if it becomes necessary.

I don't want to add this to the documentation because it seems like unnecessary complication. Why would anybody ever want to delete the results of ANALYZE? What do you hope to accomplish by doing that? If the ANALYZE results have become obsolete, the reasonable thing to do is to rerun ANALYZE.

Recommended practice is to run the following commands prior to shutting down an application that uses SQLite:

PRAGMA analysis_limit=200; -- optional
PRAGMA optimize;

The "PRAGMA optimize" command will run ANALYZE when needed (which is to say "rarely"). Usually "PRAGMA optimize" is a no-op. Setting the analysis_limit to 200 guarantees that when ANALYZE does run, it runs quickly, even for large databases.

ANALYZE results are preserved across VACUUM.

11:02 Reply: Docs: julianday() returns a real number, not a string (artifact: 7883e315f5 user: drh)

All of the SQLite date/time functions are implemented by a single source-code file src/date.c. There are comments in the code explaining how everything works.

18:29 Reply: Proposed JSON enhancements. (artifact: a9663f3ae6 user: drh)

I don't know, but presumably in PG, the array operator accepts any expression that evaluates to a string inside the [...] operation, not just a string literal. Perhaps a string literal is the most commonly used case for JSON, but we shouldn't limit it to that.

11:30 Reply: binding by reference instead of value (artifact: c6db62a716 user: drh)

I suspect that the bytecode would be upset if values changed out from under it while it was running, which might happen if parameters were bound by reference.

20:27 Reply: Proposed JSON enhancements. (artifact: 89ea5b9921 user: drh)

I have no doubts that using [] around identifiers is pervasive, but nontheless non-standard.

The uses of [] for accessing JSON elements in PG is non-standard too. My point is: why should we favor the new and mostly unknown non-standard-ism of PG over the ancient and widely used non-standard-ism of SQL Server?

12:41 Reply: Confusing sqlite3_vtab_distinct (artifact: d4c69efe7e user: drh)

The documentation for sqlite3_vtab_distinct is confusing

That new API has only been in the tree for less than 13 hours. It has not landed on trunk, yet. It doesn't yet fully work. Please allow me a few more days before insisting on detailed documentation.

12:33 Reply: Proposed JSON enhancements. (artifact: 5b86f4da24 user: drh)

Just in case anyone doubts that the use of [...] for quoting of identifiers is pervasive, forum post 525873d1796a4a51 from a first-time participant in this forum uses [...] quoting, and only a couple of days after my previous message.

16:42 Reply: An assertion failure still needs to be handled (artifact: 69a857ce0c user: drh)

See check-in 4db5217a28ce767f for the fix.

13:29 Reply: An assertion failure still needs to be handled (artifact: d074cb12c9 user: drh)

I should add, for the benefit of people who worry about these things, that this is not a memory error. In other words, it is not a potential vulnerability. The assertion fault indicates that one of the byte-code registers was read before it was initialized by the byte-code itself. But because all the byte-code registers are initialized to NULL prior to starting up the byte-code engine, no harm comes of this use of an uninitialized byte-code register. The problem is with a byte-code register, not a CPU register. No CPU registers or memory are read while uninitialized, nor are there NULL pointer dereferences or array overruns or anything like that. This is a fault in the byte code only. The worst possible outcome is an incorrect answer (though with this particular malfunction, it appears that even the answer is correct).

So, in other words, rewriting in Rust/Go/Swift or some other memory-safe language would not have helped here.

Since the assert() that fails is only coded when you compile with SQLITE_DEBUG and hence does not appear in release builds, and because the correct answer is obtained without the assert(), this is not a pressing issue. The problem needs to be fixed, and I am very grateful to Wang Ke for bringing it to my attention. But it is not an emergency.

13:14 Reply: An assertion failure still needs to be handled (artifact: 19b17bacf0 user: drh)

Thanks for bumping this. Somehow we missed it.

The following script does a better job of capturing the essence of the malfunction:

CREATE TABLE b(b1,b2,b3);
INSERT INTO b(b1,b2,b3) VALUES(NULL,123,456);
SELECT * FROM a LEFT JOIN b ON b1=NULL WHERE (b2,b3)==(SELECT 123, 456);

Using this script, I didn't have any trouble bisecting to check-in ddb5f0558c445699 from 2016-09-07, which makes logical sense. That was the check-in that added support for row-value comparisons.

11:07 Reply: SQLITE_ALTER_TABLE not reported by ALTER TABLE DROP COLUMN (artifact: 6537d092d0 user: drh)

The problem should be fixed on trunk. You can apply the patch or wait for the next release.

Thanks for the bug report.

14:36 Reply: Proposed JSON enhancements. (artifact: a38863e602 user: drh)

You want a flag or PRAGMA to disable a non-standard syntax feature from SQL Server that has been in wide-spread use for decades, in order to support a non-standard syntax feature of PG that has been added just recently?

12:44 Reply: feature request: vacuum into without indexes (artifact: 02cc0a9ba0 user: drh)

Is there a way to use vacuum into to only create tables and ignore indexes?

  1. Use ATTACH to add a (blank) database that you will be vacuuming into. All this new database "vac".

  2. For peak efficiency, set "PRAGMA vac.journal_mode=OFF;". Doing this means that the "vac" database will be corrupt if you lose power or crash, though the original database will be unharmed. That seems like a reasonable approach in this case.

  3. Do "CREATE TABLE vac.whatever(...)" for each table you want to vacuum.

  4. Run "INSERT INTO vac.whatever SELECT * FROM main.whatever;" to transfer the table content.

  5. Detach vac.

The "VACUUM INTO" command does essentially the above, though it also processes indexes.

02:29 Reply: Security Vulnerabilities found in sqlite3.c (artifact: faa6ad3a18 user: drh)

Thank you for the report.

It appears that ESBMC-WR is a static analyzer. Static analysis tools are notorious for generating lots of false-positives in SQLite. So even without looking, I'm guessing that will be the case here too. This comes up so often, that I'm going to assume that all of these reports are false-positives until I have hard evidence to the contrary. If you don't have a test case which involves feeding some SQL into a (possibly malformed) SQLite database file and then getting a memory error, then you don't have a vulnerability. And I do not see any test cases here, just abstract warnings about "violated properties". So your title is perhaps an exaggeration. Perhaps a more accurate title would be:

Seven places in SQLite that we cannot prove are not security vulnerabilities.

The second problem is that you did not tell us which version of SQLite you run through ESBMC-WR. Without knowing the precise version (the output of the "SELECT sqlite_source_id()" command) the line-number information in your report is useless to us.

If you will kindly let us know precisely what version of SQLite you are running through ESBMC-WR, I'll see what I can do about restructuring the code in order to help it complete its proofs and stop complaining.

18:39 Reply: Distinguishing CREATE TABLE failure (already exists) from othe failures (artifact: 6a4d8e493b user: drh)

See also the db_table_exists() function from Fossil, which demonstrates the use of the sqlite3_table_column_metadata() interface to determine if a table already exists or not.

18:36 Reply: Distinguishing CREATE TABLE failure (already exists) from othe failures (artifact: ec5c0cf747 user: drh)

Do you know about:

12:33 Reply: Proposed JSON enhancements. (artifact: 6af8c9adb0 user: drh)

The "[]" operator is not available to SQLite. Microsoft SQL Server uses square brackets to quote identifiers. For example, instead of "columnname" they write [columnname]. This is not standard SQL, but it is extremely common among programmers who first learned SQL from SQL Server. SQLite provides square-bracket quoting too, for compatibility with SQL Server, and has for 20 years. Hence, we cannot use [] as an operator.

22:50 Reply: Help me understand the path syntax used by json_extract() (artifact: 4ccd20aa79 user: drh)

It is an SQLite extension.

21:52 Reply: Help me understand the path syntax used by json_extract() (artifact: 6b4932cca0 user: drh)

The "[#-1]" syntax means the last element of an array. "#" is the number of elements in the array and JSON arrays using 0-based indexing, so the last element will be "[#-1]".

23:38 Reply: Bad interaction between STRICT and generated column in 3.37.2 (artifact: 57bb652ba9 user: drh)

Now fixed on trunk.

23:13 Reply: Bad interaction between STRICT and generated column in 3.37.2 (artifact: b438e92807 user: drh)

Simplified test case:

20:57 Reply: Delete Table Data (artifact: 8a37b79f81 user: drh)

SQLiteStudio is a downstream project. That is to say, they use the core SQLite library as a key component of their system, buy they are completely separate from SQLite and are not affiliated with SQLite. Unless another SQLiteStudio user just happens to read this, we probably won't be able to help you here. The core developers of SQLite don't know anything about SQLiteStudio. You should seek help at the SQLiteStudio website.

14:53 Post: sqlite.org website infrastructure upgrade - please report any problems (artifact: 6f3fabeaeb user: drh)

I cut over a new web server for the https://sqlite.org website yesterday (2022-01-16). Everything appears to be working fine. Nevertheless, if you observe an problems or unusual latency, please report on this forum or directly to me at drh at sqlite dot org.

12:45 Reply: www3.sqlite.org out of sync (artifact: a9ef98c7d3 user: drh)

Yes. We are working on a new version of althttpd and are testing it on www3.sqlite.org. But there is a bug that prevents POST requests from working via HTTPS. Sync uses a POST request.

The service is working as I type this. But you may expect periodic disruptions in service on www3.sqlite.org as test fixes to the problem with althttpd.

23:29 Reply: Unnecessary lemon_sprintf in lemon.c? (artifact: 80f9a396a5 user: drh)

Thanks for the report. Harmless extra code fixed on trunk.

14:26 Reply: Any use case for the Bloom filter for "large analytic queries" (artifact: cac3ff5f4a user: drh)

Probably not, because the overhead of computing the Bloom filter would overwhelm any performance advantage gained in using it.

12:44 Reply: Any use case for the Bloom filter for "large analytic queries" (artifact: bd59962986 user: drh)

For Q2.2, the run-time on my tests went from 46.261 seconds to 3.402. That was the best improvement. 2x is more typical. Your mileage may vary.

For some table X, if the query planner sees that it is going to need to do N searches (binary look-ups) against X and N is larger than the number of rows in X, and if the query planner has some reason to believe that many of those searches will end with "not found", then it goes to to the trouble of computing the Bloom filter. Computing the Bloom filter involves doing an initial scan of the entire X table, so there is a significant set-up cost. Hence, the query planner wants to ensure that there will be some overall benefit before incurring that cost. If the query planner messes that decision up, there could be a slowdown.

Bloom filters are also deployed whenever an automatic index is created, in as much as the incremental cost of computing the Bloom filter is minimal. (The use of an "automatic index" is what most other RDBMSes would call a "hash join".)

I haven't yet run across a case where the query planner adds a Bloom filter which ends up slowing things down. But that doesn't mean no such case exists.

21:30 Reply: Any use case for the Bloom filter for "large analytic queries" (artifact: 2dc3a17b58 user: drh)

Version 3.38.0 runs much faster on many of the queries in the Star Schema Benchmark. That said, I've yet to find a real-world use-case that is helped by the new Bloom filters. If you know of one, please share it.

The latest trunk check-in of SQLite is stable and quite usable. It is already being used, for example, in the software that runs this forum. I encourage you to download the latest trunk check-in of SQLite and use it for whatever applications you have that make use of SQLite and let us know if you encounter any problems.

16:16 Reply: .param init is not needed (artifact: 05cebe7545 user: drh)

My memory is that ".param init" was created precisely for the reason that Larry cites: To create the semi-magical temp.sqlite_parameters table, which is difficult to create otherwise.

20:32 Reply: SELECT with LIMIT OFFSET clause return unexpected result (with sample db and query) (artifact: aec099f0b4 user: drh)

I think this is now fixed at check-in 9282bcde301cee2a.

19:40 Reply: SELECT with LIMIT OFFSET clause return unexpected result (with sample db and query) (artifact: 9f2062842e user: drh)

Bisects to check-in 2a74129a21f9745f. I do not believe that is the source of the problem - I think it is just a change that enabled this particular test-case to work. Probably the problem goes back a lot further.

19:33 Reply: SELECT with LIMIT OFFSET clause return unexpected result (with sample db and query) (artifact: a5148faa49 user: drh)

Simplified test case:

INSERT INTO t1 VALUES(1),(1),(1);

This returns three 1's, but ought to return three NULLs, I think.

14:18 Reply: sqlite native library in Android returns disk I/O error (artifact: fd0dcbc567 user: drh)

The SQLite core does not have any limits like this. But I don't know what kind of limits you might be encountering from Android or from the wrapper class that you are using to access SQLite from your app.

00:52 Reply: Proposed JSON enhancements. (artifact: cc92f4ce3b user: drh)

Because I forgot about mkctimec.tcl. Fixed now.

00:13 Reply: intterupted vacuum (artifact: 4c2bd0c0f3 user: drh)

Have you tried using VACUUM INTO to create a vacuumed copy of the original database? When the copy has been created, then just delete the original and rename the copy back into the original name. (NB: This will only work if the database is not being used simultaneously by some other process.)

00:07 Reply: Proposed JSON enhancements. (artifact: fc742145dd user: drh)

The changes have landed on trunk. The draft 3.38.0 website contains preliminary documentation:

A prerelease snapshot tarball is available on the download page.

Please report any issues seen to this forum, or directly to me at drh at sqlite dot org.

00:38 Reply: SQLite3 shell doing math operation on parameter substitution (artifact: 3eae000383 user: drh)

Not a bug.

The command that is giving you trouble is this line:

.parameter set @contact     '+66-2-615-3964'

The single-quotes on the argument are being stripped off by the CLI itself, leaving @contact with the value as a bare +66-2-615-3964. This gets evaluated to -4515, as you observe. You work-around is to bind @contact to a quoted string instead:

.parameter set @contact     "'+66-2-615-3964'"

The prior line of the form:

.parameter set @ip          ''

Works because the bare string is a syntax error, and after seeing that syntax error, the CLI tries putting the string inside of quotes to see if that works better, and it does. But +66-2-615-3964 is not a syntax error, so it never gets quoted.

You are correct that the details of this behavior are not documented. You might even argue that they are unintuitive. But the behavior is as originally intended.

Perhaps we need to invent an easier way for shell scripts to get parameter values into the CLI.

00:21 Reply: SQLite3 shell doing math operation on parameter substitution (artifact: 9358c92be3 user: drh)

Much simpler repro script:

.parameter set @one '+1+2-3+4'
SELECT @one;
21:25 Reply: Proposed JSON enhancements. (artifact: fdcb05e6bd user: drh)

Why? What problem are you trying to solve with this new restriction?

17:44 Reply: Proposed JSON enhancements. (artifact: b33d976663 user: drh)

Fixed now in the latest.

15:44 Edit reply: Proposed JSON enhancements. (artifact: 96a4af7c19 user: drh)

New proposed -> and ->> operators for SQLite: https://sqlite.org/src/artifact/7f67a2e75de23958. These changes have not been implemented as of this writing.

Update: Code changes are now on the json-in-core branch. Your efforts at trying to poke holes in the new design are appreciated.

13:59 Reply: Using SQLITE_OMIT_xxx in SQLite 3.36 (artifact: 94b9f64bd8 user: drh)

Using -Os instead of -O2 makes a big difference is size. If size is important to you, you should always prefer -Os over -O2. That change will make way more difference than any -DSQLITE_OMIT options.

13:58 Reply: Proposed JSON enhancements. (artifact: e7be292116 user: drh)

New proposed -> and ->> operators for SQLite: https://sqlite.org/src/artifact/7f67a2e75de23958. These changes have not been implemented as of this writing.

12:08 Reply: Proposed JSON enhancements. (artifact: ecac4acaee user: drh)

Having explored this question further, I now think I better understand why MySQL and PG have separate -> and ->> operators. It is because of their rigid type system, that requires each function to always return values of a specific type. Hence you have -> for returning JSON values and ->> for returning TEXT values. SQLite is not constrained in this way, and so, in theory, SQLite can have a single operator -> that returns either JSON or TEXT (or INTEGER or REAL) as appropriate for the JSON and path expression. This is a simpler and more intuitive approach, but is not possible in MySQL and PG due to the limitations of their type system.

Hence, for the sake of compatibility, I'm proposing to completely revamp my SQLite JSON improvement proposal as follows:

  • Omit the json_nextract() and json_ntype() functions, as the same thing can be accomplished using CASE and the need for these does not seem to come up as often as I anticipated.

  • Merge the JSON functions into the SQLite core. This is mostly transparent to applications. It just means the JSON functions are included by default, and they use a little less memory.

  • Add a -> operator that works kind of like json_extract() but that always returns JSON. Thus -> is compatible with MySQL and PG.

  • Add a ->> operator that always returns a primitive SQL type - TEXT, INTEGER, REAL, or NULL - depending on the input JSON and the path expression. This behavior is slightly different from MySQL and PG in that in those others, ->> always returns either TEXT or a NULL, and never INTEGER or REAL. But I haven't been able to come up with a situation where returning INTEGER or REAL when the underlying JSON value is INTEGER or REAL would cause a problem, so I'm going to call this new ->> operator "compatible" too.

My intent is to write up the new proposal in more detail, with examples, and add a working implementation to a branch (probably the json-in-core branch) at some point. But I have some other unrelated matters that will need to take priority so I'm not sure how soon that will happen.

11:41 Reply: pragma table_info Bug (artifact: 69261d3a0d user: drh)

Unable to repro. Please launch the CLI against your database and then run the following commands:

SELECT * FROM sqlite_schema;

Post the output here. Thanks.

21:01 Reply: Is it possible to make -shm/-wal files permanent? (artifact: 5ccd3a76b4 user: drh)

The SQLITE_FCNTL_PERSIST_WAL option supports that. But it is not sticky and has to be done for each writer.

21:00 Reply: Is it possible to make -shm/-wal files permanent? (artifact: fbf619fc79 user: drh)

Does it work like you want if you leave off the "mode=ro" option?

20:54 Reply: Proposed JSON enhancements. (artifact: 4639cc7838 user: drh)

The extra code space for the JSON functions is about 20KB (x64, gcc -Os). That amounts about a 3.3% size increase. You get much, much bigger changes in binary size by adjusting compiler optimization settings.

20:52 Reply: Proposed JSON enhancements. (artifact: 86277342d2 user: drh)

The description of the proposed JSON enhancements has been updated for clarity, based on comments received so far. Please re-read and offer any critique.

19:43 Reply: Is it possible to make -shm/-wal files permanent? (artifact: ce4c4d8993 user: drh)

SQLite version 3.22.0 (2018-01-22) and later should be able to read a read-only WAL-mode database that does not have -wal and -shm files present. What version of SQLite are you using that is giving trouble?

More ↓