SQLite Forum

Timeline
Login

50 forum posts by user drh occurring on or before 2021-08-13 12:24:34.

More ↑
2021-08-13
12:24 Reply: Test ACID properties SQLITE (artifact: 6354094425 user: drh)

How did you get your estimate? Other researches tell me that every phone has several hundred databases.

11:37 Reply: Sqlite connections isolated even after COMMIT (artifact: 8b24a48d44 user: drh)

Probably the reader is in a transaction that started before the writer transaction, so that the reader continues to see the state of the database before the new content was written. Fix this by committing the reader transaction and starting a new one.

The transaction may come about because you have a prepared statement that is not finalized.

2021-08-12
13:54 Edit: SQLite irrespective query (artifact: 2acbfdfadb user: drh)
I am trying to retrieve the data from the table shoppers. I have to retrieve the shoppers who joined the company after 01-01-2020, and all woman irrespective of when they joined. how can I create the query to meet this 2 conditions .

SELECT shopper_first_name, shopper_surname, shopper_email_address,IFNULL(gender, 'Not known' ) AS 'Gender',STRFTIME('%d-%m-%Y', date_joined) AS 'date_joined', STRFTIME('%Y',date('now'))- STRFTIME('%Y',date(date_of_birth)) AS 'Age'
FROM shoppers
HAVING gender = 'M'
AND date_joined >= '2020-01-01' 
ORDER BY gender,Age DESC; 

In works for all shoppers but I don't know how to do woman irrespective.

Thank You in advance
2021-08-11
13:09 Reply: inner join bug with compound queries (artifact: 2417eac1f3 user: drh)

Thanks for the report and for simplifying the test case. Here is an even simpler test case:

WITH
  t1(x) AS (SELECT 111),
  t2(y) AS (SELECT 222),
  t3(z) AS (SELECT * FROM t2 WHERE false UNION ALL SELECT * FROM t2)
SELECT * FROM t1, t3;

I think I understand the malfunction. I should have a fix later today. If you are just curious about what is going wrong, you can keep reading.

Processing begins by expanding the various subqueries defined by the WITH clause. We have:

SELECT *
  FROM (SELECT 111) AS t1,
       (SELECT * FROM (SELECT 222) WHERE false UNION ALL SELECT * FROM (SELECT 222)) AS t2;

The query optimizer then brings the UNION ALL operator to the top-level as follows:

SELECT *
  FROM (SELECT 111) AS t1, (SELECT * FROM (SELECT 222)) AS t2 WHERE false
UNION ALL
SELECT *
  FROM (SELECT 111) AS t1, (SELECT * FROM (SELECT 222)) AS t2;

Next the push down optimization is applied to the left-hand side of the UNION ALL operator to give us:

SELECT *
  FROM (SELECT 111 WHERE false) AS t1, (SELECT * FROM (SELECT 222) WHERE false) AS t2 WHERE false
UNION ALL
SELECT *
  FROM (SELECT 111) AS t1, (SELECT * FROM (SELECT 222)) AS t2;

So far, everything is copacetic. The optimized query is still equivalent to the original. But here comes the problem: While SQLite is generated the byte-code for the left-hand side of the UNION ALL, it sees the "T1" subquery and it notices that T1 will be reused when evaluating the right-hand side of UNION ALL so it saves the result of the first T1 and tries to reuse it for the second T1. SQLite fails to notice that the first T1 was modified by the push-down optimization and is hence no longer equivalent to the second T1. Thus the query that ends up being evaluated is:

SELECT *
  FROM (SELECT 111 WHERE false) AS t1, (SELECT * FROM (SELECT 222) WHERE false) AS t2 WHERE false
UNION ALL
SELECT *
  FROM (SELECT 111 WHERE false) AS t1, (SELECT * FROM (SELECT 222)) AS t2;

The extra "WHERE false" (shown in red) that gets inserted into the second T1 is the cause of the incorrect result.

2021-08-10
13:34 Edit: Handling of json keys containing double quotes (artifact: 96abde02a0 user: drh)

