SQLite Forum

Timeline
Login

50 most recent forum posts by user drh

2021-06-18
15:07 Reply: Division by decimals wrongly returning a rounded int result (works fine when casting to real) (artifact: 3763455998 user: drh)

You can perhaps argue that DECIMAL should translate into floating-point instead of numeric. But the time to have made that argument was 2004. That's now all water under the bridge. To convert DECIMAL to be floating point now, 17 years later, would be a breaking API change that could potential cause problems countless legacy applications.

14:51 Reply: Division by decimals wrongly returning a rounded int result (works fine when casting to real) (artifact: 8a1ecfb31e user: drh)

And as you can see on my schema, they are indeed decimals and not integers.

See https://www.sqlite.org/datatype3.html#affinity_name_examples. SQLite stores integers in a DECIMAL column when it can. To force floating-point values, you want to use REAL.

Also be mindful of the limitations of floating point numbers. Depending on your requirements, you might want to store values as in integer number of cents, then convert to dollars (or whatever currency you are using) in the application code.

13:50 Reply: website: Wapp Application Error (artifact: 3fa7fe2bd8 user: drh)

I think the problem is now fixed in Wapp. Please try again.

2021-06-17
16:20 Reply: Disable File IO (artifact: 5ed543308a user: drh)

The -DSQLITE_OMIT_DISKIO option has not been maintained and no longer appears to be working. However, you can probably fix it up and get it to work again.

If you don't want to do that yourself and you have budget, you can perhaps engage the SQLite developers to do necessary fixes for you.

2021-06-16
14:51 Reply: How to find out which table is affected by page corruption (artifact: 5f2b2948c9 user: drh)

You need the canonical source tarball, not the "amalgamation" tarball. Get the canonical source tarball under the "Alternative Source Code Formats" section of the download page, or clone the Fossil repository.

10:41 Reply: How to find out which table is affected by page corruption (artifact: 6ba85487ca user: drh)

There is no simple utility program or function that you can run to figure out which tables are affected by database corruption. You have to do a deep analysis. You try running a command like:

showdb $YOURDATABASE pgidx

To get an idea of which each page in the file is used for. But badly corrupted pages might not show up in the output, as that utility is not able to trace them back to their use.

Using the "showdb" utility, you can dump the content of page 3282. I'm guessing you will find that it has been zeroed out by your hardware.

To get the "showdb" utility program, download the source code and run "make showdb".

2021-06-15
18:44 Reply: How to optimize writing from several processes? (artifact: fe05c77ea7 user: drh)

Are there UNIQUE constraints or FOREIGN KEY constraints? Or are you just blindly appending to the end of each database file?

15:17 Reply: Bug A NULL pointer dereference bug was discovered in SQLite (artifact: cd6e9c0b61 user: drh)

This is not really a bug since it is not reachable. However, I have added additional code to trunk which should squelch the warnings from your static analyzer.

11:43 Reply: A stack overflow in tool used to help build SQLite's TCL extension on Windows (artifact: 47c7eb6802 user: drh)

It's not our code. We copy/pasted from another project. I'll check for updates. Nevertheless, we haven't yet seen a vulnerability in the current code. Can you elaborate on what you think is incorrect?

11:08 Edit reply: A stack overflow in tool used to help build SQLite's TCL extension on Windows (artifact: 50b12dc4ba user: drh)

This Is Not An SQLite Vulnerability

Just to be clear to people who may be alarmed by the salmonx's title, this is not a bug in SQLite.

The name of the file is "nmakehlp.c", not "nmakehelp.c". And it is not part of SQLite. "nmakehlp.c" is a helper program used to assist in building the TCL Extension on Windows. It is part of TEA or the "TCL Extension Architecture". In other words, the "nmakehlp.exe" program is built and used to help with the build process for the SQLite TCL extension under Windows. Nobody ever runs this program, except when building the SQLite TCL interface on a Windows host. The inputs to nmakehlp.exe are well-controlled such that even if it does contain a vulnerability (which is probably does not) it would be completely harmless.

