SQLite Forum

Timeline
Login

50 most recent forum posts by user gunter_hick

2021-10-21
15:06 Reply: Inconsistency in BETWEEN Query (artifact: 0eaf895da7 user: gunter_hick)
You need to read up on data types is SQLite and the concept of "affinity".

See https://sqlite.org/datatype3.html#sorting_grouping_and_compound_selects

In section 4.2. Type Conversions Prior To Comparison, the second bullet is "If one operand has TEXT affinity and the other has no affinity, then TEXT affinity is applied to the other operand."

So you are performing a string (lexical) comparison of (duplicate digits removed for clarity) "40100" <= "4011" <= "40199", which is obviously true; because "4011" comes after any string beginning with "4010" and before any string beginning with "4019".
2021-10-15
10:24 Reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: c35c2625be user: gunter_hick)
This will probably calculate the number of days: (julianday(date_out) - julianday(date_back) + 1)

You need to use
 WHERE rowid = NEW.ROWID
to restrict changes to the affected row. 

 WHERE rental_cost IS NULL
will update ALL of the rows that have a NULL rental cost

The price in the subselect will be the one from the first Rental record visited in the subselect. I don't think that is what you intend. If you want the price from the affected row, just use NEW.price instead.
09:04 Edit reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: 1785d2aef0 user: gunter_hick)
Lots of misconceptions here.

x) SQLite trigger programs do not have variables. The result column price from the SELECT ist not available to the separate UPDATE statement. Use a subselect to retrieve the price that relates to the row being updated.

x) The UPDATE is going to affect every row of Rental that matches the WHERE clause, not just the NEW row. Use rowid = NEW.rowid instead

x) rental_cost = NULL is always FALSE; nothing is ever equal to NULL, not even NULL. Use IS NULL to check for NULL values.

x) I think you meant to compute (date_out - date_back), not subtract a date from a product of a price and a date.

Edit: Subtracting dates stored as TEXT will probably not yield the expected result.
09:01 Reply: Error Code : Error while executing SQL query on database ‘test’: no such column: price (artifact: b6c6137cdd user: gunter_hick)
Lots of misconceptions here.

x) SQLite trigger programs do not have variables. The result column price from the SELECT ist not available to the separate UPDATE statement. Use a subselect to retrieve the price that relates to the row being updated.

x) The UPDATE is going to affect every row of Rental that matches the WHERE clause, not just the NEW row. Use rowid = NEW.rowid instead

x) rental_cost = NULL is always FALSE; nothing is ever equal to NULL, not even NULL. Use IS NULL to check for NULL values.

x) I think you meant to compute (date_out - date_back), not subtract a date from a product of a price and a date.
2021-10-13
15:10 Reply: Data Types (artifact: 2aa9f14827 user: gunter_hick)
Maybe you are confusing values returned from interfaces with serial type codes used in the internal record format.
2021-10-08
11:44 Reply: Updating in-process connections on write (artifact: 16f6f9cace user: gunter_hick)
Sharing a connection between threads that use implicit transactions means that none of the threads can be sure when a transaction begins or ends. They share a common transaction that begins when any thread starts reading and ends when all threads are done with reading simultaneously. This is not transparent to the individual threads. E.g. if two threads alternate in reading, each starting a new SELECT before the other one ends it's SELECT, the transaction never commits, and thus never sees the result of a write transaction on the other connection.

Using a separate connection for each thread gives each thread control over it's own transaction. No transaction can see "intermediate" values if you properly BEGIN and END any write transactions.
2021-10-04
09:18 Reply: Is the lifetime of argv in sqlite3_module::xConnect greater than sqlite3_module::xFilter? (artifact: ba002147ae user: gunter_hick)
There are at least two possible sources for the module name passed to the xConnect method.

- it could be the argument passed to the sqlite3_create_module[_v2] function, in which case it's lifetime is determined by you, the caller

- it could be a copy of the above, stored in SQLite internal structures associated with the module, which would then probably be valid until the module is dropped

- it could be the argument given in the CREATE VIRTUAL TABLE statement, in which case it could reside in the input buffer, or inside the SQL program created to process the statement, or somewhere else convenient for SQLite; all of which  probably expire after your xConnect function returns
2021-09-30
11:20 Reply: How to filter find duplicate and show row with same field using sqlite code? (artifact: b9aad0d5dd user: gunter_hick)
What you are looking for is an "autojoin", i.e. joining a table to itself.