Hi, I was using SQLite JSON extension with data that has special characters as part of keys (Could be '*','.',' " ' etc). One of the limitations I saw was the handling of double quotes in JSON key when performing path traversal. The issue stems from the fact that SQLite parser doesn't allow escaping of double quotes in the JSON parser. When encountering a dot`, if the next character is a double quote, the parser reads up to the next double quote and uses the enclosed text as the name. Otherwise, it reads up to the next . or [ (Or end of string)

SO post with some more details: https://stackoverflow.com/questions/67993982/sqlite-with-special-character-in-the-json-path

Is there any plan to allow for escaped character handling in this path? Reading the code, I couldn't identify any workarounds we could do.

2021-08-07
20:56 Reply: Old Nabble SQLite mailing list site down? (artifact: 04c0216524 user: drh)

One of the arguments in favor of switching from a traditional mailing list to this forum (a switch that received considerable push-back) is that historical conversations are preserved, in a searchable format, and can be easily replicated using "fossil clone".

This is one of the advantages of Fossil over Git. You have the ability to keep historic conversations about the code together with the code and in an easy to use and easy to preserve format. Yes, I now I keep this forum in a separate repository from the SQLite source code. But I could combine them. Maybe I will someday. And certainly many of the smaller projects I manage do keep the forum and source code and tickets and all the rest together in the same repository so that by cloning the repository, you get the full historical context of the project.

One commentator recently observed that Git really is "distributed", but not in a good way: "You go here for version control, and this other place for wiki, and over at this third place for the mailing list, and ...."

2021-08-06
22:29 Reply: Bug: unexpected result from an empty table (artifact: e4def599a5 user: drh)

The "t1.c1" value is a "bare column". Among SQL database engines, SQLite is the only one that allows bare columns in aggregate queries. It has always been implied, but never explicitly stated, that the value of a bare column on an aggregate query that has no input rows is arbitrary. The fix for this problem is to update the documentation to actually say that, which I have down here.

Think about it. The value of a bare column will be one of the values that that column takes on for the duration of the aggregate. But if the aggregate has no input rows, what value does it take?

Usually the value of a bare column in an aggregate with no inputs will be NULL. But in this specific example, the query optimizer recognizes that the value of "t1.c1" will always be the constant "1", so it just hard-codes a "1" in that spot.

15:27 Reply: The amalgamation has repeated files? (artifact: 0e2e2a8264 user: drh)

The surplus copies of "os_common.h" and "hwtime.h" are harmless. Nevertheless, the duplication should now be fixed on trunk

14:31 Reply: Bug: inconsistent result when an optimization is on and off (artifact: 01d879f18b user: drh)

I believe that the documentation at https://sqlite.org/datatype3.html#affcompoundview applies in this case. In other words, this is the same (non-)issue that you reported previously at forum post 02d7be94d7.

You have a compound sub-query in which the output column sometimes has TEXT affinity and other times has INTEGER affinity. So the affinity of the C2 column in the overall sub-query is indeterminate. According to the documentation:

Different constituent SELECT statements might be used to determine affinity at different times during query evaluation. The choice might vary across different versions of SQLite. The choice might change between one query and the next in the same version of SQLite. The choice might be different at different times within the same query. Hence, you can never be sure what affinity will be used for columns of a compound SELECT that have different affinities in the constituent subqueries.

Best practice is to avoid mixing affinities in a compound SELECT if you care about the datatype of the result. Mixing affinities in a compound SELECT can lead to surprising and unintuitive results.

12:05 Reply: SQLITE_NOTICE(283): recovered 2 frames from WAL file /home/hwaci/fossil/sqlite.fossil-wal (artifact: 3e8f912d7c user: drh)

That was just a warning to me that I need to do some site maintenance, which has now been accomplished, so you shouldn't see that message any more.

2021-07-30
20:11 Reply: Bug: `GENERATED ALWAYS` constraint ends up in column type (artifact: 82e5f7eb2e user: drh)
2021-07-29
13:44 Reply: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: f2096cd579 user: drh)