So, in other words, salmonx's original post above appears to be complete nonsense. One suspects that he is running a source-code scanning tool across every source file he can find and reporting his results here.

Edit: The original title has been edited to make this point clear.

11:07 Edit: A stack overflow in tool used to help build SQLite's TCL extension on Windows (artifact: 7d5c8b1923 user: drh)

The local variable 'szBuffer' in GetVersionFromFile can be exploited by a local attacker for arbitrary code execution.

static const char *
GetVersionFromFile(
    const char *filename,
    const char *match)
{
    size_t cbBuffer = 100;
    static char szBuffer[100];
    char *szResult = NULL;
    FILE *fp = fopen(filename, "rt");

    if (fp != NULL) {
	/*
	 * Read data until we see our match string.
	 */

	while (fgets(szBuffer, cbBuffer, fp) != NULL) {
	    LPSTR p, q;

	    p = strstr(szBuffer, match);
	    if (p != NULL) {
		/*
		 * Skip to first digit.
		 */

		while (*p && !isdigit(*p)) {
		    ++p;
		}

		/*
		 * Find ending whitespace.
		 */

		q = p;
		while (*q && (isalnum(*q) || *q == '.')) {
		    ++q;
		}

		memcpy(szBuffer, p, q - p); // Vulnerability
		szBuffer[q-p] = 0;
		szResult = szBuffer;
		break;
	    }
	}
	fclose(fp);
    }
    return szResult;
}

(Edit: Title changed for clarity)

11:05 Reply: A stack overflow in tool used to help build SQLite's TCL extension on Windows (artifact: e48a97787b user: drh)

This Is Not An SQLite Vulnerability

Just to be clear to people who may be alarmed by the salmonx's title, this is not a bug in SQLite.

The name of the file is "nmakehlp.c", not "nmakehelp.c". And it is not part of SQLite. "nmakehlp.c" is a helper program used to assist in building the TCL Extension on Windows. It is part of TEA or the "TCL Extension Architecture". In other words, the "nmakehlp.exe" program is built and used to help with the build process for the SQLite TCL extension under Windows. Nobody ever runs this program, except when building the SQLite TCL interface on a Windows host. The inputs to nmakehlp.exe are well-controlled such that even if it does contain a vulnerability (which is probably does not) it would be completely harmless.

So, in other words, salmonx's original post above appears to be complete nonsense. One suspects that he is running a source-code scanning tool across every source file he can find and reporting his results here.

2021-06-14
15:34 Post: Version 3.36.0 now in beta (artifact: 4326adcd2b user: drh)

There is now a feature freeze on the 3.36.0 release of SQLite.

Please try out the latest code and report any issues. You can append trouble reports to this forum thread, or send email to drh at sqlite dot org.

14:02 Reply: Affinity problem existing in functions like likely() (artifact: 0ec3fd2c80 user: drh)
12:24 Reply: xBestIndex constraints: discern between JOIN and regular constraints (artifact: 6b588e30b0 user: drh)

How can the BestIndex function determine if a given constraint comes from a "result set limiting" constraint or from a "join constraint"?

I'm not sure what those two categories of constraint mean. SQLite certainly doesn't make any such distinctions anywhere internally, as far as I know.

2021-06-13
10:58 Reply: Bug: ALTER TABLE RENAME COLUM TO vs CTE VIEWs (artifact: 2fe926277c user: drh)

I think this is a logic error in your SQL. I think SQLite is doing the right thing.

When you rename t0.col1 to t0.col2, the view is transformed from this:

CREATE VIEW v0 AS
  WITH n AS (SELECT t0.col1 FROM t0 ),
       o AS (SELECT col1 FROM n)
  SELECT * FROM o
;

Into this:

