Hi,
I wanted to manually optimize a query by using a CROSS JOIN. This should prevent the optimizer to change the order in which tables are visited. But with my query Sqlite still uses a table at the right hand side of the CROSS JOIN for the outer loop. I expected it to use the table at the left hand side of the CROSS JOIN (a CTE in my case) as the outer loop.
Tested with sqlite version 3.34.0.
The query below is simplified a lot wrt the actual version I use, but most likely not a "minimal" example. Semantically it is meaningless now, but it is only intended to show that Sqlite CROSS JOINs do not always prevents re-ordering as documented on <https://www.sqlite.org/lang_select.html>, Side note in section 2.1.
A workaround to make Sqlite start with the records from the cte, is to write the results of the cte to an separate table, and use this table in the main query instead of the cte.
Example:
```
CREATE TABLE IF NOT EXISTS aaa(a1 INTEGER PRIMARY KEY, a2, a3);
CREATE TABLE IF NOT EXISTS ccc(c1 INTEGER PRIMARY KEY, c2, c3);
CREATE INDEX IF NOT EXISTS idx_aaa_a2 ON aaa(a2);
CREATE INDEX IF NOT EXISTS idx_ccc_c2 ON ccc(c2);
.eqp ON
WITH cte(c1, c2, c3, c4, c5) AS
(SELECT ccc.c2 AS c1, ccc.c1 AS c2, ccc.c1 AS c3, ccc.c3 AS c4, aaa.a3 <> 0 AS c5
FROM ccc INNER JOIN aaa ON aaa.a1 = ccc.c3 WHERE ccc.c2 = 12345
UNION
SELECT cte.c1 AS c1, ccc2.c1 AS c2, ccc2.c1 AS c3, ccc2.c3 AS c4, aaa.a3 <> 0 AS c5
FROM cte
INNER JOIN ccc ccc1 ON ccc1.c3 = cte.c4 AND ccc1.c2 <> cte.c1
INNER JOIN ccc ccc2 ON ccc2.c3 = ccc1.c2 AND ccc2.c3 <> ccc1.c3
INNER JOIN aaa ON aaa.a1 = ccc2.c3
WHERE cte.c5 = 1)
SELECT *
FROM cte cte
CROSS JOIN ccc outccc1 ON outccc1.c1 = cte.c1
INNER JOIN ccc outccc2 ON outccc2.c2 = outccc1.c2
AND outccc2.c3 <> outccc1.c3
WHERE cte.c5 = 0;
QUERY PLAN
|--CO-ROUTINE 2
| |--SETUP
| | |--SEARCH TABLE ccc USING INDEX idx_ccc_c2 (c2=?)
| | `--SEARCH TABLE aaa USING INTEGER PRIMARY KEY (rowid=?)
| `--RECURSIVE STEP
| |--SCAN TABLE cte
| |--SEARCH TABLE ccc AS ccc1 USING AUTOMATIC COVERING INDEX (c3=?)
| |--SEARCH TABLE ccc AS ccc2 USING AUTOMATIC COVERING INDEX (c3=?)
| `--SEARCH TABLE aaa USING INTEGER PRIMARY KEY (rowid=?)
|--SCAN TABLE ccc AS outccc2
|--SEARCH SUBQUERY 2 AS cte USING AUTOMATIC PARTIAL COVERING INDEX (c5=?)
`--SEARCH TABLE ccc AS outccc1 USING INDEX idx_ccc_c2 (c2=? AND rowid=?)
```
Although outccc2 is at the right hand side of a CROSS JOIN it is used as outer loop. The request is that the no-reorder requirement of the CROSS JOIN is fullfilled (or to document that it does not hold if the left had side operand is a cte).