What should i add

Working code that we can compile and run to see the problem. The code fragment you supplied in your original post appears to be incomplete. In other words, send in a reproducible test case.

2021-07-27
12:53 Reply: Bug in sqlite3PagerSharedLock? (artifact: 03faa0b7de user: drh)

Because dbFileVers is an array, "dbFileVers" (the variable name without a subsequent [..]) and "&dbFileVers" (the variable name preceded by "&") mean the same thing in C. So this is not a bug.

2021-07-26
02:21 Reply: sqlite3_carray_bind and constness (artifact: 5b9d77a2cd user: drh)

Pointers to functions and pointers to objects can be different

They can be, but are they on any platform in common use today, or in the previous 20 years?

2021-07-23
23:54 Reply: ALTER TABLE DROP COLUMN corrupts data (artifact: ec86256907 user: drh)

I just failed to close the ticket.

23:17 Reply: ALTER TABLE DROP COLUMN corrupts data (artifact: ac6086ecde user: drh)

The problem was resolved by check-in 354a4db5cb769c6a (2021-04-18). The fix appears in release 3.36.0.

2021-07-22
23:10 Edit reply: problem report: incorrect join behavior (artifact: f8786ea8e1 user: drh)

A bisect shows that the error was introduced here (2021-02-26).

Here is the cross-platform test case:

CREATE TABLE onerow(x INT);
INSERT INTO onerow(x) VALUES(0);
SELECT * 
FROM (
  SELECT null AS aaa FROM onerow
  UNION ALL
  SELECT 'missing' AS aaa FROM onerow
) AS a
LEFT JOIN (SELECT 1 AS bbb) AS b ON a.aaa IS NULL;
22:27 Edit reply: How can I calculate trip duration? (artifact: 572f99b3e9 user: drh)

SELECT (julianday(ended_at)-julianday(started_at)) as duration FROM ...

The duration computed this way will be in days. Multiply by 24.0 to get hours, or 86400.0 to get seconds, and so forth. Example:

SELECT (julianday('now') - julianday('2000-05-29 14:16')) AS duration;
┌──────────────────┐
│     duration     │
├──────────────────┤
│ 7724.33961798623 │
└──────────────────┘
SELECT (julianday('now') - julianday('2000-05-29 14:16'))*86400.0 AS 'duration in seconds';
┌─────────────────────┐
│ duration in seconds │
├─────────────────────┤
│ 667383060.794015    │
└─────────────────────┘

The SQLite project has been going for a little over 7724 days, or about 667 million seconds.

22:22 Reply: How can I calculate trip duration? (artifact: 7656460e6b user: drh)

SELECT (julianday(ended_at)-julianday(started_at)) as duration FROM ...

The duration computed this way will be in days. Divide by 24.0 to get hours, or 86400.0 to get seconds, and so forth.

16:23 Reply: problem report: incorrect join behavior (artifact: 74fe3e169e user: drh)

This problem originated in our efforts to resolve a performance issue described by an earlier forum post from Jinho Jung. I think the problem has now been resolved by check-in 1f6796044008e6f3, though testing is still on-going.

14:55 Reply: problem report: incorrect join behavior (artifact: 2078b7edd2 user: drh)

Another simplification:

CREATE TABLE t1(a INT);
INSERT INTO t1(a) VALUES(1);
CREATE TABLE t2(b INT);
SELECT * FROM (SELECT 3 AS c FROM t1) AS t3 LEFT JOIN t2 ON c IS NULL;

Should give one row of output, but it fact gives zero rows.

14:14 Reply: problem report: incorrect join behavior (artifact: 33c9cd5abf user: drh)

Simplified test case that omits the UNION ALL:

CREATE TABLE onerow(x INT);
INSERT INTO onerow(x) VALUES(0);
SELECT * 
FROM (SELECT 'missing' AS aaa FROM onerow) AS a
LEFT JOIN (SELECT 1 AS bbb) AS b ON a.aaa IS NULL;