CREATE VIEW v0 AS
  WITH n AS (SELECT t0.col2 FROM t0 ),
       o AS (SELECT col1 FROM n)
  SELECT * FROM o
;

Because you have not defined the column names of CTE "n", names are selected by SQLite. Formerly, SQLite was choosing "col1" as the name of the only column in "n". After the ALTER TABLE it chooses "col2". SQLite is free to choose any column name it wants for "n", since you haven't specified one. But later, in the "o" CTE, you assume that the column name for "n" is "col1".

2021-06-12
15:28 Reply: Bug: `GENERATED ALWAYS` constraint ends up in column type (artifact: 85d7ea4d72 user: drh)

That's the way it gets parsed in SQLite.

When the GENERATED ALWAYS keywords were added in SQLite 3.31.0 (2020-01-22), we needed to ensure that legacy SQLite database files that might use the words "generated" or "always" as column or table names would continue to work. For that reason, "generated" and "always" can still be understood as identifiers in addition to keywords. Furthermore, the typename of a column is any sequence of identifiers. Hence "int generated always" ends up being parsed as the typename for the column.

If the typename contains parenthesized arguments (ex: VARCHAR(32)), then the parser knows that the typename ends at the close parenthesis. In that case the GENERATED ALWAYS keywords are understood as being part of the computed column clause, not as part of the typename.

If sometimes seeing "GENERATED ALWAYS" in the typename is a problem for you, you can simply leave those keywords out of your table definition. "AS" is the only keyword that is needed to make use of computed columns. The "GENERATED ALWAYS" prefix is just some filler words that are thrown into the SQL standard, perhaps to make SQL seem more like COBOL or something. Those words are not required and do not really accomplish anything, other than slowing down schema parsing a little.

2021-06-11
13:27 Reply: Bug: ALTER TABLE RENAME TO vs CTE VIEWs / multiple references (artifact: 5a2528deaa user: drh)

This issue appears to be resolved as of check-in 8b1f9a51e962cd9a.

12:49 Reply: bug A stack buffer overflow vulnerability was discovered in SQLite 3.36.0 (artifact: 41702338dd user: drh)

This appears to be a bug (or at least an undocumented limitation) in the dlopen() routine of the standard C library, not a bug in SQLite. The problem arises when dlopen() is invoked with a filename that is very long.

Check-in 01f3877c7172d522 works around this problem by simply not calling dlopen() if the filename exceeds FILENAME_MAX characters.

Note also that the load_extension() SQL function is disabled by default. So most applications are unable to trigger this bug in dlopen() even if it exists. The CLI is a rare exception to this rule in that it does take the extra steps needed to activate the load_extension() SQL function.

Because load_extension() is disabled by default, it seems dubious to call this problem "critical".

2021-06-10
15:16 Reply: Opening a DB with SQLITE_OPEN_EXCLUSIVE (artifact: 20bcd17b63 user: drh)

What problem are you trying to avoid by adding O_EXCL to open() calls? Why do you need to ensure that no other process is jumping in and creating the database file ahead of you?

Suppose your application is doing this:

  1. Check to see if the SQLite database already exists
  2. If it does not exist, create it.

What harm would come about if another rogue process created the database in between steps 1 and 2? I can invent a contrived scenario where that might lead to trouble, but I'm having trouble coming up with a real-world use case where that might be harmful. Presumably you have such a use-case. Can you share it with us?

14:51 Reply: Inconsistent memory stats of sqlite3 with different compilers (artifact: d0eb2790eb user: drh)

The "diff.sql" is fuzzer output. What are you doing? Are you working on something useful, or are you just trying to break things?

01:02 Reply: Opening a DB with SQLITE_OPEN_EXCLUSIVE (artifact: 05b04c07b0 user: drh)

