SQLite Forum

Timeline
Login

50 most recent forum posts by user drh

2021-10-21
17:28 Reply: Truncated Words bug with fts5vocab (artifact: fb1e6b3c25 user: drh)

The Porter Stemmer algorithm does that. It is a feature, not a bug. The idea of a "stemmer" is to map words that share a common root into the same base form, so that they appear to the search algorithm as the same word.

The Porter Stemmer algorithm (named for its inventor, Martin Porter), only works for English. But it has been widely used for over 4 decades and works fairly well within its domain.

If you don't want to use the Porter Stemmer, leave off the "porter" keyword when you declare the FTS5 table.

2021-10-20
13:48 Reply: BUG carray.c memcpy() buffer overflow (artifact: 191ad0797f user: drh)

Now fixed on trunk. Thanks for the bug report.

11:26 Reply: How do i submit a bug report (artifact: d04f5871e3 user: drh)

Lemon is a command-line tool. Nobody runs Lemon who does not already have full shell access on the target machine. So memory safety issues in Lemon don't really matter that much.

Lemon generates code that is used by SQLite. But Lemon is not itself part of SQLite. If you find faults in the code that Lemon generates, that is an issue. But faults in Lemon itself are scarcely a concern.

2021-10-14
14:04 Reply: Pragma_Function_List() (artifact: 48b468b7e4 user: drh)

The flags column is an internal implementation detail and is subject to change. But a few of the bits are fixed. From sqlite3.h:

#define SQLITE_DETERMINISTIC    0x000000800
#define SQLITE_DIRECTONLY       0x000080000
#define SQLITE_SUBTYPE          0x000100000
#define SQLITE_INNOCUOUS        0x000200000

Thus, for example, to see a list of all functions that are not allowed to be used inside of triggers and views (SQLITE_DIRECTONLY functions) you could write:

SELECT DISTINCT name
  FROM pragma_function_list
 WHERE flags & 0x80000
 ORDER BY name;

And this gives you:

┌────────────────────┐
│        name        │
├────────────────────┤
│ fts3_tokenizer     │
│ geopoly_debug      │
│ icu_load_collation │
│ load_extension     │
│ readfile           │
│ sha3_query         │
│ writefile          │
└────────────────────┘
2021-10-13
14:48 Reply: Opening a DB with SQLITE_OPEN_EXCLUSIVE (artifact: cebd13c65b user: drh)

That check-in has been backed out.

  1. The change only worked for unix, not Windows. Such asymmetry, while occasionally necessary, is unwelcomed.

  2. There are corner cases where it does not work on unix either. And those corner cases would be difficult to fix.

  3. We don't see a compelling need for this feature (there are better ways to accomplish the same thing) and so we do not want to risk the stability of the project in order to implement it.

12:10 Reply: binary .dump format (artifact: f96d8d6001 user: drh)

Is there a way to get a file similar to what .dump outputs in a binary format?

Yes. The https://www.sqlite.org/src/file/ext/misc/dbdump.c file contains a stand-alone implementation of ".dump". You can make a copy of that file and modify it to generate whatever binary output format you desire.

How do you propose to get your binary-dump format back into an SQLite database?

11:59 Reply: Build fails with SQLITE_OMIT_WAL and SQLITE_MAX_MMAP_SIZE=0 (artifact: e1f858f5bb user: drh)

I'd rather not remove SQLITE_OMIT_WAL from the configuration.

Why not? What harm is WAL mode causing you?

2021-10-12
14:03 Reply: sqlite3_serialize / deserialize 2GB limit on 64 bit (artifact: 31a4fa7c69 user: drh)

We have discussed making it work during the next release cycle. No promises, though.

2021-10-10
19:24 Reply: x86_64/amd64 sqlite3 binaries (artifact: a93268ede4 user: drh)

What is so hard about running "./configure && make"?

2021-10-04
11:25 Reply: Unexpected output from the SELECT (artifact: e76abae426 user: drh)