The above should return one row of output, and it does in PostgreSQL, MySQL, SQL Server, and SQLite version 3.34.0 and earlier. But in 3.35.0 through the latest check-in, it returns zero rows.

13:41 Reply: problem report: incorrect join behavior (artifact: 9c3268b134 user: drh)

A bisect shows that the error was introduced here (2021-02-26).

Here is the cross-platform test case:

CREATE TABLE onerow(x INT);
INSERT INTO onerow(x) VALUES(0);
SELECT * 
FROM (
  SELECT null AS aaa FROM onerow
  UNION ALL
  SELECT 'missing' AS aaa FROM onerow
) AS a
LEFT JOIN (SELECT 1 AS bbb) AS b ON a.aaa IS NULL;
2021-07-20
18:36 Edit reply: SDS.SEE.xml (artifact: 34916a6ab2 user: drh)

Joe sent you an answer directly to your email, bypassing this forum. Please check your spam folder. Use your SEE credentials to login at https://sqlite.org/see/login and use the Forum there if you need more help.

18:35 Reply: SDS.SEE.xml (artifact: debaaed29c user: drh)

Joe sent you an answer directly to your email, bypassing this forum. Please check your spam folder. User your SEE credentials to login at https://sqlite.org/see/login and use the Forum there if you need more help.

16:18 Reply: Unexpected return from the NULL column (artifact: f42238afcd user: drh)

Check-in e3794997c34f03db enhances the ALTER TABLE ADD COLUMN command so that (if necessary) it will run the equivalent of "PRAGMA quick_check" on the modified table after adding the new column in order to ensure that any NOT NULL or CHECK constraints added as part of the new column are satisfied by existing rows of the table. If any constraints are violated, the ADD COLUMN is aborted and the changes are rolled back.

The existing documentation for ALTER TABLE ADD COLUMN says that when adding a CHECK constraint....

"the CHECK constraint is not tested against preexisting rows of the table. This can result in a table that contains data that is in violation of the CHECK constraint. Future versions of SQLite might change to validate CHECK constraints as they are added."

The "future versions of SQLite" mentioned in this remark means check-in e3794997c34f03db and later. And this applies to NOT NULL constraints on generated columns as well. The documentation will be updated for the next release (3.37.0).

14:39 Reply: ROWID in compound SELECT (artifact: 250fe78dbc user: drh)

was this change in alias behaviour of ROWID made deliberately?

Yes it was. Item #3 on the release notes for version 3.36.0 and/or check-in a2ddb89b206c1387.

Subqueries and views do not have ROWIDs. Referencing the ROWID of a subquery or view would sometimes work, and would at other times give you a NULL or an arbitrary integer, depending on the chosen query plan. This was a bug in that SQLite was not raising an error when it should have. The change to disallow ROWIDs for subqueries and views is considered a bug fix.

I anticipated that there might be legacy applications that depend on the old buggy behavior. For that reason there is a compile-time option -DSQLITE_ALLOW_ROWID_IN_VIEW that disables the bug fix and restores the legacy behavior. In as much as the legacy ROWIDs would sometimes work and sometimes not, though, a better solution is to fix your application.

01:12 Reply: SDS.SEE.xml (artifact: ae08174c6b user: drh)

Question answers via private channels

01:07 Reply: sqlite3_deserialize double memory freeing (artifact: 3dbd10abf7 user: drh)

The sqlite3_deserialize() interface does not work for TEMP. That is not intentional. It should work. But TEMP is special and requires some special handling. And, as it turns out, I neglected to include any test cases that deserialize TEMP in any of the SQLite test suites. So it probably has never worked. I suspect that you are the first person to have tried to deserialized into TEMP.

2021-07-19
20:54 Reply: Different comparison results in WHERE and SELECT clauses (artifact: 6075b3f357 user: drh)
17:39 Reply: Is pragma integrity_check safe during app working? (artifact: 50372c92a7 user: drh)

