SQLite Forum

Timeline
Login

50 forum posts by user dan occurring on or before 2021-05-13 14:30:49.

More ↑
2021-05-13
14:30 Reply: Discovered bug in xBestIndex of virtual tables (artifact: f8abd5afcd user: dan)

In example 4, "bar = foo" cannot be used by the virtual table, and so is not passed to xBestIndex. For a virtual table to use a constraint, one side of the operation must be a virtual table column and the other side some value that is available before the virtual table scan is begun. In other words, there must be a value available to pass to xFilter. If both sides of the constraint are columns of the virtual table, neither are available before the scan is started and so the constraint cannot be used by the virtual table.

It's curious that example 2 gives you 3 constraints. Two would be correct I think - the explicit "foo = ?" and the implied "bar = ?" (if "bar=foo" and "foo=?", it must be the case that "bar=?").

I suspect the 3rd constraint is the code that finds the "bar=?" constraint being too aggressive - reasoning that since bar=? and bar=foo, there is a second foo=?.

Dan.

2021-04-12
10:57 Reply: Regression: Mixing AND with OR results in no rows returned (artifact: 7e9f0bdcca user: dan)

Sorry - I visited the bugzilla page but somehow managed to miss the linked reproducer db. This problem is indeed fixed on trunk.

Dan.

10:52 Reply: Regression: Mixing AND with OR results in no rows returned (artifact: 6fc95636bc user: dan)

Thanks for reporting this. I think this problem was fixed just a couple of days ago here:

()[https://sqlite.org/src/info/40852ca8e215e51f]

Can you try with the latest SQLite trunk to see if the problem is fixed?

Cheers,

Dan.

04:56 Reply: sqlite3_bind_() problem I do not understand (artifact: 00528dda4a user: dan)

value.c_str() changing? Try binding with SQLITE_TRANSIENT to ensure SQLite takes a copy of the string when it is bound.

2021-04-10
15:03 Reply: useless constant term lead to incorrect output (artifact: 40caded740 user: dan)

This is a bug. Thanks for reporting it. Now fixed here:

https://sqlite.org/src/ci/40852ca8

Dan.

2021-04-08
17:15 Reply: index misuse? (artifact: d8368bfda4 user: dan)

How did you patch the SQLite code to get it to do this?

16:02 Edit reply: index misuse? (artifact: b6b5f9b5e0 user: dan)

Oh. Right - it's a transitive term. Your WHERE clause is:

    t1.c0 == t0.c0 AND t1.c1 == t0.c0;

which implies:

    t1.c0 == t1.c1

(because they're both equal to t0.c0). Therefore it can use an index on t1.c0 for constraints on t1.c1 - like "t1.c1 == t0.c0".

Simple example - index on "y" is used for "x=?":

$ ./sqlite3 SQLite version 3.36.0 2021-04-07 18:17:53 sqlite> CREATE TABLE t1(x, y); sqlite> CREATE INDEX t1y ON t1(y); sqlite> explain query plan SELECT * FROM t1 WHERE y=x AND x=?; QUERY PLAN `--SEARCH t1 USING INDEX t1y (y=?)

16:01 Edit reply: index misuse? (artifact: 744d2dec09 user: dan)

Oh. Right - it's a transitive term. Your WHERE clause is:

t1.c0 == t0.c0 AND t1.c1 == t0.c0;

which implies:

t1.c0 == t1.c1

(because they're both equal to t0.c0). Therefore it can use an index on t1.c0 for constraints on t1.c1 - like "t1.c1 == t0.c0".

Simple example - index on "y" is used for "x=?":

$ ./sqlite3 SQLite version 3.36.0 2021-04-07 18:17:53 sqlite> CREATE TABLE t1(x, y); sqlite> CREATE INDEX t1y ON t1(y); sqlite> explain query plan SELECT * FROM t1 WHERE y=x AND x=?; QUERY PLAN `--SEARCH t1 USING INDEX t1y (y=?)

15:56 Reply: index misuse? (artifact: 84c5a8ba1e user: dan)

Oh. Right - it's a transitive term. Your WHERE clause is:

t1.c0 == t0.c0 AND t1.c1 == t0.c0;

which implies:

t1.c0 == t1.c1

(because they're both equal to t0.c0). Therefore it can use an index on t1.c0 for constraints on t1.c1 - like "t1.c1 == t0.c0".

11:18 Reply: index misuse? (artifact: 0824edd0e3 user: dan)

Isn't it proposing that index to optimize the "t1.c0 == t0.c0" term?

2021-03-23
11:28 Reply: incremental_vacuum and WAL size (artifact: 390af9ab9a user: dan)

I think it's because SQLite needs to spill its page cache midway through the big incr-vacuum operation.

As it does the incremental vacuum, SQLite is marking each free page removed from the end of the file as dirty. If it flushes these out at the end of the transaction, when it knows the db image is about to be truncated, then it is smart enough not to write to the wal file any pages that will be truncated away anyway. But, if the cache fills up mid-transaction, then SQLite just starts writing dirty pages to the wal file. Including those that would be discarded at commit time when the database image is being truncated.

So the big incr-vacuum, because it overflows the page-cache, ends up writing lots of pages to the wal file that the series of small incr-vacuum operations do not.

You could confirm by adding something like the following to the big incr-vacuum case:

    PRAGMA cache_size = 30000;

With the larger cache size no extra pages should be written to the wal file.

There's an optimization opportunity here of course. I think it may cases, these pages should not be being marked as dirty in the first place.

2021-03-22
20:17 Reply: Bug report: Complex CTE generates segmentation fault depending on the order of joined tables in its body (artifact: 7a3ab9806d user: dan)

estimatedRows is used as the basis for the cost of an external sort, if the virtual table cannot supply rows in the order requested by SQLite. Also, if SQLite is considering the virtual table as the outer loop of a join, then estimatedRows is used to estimate how many times the inner loops are expected to run, which is used to determine their overall cost.

Dan.

2021-03-18
11:25 Reply: Find rows not in other sub query? (artifact: 9678f465a6 user: dan)

Perhaps:

    SELECT zip FROM zip WHERE zip.zip NOT IN (SELECT main.zip FROM main);

to find any rows in table "zip" that have no counterpart in table "main".

2021-03-16
18:48 Reply: sqlite 3.35 stalls tracker-miner-fs-3 (artifact: 10e4524045 user: dan)

It would be good to fix this quickly I think.

We can see the query under "Example Query" in a post from Carlos Garnacho in the thread you linked. If we could also get a copy of the database that this query is running against we should be able to figure something out. Are you able to run the Nautilus tests and get us that database?

Thanks,

Dan.

2021-03-09
11:16 Reply: IN parameters within a query (artifact: be238ec307 user: dan)

How do you reproduce this problem?

On Linux I just get:

$ ~/sqlite/bld4/sqlite3 ./panlex_lite/db.sqlite SQLite version 3.35.0 2021-03-08 17:22:01 Enter ".help" for usage hints. sqlite> .expert sqlite> SELECT expr.langvar,langvar.uid,expr.txt,expr2.txt,expr2.langvar ...> FROM denotationx ...> JOIN expr ON (expr.id = denotationx.expr) ...> JOIN denotationx denotationx2 ON (denotationx2.meaning = denotationx.meaning) ...> JOIN expr expr2 ON (expr2.id = denotationx2.expr) ...> JOIN langvar ON (expr.langvar=langvar.id) ...> WHERE denotationx.expr != denotationx2.expr AND expr.langvar IN (1261,1776,3003,5741) AND expr2.langvar = 187 ...> ORDER BY expr.langvar; CREATE INDEX denotationx_idx_761331ba ON denotationx(meaning, expr); . SEARCH TABLE expr AS expr2 USING INDEX expr_langvar (langvar=?) SEARCH TABLE denotationx AS denotationx2 USING INDEX denotationx_expr (expr=?) SEARCH TABLE denotationx USING COVERING INDEX denotationx_idx_761331ba (meaning=?) SEARCH TABLE expr USING INTEGER PRIMARY KEY (rowid=?) SEARCH TABLE langvar USING INTEGER PRIMARY KEY (rowid=?) USE TEMP B-TREE FOR ORDER BY

Cheers,

Dan.

2021-03-05
17:44 Reply: BEGIN CONCURRENT SQLITE BUSY (artifact: ba3b3eae31 user: dan)

The db is in wal mode, correct? "BEGIN CONCURRENT" is no different from "BEGIN" in rollback mode.

In any case SQLite will only use the busy-timeout if there is some prospect of the COMMIT operation succeeding on the second attempt. This is the case if the SQLITE_BUSY is being reported because the client can't get the write lock, but is not so if the SQLITE_BUSY error means the transaction cannot be committed due to page conflicts.

What is being returned by sqlite3_extended_errcode() in these cases?

Dan.

2021-02-25
19:47 Reply: DBStat returns wrong values for compressed databases (artifact: d0785d4276 user: dan)

pgoffset and pgsize describe the compressed page as stored on disk. The other fields describe the uncompressed page in memory.

Dan.

06:34 Edit reply: Update Query Problem (artifact: 05ac55f65a user: dan)

I think so.

SQLite joins the target table against the tables in the FROM clause of an UPDATE FROM statement. So your first attempt was similar to:

    SELECT _rowid_, ... FROM Test, Test INNER JOIN WindowOrder ON ...

Hence any reference to a Test.* column was ambiguous.

From memory, this is different to the way SQL Server does it. I think your statement (with the target table repeated in the FROM clause) would be correct for SQL Server. Not 100% sure about that though.

Dan.

06:31 Reply: Update Query Problem (artifact: 2c87513289 user: dan)

I think so.

SQLite joins the target table against the tables in the FROM clause of an UPDATE FROM statement. So your first attempt was similar to:

    SELECT _rowid_, ... FROM Test, Test INNER JOIN WindowOrder ON ...

Hence any reference to a Test.* column was ambiguous.

Dan.

2021-02-24
20:54 Reply: Update Query Problem (artifact: a25120d693 user: dan)

Try leaving out the first instance of "Test" following the FROM clause:

WITH WindowOrder AS ( SELECT ID, row_number() OVER (ORDER BY ID desc) AS RowNumber FROM Test WHERE Included ) UPDATE Test SET SelectOrder = WindowOrder.RowNumber FROM WindowOrder WHERE Test.ID = WindowOrder.ID AND Test.Included;

Dan.

17:21 Edit reply: Reporting performance issue (artifact: b19c33cd02 user: dan)

Thanks for posting these. Very interesting indeed.

All of them seem to be cases where the planner should be able to determine that no work (or very little) is required. I guess that's a product of your 1000x threshold - it's hard to be 1000x slower unless you miss a really good shortcut! I think instances where one db is consistently more than (say) 10x slower would be quite interesting as well.

Anyway, for the record these all seem to come down to two things:

(1) SQLite is not taking advantage of NOT NULL constraints when processing IS NULL. With SQLite, if you do:

    CREATE TABLE t1(a NOT NULL);
    SELECT * FROM t1 WHERE a IS NULL;

SQLite does a full-table scan. But it should be possible to determine at compile-time that any "col IS NULL" expression for which col has a NOT NULL constraint and does not come from a table on the right-hand-side of a LEFT JOIN is always false.

(2) SQLite is not ignoring columns in the result-set of an EXISTS(SELECT ...) when determining whether or not the SELECT is correlated. i.e. if you do:

    CREATE TABLE t1(a, b);
    CREATE TABLE t2(x, y);
    SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE x=1);

then SQLite runs the sub-query once for every row of t1 because it thinks the "a" in the sub-query makes it correlated. It doesn't - the sub-query only has to be run once.

Naive changes to support the two optimizations above fix most of these examples. But there are a couple of tricks. For example, query 1753 features the equivalent of:

    CREATE TABLE t1(a, b);
    CREATE TABLE t2(x, y NOT NULL);
    SELECT * FROM t1 LEFT JOIN t2 WHERE t1.a=t2.x AND y IS NULL;

In the query, the LEFT JOIN can be transformed to a regular join (because t1.a=t2.x implies that t2.x cannot be NULL) which allows the database to determine that "y IS NULL" will always be false. But currently, by the time SQLite realizes it can transform the join it's tricky to tell exactly which LEFT JOIN (there may be many in the query) column "y" came from.

There's a similar problem in 1948:

    CREATE TABLE t1(a, b);
    CREATE TABLE t2(x, y NOT NULL);
    CREATE TABLE t3(c, d);
    SELECT * FROM t1 LEFT JOIN t2 WHERE t1.a=t2.x EXISTS (SELECT 1 FROM t3 WHERE t2.y IS NULL OR t3.c=10);

By the time the LEFT JOIN is transformed to a regular join, the sub-select has already been marked as correlated. So even though the "t2.y IS NULL" is transformed to "false", making the sub-select uncorrelated, SQLite still runs it once for every row visited by the query.

Apparently, other database engines do better in these cases.

I'm not sure how much of a rush we'll be in to fix these specific cases. Something for after 3.35.0 anyway.

Thanks,

Dan.

17:17 Reply: Reporting performance issue (artifact: bb2af399cd user: dan)

Thanks for posting these. Very interesting indeed.

All of them seem to be cases where the planner should be able to determine that no work (or very little) is required. I guess that's a product of your 1000x threshold - it's hard to be 1000x slower unless you miss a really good shortcut! I think instances where one db is consistently more than (say) 10x slower would be quite interesting as well.

Anyway, for the record these all seem to come down to two things:

  • SQLite is not taking advantage of NOT NULL constraints when processing IS NULL. With SQLite, if you do:

    CREATE TABLE t1(a NOT NULL); SELECT * FROM t1 WHERE a IS NULL;

SQLite does a full-table scan. But it should be possible to determine at compile-time that any "col IS NULL" expression for which col has a NOT NULL constraint and does not come from a table on the right-hand-side of a LEFT JOIN is always false.

  • SQLite is not ignoring columns in the result-set of an EXISTS(SELECT ...) when determining whether or not the SELECT is correlated. i.e. if you do:

    CREATE TABLE t1(a, b); CREATE TABLE t2(x, y); SELECT * FROM t1 WHERE EXISTS (SELECT a FROM t2 WHERE x=1);

then SQLite runs the sub-query once for every row of t1 because it thinks the "a" in the sub-query makes it correlated. It doesn't - the sub-query only has to be run once.

Naive changes to support the two optimizations above fix most of these examples. But there are a couple of tricks. For example, query 1753 features the equivalent of:

    CREATE TABLE t1(a, b);
    CREATE TABLE t2(x, y NOT NULL);
    SELECT * FROM t1 LEFT JOIN t2 WHERE t1.a=t2.x AND y IS NULL;

In the query, the LEFT JOIN can be transformed to a regular join (because t1.a=t2.x implies that t2.x cannot be NULL) which allows the database to determine that "y IS NULL" will always be false. But currently, by the time SQLite realizes it can transform the join it's tricky to tell exactly which LEFT JOIN (there may be many in the query) column "y" came from.

There's a similar problem in 1948:

    CREATE TABLE t1(a, b);
    CREATE TABLE t2(x, y NOT NULL);
    CREATE TABLE t3(c, d);
    SELECT * FROM t1 LEFT JOIN t2 WHERE t1.a=t2.x EXISTS (SELECT 1 FROM t3 WHERE t2.y IS NULL OR t3.c=10);

By the time the LEFT JOIN is transformed to a regular join, the sub-select has already been marked as correlated. So even though the "t2.y IS NULL" is transformed to "false", making the sub-select uncorrelated, SQLite still runs it once for every row visited by the query.

Apparently, other database engines do better in these cases.

I'm not sure how much of a rush we'll be in to fix these specific cases. Something for after 3.35.0 anyway.

Thanks,

Dan.

2021-02-23
15:54 Reply: Window Functions and Views: Missing Optimization? (artifact: 5bff4f7ae6 user: dan)

Seems like a good optimization. Thanks!

https://sqlite.org/src/info/20689468100aed26

Dan.

10:40 Reply: RAM consumption spike when using SAVEPOINT and FK update (artifact: 9bb4c28600 user: dan)

I think so. These things are never certain though, we might find a problem with the patch yet.

Dan.

2021-02-22
21:13 Reply: DROP COLUMN feature with indexes (artifact: e5b5e37fb6 user: dan)

On a related note, I'm not sure how much schema checking SQLite does for triggers, but CREATE TRIGGER x BEFORE UPDATE OF z ON c1 also remains unmodified after a column is dropped.

Good eye! This is a special case, because SQLite never checks for or complains about unknown column names in the "UPDATE OF" clause of a trigger. If the column being dropped is used anywhere else in the trigger it should be an error.

Dan.

16:46 Reply: DROP COLUMN feature with indexes (artifact: 64ab1396d5 user: dan)

What should it do instead? Automatically drop the indexes? Automatically change "c1(x,z)" to "c1(x)"?

Dan.

15:55 Reply: RAM consumption spike when using SAVEPOINT and FK update (artifact: 0a7a35e9d8 user: dan)

I think this is a slightly different problem than the one in 2014. Back then we were worried about statement journal growth when writing to the parent table of an FK constraint - this example is writing to the child table.

Given that you have no triggers or anything, and that your DML statement affects a single row, it's most likely possible to run this SQL without a statement journal at all, which would fix the problem. But it's a fairly tricky change.

This patch:

https://sqlite.org/src/info/e36327fb22db0876

attempts to prevent the statement journal from growing indefinitely at a lower level. I think it will help in many cases, but not all. It does with your example, FWIW.

If you get the chance, can you try this in your application code and let us know if it helps any?

Thanks,

Dan.

2021-02-18
16:27 Reply: sqlite3_preupdate_hook and vacuum (artifact: 1ace11cace user: dan)

Thanks for reporting this. Now fixed here:

https://sqlite.org/src/info/3c25cb4ab8885a50

This change means no pre-update or update hooks are invoked as part of a VACUUM operation, even if that VACUUM operation changes rowid values.

2021-02-15
11:16 Reply: Test backup2-10 failing on OpenBSD VM (artifact: 81740e9e56 user: dan)

Oops. How about now?

https://sqlite.org/src/info/ecd71203

Turns out this probem affected 6.8 as well, btw.

Dan.

2021-02-13
14:46 Reply: Test backup2-10 failing on OpenBSD VM (artifact: 791bb74c52 user: dan)

Thanks for reporting this. Now fixed here:

https://sqlite.org/src/info/ecd712032f56a20d

Dan.

2021-02-08
13:48 Reply: Bug: sqldiff --changeset SOME_FILE ... produces 0-length file (artifact: c99134e6f0 user: dan)

I think specifying --changeset causes tables with no explicitly declared primary key to be silently ignored. Rows with NULL values in their primary key fields are also ignored. These are the same limitations as apply to the sessions module.

Does that explain the zero-length output?

I think you're correct that the docs should mention these things.

Dan.

12:06 Edit reply: Changes are missing after savepoint release (artifact: 57e67acb5c user: dan)

ROLLBACK TO does not close the named savepoint. So your program exits without committing the top level transaction. If you add:

    exec("RELEASE sp1");

before the sqlite3_close() it will work as expected.

https://sqlite.org/lang_savepoint.html

12:05 Reply: Changes are missing after savepoint release (artifact: dcd9c74938 user: dan)

ROLLBACK TO does not close the named savepoint. So your program exits without committing the toplevel transaction. If you add:

    exec("RELEASE sp1");

before the sqlite3_close() it will work as expected.

https://sqlite.org/lang_savepoint.html

06:01 Reply: Can I select the FTS4 hidden column? (artifact: ac70886bd9 user: dan)

There's no useful data for users in that column. It's an opaque handle only useful to fts4 internals.

Dan.

2021-02-01
16:49 Reply: Offsets function for FTS5, any ready-to-use or sample implementation? (artifact: d711037d68 user: dan)

I read from the SQLite document that the offsets function might be added in the future, so what's the estimated time frame, if any?

Huh. Where does it say that?

Anyone can point me to any custom offsets implementations for FTS5 so that i can port it to my language?

I don't know of one. You would need to create a function like this one:

https://sqlite.org/fts5.html#custom_auxiliary_functions

That uses the xInstCount(), xInst(), xColumnText() and xTokenize() methods to figure out the byte offsets of each matching phrase:

https://sqlite.org/fts5.html#xTokenize

I think the implementation would have a lot in common with the implementation of the built-in highlight() function:

https://sqlite.org/src/info/f558e1fb9f0?ln=108-247

2021-01-27
11:14 Reply: What don't I understand about set theory? (artifact: 8adaaf264a user: dan)

What does running:

    PRAGMA integrity_check

against the db file show?

2021-01-21
11:07 Reply: Reload WAL when modified by outside process (artifact: f1dae491d2 user: dan)

There is no supported way to do this.

But if that does not deter you, one way is to put the db in "PRAGMA journal_mode = delete" (so there is no wal file). Copy the *-wal next to the db. The next time any existing connection reads from the db file it will automatically switch to wal mode and read from the db and the new wal file. Everything will proceed as normal.

Thing is, the db won't switch back to rollback journal mode until the last connection closes the db and deletes the wal file. So you could only do this once without closing all connections.

If the db is already in wal mode, you would have to: (a) make sure there is no content in the current wal file (b) copy the contents of your wal file over the top of the current wal file and (c) zero the first few bytes of the *-shm file. Step (c) will force the next reader to run recovery and thereby pick up on the new wal file content.

Dan.

2021-01-16
21:21 Edit reply: Apparent bug in ALTER TABLE (artifact: f301107068 user: dan)

Without "PRAGMA legacy_alter_table = 1", the ALTER TABLE command will not even attempt to operate on schemas that contain views with missing references. So the error is being produced before ALTER TABLE even looks at the new table name.

2021-01-13
15:25 Reply: Multi-threading sluggishess on Mac? (artifact: c0bfb3262a user: dan)

Is HAVE_USLEEP defined at build time?

Dan.

2021-01-12
14:35 Reply: Previous SQLite AAR Files (artifact: a9a78ab5a3 user: dan)

There's nothing that old unfortunately. 3.7.11 is from 2012, but we didn't start building Android packages until 2014.

Not sure exactly what you're trying to do, but I'd say if you get amalgamation files for 3.7.11 and then build then into a custom aar file following method (2) here:

https://sqlite.org/android/doc/trunk/www/install.wiki

it will probably work.

Dan.

14:27 Reply: configure.ac should remove tclsh8.7 (artifact: ac938f85dd user: dan)

Does it work for you as of this commit?

https://sqlite.org/src/info/8f3ab5da4c8906b6

Thanks,

Dan.

2021-01-11
13:32 Reply: Bug: FTS5 Unicode61 Tokenizer doesn't recognize "ł" and "Ł" characters (Polish language) (artifact: fbffd95594 user: dan)

The problem is in the unicode definitions that we use to construct the tokenizer. The UnicodeData.txt entries for the upper and lower case version of that character are:

    0141;LATIN CAPITAL LETTER L WITH STROKE;Lu;0;L;;;;;N;LATIN CAPITAL LETTER L SLASH;;;0142;
    0142;LATIN SMALL LETTER L WITH STROKE;Ll;0;L;;;;;N;LATIN SMALL LETTER L SLASH;;0141;;0141

For other such characters used by European languages, unicode includes a mapping to the codepoints for the base character and diacritic. e.g.

    013F;LATIN CAPITAL LETTER L WITH MIDDLE DOT;Lu;0;L;<compat> 004C 00B7;;;;N;;;;0140;
    0140;LATIN SMALL LETTER L WITH MIDDLE DOT;Ll;0;L;<compat> 006C 00B7;;;;N;;;013F;;013F

(the base characters for these two are 004C and 006C).

You could create your own tokenizer:

https://sqlite.org/fts5.html#custom_tokenizers

Dan.

2021-01-07
18:51 Reply: test/crash5.test and get_pwd (artifact: 2966bce1ae user: dan)

Thanks for looking into this. Should now be fixed here:

https://sqlite.org/src/info/0c8e2ede5c325aa7

Dan

2021-01-04
18:30 Reply: ext/fts3/fts3_unicode: not handle tokens that contain embedded nul characters (artifact: 5f9bfae7e5 user: dan)

Hi,

Does this actually cause a problem? As far as I can tell, fts4 unicode61 tokenizer just stops tokenizing at the first 0x00 byte in its input. Which might not be ideal in some cases, but seems to be internally consistent. The problem with the trigram tokenizer was that embedded 0x00 bytes caused integrity-check failures.

Thanks, Dan.

17:55 Reply: Apparent bug in ALTER TABLE (artifact: 180930ec57 user: dan)

Without "PRAGMA legacy_alter_table = 0", the ALTER TABLE command will not even attempt to operate on schemas that contain views with missing references. So the error is being produced before ALTER TABLE even looks at the new table name.

2020-12-22
11:01 Edit reply: Possible Bug - View behaves differently to Table in SELECT (artifact: 0948eec194 user: dan)

Thanks for reporting this one - and for bumping it since it looks like it was overlooked at first. Should now be fixed on trunk.

To get this working, we had to fix 1 longstanding bug to do with LEFT JOIN and queries that use more than 65536 database cursors, 1 bug in unreleased code to do with joining UNION ALL subqueries with LEFT JOIN subqueries, and 1 bug in a general purpose routine used for iterating through expression nodes. It's not clear whether that last one could actually be exploited in released code, but it's quite possible.

I really can't remember the last time a single query found 2 bugs, let alone 3. So you certainly have one of the most complicated SQLite schemas out there - perhaps the most complicated overall!

Dan.

11:00 Reply: Possible Bug - View behaves differently to Table in SELECT (artifact: f58cf903a7 user: dan)

Thanks for reporting this one. Should now be fixed on trunk.

To get this working, we had to fix 1 longstanding bug to do with LEFT JOIN and queries that use more than 65536 database cursors, 1 bug in unreleased code to do with joining UNION ALL subqueries with LEFT JOIN subqueries, and 1 bug in a general purpose routine used for iterating through expression nodes. It's not clear whether that last one could actually be exploited in released code, but it's quite possible.

I really can't remember the last time a single query found 2 bugs, let alone 3. So you certainly have one of the most complicated SQLite schemas out there - perhaps the most complicated overall!

Dan.

2020-12-21
13:17 Reply: CREATE INDEX ignoring pragma cache_size - is there a way to fix? (artifact: 63e736fd79 user: dan)

How are you measuring memory usage?

With temp_store=FILE, a CREATE INDEX statement for a large index should use approximately 2x (or (n+2)x if you have SQLite configured to use n background threads when sorting) the configured cache-size in heap memory. It also uses temporary files, usually in /tmp, but sometimes in some other place:

https://sqlite.org/tempfiles.html#temporary_file_storage_locations

Depending on how you're measuring memory usage and where they are being created, the temp files may be being included in the measurement.

Dan.

2020-12-11
14:24 Reply: CLI: .expert fails with "no such column" when query contains generated columns (artifact: 882161d629 user: dan)

Thanks for reporting this. Now fixed here:

https://www.sqlite.org/src/info/c2ae7ba6f8f4b10b

Dan.

2020-12-10
11:40 Edit reply: Compiling spellfix on macos - Error: Undefined symbols for architecture x86_64 (artifact: 24e6db2783 user: dan)

The same recipe works here (older system though - "Apple LLVM version 10.0.1 (clang-1001.0.46.4)").

spellfix1RunQuery() should not be invoking sqlite3_bind_text() directly in a loadable extension:

    "_sqlite3_bind_text", referenced from:
       _spellfix1RunQuery in spellfix-f9aba8.o

It should be calling it via a macro defined in included file sqlite3ext.h:

    #define sqlite3_bind_text              sqlite3_api->bind_text

If you figure out why this macro doesn't seem to be defined when spellfix1RunQuery() is compiled, things will probably become clear.

More ↓