The SQLITE_OPEN_EXCLUSIVE flag is an internal-use-only flag in a SQLite. The SQLite core will sometimes send that flag down into the VFS in order to tell the VFS that it wants the open to fail if the file already exists. But it is not legal to pass the SQLITE_OPEN_EXCLUSIVE flag into sqlite3_open(). That flag is silently masked off here.

This fact is not well documented. The only hint that you, the reader, have is that if you look at the documentation page for open flags, you will see the "VFS only" comment after the SQLITE_OPEN_EXCLUSIVE definition. This is, admittedly, a feeble hint. That part of the documentation could use improvement.

But the end analysis is that SQLite is currently performing as designed and what you are asking for is an enhancement. You want the ability to pass in the SQLITE_OPEN_EXCLUSIVE flag and have it work like O_EXCL. Probably this enhancement will also need a new extended return code: SQLITE_CANTOPEN_EXISTS.

It seems like a reasonable request. But we need to move cautiously about these kinds of things. So all I can say for now is that we will look into it.

2021-06-09
14:52 Reply: Bug in sqlite3_bind_blob64? Database error code is not set (artifact: 7e46e93f8d user: drh)

Follow-up comment added on the prior thread.

14:51 Reply: zeroblob64 and errcode (artifact: 68adf1c30e user: drh)

Please try again with any SQLite check-in 56ff58c0b8905aa1 or later, or using the latest prerelease-snapshot, and report back whether or not this still an issue.

2021-06-08
20:20 Reply: Requesting optimization for index expression case (artifact: 780b44cc6b user: drh)

Have you measured the speed to see if your preferred query plan really is faster than the query plan that SQLite actually generates?

19:09 Reply: Requesting optimization for index expression case (artifact: d615acf5a8 user: drh)

The whole point of OP_DeferredSeek is that it does not do a seek on the table right away, but defers that seek until it is actually needed, and if nothing is ever read from the table, the seek never occurs.

17:57 Reply: Interpreting output from showdb (artifact: 7906c85e96 user: drh)

Please try the latest change to showdb.c and let me know if that clears your problem.

12:18 Reply: Interpreting output from showdb (artifact: 7dbfb55eb4 user: drh)

I'm guessing that you are not showing us the complete showdb output, but just the part that has you confused. I'm guessing that there was a prior entry about "page 128 used multiple times". Probably page 128 appears twice in the same btree, or in two separate btrees. Overflow entries on that page are thus used twice.

Your patch appears to be a no-op. You are setting "zPageUse[pgno] = 0;" immediately prior to "zPageUse[pgno] = zMsg;". I would expect that an optimizing C compiler would recognize that the new statement does nothing and hence omit it from the generated code. Am I missing something?

12:09 Edit: how to use dbstat table in a trigger? (artifact: f52ba49e3e user: drh)

i need to limit my table size to suppose 10 Mb using dbstat i want to create a trigger for the same but iam getting below error SQL error: unsafe use of virtual table "dbstat"

below is the trigger query:

CREATE TRIGGER less_den_ten_mb
BEFORE INSERT ON mqtt6 
BEGIN 
DELETE FROM mqtt6 
WHERE timestamp=(select min(timestamp) from mqtt6 where 1=(SELECT sum(pgsize)>1024000 from dbstat where name="mqtt6")); 
end;
12:04 Reply: how to download older version (artifact: fa8a6c6ace user: drh)

The complete source-code history is available at https://www.sqlite.org/src/timeline.

If you are looking for precompiled binaries, consider looking at the filename for the latest precompiled binaries, figuring out the pattern (perhaps with help from the hints at the bottom of the download page) and then construct the name of the particular precompiled binary that you are interested in. You didn't tell us which binary product you are looking for, so we cannot do this for you.

11:59 Reply: how to use dbstat table in a trigger? (artifact: ff7b9ea437 user: drh)

