SQLite Forum

Thanks for the bug report.

The crash is due to a NULL pointer dereference in the byte-code engine caused
by incorrect byte-code.  The incorrect byte-code results for a fault in the
code generator.

Each table or subquery in a complex SELECT statement is assigned a cursor
number.  The name resolution logic for aggregate functions depends
on the fact that cursor numbers for subqueries are always greater than 
cursor numbers in outer queries.  But that assumption was violated by a new
UNION ALL optimization that as [added on 2020-12-19][1].  The query in question
invokes that optimization, causing cursor numbers to be misordered, resulting
in incorrect byte-code, and ultimately the NULL pointer dereference.

[1]: https://sqlite.org/src/info/df1d6482f9e92daf

A simplified query is this:

> ~~~~
SELECT * FROM (
  SELECT 1 FROM rt0 AS q3
  UNION ALL
  SELECT 2 FROM rt0 AS q4
) LEFT JOIN (
  SELECT * 
    FROM (
           SELECT (SELECT sum(q2.c1) + rank() OVER () FROM rt0 AS q1) AS ca1
             FROM rt0 AS q2
         ) AS q5
   WHERE q5.ca1=0
);
~~~~

The [UNION ALL optimization][1] transforms this query into the following:

> ~~~~
SELECT 1, q7.* 
  FROM rt0 AS q3
  LEFT JOIN (
    SELECT * 
      FROM (
             SELECT (SELECT sum(q2.c1) + rank() OVER () FROM rt0 AS q1a) AS ca1
               FROM rt0 AS q2a
           ) AS q5a
     WHERE q5a.ca1=0
  ) AS q7
UNION ALL
SELECT 2, q8.*
  FROM rt0 AS q4
  LEFT JOIN (
    SELECT * 
      FROM (
             SELECT (SELECT sum(q2.c1) + rank() OVER () FROM rt0 AS q1b) AS ca1
               FROM rt0 AS q2b
           ) AS q5b
     WHERE q5b.ca1=0
  ) q8;
~~~~

If you enter the second optimized SQL directly, it works
(because all of the cursor numbers are well ordered).
But when the query optimizer makes that translation,
it ends up with the cursor number for q2b being less than the cursor
number for q5b.

Multiple things need to be fixed here:

  1.   The [UNION ALL optimization][1] needs to be fixed so that it yields
       a parse tree where the cursor
       numbers of all subquerys greater than than the cursor number
       of any outer query.

  2.   New assert() statements need to be added to check every parse tree
       transformation and assure that the cursor numbers are always
       well-ordered.

I'm working on these changes now.

Everything in this post is the result of a preliminary analysis
and is subject to change or correction in follow-up posts.