There are two major paths through the query planner in SQLite. The whereShortCut() path is a very quick planner that is appropriate for simple queries (only a single table in the FROM clause), and there is a slower path that does a much more detailed analysis of the query and is used for joins and queries against virtual tables and other situations where a more detailed analysis is appropriate. The problem described by this thread is essentially the same as the problem previously described by Wang Ke in forum thread eb8613976a, except that in this case the problem was in whereShortCut() rather than in the full-analysis path.

Both problems should be fixed as of check-in 8b24c177061c3836.

The changes at check-in e038ce8955e785af which the bisect above identified as the problem source actually have nothing to do with the problem at all. That change merely helped to expose the problem.

2021-10-03
10:37 Reply: Unexpected output from the SELECT (artifact: 1acf29f24a user: drh)

Simplified SQL:

CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(0),(3);
CREATE TABLE t2(b INT UNIQUE, c INT);
INSERT INTO t2 VALUES(1,4),(0,5);
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE a=c AND a IS b);

The one-row answer returned is incorrect. The correct answer is no rows. This can be seen by disabling the "transitive constraint" query optimization:

CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(0),(3);
CREATE TABLE t2(b INT UNIQUE, c INT);
INSERT INTO t2 VALUES(1,4),(0,5);
.echo on
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE a=c AND a IS b);
.testctrl optimizations 0x80
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE a=c AND a IS b);

The first of the two identical queries returns one row, but the second (with the 0x80 optimization disabled) returns zero rows.

The equivalent PostgreSQL query is:

CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES(0),(3);
CREATE TABLE t2(b INT UNIQUE, c INT);
INSERT INTO t2 VALUES(1,4),(0,5);
SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE a=c AND a IS NOT DISTINCT FROM b);

PostgreSQL gives a zero-row result, further confirming that something is wrong.

2021-10-01
13:24 Edit reply: Expired certificate (artifact: 000b54e897 user: drh)

This is due to the Let's Encrypt Root Cert expiration. SQLite's cert is fine. The problem is that your client has not updated its set of root certs lately.

I had the same problem with "curl" on my Ubuntu 16.04 desktop this morning. Everything else on my desktop continued to work fine, but for whatever reason, "curl" was clinging to an older set of root certificates. I solved that problem by copying the /etc/ssl/certs directory (and all of its content) from a newer Ubuntu 20.04 machine over into my desktop.

13:23 Reply: Expired certificate (artifact: fbe060e94e user: drh)

This is due to the Let's Encrypt Root Cert expiration. SQLite's cert is fine. The problem is that your client has not updated its set of root certs lately.

I had the same problem today with "curl" on my Ubuntu 16.0.4 desktop this morning. Everything else on my desktop continued to work fine, but for whatever reason, "curl" was clinging to an older set of root certificates. I solved that problem by copying the /etc/ssl/certs directory (and all of its content) from a newer Ubuntu 20.04 machine over into my desktop.

2021-09-29
14:52 Reply: HTML generation issue in sqlite.org's search engine (artifact: 76399be9f9 user: drh)

I was not able to reproduce the anomaly with a quick check. Do you have an example of a search that generates the erroneous behavior?

13:14 Reply: HTML generation issue in sqlite.org's search engine (artifact: 726e410466 user: drh)

Should be fixed now.

00:08 Edit: User-defined functions in the CLI? (artifact: 074d0a3dd6 user: drh)

Is it possible to use sqlite3_create_Function to create user-defined function in the CLI?

2021-09-27
10:48 Reply: Test SQLITE_NOMEM result code on Linux (Memory : 8Gb) (artifact: 4203b0ca76 user: drh)