The DBSTAT virtual table is marked with SQLITE_VTAB_DIRECTONLY, meaning that it cannot be used inside of triggers or views for security reasons. I don't recall what those security reasons are, right this moment. I should have put that information in a comment, I suppose. DBSTAT is read-only, so how could it present a security risk? Perhaps the DIRECTONLY designation was a mistake. But, following the principle of Chesterton's fence, I'm not going to remove that restriction until I understand why it is there.

2021-06-07
14:28 Reply: unable to get latest updates to database (WAL mode) from one open handler when updated from other open handler (artifact: 9d09ca6259 user: drh)

The statement "PRAGMA main.wal_checkpoint(RESTART);" is a one-time operation, not a configuration setting.

You didn't mention what OS and Filesystem you are using. Is this perhaps happening on a network filesystem of some kind? If so, have you tried setting "PRAGMA synchronous=FULL;" to see if that helps?

14:23 Edit: unable to get latest updates to database (WAL mode) from one open handler when updated from other open handler (artifact: e593d7e998 user: drh)

Our device has SQLite database configured in WAL mode. Our SQLite database is configured as follows:

PRAGMA main.journal_mode = WAL
PRAGMA main.wal_checkpoint(RESTART)
PRAGMA locking_mode = NORMAL
PRAGMA busy_timeout = 100

Compile time options selected are as follows:

-DSQLITE_TEMP_STORE=2 
-DSQLITE_OMIT_DECLTYPE=1 
-DSQLITE_DEFAULT_JOURNAL_SIZE_LIMIT=32768 
-DSQLITE_OMIT_DEPRECATED=1 
-DSQLITE_OMIT_SHARED_CACHE=1

FW has 2 separate SQLite open connections for accessing the database: Connection 1 - to read/write from different tables of the database Connection 2 - only to read contents of few tables from the database

We are observing an issue where an entry to one of the table is added using "Connection 1". Immediately after this "Connection 2" is used to read the contents of same table expecting that newly added information by "Connection 1" must be updated. But "Connection2" does not find the updated information in the table.

If a sleep(1) is added just before the "Connection 2" runs query to fetch the updated information then it correctly gets the updated information from the table. But otherwise "Connection 2" does not get the updates. Alternatively a sync() invocation just before "Connection 2" read operation also resolves the issue.

We also tried doing wal-checkpoint(PASSIVE) after "Connection 1" updates; but this does not resolve the issue.

  1. Why "Connection 2" is not able to see the updated information from "Connection 1" in SQLite database? and Why a sleep() or a sync() call is resolving the issue?

  2. Is it not that all processes will first refer to WAL file for read / write operations instead of directly referring the database?

  3. Is there some concurrency issue between "Connection 1" and "Connection 2"?

Request help or some directions to look into for understanding and resolving this issue at hand.

Thanks in advance.

2021-06-05
14:40 Reply: Query performance regression after v3.33.0 (OP_SeekScan?) (artifact: 8c53a4c826 user: drh)

One of our clients has real-world cases that run slowly if OP_SeekScan is turned off. In fact, OP_SeekScan was added specifically to support that client. We have cases in the TH3 test suite that run more slowly when OP_SeekScan is disabled.

If you don't want to ever use OP_SeekScan, you can now disable it at run-time using the SQLITE_TESTCTRL_OPTIMIZATIONS test-control with a mask of 0x20000:

 sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS, db, 0x20000);

In the CLI use this dot-command:

.testctrl optimizations 0x20000

If you discover a test case that runs faster with OP_SeekScan disabled, please bring it to our attention by posting it on this forum.

2021-06-04
18:29 Reply: Query performance regression after v3.33.0 (OP_SeekScan?) (artifact: ef9582f06f user: drh)

I believe this issue is now resolved on trunk, check-in 9a2ab6092d644fc3 and later. Please confirm.

16:19 Reply: Broken logical NOT in ext/misc/regexp.c (artifact: 9b8c234f4d user: drh)

Please try again with check-in 569e00d4acd42666 or later. The two check-ins that follow fix harmless compiler warnings and missing dependencies in the Makefile, so you might want to pick them up as well.