SELECT a.* from person a join person b on (a.name = b.name and a.person_no <> b.person_no);
07:33 Reply: Multithread processing, storing in memory and writing to database (artifact: c8a46bbb28 user: gunter_hick)
The heretic's approach:

- have each thread write INSERT INTO <table> VALUES (<values>); statements into a text file 
- when triggered (and/or periodically), each thread closes and renames the text file, then opens a new file
- main thread processes the renamed text files into the DB and renames them again (or deletes them if you don't require rollforward capability)

No pesky synchronization of databases, predictable effort storing the metrics, just one connection doing all the writes in a batch transaction.

Alternatively, instead of full statements, have the metric threads write csv files containing just the data and import those either directly in the shell or via a CSV table valued function.

INSERT INTO <table> SELECT * FROM CSV('<filename>');
2021-09-28
08:58 Reply: Views, Casting vs and converters (artifact: 44eaada7ec user: gunter_hick)
I think you are not properly separating SQL issues from Python issues.

For SQLite data types see https://sqlite.org/datatype3.html. The builtin types are NULL, INTEGER, REAL, TEXT and BLOB. It is not possible to define additional types in SQLite. Any type name you provide is mapped to one of the builtin types.

For SQL Syntax see https://sqlite.org/lang.html, https://sqlite.org/lang_select.html and https://sqlite.org/lang_expr.html.

The CAST SQL function attempts to convert whatever actual value is provided to a value of the builtin types the provided type-name is mapped to.

An integer value of 1 stored in column my_col with a declared type of INTEGER is stored as an integer value of 1.

my_col thus returns the integer value 1,
CAST(my_col as FLOAT) returns real value 1.0,
CAST(my_col as BOOLEAN) leaves the integer value 1 as the integer value 1

SQLite does not attach any meaning to the column name. It seems you are attempting to convey information to Python through this channel. IMHO this violates the layer model. SQLite is in the data layer; SQL Queries should focus on retrieval of the data. Your Python script handles the presentation layer, so it should already know how the data is returned, and not rely on abusing column names as data.

Python questions are better posed on a Python orieanted plattform.
2021-09-14
06:11 Reply: integer overflow in pager.c (artifact: 4c5e8c2fa9 user: gunter_hick)
I asked my C compiler (GCC 4.8.5/RH7/IA64) about this:

    int32_t              a = 65536;
    int32_t              b = 0x7ffffff;
    int64_t              p;

    p = a*b;

and got this

32 bit init:   c7 45 fc 00 00 01 00    movl   $0x10000,-0x4(%rbp)
32 bit init:   c7 45 f8 ff ff ff 07    movl   $0x7ffffff,-0x8(%rbp)
32 bit load:   8b 45 fc                mov    -0x4(%rbp),%eax
32 bit mult:   0f af 45 f8             imul   -0x8(%rbp),%eax
32->64 conv:   48 98                   cltq
64 bit stor:   48 89 45 f0             mov    %rax,-0x10(%rbp)

and then about this:

    int32_t              a = 65536;
    int32_t              b = 0x7ffffff;
    int64_t              p;

    p = (int64_t)a*b;

32 bit init:   c7 45 fc 00 00 01 00    movl   $0x10000,-0x4(%rbp)
32 bit init:   c7 45 f8 ff ff ff 07    movl   $0x7ffffff,-0x8(%rbp)
32 bit load:   8b 45 fc                mov    -0x4(%rbp),%eax
64 bit cast:   48 63 d0                movslq %eax,%rdx
32 bit load:   8b 45 f8                mov    -0x8(%rbp),%eax
32->64 conv:   48 98                   cltq
64 bit mult:   48 0f af c2             imul   %rdx,%rax
64 bit stor:   48 89 45 f0             mov    %rax,-0x10(%rbp)

So yes, there may be truncation without a cast. Machine code is 6 bytes longer and 64 bit multiplication seems to require both operands in registers.
2021-09-13
13:10 Reply: integer overflow in pager.c (artifact: 0a1b617041 user: gunter_hick)
I don't think so.

Both statements multiply two 32 bit integers yielding a 64 bit result. The values you claim as problematic both use 16 or 17 bits, which would overflow a 32bit result, but come nowhere near overflowing 64 bit integers.

i64 offset = (i64)pSavepoint->iSubRec*(4+pPager->pageSize);

i64 sz = (pPager->pageSize+4)*pRel->iSubRec;

Even multiplying the maximum pagesize (17 bits) by maxint (31 bits) only yields 48 bits of product, well within the 63 bits available for positive integers.
2021-09-10
06:40 Reply: Method to temporarily disable Not Null constraint? (artifact: a63d3daeea user: gunter_hick)
Since you have foreign keys disabled, would it not be possible to insert a non-NULL token value for the relational properties? After all, the second pass is going to fix them anyway. And you can check against a suitably selected token value before enabling constraint checking.
2021-09-09
09:00 Reply: How to insert duplicate rows? (artifact: 97d7f045e7 user: gunter_hick)
What you are referring to is a formatting quirk. The OP is building the CREATE TABLE statement by concatenating string literals on continuation lines. The double quotes do not appear in the statement itself.

"fragment"<whitespace><backslash><newline>
"fragment" ...
06:51 Reply: How to insert duplicate rows? (artifact: d76d0062ef user: gunter_hick)
Can you replicate the issue using the SQLite shell alone? If that is the case, then please post the exact SQL used.

If not, then there is probably something wrong in what you are not showing. Like which release of SQLite you are working with, the programming language used (maybe C or similar from the obvious expectation that white space separated literal strings are concatenated and the extraneous backslash line continuation characters?), and the actual code.
2021-09-06
12:26 Reply: Automatic indexing (idle question) (artifact: 02bba99d9b user: gunter_hick)
Idle answer: Apart from indices required to fulfil constraints (e.g. UNIQUE and FOREIGN KEY), the utility of an index is exposed by it being used in the queries run against the database. This is by principle available only in retrospect. AFAIK SQLite does consider creating an index to run just one statement fast, but only if the cost of creating such an index is expected to be  amortised within that single statement.

Even if the application programmer has a defined set of queries, there is no guarantee that SQLite will have seen all of said set; and it may well be that the one index never before used is justified by a need for a blindingly fast response to an "emergency query" required to "save the planet from an asteroid strike" (gross exaggeration), in which case Murphy ensures that SQLite will have deleted exactly this index just 5 minutes before it is needed.

And then there is still the issue of how much time one is prepared to sacrifice in each and every query for an as yet unknown benefit that may arise from an automatically added/dropped ondex.
2021-09-03
06:58 Reply: Virtual table ORDER BY and GT/LT (GE/LE) constraints expected behavior - full table scan when not needed? (artifact: bcdd9dfa97 user: gunter_hick)
There are two separate things going on here.

SQLite is asking about a constraint AND about an ordering.

I guess you are already correctly setting an index number and the orderByComsumed flag (the query plan should reflect this) so that your xFilter and xNext functions return rows in the correct order.

What you are observing is consistent with telling SQLite that you can NOT handle the constraint; SQLite codes a check and discards any returned rows that do not match. It will not stop until you tell it that the EOF has been reached (xEOF returns true).

You can speed things up by handling the constraint (field,>) yourself. You need to assign an argv index value to receive the value to compare the field against in the xFilter function and code to return EOF when the constraint is no longer met.

You should also change your estimated cost and estimated rows return values to reflect that you will be retrieving, on average, half as many rows for a greater/less than scan.

If you set the omit flag, SQLite will trust your VT table implementation and not code a constraint check itself.
2021-09-02
14:01 Reply: Why data is lost in SQLite database with WAL mode on when connection is not closed properly? (artifact: f77e1f9c55 user: gunter_hick)

As confessed in another thread, the OP is running two different versions of SQLite within the same "app" and is unable to discern if he is doing so within different threads or different processes.

I guess it is just interference from using two different SQLite libraries, possibly compounded by loss of POSIX locks if runnign within the same process.

13:53 Edit reply: Why WAL file (-wal) may disappear? (artifact: d73300eb0a user: gunter_hick)
As confessed in a different thread, the OP is running different versions of SQLite while accessing the same db file. And probably also in threads of the same process, but is unable to determine if this is so or not.
13:51 Reply: Why WAL file (-wal) may disappear? (artifact: 596160733a user: gunter_hick)
The OP is running different versions of SQLite while accessing the same db file. And probably also in threads of the same process, but is unable to determine if this is so or not.
2021-09-01
13:48 Reply: Lua db:close deletes WAL file (artifact: 6f803472eb user: gunter_hick)
Then you already have the answer you are looking for and can stop repeat-posting the same problem over and over again.

There is no problem with SQLite, only with the way you are abusing it. The book 1984 is a warning, not an instruction manual. The same goes for https://sqlite.org/howtocorrupt.html
10:22 Reply: Lua db:close deletes WAL file (artifact: 1f282de976 user: gunter_hick)
Are you sure your "app" and your "lua script" are actually running in truly separate processes, and not just separate threads?

Are you sure that your "app" and your "lua script" are calling the same sqlite library? Maybe one is hard linked and the other is using the "installed" library.
2021-08-30
06:17 Edit reply: Can an application-defined function be used to convert one row of data to multiple rows and insert them into another table? (artifact: 98819819db user: gunter_hick)
We are using virtual tables for similar purposes since release 3.7.14. The mechanism has been expanded to allow table valued functions by declaring hidden columns in the internal table declaration passed back to SQLite, e.g.

CREATE TABLE split (word TEXT, list TEXT hidden)

which allows one to 

SELECT word FROM split WHERE list = 'list of words, with punctuation';

and also JOIN to other tables
06:17 Reply: Can an application-defined function be used to convert one row of data to multiple rows and insert them into another table? (artifact: cd7cc9fe98 user: gunter_hick)
We are using virtual tables for similar purposes since release 3.7.14. The mechanism has been expanded to allow table valued functions by declaring hidden columns in the internal table declaration passed back to SQLite, e.g.

CREAT TABLE split (word TEXT, list TEXT hidden)

which allows one to 

SELECT word FROM split WHERE list = 'list of words, with punctuation';

and also JOIN to other tables
2021-08-18
07:05 Reply: SIGBUS in sqlite (artifact: 9c1225e45f user: gunter_hick)
This is exactly what one would expect if one is bypassing the SQLite library to access an SQLite db file directly. SQLite Linux drivers know about this and take care to only ever use one file descriptor, even for multiple connections, and resolve symbolic links to the canonical file name.
05:58 Reply: SIGBUS in sqlite (artifact: a65cd5e0a2 user: gunter_hick)

Not familiar with android.

On linux, the documented "broken POSIX advisory lock" scenario relates to events within a single process. If two file handles point to the same file, closing one will release all locks on the file held by the process, regardless of which file handle was used to obtain them and which thread issued the request.

Maybe your "processes" are really threads? Or maybe you are using a separate file handle to "check on the shm file" for debugging purposes in process A?

2021-08-16
14:30 Reply: Download .TAB file into SQLITE3 Table (artifact: 276fa9a058 user: gunter_hick)
This appears to be a limit imposed by the sender of the data, not related to anything in SQLite.
2021-08-12
11:21 Reply: Test ACID properties SQLITE (artifact: 0086fcd1e9 user: gunter_hick)

Impressive. That would be about 125 copies per person alive today. Or over 200 times the total COVID vaccine shots administered worldwide.

11:08 Reply: Increasing insert performance of an in-memory db (artifact: abb64f38e2 user: gunter_hick)
Be sure to keep TEXT and BLOB fields near the end of the row, in decreasing access frequency, and fields used in the indices near the begining of the row. This minimizes the amount of decoding, as accessing any fields located after a TEXT/BLOB with sizeable data will require access to overflow page(s).
11:03 Reply: Increasing insert performance of an in-memory db (artifact: 219fff0ae3 user: gunter_hick)

It may be faster to create indices (apart from the one corresponding to insert order) after populating the tables.

10:58 Reply: help! decimal field multiplication error? (artifact: 3827a278eb user: gunter_hick)
There is no DECIMAL type in SQLite, thus you are using float arithmetic operations to process "money" (which is never a good idea) instead of integers with appropriate rounding rules.
2021-08-11
10:18 Reply: Test ACID properties SQLITE (artifact: 8f494daa0b user: gunter_hick)
Check the definition and test the property behind each letter separately.

Atomic: Try making two changes and then either ROLLBACK or COMMIT. Look for not-ROLLBACKED and not-COMMITED changes. Check combinations of rows changed by 1 UPDATE, rows changed by 2 UPDATEs in the same table, different tables or different attached databases.

Consistency: Try inserting/updating rows that violate constraints (remember to enable foreign key constraints). What those are is up to the author of the schema.

Independance: Try performing two transactions on the same row/table/database/across multiple attached databases in independent threads/processes. Check that data reread after both transactions are complete conform to expecations. In SQLite you will have to set a busy timeout to avoid the second transaction failing with "database is locked". Try to start the transactions simultaneously, so the changes occur in both orders.

Durability: Try killing threads/processes/power while transactions are in all states of progress (before/after first/last change, during commit), check to see if all comitted transactions are still present after the journal/WAL file has been processed.
2021-08-03
09:32 Reply: null character sorts greater than 0x7f and less than 0x80 (artifact: 2fcca78e65 user: gunter_hick)
TEXT means you are supposed to store valid utf-whatever and SQLite will return exactly what you stored, up to and including the first NUL character (in the original meaning of a single byte with value zero). You can also request translation to some other utf encoding based on the premise that what you stored corresponds to the label you provided.

BLOB means you can store anything and SQLite will return exactly what you stored.

If you store something as BLOB and request a cast to TEXT, SQLite will return everything up to and including the first NUL character (see above), or, if there is no NUL character, append one.

You are storing something that is NOT VALID utf-8, but MUTF-8 - probably from Java - in a box labeled utf-8 and then complaining that it does not do what you expect.

If you insist on storing MUTF-8 then use a blob field, although I don't expect it will change the sort order - BLOB fields sort by the byte representation, so since (UTF8 for ASCII 0x7F) 0x7Fnn < 0xC080 < 0xC280 (UTF-8 for ASCII 0x80) the sort order will not change.

Solving the ordering problem will require writing and loading your own collation function.
2021-08-02
06:15 Reply: Feature Request: Allow REAL to serve as rowid by having the B-Tree interpret its byte signature as a 64bit INTEGER. (artifact: 774319c618 user: gunter_hick)
Being able to compare values is central to BTree. Or any other method of retrieving records by key.

Leaf nodes contain records in sorted order and are linked together in sort order.

Internal nodes contain key values in sort order, separeted by node references to the next level node that pertains to that key range.

An ordering function needs to be tri-valued (return exactly one of "less", "equal" and "greater") and transitive ("a <op> b" and "b <op> c" implies "a <op> c").

Trying to compare fp64 NaN bit patterns will not conform, and pretending that all NaN bit patterns are equal is going to disrupt operations considerably, because inserting a records with rowid of "NaN++" is going to overwrite the record with rowid NaN.
2021-07-30
19:49 Reply: AUTOINCREMENT by value (artifact: 4bc3c1241c user: gunter_hick)

If you are picky about the exact values of rowids, you should be assigning them yourself.

Also note that AUTOINCREMENT does not guarantee that successive rowids will be adjacent, only monotonously ascending; the increment of a rowid may be committed, even if the record to be inserted is rolled back for any reason, including a constraint violation.

19:41 Reply: Feature Request: Allow REAL to serve as rowid by having the B-Tree interpret its byte signature as a 64bit INTEGER. (artifact: 7ae29588f1 user: gunter_hick)
The sort order of 64 bit patterns is different if they are interpreted as 2s complement integers (int64) than if they are interpreteed as fp64 values.

E.g. 0.0 and -0.0 compare as equal in fp64, but not as int64. Then you have inf and -inf and a whole bunch of NaN that you cannot compare as fp64 at all, but have different values as int64.

So a function for comparing fp64 by using int64 comparisons would have to check for the special cases first, and a function generating rowids would have to avoid anything beginning with 0xFFF.
2021-07-19
07:01 Reply: Clear shared memory DB and backup problem (artifact: b581b6b536 user: gunter_hick)
"I assume that should be no memory increase because I backup again same data."

Your assumption is wrong. What you are seeing is called "fragmentation". This is characteristic of every storage scheme for variable size objects in spaces of fixed capacity, even for the stuff in your garage.

The space used will increase more and more slowly until a balance point is reached, where "deleted" space ist being re-used at the same rate as it is being created. This is charaterized by the increase of space usage declining over iterations. I.e. asymptotic space usage.

A true leak would manifest itself in a constant increase of space usage for each iteration. I.e. linear space usage
2021-07-16
05:58 Reply: Should SQLite be able to optimize this query? (artifact: 445f8007b4 user: gunter_hick)

"Ordinary tables (and probably also virtual tables) are always unordered".

Nope. Virtual tables can implement indexed access. That is what the xBestIndex method is for, which is required to set the "orderByConsumed" Flag if the order of rows is guaranteed to be the requested order.

2021-07-14
12:26 Reply: Help with a query (artifact: e5b60e7cf2 user: gunter_hick)
Possibly what you are looking for is "GROUP BY aDate", which would return one row for each distinct aDate and do the computation within each group of records.

Also, try "sum(column)/2 as c" if you want half of the groups' sum; the value of v is only known at the end of the group and would be hard to determine during processing of rows.
2021-07-09
08:48 Reply: Custom error messages for required table-valued function parameter (artifact: f3c176c621 user: gunter_hick)

AFAICT SQLite builds an array of all possible constraints based on the query and will then set the "usable" field to reflect those available for the specific query plan.

There is no specific order documented, so even if a heuristic can be applied, it may well become unusable due to a changes in the implementation. the xBestIndex function therefore cannot "wait for a better opportunity".

Consider:

SELECT .. FROM a JOIN b ON (a.x == b.x) WHERE a.x == 5;

The constraint array for table a will contain 2 identical constraints for equality on field x, one from the JOIN condition and one from the WHERE clause.

For table a on the LHS, one of them will not be usable (which one is determined by implemenation details); for table a on the RHS, BOTH will be usable and the BestIndex function cannot tell which is which.

For table b, there is only one entry, which is usable only for the RHS.

Assuming both virtual tables implement indices starting with field x, this will strongly favor table a on the LHS. (search X search vs. scan X search)

Consider the logically equivalent:

SELECT .. FROM a JOIN b ON (a.x == b.x) WHERE b.x == 5;

For the reasons stated above, this will strongly favor table b on the LHS.

SQLite currently does not recognize that a.x == b.x AND a.x == 5 implies b.x == 5, so the optimal comparison (search a X search b vs. search b X search a) is never made. It is up to the programmer to guess which plan is actually superior and formulate the constraints accordingly

In the case of multiple identical usable constraints, I am not sure if it is actually irrelevant which one of them is marked as suitable; nor if marking both/all of them can possibly lead to conflicting constraints in a three or more way join.

2021-07-08
06:35 Reply: Repeat values n times (artifact: b8a1c3b882 user: gunter_hick)

That now makes sense. Stating the business case "I want to print labels for the items of an order" woudl have provided the necessary context.

2021-07-07
09:10 Reply: Repeat values n times (artifact: 4c76418041 user: gunter_hick)

What makes you think you need repeated identical records in a table? What is the difference between individual repeated records?

2021-06-21
15:08 Reply: Connection Pool always empty? (artifact: ef586152fa user: gunter_hick)
Obviously you are using a wrapper and some kind of object oriented language to call the SQLite library. Maybe you could provide a little more information on what exactly you are using.
2021-06-18
06:22 Reply: How to ReadUncommitted for c# .net framework 4.5 (artifact: 8a3150c082 user: gunter_hick)
That is NOT what I suggested, and it obviously does not change what is going on.

To be more explicit: Be sure to COMMIT the CREATE TABLE statement on the first connection BEFORE attempting to open the second connection.
2021-06-17
14:29 Reply: How to ReadUncommitted for c# .net framework 4.5 (artifact: fe2090433d user: gunter_hick)
Try creating the table in a separate transaction. I have no idea what the wrapper you are using may be doing transactionwise if you have multiple statements in one command.
2021-06-14
14:17 Reply: xBestIndex constraints: discern between JOIN and regular constraints (artifact: 09893e4d3b user: gunter_hick)

A "result set limiting" constraint associates a field with a constant (or a variable), whereas a "join constraint" defines a relation between two fields of the tables being joined.

There are three (not including syntax variations) logically equivalent ways to express an equijoin between two tables limited to a certain value of the field used:

  1. T1.a == x AND T1.a == T2.a
  2. T2.a == x AND T1.a == T2.a
  3. T1.a == x AND T2.a == x

Form 1 favors T1 in the outer loop Form 2 favors T2 in the outer loop Form 3 is indifferent

Combining Form 1 with a cross join with T2 in the outer loop makes SQLite check T2.a == x inside the full table scan loop instead of asking for a partial key scan of the same condition.

11:12 Post: xBestIndex constraints: discern between JOIN and regular constraints (artifact: 57f4cb4d40 user: gunter_hick)
with SQLite 3.24.0 (sorry, no newer version in production here yet) I have two virtual tables corresponding to the following table definitions:

CREATE TABLE privilege (
module_def_no INTEGER,
user_person_id INTEGER,
module_action_no INTEGER,
read_access_allowed INTEGER,
write_access_allowed INTEGER,
UNIQUE (module_def_no, user_person_id, module action no);


CREATE TABLE assmnt(
module_def_no INTEGER,
state_def_no INTEGER,
module_action_no INTEGER,
state_transition_no INTEGER,
entity_id INTEGER,
UNIQUE (module_def_no, module_action_no,state_def_no,state_transition_no));


When preparing the following statement


SELECT ... FROM privilege p, assmnt a
<C1> WHERE a.state_def_no=13
<C2>  AND a.module_def_no=14076
<C3>  AND p.module_def_no=a.module_def_no
<C4>  AND p.user_person_id=1002
<C5>  AND p.module_action_no=a.module_action_no
<C6>  AND p.write_access_allowed = 1
<C7>  AND a.module_action_no NOT IN (12);


the BestIndex function are called thus (with answers shown):

Note: Constraint information is formatted "C" for constraint, field name, operation : argvIndex, "O" if Omit flags is set
Note: Return information is formatted "R" for result, index name, (index number), cost, rows, "Unique" if Unique flag is set

Call #1

with all four fields of privilege table available, i.e. RHS of the JOIN,
Bestindex responds with the fields of the index in index order and result is unique

2021-06-14 11:19:19.215: B  BestIndex for table privilege
2021-06-14 11:19:19.215: Constraints
2021-06-14 11:19:19.216: C  module_def_no                   EQ  : 1 O
2021-06-14 11:19:19.216: C  user_person_id                  EQ  : 2 O
2021-06-14 11:19:19.216: C  module_action_no                EQ  : 3 O
2021-06-14 11:19:19.216: C  write_access_allowed            EQ  : 0
2021-06-14 11:19:19.216: R  privilege_idx    (1) Cost   15.39 Rows 1 Unique

Call #2

with only the fields available to the LHS of the JOIN being usable,
BestIndex responds with a full table scan returning all rows

2021-06-14 11:19:19.216: B  BestIndex for table privilege
2021-06-14 11:19:19.216: Constraints
2021-06-14 11:19:19.216: C (module_def_no                   EQ) : 0
2021-06-14 11:19:19.216: C  user_person_id                  EQ  : 0
2021-06-14 11:19:19.216: C (module_action_no                EQ) : 0
2021-06-14 11:19:19.216: C  write_access_allowed            EQ  : 0
2021-06-14 11:19:19.216: R  privilege_idx    (1) Cost 43061.39 Rows 43046

Call #3

with all 5 fields available, i.e. the RHS of the JOIN,
BestIndex responds with a partial key scan using the first three index fields

     B  BestIndex for table assmnt
     Constraints
<C1> C  state_def_no                    EQ  : 3 O
<C2> C  module_def_no                   EQ  : 1 O
<C7> C  module_action_no                NE  : 0
<C5> C  module_action_no                EQ  : 2 O
<C3> C  module_def_no                   EQ  : 0
     R  assmnt_idx (1) Cost   14.34 Rows 5

Call #4

with only the fields available to the LHS of the JOIN being usable,
BestINdex responds with a partial key scan using the first field

2021-06-14 11:19:19.216: B  BestIndex for table assmnt
2021-06-14 11:19:19.216: Constraints
2021-06-14 11:19:19.216: C  state_def_no                    EQ  : 0
2021-06-14 11:19:19.216: C  module_def_no                   EQ  : 1 O
2021-06-14 11:19:19.216: C  module_action_no                NE  : 0
2021-06-14 11:19:19.216: C (module_action_no                EQ) : 0
2021-06-14 11:19:19.216: C (module_def_no                   EQ) : 0
2021-06-14 11:19:19.216: R  assmnt_idx (1) Cost  137.34 Rows 128

this resulta in the

QUERY PLAN
|--SCAN TABLE assmnt VIRTUAL TABLE INDEX 1:
`--SCAN TABLE privilege VIRTUAL TABLE INDEX 1:

How can the BestIndex function determine if a given constraint comes from a "result set limiting" constraint or from a "join constraint"?
In the case of module_action_no, looking at the operation (EQ vs. NE) is sufficient to ensure that the value passed to the Filter function comes from the JOIN.
In the case of module_def_no, the case is not so clear; indeed, if forced to use the "wrong" query plan, I can see that SQLite is checking p.module_def_no against the constant  (addr 37,10,11) and passing a copy of the constant to the Filter function (addr 12..17) instead of a copy of the field value. 

addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     35    0                    00  NULL
1     VOpen          0     0     0     vtab:DD205A0   00  NULL
2     VOpen          1     0     0     vtab:DD221F0   00  NULL
3     Integer        1     1     0                    00  NULL
4     Integer        0     2     0                    00  NULL
5     VFilter        0     34    1                    00  NULL
6       VColumn        0     1     3                    00  NULL
7       Ne             4     33    3     (BINARY)       53  NULL
8       VColumn        0     4     5                    00  NULL
9       Ne             6     33    5     (BINARY)       54  NULL
10      VColumn        0     0     7                    00  NULL
11      Ne             8     33    7     (BINARY)       53  NULL
12      Integer        14076  11    0                    00  NULL
13      VColumn        0     2     12                   00  NULL
14      Integer        13    13    0                    00  NULL
15      Integer        1     9     0                    00  NULL
16      Integer        3     10    0                    00  NULL
17      VFilter        1     33    9                   00  NULL
18        VColumn        1     0     14                   00  NULL
19        Ne             14    32    7     (BINARY)       53  NULL
20        VColumn        1     2     15                   00  NULL
21        Eq             16    32    15    (BINARY)       53  NULL
22        Concat         15    22    21                   00  NULL
23        Concat         22    21    17                   00  NULL
24        Copy           15    21    0                    00  NULL
25        Function0      0     21    18    hex(1)         01  NULL
26        VColumn        0     2     23                   00  NULL
27        Concat         23    22    21                   00  NULL
28        Concat         22    21    19                   00  NULL
29        Copy           23    21    0                    00  NULL
30        Function0      0     21    20    hex(1)         01  NULL
31        ResultRow      17    4     0                    00  NULL
32      VNext          1     18    0                    00  NULL
33    VNext          0     6     0                    00  NULL
34    Halt           0     0     0                    00  NULL
35    Integer        1002  4     0                    00  NULL
36    Integer        1     6     0                    00  NULL
37    Integer        14076  8     0                    00  NULL
38    Integer        12    16    0                    00  NULL
39    String8        0     22    0     *              00  NULL
40    Goto           0     1     0                    00  NULL
10:28 Reply: create table (artifact: 1c472938e8 user: gunter_hick)

Try forEIgn instead of forIEgn

2021-06-01
06:54 Reply: What am I doing wrong to get errors when trying to compile amalgamation file in Linux (artifact: 39470049fc user: gunter_hick)
You are missing the pthread and the dynamic load libraries required by SQLite in the link phase of the second command. Add -lpthread and -ldl
06:22 Reply: Error when disable SQLITE_SimplifyJoin (artifact: ff44af3331 user: gunter_hick)
SQLite has become picky about the terms in the ON clause of joins some time ago.

The fragment "t0 LEFT JOIN t2 ON t0.c0=t1.c1" does not actually constrain t2 at all, but references an as yet unknown table. I would consider this a semantic error and SQLite is quite right for telling you off on it.

I suspect with optimization turned on, the statement is interpreted as

t0 LEFT JOIN (t2 JOIN t1 ON t1.c0>t2.c0) ON t0.c0=t1.c1

which "shifts" the offending ON clause to a position where it can be checked
More ↓