SQLite Forum

CROSS JOIN sometimes re-orders tables when lhs is a CTE
> why the query planner is using an index here, rather than just doing a direct ROWID lookup

Answer:  With a rowid lookup, the query planner expects to find one row of
result.  But with the indexed lookup on both column C2 and the rowid, it is
expecting to find something closer to zero rows.  And it turns out that doing
an index lookup that finds zero rows is less work than a rowid lookup that finds
one row, at least when downstream computations are taken into account.

As for the original CROSS JOIN question, Keith Medcalf has that right:  The
CROSS JOIN only determines the ordering of its immediate left and right terms.
In the original query, the outccc2 table is allowed to reorder across the
CROSS JOIN because it is not immediately adjacent to the CROSS JOIN.  In other
words, a CROSS JOIN is not a reordering barrier.  The CROSS JOIN simply determines
the relative order of its immediate left and right tables.