14:26 Reply: conversion of literal values, or lack thereof (artifact: 88f42578fc user: drh)

The suggestion makes sense. Except the current behavior is very carefully documented and tested and has been in use for many years. We can't risk breaking billions and billions of deployed applications for a small increase in design consistency.

11:46 Reply: Hidden noCase bug in ext/misc/regexp.c in pRe->zInit prefix optimization (artifact: 231c09ed3c user: drh)

A problem with using the SQLITE_DETERMINISTIC flag on REGEXP and thus permitting its use in the schema, for example in the WHERE clause of a partial index, is that different installations might easily use different definitions of REGEXP. The commonly used ICU extension defines a slightly different REGEXP operator, for example. And I think versions of REGEXP based on Perl regular expressions are in wide circulation. If you create an index using the built-in REGEXP and then some other application tries to use it using a different definition of REGEXP, then the index might not work right.

2021-06-03
18:53 Reply: Delete all rows from a "without rowid" table will get a wrong number of changed rows (artifact: c6762f736c user: drh)

Bug. Fixed at check-in 820ae3b117c2d8c1.

13:58 Reply: Hidden noCase bug in ext/misc/regexp.c in pRe->zInit prefix optimization (artifact: e75c22b979 user: drh)

This bug, plus one other, now fixed on trunk. I also added a new SQL function "regexpi(PATTERN,STRING)" that does case-independent REGEXP matching. And the regexp extension has been added to the CLI.

12:52 Reply: pragma_.. fails (artifact: 69a92fb06f user: drh)

That feature was added in 3.16.0 (2017-01-02). Version 3.7.13 dates from 2012-06-11.

2021-06-02
14:38 Reply: Bug report: a bug in VIEW (artifact: 4638b794d3 user: drh)

I have now amplified the documentation by giving it a heading and adding a link to this forum thread. The same hyperlink above still works. The paragraph that describes the indeterminacy of affinity in compound SELECT statements has been there for a long while - only the heading and the link to this forum thread are new.

12:47 Reply: Query performance regression after v3.33.0 (OP_SeekScan?) (artifact: 01f5bc23da user: drh)

The following script demonstrates the problem. I am adding the script to this forum thread to save is as part of the historical record:

/* Adjust $N and $NSUB as necessary to increase/descrease the
** problem size.  $NSUB was $N/10 in the original problem */
.param set $N 10000
.param set $NSUB 1000
CREATE TABLE node(node_id INTEGER PRIMARY KEY);
CREATE TABLE edge(node_from INT, node_to INT);
CREATE TABLE sub_nodes(node_id INTEGER PRIMARY KEY);
WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<$N )
  INSERT INTO node(node_id) SELECT NULL FROM s;
WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<$N )
  INSERT INTO edge(node_from, node_to) SELECT i, i+1 FROM s;
WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<$N )
  INSERT INTO edge(node_from, node_to)
    SELECT i, abs((i*1103515245 + 12345) % $N) FROM s;
WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<$NSUB )
  REPLACE INTO sub_nodes(node_id) SELECT abs(i) FROM s;
CREATE INDEX edge_from_to ON edge(node_from,node_to);
CREATE INDEX edge_to_from ON edge(node_to,node_from);
ANALYZE;

.mode box
SELECT * FROM sqlite_stat1;
.stats vmstep
.eqp on
.echo on

SELECT count(*) FROM edge 
 WHERE node_from IN sub_nodes AND node_to IN sub_nodes;

SELECT count(*) FROM edge 
 WHERE +node_from IN sub_nodes AND node_to IN sub_nodes;

SELECT count(*) FROM edge NOT INDEXED
 WHERE node_from IN sub_nodes AND node_to IN sub_nodes;
12:28 Reply: Bug report: a bug in VIEW (artifact: 90fba2127e user: drh)

Alternative formulation:

