Set operation with ORDER BY / LIMIT on the first SELECT gives a different result from the second run #2605

A set operation whose first SELECT has its own ORDER BY / LIMIT gives the correct result on the first run only. Every later run of the same compiled statement ignores that ORDER BY / LIMIT. No error is raised.

This hits every call of the same SQL text with the statement cache on (the default), and every call of a statement from alasql.compile().

Reproduced on develop (v4.20.0):

alasql('CREATE TABLE e1 (a INT); INSERT INTO e1 VALUES (1),(2),(3),(4)');
alasql('CREATE TABLE e2 (a INT); INSERT INTO e2 VALUES (1),(5)');

const sql = '(SELECT a FROM e1 ORDER BY a DESC LIMIT 2) UNION ALL (SELECT a FROM e2 ORDER BY a DESC LIMIT 1)';
alasql(sql);  // [{a:4}, {a:3}, {a:5}]                    correct
alasql(sql);  // [{a:1}, {a:2}, {a:3}, {a:4}, {a:5}]      wrong: LIMIT 2 and ORDER BY of the first SELECT are lost
alasql(sql);  // [{a:1}, {a:2}, {a:3}, {a:4}, {a:5}]      wrong

const st = alasql.compile(sql);
st();         // [{a:4}, {a:3}, {a:5}]
st();         // [{a:1}, {a:2}, {a:3}, {a:4}, {a:5}]

alasql.options.cache = false;
alasql(sql);  // [{a:4}, {a:3}, {a:5}]                    correct on every run

ORDER BY without LIMIT is hit the same way: (SELECT a FROM e1 ORDER BY a DESC) UNION ALL (SELECT a FROM e2 ORDER BY a DESC) gives 4,3,2,1,5,1 on the first run and 1,2,3,4,5,1 after that.

Cause

queryfn3 in src/38query.js (added in #2417). When the right operand has its own ORDER BY / LIMIT, the ORDER BY / LIMIT of the first SELECT are applied before the set operation. To stop them from being applied again after it, the code clears them on the compiled query:

query.orderfn = null;
...
query.limit = null;
query.offset = null;

query is the compiled statement, not a per-run copy. The statement cache and alasql.compile() reuse it, so from the second run on there is no ORDER BY / LIMIT left to apply.

Direction

Do not change the compiled query at run time. Keep the "already applied" state in a local variable of the run (or decide it at compile time), and skip the final ORDER BY / LIMIT based on that.

Tests: run each query of test2414.js that uses this pattern twice, and expect the same result both times. Also call a statement from alasql.compile() twice.

Related: #2414, #2417, #2582 (its test test202-B.js leaves this pattern out of the toString() round-trip test because of this bug).