In rollback mode, if you try to write while PRAGMA integrity_check is running, you will get an SQLITE_BUSY error. To avoid this error, you can:

  • Run in WAL mode

  • Set "PRAGMA busy_timeout=N" to cause the writes to wait for N milliseconds before giving up and returning SQLITE_BUSY.

17:05 Reply: Programmatically getting information about the 'where' clause (artifact: d4b73f1cbf user: drh)

Is there any API for obtaining information about the constraints in a query?

No there is not. Nor do we plan to add one as such an API would put constraints on the implementation and limit future design flexibility. If you want to know the constraints, you'll have to parse the SQL for yourself.

16:10 Reply: Is pragma integrity_check safe during app working? (artifact: 7cda26315a user: drh)

The PRAGMA integrity_check takes a read transaction. This might interfere with simultaneous write operations if you are not in WAL mode. Other than that, there shouldn't be any issues.

2021-07-17
14:47 Reply: SQLITE_WITHIN macro with one compare operator (artifact: 00f7d00418 user: drh)

Cachegrind tells me that your rewrites is actually a little slower, on the standard SQLite performance test benchmark. I get 1,035,083,032 CPU cycles with the existing SQLITE_WITHIN and 1,035,107,651 CPU cycles with your rewrite. Your rewrite also makes the executable 45 bytes larger.

What performance measurement tool is telling you that the rewrite is faster? The numbers above are using cachegrind on a x64 with a binary generated using gcc 5.4.0 and -Os.

2021-07-16
22:51 Reply: Should SQLite be able to optimize this query? (artifact: 878ca7a9be user: drh)

This optimization is now on trunk. The following description is intended to serve as archival documentation.

If a subquery in the FROM clause has an ORDER BY, that ORDER BY is omitted if all of the following conditions are also true:

  1. There is no LIMIT clause in the subquery
  2. The subquery was not one of the virtual subqueries added internally by SQLite for window-function processing
  3. The subquery is not part of the FROM clause in an UPDATE-FROM statement
  4. The outer query does not use any aggregate functions other than the built-in count(), min(), and/or max() functions.
  5. Either the outer query has its own ORDER BY clause or else the subquery is one term of a join.
  6. The omit-ORDER-BY optimization is not disabled by the SQLITE_TESTCTRL_OPTIMIZATION test-control.

The omit-ORDER-BY optimization can be disabled (item 6 above) using the following command in the CLI:

 .testctrl optimizations 0x40000
19:46 Edit reply: Should SQLite be able to optimize this query? (artifact: f6901a235d user: drh)

I now think that neither of these cases is a problem. (Edit: And Tom Lane concurs.)

SELECT sideeffectfunc(x) FROM (SELECT x FROM table ORDER BY x);

This case would not omit the ORDER BY clause because of the restriction described by post 062d576715d277c8 above.

SELECT y FROM (SELECT sideeffectfunc(x) AS y FROM table1 ORDER BY x), table2;

In this case, the order of invocations of sideeffectfunc() is arbitrary anyhow. SQLite has never made any guarantees about the order of evaluation in this case. The sideeffectfunc() calls might be in x order or not, depending on available indexes, table statistics, which version of SQLite is running, and so forth.

Notice that I added another table into the outer FROM clause to make it a join. Otherwise, the restriction described by post 062d576715d277c8 above would apply and the ORDER BY would be retained.

19:40 Edit reply: Should SQLite be able to optimize this query? (artifact: f9df43adb5 user: drh)

I don't see that that really helps. Because I still need to deal with (omit the optimization from) this case:

SELECT count(*) FROM (SELECT sideeffectfunc(x) FROM table ORDER BY x);

Edit: I now believe my reasoning was incorrect. See 4a76777e6d7aa126 above. Ignore this post.

19:38 Reply: Should SQLite be able to optimize this query? (artifact: 4a76777e6d user: drh)

I now think that neither of these cases is a problem.

SELECT sideeffectfunc(x) FROM (SELECT x FROM table ORDER BY x);

