SQLite Forum

outcc2 is on the RHS of an INNER JOIN, not a CROSS JOIN.  

CROSS JOIN only preserves the descent order from the LHS table to the RHS table.

cte is still in the outer loop with respect to outcc1 as specified by the CROSS JOIN.

If you want lookup into outcc2 to occur in the innermost loop, you need to change the word INNER to CROSS in order to prevent re-ordering:

```
sqlite> 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
   ...>         CROSS JOIN ccc outccc2  ON outccc2.c2  = outccc1.c2
   ...>                                AND outccc2.c3 <> outccc1.c3
   ...>     WHERE cte.c5 = 0;
QUERY PLAN
|--CO-ROUTINE cte
|  |--SETUP
|  |  |--SEARCH ccc USING INDEX idx_ccc_c2 (c2=?) (~10 rows)
|  |  `--SEARCH aaa USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|  `--RECURSIVE STEP
|     |--SCAN cte (~524288 rows)
|     |--SEARCH ccc1 USING AUTOMATIC COVERING INDEX (c3=?) (~20 rows)
|     |--SEARCH ccc2 USING AUTOMATIC COVERING INDEX (c3=?) (~20 rows)
|     `--SEARCH aaa USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
|--SCAN cte (~100663296 rows)
|--SEARCH outccc1 USING INTEGER PRIMARY KEY (rowid=?) (~1 row)
`--SEARCH outccc2 USING INDEX idx_ccc_c2 (c2=?) (~9 rows)
```