SQLite Forum

CROSS JOIN sometimes re-orders tables when lhs is a CTE
Login
> SEARCH TABLE ccc AS outccc1 USING INDEX idx_ccc_c2 (c2=? AND <b>rowid=?</b>)
> <i>(Emphasis added)</i>

I'm less concerned about the CROSS JOIN issue that I am about why the
query planner is using an index here, rather than just doing a direct
ROWID lookup.  Investigating now...

Simplified test case:

> ~~~~
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 xc2 ON ccc(c2);
.eqp ON
WITH bbb(b1, b2) AS(
   VALUES(1,2)
   UNION ALL
   SELECT b1+1, b2+1 FROM bbb, aaa WHERE b2=1
)
SELECT *
  FROM bbb
 CROSS JOIN ccc AS c11 ON c11.c1  = bbb.b1
 INNER JOIN ccc AS c22 ON c22.c2  = c11.c2 AND c22.c3 <> c11.c3
 WHERE bbb.b1 = 0;
~~~~

Output:

> ~~~~
QUERY PLAN
|--CO-ROUTINE bbb
|  |--SETUP
|  |  `--SCAN CONSTANT ROW
|  `--RECURSIVE STEP
|     |--SCAN bbb
|     `--SCAN aaa
|--SCAN c22
|--SEARCH bbb USING AUTOMATIC PARTIAL COVERING INDEX (b1=?)
`--SEARCH c11 USING INDEX xc2 (c2=? AND rowid=?)
~~~~