This case would not omit the ORDER BY clause because of the restriction described by post 062d576715d277c8 above.

SELECT y FROM (SELECT sideeffectfunc(x) AS y FROM table1 ORDER BY x), table2;

In this case, the order of invocations of sideeffectfunc() is arbitrary anyhow. SQLite has never made any guarantees about the order of evaluation in this case. The sideeffectfunc() calls might be in x order or not, depending on available indexes, table statistics, which version of SQLite is running, and so forth.

Notice that I added another table into the outer FROM clause to make it a join. Otherwise, the restriction described by post 062d576715d277c8 above would apply and the ORDER BY would be retained.

19:05 Reply: Should SQLite be able to optimize this query? (artifact: 6be1468b85 user: drh)

I don't see that that really helps. Because I still need to deal with (omit the optimization from) this case:

SELECT count(*) FROM (SELECT sideeffectfunc(x) FROM table ORDER BY x);
17:27 Reply: Custom error messages for required table-valued function parameter (artifact: c83977a7e6 user: drh)

The preferred technique for enforcing required arguments for table-valued functions is now documented in the version 3.37.0 draft documentation. There is nothing new in SQLite to provide this capability - only the documentation has been changed. So the technique described should work just as well in historical versions of SQLite as it does in (currently unreleased) 3.37.0.

See the current documentation draft for details.

Note that the loadable-extension that implements the generate_series() table-valued function has been updated to make its first parameter required, in order to illustrate the techniques described above. The changes to generate_series() are not in the current release and will appear with version 3.37.0.

15:05 Reply: Should SQLite be able to optimize this query? (artifact: 442c21d1d2 user: drh)

Suppose an application-defined SQL function "sideeffectfunc(x)" has some external side effects. Or suppose that function maintains some kind of internal state such that result of each call depends on previous calls. Then the following two queries might give surprising and undesirable results if the ORDER BY clause is omitted:

SELECT sideeffectfunc(x) FROM (SELECT x FROM table ORDER BY x);
SELECT y FROM (SELECT sideeffectfunc(x) AS y FROM table ORDER BY x);

HT to Tom Lane at Postgres for spotting this one. I have not yet adjusted the implementation to take this case into account.

01:22 Reply: Should SQLite be able to optimize this query? (artifact: e071e0991f user: drh)

This optimization is now available on the omit-subquery-order-by branch. Try it out if you dare. All the core SQLite tests pass now, but there is still no guarantee that this will ever land on trunk.

2021-07-15
23:29 Edit reply: Should SQLite be able to optimize this query? (artifact: 062d576715 user: drh)

Consider this SQL:

CREATE TABLE t1(a);
INSERT INTO t1 VALUES(4),(8),(3),(5),(1),(7),(2),(6);
CREATE VIEW v2 AS SELECT a FROM t1 ORDER BY a;
SELECT * FROM v2;

Would you expect the values to come out of v2 in sorted order because of the ORDER BY clause on the view? They do on all historical versions of SQLite. But if I omit the ORDER BY clauses from subqueries, that ORDER BY clause goes away. The original "SELECT * FROM v2" query is transformed into:

SELECT * FROM (SELECT a FROM t1 ORDER BY a);

The ORDER BY clause is removed from the subquery. Then the query flattener kicks in and simplifies the query to be just:

SELECT * FROM t1;

And the rows come out in some arbitrary order. I'm not sure what the SQL standards say about ORDER BY clauses on VIEWs, but the result here seems counter-intuitive to me.

My current fix for this is to only remove the ORDER BY clause on the subquery if either:

  1. The outer query has an ORDER BY clause of its own, or
  2. The outer query is a join - in other words there are FROM clause terms other than the subquery that contains the ORDER BY.
23:27 Reply: Should SQLite be able to optimize this query? (artifact: f7f6637918 user: drh)

Consider this SQL:

CREATE TABLE t1(a);
INSERT INTO t1 VALUES(4),(8),(3),(5),(1),(7),(2),(6);
CREATE VIEW v2 AS SELECT a FROM t1 ORDER BY a;
SELECT * FROM v2;

