From the evidence you have shown and mentioned, it is premature to conclude or declare that the crash is due to "a SQLite bug". There certainly won't be a workaround for an access fault occurring when a data structure used successfully in numerous other applications goes wrong because the pointer being dereferenced has been clobbered by as-yet mysterious previous code.

Can you run the crash-inducing scenario under a debugger?

This is a problem that probably reflects operation of a bug somewhere, but until it can be determined when, and by what, the pointer value picked up by "pLeft = pExpr->pLeft" has been made invalid, reliably avoiding it by query selection or adjustment is going to be difficult and prone to failure. Discovering what code improperly rewrote that value is a relatively simple task when a debugger can be used and the crash scenario and resulting crash are repeatable.

You need to abandon the idea of "potentially dangerous queries". They are all dangerous when something is trashing random memory locations.

Later amendment:

After sifting through the OP's interactions in another forum on this problem, I was able to find the DB and query that exhibits the fault. That DB is where the schema in my below post came from. The crash requires no data as the failure occurs during the prepare.

Given that the problem reproduces with the released sqlite3.exe shell (v3.31.1), and some known SQL, I now think it can be fairly called a SQLite bug.

My advice, about abandoning the "dangerous queries" idea, stands.

(To OP: This is repro for the bug that demonstrates the problem with v3.31.0 and not with v3.32.0 (nearing release.))

Create a file, aeroxr_crash_schema.sql, reading:

CREATE TABLE [Quote] (_id INTEGER PRIMARY KEY,json TEXT);
CREATE TABLE [a_customerbase] (_id INTEGER PRIMARY KEY,json TEXT);
CREATE INDEX Quote__entity_customer_customerId_index ON Quote (CAST (json_extract([json], '$.entity.customer.customerId') AS TEXT));
CREATE UNIQUE INDEX Quote__localId_index ON [Quote](CAST(json_extract(json,'$.localId') AS TEXT));
CREATE INDEX Quote__localLinks_entity_customer_customerId_index ON Quote (CAST (json_extract([json], '$.localLinks."entity.customer.customerId"') AS TEXT));
CREATE INDEX Quote__remoteId_index ON [Quote](CAST(json_extract(json,'$.remoteId') AS TEXT));
CREATE UNIQUE INDEX a_customerbase__localId_index ON [a_customerbase](CAST(json_extract(json,'$.localId') AS TEXT));
CREATE INDEX a_customerbase__remoteId_index ON [a_customerbase](CAST(json_extract(json,'$.remoteId') AS TEXT));

Create a file, aeroxr_crash_query.sql, reading:

SELECT      CAST(json_extract([a_customerbase].[json], '$.remoteId') AS TEXT) AS remoteid,
CAST (json_extract([Quote].[json], '$.entity.customer.customerId') AS TEXT) as customerId,
CAST (json_extract([a_customerbase].[json], '$.localId') AS TEXT) as custmerLocalId,
CAST (json_extract([Quote].[json], '$.localLinks."entity.customer.customerId"') AS TEXT) as localLinksCustomerID
FROM      [a_customerbase]
LEFT JOIN Quote
ON        (( CAST (json_extract([a_customerbase].[json], '$.remoteId') AS TEXT) IS NOT NULL
AND CAST (json_extract([a_customerbase].[json], '$.remoteId') AS TEXT) = CAST (json_extract([Quote].[json], '$.entity.customer.customerId') AS TEXT))
OR
CAST (json_extract([a_customerbase].[json], '$.localId') AS TEXT) = CAST (json_extract([Quote].[json], '$.localLinks."entity.customer.customerId"') AS TEXT))

WHERE     CAST (json_extract([a_customerbase].[json], '$.remoteId') AS TEXT) in (4832);

In the shell, enter commands:

.read aeroxr_crash_schema.sql
.read aeroxr_crash_query.sql

In the v3.32.0 pre-release of a few days ago, (SQLite 3.32.0 2020-04-23 20:45:46 65c6c26bb48d5347ce53bb3607de3a03a5a03946b232d35e46a20533f867alt2), the above schema and query does not cause a crash on Windows 10.

In the v3.31.1 release of 3 months ago, (SQLite 3.31.1 2020-01-27 19:55:54 3bfa9cc97da10598521b342961df8f5f68c7388fa117345eeb516eaa837bb4d6), they produce some kind of address fault on Windows 10.

Full disclosure: Testing of v3.32.0 was with a modified shell. I doubt that has anything to do with this problem, as the library code is unchanged except for applying the change of the last trunk check-in yesterday, b73d9a7d6f .

Amendment 1:

Testing some previous versions, v3.25.0 and v3.26.0 do not crash with above. v3.27.0 and higher thru v3.31.1 do crash with it.

Thanks :) Should I open a ticket even if this issue seems to be already resolved in the next release?

You're welcome. It was not clear to me from the crash evidence that the bug has been resolved. Depending on why an access fault occurs, the defect that leads to an invalid pointer may still exist even if it is not dereferenced. In general, with C/C++ code, absence of access faults does not imply absence of bugs. Invalid pointers can accidentally refer to mapped memory and be dereferenced without the memory management hardware detecting an invalid access. Likewise, access faults do not, by themselves, imply a bug in the code that was executing when the fault occurs. This is especially true with libraries which run in the same process as other code which may or may not be well behaved. However, as shown below, there is reason to believe this bug has been fixed.

Somewhere in the SQLite docs, this forum is said to be the place to report bugs. That is more or less done with your first post combined with mine demonstrating that 3rd party code cannot be responsible for the fault. You might edit your first post to change the subject to contain the word "bug". As I next show, "bug, resolved" would be better yet IMO.

Interestingly, the code shown in your first post cannot be found in the nearing-release version of SQLite, at least not exactly. The crashing code is:

      if( (pExpr->pLeft->op==TK_COLUMN && IsVirtual(pExpr->pLeft->y.pTab))
       || (pExpr->pRight->op==TK_COLUMN && IsVirtual(pExpr->pRight->y.pTab))
      ){
       return WRC_Prune;
      }
    default:
      return WRC_Continue;

whereas today's version of the code handling the same case in the same optimization function is:

      /* The y.pTab=0 assignment in wherecode.c always happens after the
      ** impliesNotNullRow() test */
      if( (pLeft->op==TK_COLUMN && ALWAYS(pLeft->y.pTab!=0)
                               && IsVirtual(pLeft->y.pTab))
       || (pRight->op==TK_COLUMN && ALWAYS(pRight->y.pTab!=0)
                               && IsVirtual(pRight->y.pTab))
      ){
        return WRC_Prune;
      }

This leads me to believe that, in fact, the bug is resolved in v3.32-to-be.

Thanks a lot ! :) You are very clear ! I'm going to edit the title.