You can register your own memory allocator using the [sqlite3_config(SQLITE_CONFIG_MALLOC, ...)1 interface. This is what the various SQLite test harnesses do. Normally calls to the custom malloc fall through into system malloc, but it is possible to inject faults. There is an example of doing this in the CLI source code.

You might also set the PRAGMA hard_heap_limit to some small value in one connection, and then open a separate connection. The hard_heap_limit applies to the entire process, not to individual connections.

2021-09-23
15:12 Reply: Best way to use carray (artifact: b8eaa5b6b8 user: drh)

Indexing will happen in the SQLite core, not in the carray extension. Indexing will occur if you create an index:

CREATE INDEX foo_sid ON foo(sourceId);
2021-09-22
19:35 Reply: cannot start a transaction within a transaction using sqlite shell (artifact: 2507664507 user: drh)

I'm guessing:

  1. First window does: BEGIN; INSERT INTO ...;

  2. Second window does DELETE FROM ...; with an implicit COMMIT.

  3. First window tries to do: END;. But because END is just an alternative spelling for COMMIT, it cannot, because the second window jumped in line ahead of it. So the COMMIT fails, leaving the transaction open.

  4. All subsequent BEGIN operations fail because there is already a transaction open.

Possible solutions:

  • Use "BEGIN IMMEDIATE" instead of "BEGIN TRANSACTION".

  • Put the BEGIN and the END on separate input lines. I think (but I'm not sure - I'll need to check the code) that the CLI works a line at a time. So if the BEGIN fails, the END never has a chance to run. Larry has been in that code more recently than me → perhaps he can answer off the top of his head.

14:39 Reply: sqlite3_exec: 5th parameter (artifact: 69c418c0a0 user: drh)

The error message is only set and meaningful if a non-SQLITE_OK code is returned.

Note that the error message might not be set for SQLITE_NOMEM too.

12:58 Reply: vfs write amount size without WAL (artifact: e97853e517 user: drh)

SQLite itself will only write to the database file page-size chunks that are aligned to a page boundary.

But beware: Intermediate VFS shims might change that. Also, an application could, if it wanted to, acquire the sqlite3_file object for the database and then invoke the xWrite method with any parameters it wants.

So, in other words, it would be reasonable to return SQLITE_IOERR or some other error code if you get an iAmt that is different from the page size, but it would not be reasonable to assert().

00:24 Edit: Question about UPDATE with LIMIT (artifact: 062a215114 user: drh)

we have the following sqlite command that works:

UPDATE feeder SET quantity = quantity + 1 WHERE quantity < 200 AND available = 1 RETURNING id, food_weigth, quantity

but we would like to add a limit 1 since we don't want to update all the feeders

something like (it doesn't work):

UPDATE feeder SET quantity = quantity + 1 WHERE quantity < 200 AND available = 1 LIMIT 1 RETURNING id, food_weigth, quantity

nyl

00:22 Edit: Question about UPDATE with LIMIT (artifact: 1e1ca3b821 user: drh)

we have the following sqlite command that works:

UPDATE feeder SET quantity = quantity + 1 WHERE quantity < 200 AND available = 1 RETURNING id, food_weigth, quantity

but we would like to add a limit 1 since we don't want to update all the feeders

something like (it doesn't work):

UPDATE feeder SET quantity = quantity + 1 WHERE quantity < 200 AND available = 1 LIMIT 1 RETURNING id, food_weigth, quantity

nyl

2021-09-16
20:20 Edit reply: Javascript enforcement (artifact: 4f158f4697 user: drh)

It is still surprisingly effective, as most robots do not simulate mouse movements, and the hyperlinks typically do not appear until after the mouse moves.

Automatic robot blocking is useful, because it means I have to spend less time blocking robots and hence have more time available to do actual programming.

19:59 Reply: Javascript enforcement (artifact: 6afeb7df24 user: drh)

It is still surprisingly effect, as most robots do not simulate mouse movements, and the hyperlinks typically do not appear until after the mouse moves.

Automatic robot blocking is useful, because it means I have to spend less time blocking robots and hence have more time available to do actual programming.

2021-09-15
11:39 Reply: Writing a SQLite db as separate files (artifact: 5adcfd85cc user: drh)
2021-09-14
19:21 Reply: In memory db actually creating file (artifact: 3cf0bf1f53 user: drh)

Maybe you need to enable URI filenames. They are disabled by default for compatibility with older versions of SQLite.

15:30 Edit: Slow query (artifact: a6c364b845 user: drh)

I have a table in an SQLite database in the following form (6 columns):

Timestamp fst_field ... fifth_field
10001 X ... ...
10003 Y ... ...
10005 Z ... ...
10020 W ... ...

It doesn't have any primary key and all the fields are of type text except for the Timestamp (int)

The following query:

SELECT s.Timestamp, s.fst_field , ..., s.fifth_field, p.Timestamp, p.fst_field, ..., p.fifth_field
FROM table s JOIN table p ON s.Timestamp < p.Timestamp AND p.Timestamp - s.Timestamp < 10

should output every row next to all its following rows within a threshold (here 10).

The output looks like the following (12 columns):

s.Timestamp s.fst_field ... s.fifth_field p.Timestamp p.fst_field ... p.fifth_field
10001 X ... ... 10003 Y ...
10001 X ... ... 10005 Z ...
... ... ... ...

The query works for tables with 4000 rows. However, when I try it with tables with 22 million rows (1,4 GB file) it keeps running for days without ever stopping. I tried adding an index on the attribute 'Timestamp', to make the query faster but that didn't help. I ran the code both on Linux and Windows.

Any idea why this doesn't work?

2021-09-13
18:17 Edit reply: no check for a failled malloc (artifact: b322136106 user: drh)

Thanks for the report.

The first bullet is not possible because of this line that forces p->pSrc to be NULL if pSub is NULL. Similarly the second bullet is not possible because the test of db->mallocFailed down inside of sqlite3DbMallocRawNN() causes pEq to be NULL if pE2 is NULL.

So, neither of these cases are actual problems. No action required.

Edit: See also check-in 83a83475c5064ea6.

18:02 Reply: no check for a failled malloc (artifact: 5136f7619e user: drh)

Thanks for the report.

The first bullet is not possible because of this line that forces p->pSrc to be NULL if pSub is NULL. Similarly the second bullet is not possible because the test of db->mallocFailed down inside of sqlite3DbMallocRawNN() causes pEq to be NULL if pE2 is NULL.

So, neither of these cases are actual problems. No action required.

09:50 Reply: Q shrink.test (artifact: 8891d52827 user: drh)
2021-09-10
18:24 Reply: ZIPVFS questions (artifact: 683435e2ba user: drh)

If the databases truly are read-only, have you considered CEROD instead of ZIPVFS?

2021-09-09
19:45 Reply: Issues with sqlite3IsNaN() and HAVE_ISNAN (artifact: 512166c2a5 user: drh)

Double-check your results please. You should have gotten a correct database as in post (13), with the last four bytes in ascending order of value.

Another thing to try is to compile with -DSQLITE_DEBUG. With that option enabled, you should get an assertion fault if you try to read or write a floating point value using the wrong byte order.

Based on the experience of this thread, the next release might raise an error if you try to run sqlite3_open() using an SQLite that has been built with the wrong SQLITE_MIXED_ENDIAN_64BIT_FLOAT setting, regardless of the SQLITE_DEBUG setting.

12:26 Reply: SQLite3.exe fails to handle huge multi-line-comment (artifact: d5d7e8dcca user: drh)

My suggestion would be to avoid adding not required lines to that buffer

That's a lot of complication and potential for bugs for an obscure performance-optimization case. In particular the function that determines whether or not a line is required seems likely to be subtle and error-prone.

10:07 Reply: Issues with sqlite3IsNaN() and HAVE_ISNAN (artifact: c4b60638b6 user: drh)

Thanks.

Please recompile SQLite using -DSQLITE_MIXED_ENDIAN_64BIT_FLOAT and rerun the experiment for me.

00:56 Reply: v3.36.0 fails to compile under Ubuntu (artifact: 81173f663c user: drh)

Try leaving out the -DSQLITE_OMIT_WAL.

2021-09-08
14:06 Reply: NEXT VALUE FOR in SQLite (artifact: 3e43565f77 user: drh)
CREATE TABLE kv(name TEXT PRIMARY KEY, value INTEGER);
CREATE INDEX kv_value ON kv(value);
CREATE TRIGGER kv_autoinc AFTER INSERT ON kv
WHEN new.value IS NULL BEGIN
  UPDATE kv SET value=(SELECT coalesce(max(value),0)+1 FROM kv) 
   WHERE name=new.name;
END;

Then when you insert a NULL

INSERT INTO kv(name) VALUES('first');

The trigger will automatically add in the next larger value, or 1 if the table is initially empty.

14:00 Reply: SQLite3.exe fails to handle huge multi-line-comment (artifact: 9f1fa87d28 user: drh)

I think the following shell script demonstrates the problem:

cat >script.tcl <<\EOF
  puts {/*}
  for {set i 0} {$i<10000} {incr i} {puts "SELECT null;"}
  puts {*/}
  puts {SELECT 1, 2, 3;}
EOF
tclsh script.tcl | time sqlite3

The TCL script generates 10000 lines of comment prior to a single SELECT statement. Each comment line ends with ";".

What I think is going wrong is this: The sqlite3 shell reads its input line by line. As each line is input, it asks "do I have a complete SQL statement, or do I need to wait for more?" To implement this test, it first checks to see if the input ended with ";". If it did, then it also calls "sqlite3_complete()" to see whether the input really is complete, or if the final ";" was perhaps in the middle of a string literal or CREATE TRIGGER statement or within a comment. Only if sqlite3_complete() returns true does the sqlite3 shell try to process the input.

So what is happening here is that because each line of the input comment ends with ";", sqlite3_complete() gets called over and over again, each time on successively larger inputs.

13:47 Edit: WAL/SHM files do not get deleted with ReadOnly flag (artifact: c574e4fcc5 user: drh)
Hi,
Open/Close ReadWrite - wal file deleted.
Open/Close ReadOnly  - wal/shm files retained.
Is that by design?
Thanks
10:49 Reply: Issues with sqlite3IsNaN() and HAVE_ISNAN (artifact: e198970419 user: drh)

Please run this test for me on your RISC OS system:

make sqlite3 dbtotxt
rm -f byteorder.db
./sqlite3 byteorder.db 'CREATE TABLE t1 AS SELECT 1579060583422312000000.0 x;'
./dbtotxt byteorder.db

Post the output of "dbtotxt" command on this chat thread. I'm expecting to see this output:

| size 8192 pagesize 4096 filename byteorder.db
| page 1 offset 0
|      0: 53 51 4c 69 74 65 20 66 6f 72 6d 61 74 20 33 00   SQLite format 3.
|     16: 10 00 01 01 00 40 20 20 00 00 00 01 00 00 00 02   .....@  ........
|     32: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 04   ................
|     48: 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00 00   ................
|     80: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01   ................
|     96: 00 2e 57 48 0d 00 00 00 01 0f dc 00 0f dc 00 00   ..WH............
|   4048: 00 00 00 00 00 00 00 00 00 00 00 00 22 01 06 17   ................
|   4064: 11 11 01 31 74 61 62 6c 65 74 31 74 31 02 43 52   ...1tablet1t1.CR
|   4080: 45 41 54 45 20 54 41 42 4c 45 20 74 31 28 78 29   EATE TABLE t1(x)
| page 2 offset 4096
|      0: 0d 00 00 00 01 0f f4 00 0f f4 00 00 00 00 00 00   ................
|   4080: 00 00 00 00 0a 01 02 07 44 55 66 77 88 99 aa ff   ........DUfw....

I'm interested in the last 8 bytes of output, which should be "44 55 66 77 88 99 aa ff". What are those 8 bytes on your RISC OS system?

2021-09-07
17:18 Reply: Segmentation fault in function isLikeOrGlob() (artifact: 56a527a1ba user: drh)

The best I could do is shown in the test case that is included with the fix.

  CREATE TABLE t0(a PRIMARY KEY,b TEXT AS ('2') UNIQUE);
  SELECT * FROM t0 AS x JOIN t0 AS y
   WHERE x.b='2'
     AND (y.a=2 OR (x.b LIKE '2*' AND y.a=x.b));

So I was able to take off the "WITHOUT ROWID" from the table definition, and remove the "likely()" term from the WHERE clause. I wasn't able to simplify beyond that.

The problem arose for a tricky and unlikely interaction between:

  • The LIKE optimizer
  • The OR optimizer
  • Generated columns
16:30 Reply: Segmentation fault in function isLikeOrGlob() (artifact: 2d8537a66e user: drh)

Just out of curiosity.... Do you have an estimate of how man CPU-hours your fuzzer took to find this bug?

15:42 Reply: Segmentation fault in function isLikeOrGlob() (artifact: 977c4b0f31 user: drh)

Thanks for the bug report. Should now be fixed on trunk.

2021-09-06
11:48 Reply: Issues with sqlite3IsNaN() and HAVE_ISNAN (artifact: 848e4cf7ea user: drh)

double and u64 (unsigned long long int) have different word orders on this platform

If that is true, then probably the database files generated by SQLite do not follow the spec. The sqlite3IsNan() might be the least of your worries.

Please try this experiment:

  • Generate a database that contains floating point values (with fractional parts - example: 4.5 not 4.0) on RISC OS.
  • Move the database file to another platform (Windows, Linux, or Mac).
  • Check to see if the floating point values are preserved.

Let me know the result of the experiment.

Meanwhile, the use of system isnan() has been restored by check-in b3cfe23bec0b95ca. But remember: Don't just blindly take this patch and decide "it works!" because you may well have deeper and subtler problems.

2021-08-31
11:03 Reply: Possible bug: Unexpected result using correlated aggregate subquery (artifact: 810c6f5393 user: drh)

SQLite computes the same answer as PostgreSQL here.

To be clear, I had to modify the code slightly to conform to PG syntax:

  • Add a LIMIT 1 to the subquery. The LIMIT 1 is implied by SQLite, but must be explicit in PG (also in MySQL and SQL Server).

  • Change group_concat() into string_agg().

I end up with this:

CREATE TABLE t1(a varchar);
CREATE TABLE t2(b varchar);
INSERT INTO t1 VALUES(1);
INSERT INTO t2 VALUES(2),(3);
SELECT (SELECT string_agg(a,',') FROM t2 LIMIT 1) FROM t1;

To which PG gives an answer of just "1", same as SQLite.

2021-08-28
23:27 Reply: Module pragma_function_list registers only after first usage (artifact: 5f750f85ce user: drh)

All of the pragma_* virtual tables are demand-loaded. They don't show up until they are used.

01:18 Reply: Two potential bugs of memory leak (artifact: 97a76e677c user: drh)
01:17 Reply: A potential bug of return stack address (artifact: e68dcab09f user: drh)
01:15 Reply: several potential bugs of null pointer dereference (artifact: 7f2cc8ecf7 user: drh)

Would you help me check whether the bugs mentioned below are true?

No. Reasons:

  1. You are referencing version 3.22.0 which is now over 3.5 years old. The latest release is 3.36.0.

  2. Static analyzers are usually wrong about this stuff.

2021-08-24
14:56 Reply: Assertion Failure in constructAutomaticIndex() (artifact: 310fecd471 user: drh)

Thanks for the report.

I cannot reproduce the problem as you describe it, however I did get a valgrind error on trunk using your repro script. The problem I see does not appear to be in any official release. Bisecting shows that the problem originates on this check-in: 832ac4c1ee384be0.

Does that agree with what you are seeing?

More ↓