SQLite

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

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;

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.