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. The query in question invokes that optimization, causing cursor numbers to be misordered, resulting in incorrect byte-code, and ultimately the NULL pointer dereference.

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 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 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.

Plans did change.

The end solution (seen at check-in 74aec5dd1df95b56) was to refactor the logic that figures out which query in a cascade of nested and correlated queries a particular aggregate function belongs too. The new logic does not depend on subquery cursor numbers being greater than outer query cursor numbers. That constraint is no longer on the parse tree, and so no new assert() logic to check is was required.

Should be working now.

Great job done!

Compared to guaranteeing the order of query cursor numbers and subquery cursor numbers, this is indeed a bit more targeted and have potentially less code modification, although this is already quite a lot of work.

I 'm trying to fuzz with testcases similar to this one and no further problems occurred yet.

Thank you for your explanation and hard work.