Would you expect the values to come out of v2 in sorted order because of the ORDER BY clause on the view? They do on all historical versions of SQLite. But if I omit the ORDER BY clauses from subqueries, that ORDER BY clause goes away. The original "SELECT * FROM v2" query is transformed into:

SELECT * FROM (SELECT a FROM t1 ORDER BY a);

The ORDER BY clause is removed from the subquery. Then the query flattener then kicks in and simplifies the query to be just:

SELECT * FROM t1;

And the rows come out in some arbitrary order. I'm not sure what the SQL standards say about ORDER BY clauses on VIEWs, but the result here seems counter-intuitive to me.

My current fix for this is to on remove the ORDER BY clause on the subquery if either:

  1. The outer query has an ORDER BY clause of its own, or
  2. The outer query is a join - in other words there are FROM clause terms other than the subquery that contains the ORDER BY.
19:34 Reply: Should SQLite be able to optimize this query? (artifact: 8c0a5f40fb user: drh)

I tried to work around this objection by only omitting the ORDER BY clause if either:

  1. The outer query is not an aggregate
  2. The outer query is an aggregate but it only uses the built-in count(), min(), or max() functions.

And yet there are still issues. See the ef97c3e7c3ea2cf1 check-in for the code so far.

18:06 Reply: Should SQLite be able to optimize this query? (artifact: 5f119f0dff user: drh)

I'm finding test cases like this:

SELECT group_concat(word,',')
  FROM (SELECT word FROM dictionary ORDER BY 1);

The idea here is that you want to feed the values into group_concat() in a specific order. The ORDER BY clause in the subquery is used to impose that order.

Now, I don't think the SQL language ever guarantees an order in this particular circumstance. So, technically, we should be free to ignore the ORDER BY clause in the subquery above. But I'm wondering how often this kind of thing occurs in the wild, and how many applications will break if I add an optimization to ignore (technically) useless ORDER BY clauses in subqueries?

If you had an application that has been working great for 10 years and then starts giving incorrect answers when you upgrade to SQLite 3.37.0, would you be pleased?

In order to move forward with this optimization, I need to be convinced that it will be a net positive. I need evidence that the benefit of increased performance will outweigh the cost of fixing applications that break because they were depending on undocumented behavior. I'm not yet convinced that the benefits exceed the inconvenience in this case, but I'm open to arguments to the contrary.

17:18 Reply: Should SQLite be able to optimize this query? (artifact: fffb9616bd user: drh)

Would it be possible for SQLite to notice that the inner order by is unnecessary and ignore it when executing this kind of query?

That seems simple, doesn't it. And yet, when I omit the ORDER BY clause from a subquery in the FROM clause that lacks a LIMIT and does not use window functions, I still get 1524 errors from "make test". It will take some time to analyze this and figure out what is going on.

16:25 Reply: Should SQLite be able to optimize this query? (artifact: 0fc6dcfcf7 user: drh)

Can you post the database schema, please?

11:03 Reply: Custom error messages for required table-valued function parameter (artifact: bce87eaf56 user: drh)

To enforce one or more required parameters for a table-valued function in the current code, I think you should have xBestIndex return SQLITE_OK with a very large estimatedCost if the parameters are missing, together with an idxNum and/or idxStr that indicates that the parameters are missing. Then, raise an error in xFilter if it ever sees an idxNum or idxStr that indicates missing parameters.

This is suboptimal in that it delays the error until run-time. The error might not even occur if a WHERE clause constraint prevents the table-valued function from ever being referenced. In a UNION ALL query, you might get several rows of output before the error is raised. Better solutions will be forthcoming in a future release.

2021-07-14
00:51 Reply: fortify source (artifact: b6c72bddd4 user: drh)

All of our tests run 64-bit, except when we are deliberately testing for compatibility with 32-bit hardware. We offer 32-bit downloads because people request them.

More ↓