SQLite

The following SQL code generates an incorrect answer of three rows instead of two:

<blockquote><verbatim>
CREATE TABLE tx(id INTEGER PRIMARY KEY, a, b);
INSERT INTO tx(a,b) VALUES(33,456);
INSERT INTO tx(a,b) VALUES(33,789);

SELECT DISTINCT t0.id, t0.a, t0.b
  FROM tx AS t0, tx AS t1
 WHERE t0.a=t1.a AND t1.a=33 AND t0.b=456
UNION
SELECT DISTINCT t0.id, t0.a, t0.b
  FROM tx AS t0, tx AS t1
 WHERE t0.a=t1.a AND t1.a=33 AND t0.b=789
 ORDER BY 1;
</verbatim></blockquote>

Prior to and including check-in [93121d3097a43997] (version 3.8.3, 2013-12-17) 
the correct answer was obtained.  After that, the test script above generates
an assertion fault.  At check-in [c0fa0c0e2de50d7e] (version 3.8.4, 2014-03-03)
the assertion faults go away, but the incorrect answer is returned.

The problem was two co-routines using the same temporary registers and
expecting those temporary registers to be preserved across a Yield.  The
solution is to ensure that every co-routine has its own set of temporary
registers that are not shared with the main thread or other co-routines.

In the demonstration query shown in the problem description, each SELECT
statement is evaluated by a separate co-routine that includes an ORDER BY.
The main thread handles the UNION by running each co-routine long enough
to generate a single row, then choosing the smaller of the two
rows to output and running the corresponding co-routine to generate the
next row for that SELECT.  Each co-routine evaluates its query as a nested
loop with t0 being the outer loop and t1 being the inner loop.  The t0.a
and t0.b values are stored in temporary registers in the outer loop, and
those value are expected to remain intact even though a Yield occurs after
each iteration of the inner loop.  Prior to the fix of
check-in [ca72be8618e5d466] both co-routines were using the same temporary
registers for their t0.a and t0.b values, which means that running one
co-routine would disrupt the other.  After check-in [ca72be8618e5d466] the
two co-routines use different temporary registers for holding t0.a and
t0.b so they no longer interfere with one another.