CREATE TABLE t0(a , b INT);
INSERT INTO t0 VALUES (NULL, 1);
CREATE VIEW v0(c) AS SELECT b FROM t0 EXCEPT SELECT a FROM t0;
CREATE VIEW v1 AS SELECT b AS 'c' FROM t0 EXCEPT SELECT a FROM t0;
SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v0; -- 1
SELECT (c NOT BETWEEN '-1' AND c) AS 'x' FROM v1; -- 0

The problem bisects to d794b34da6f9c77d which is the check-in that introduced the ability to specify the column names of a view. First appeared in version 3.9.0.

2021-06-01
16:48 Reply: Query performance regression after v3.33.0 (OP_SeekScan?) (artifact: bd0a9eb830 user: drh)

As a temporary work-around, please try modifying your query by putting a single "+" in from of the node_from in the WHERE clause:

SELECT count(_rowid_)
FROM edge
WHERE +node_from IN temp.sub_nodes AND node_to IN temp.sub_nodes;
  --  ^-- extra "+" here
12:34 Edit reply: ALTER TABLE fails with what seems irrelevant error (artifact: 0f84ffeedc user: drh)

Simplified test case:

CREATE TABLE t2(b,c);
INSERT INTO t2 VALUES(1,2),(1,3),(2,5);
CREATE VIEW v3 AS 
  WITH RECURSIVE t3(x,y,z) AS (
    SELECT b,c,NULL FROM t2
    UNION
    SELECT x,y,c FROM t3, t2 WHERE b=x
    ORDER BY y
  )
  SELECT * FROM t3;
SELECT * FROM v3;  -- This works

CREATE TABLE t1(a);
ALTER TABLE t1 RENAME a TO a2; -- fails in v3
12:14 Reply: ALTER TABLE fails with what seems irrelevant error (artifact: 20c3cd4823 user: drh)

Simplified test case:

CREATE TABLE t2(b,c);
INSERT INTO t2 VALUES(1,2),(1,3),(2,5);
CREATE VIEW v3 AS 
  WITH RECURSIVE t3(x,y,z) AS (
    SELECT b,c,NULL FROM t2
    UNION
    SELECT x,y,c FROM t3, t2 WHERE b=x
    ORDER BY y
  )
  SELECT * FROM t3;
SELECT * FROM v3;  -- This works
CREATE TABLE t1(a);
ALTER TABLE t1 RENAME a TO a2; -- fails in v3
2021-05-31
14:09 Reply: Question on internals: Sync between WAL and B Tree (artifact: f0766b01ab user: drh)

Processes can see transactions that have been written to the WAL file but not into the database by reading the corresponding pages out of the WAL file instead of the database. This could also be done if the WAL file is record-oriented, but it would involve reading both the database page and changes in the WAL file and merging them together. That is a lot more complex and potentially slower.

12:52 Reply: Nothing for several days from the forum (artifact: 70b4ae6d9a user: drh)

I have made contact with the people at Gmail who tell me that they use the first 64 bits of the 128-bit IPv6 address, not the first 32-bits as reported above. Nevertheless, they do recommend that for projects that send email to Gmail from a VPS (such as a Linode, on which SQLite.org has run since 2004) should send email over IPv4, not IPv6. I thought I had done that. But apparently I didn't change the Postfix setting correctly. The correct fix is to run:

postconf -e inet_protocol=ipv4

That has now been done and the mail logs do seem to show that outbound email is now always going over IPv4. For reference, to change this back, you set the inet_protocol value to "all".

Hopefully this will resolve any lingering mail delivery problems. If you continue to have trouble, please post follow-ups.

2021-05-30
17:50 Reply: Unexpected results with IS NOT NULL (artifact: 67b7379424 user: drh)

Yes, the CREATE INDEX works in the sense that it always creates an index. But since the condition "V2 > NULL"is always NULL, no rows are ever inserted into the index, and so the index is utterly useless.

More ↓