Yes, the query plan is right.
I believe it needs to check, too. Look at these two whereloop objects.
```
with: * 1.02.01 t1.t1_c0 1 f 10201 N 1 cost 0,62,32
TERM-0 56425A4276C0 .E.. left={1:0} op=802 wtFlags=2008 prob=1 prereq=3,1
'-- EQ
|-- {1:0} pTab=56425A430150 fg.af=800000.n
'-- {0:0} pTab=56425A42E260 fg.af=800000.n
```
```
skip: * 1.02.01 t1.t1_c0 1 f 10201 N 1 cost 0,62,32
TERM-0 56425A4276F8 .E.. left={1:1} op=802 wtFlags=2008 prob=1 prereq=3,1
'-- EQ
|-- {1:1} pTab=56425A430150 fg.af=800000.n
'-- {0:0} pTab=56425A42E260 fg.af=800000.n
```
In my opinion, SQLite finds index t1_c0 is useless for the t1.c1 constraint only because the second whereloop's cost is not better than the first one. But I think SQLite should skip the second whereloop because this index shouldn't be used for the t1.c1 constraint.
Do you think we can find a test case to make the second whereloop(using index t1_c0 for the "t1.c0==t0.c0" term) has a lower cost than the first one(using index t1_c0 for the "t1.c1==t0.c0" term)? I decrease the cost of the second whereloop by modifying the source code and find it generates a mistake query plan. But I cannot provide
a test case for unmodified SQLite.
I found that the function `whereScanInit` has related comments, which I quote here:
```
/*
** Initialize a WHERE clause scanner object. Return a pointer to the
** first match. Return NULL if there are no matches.
**
** The scanner will be searching the WHERE clause pWC. It will look
** for terms of the form "X <op> <expr>" where X is column iColumn of table
** iCur. Or if pIdx!=0 then X is column iColumn of index pIdx. pIdx
** must be one of the indexes of table iCur.
**
** The <op> must be one of the operators described by opMask.
**
** If the search is for X and the WHERE clause contains terms of the
** form X=Y then this routine might also return terms of the form
** "Y <op> <expr>". The number of levels of transitivity is limited,
** but is enough to handle most commonly occurring SQL statements.
**
** If X is not the INTEGER PRIMARY KEY then X must be compatible with
** index pIdx.
*/
```
In this case, X is t1.c0 and Y is t0.c0. So term "t0.c0=t1.c1" will also be returned by function `whereScanNext`.
Maybe that is the reason why SQLite tries using the index on t1.c0 to optimize the "t1.c1=t0.c0" term.
I am confusing about "X must be compatible with index pIdx" in the comments.
From my view, t1.c1 is not compatible with index t1_c0.
Do you think it is a bug or not? Looking forward to your thoughts!