Hi everyone,
Consider the following example:
```SQL
CREATE TEMPORARY TABLE t0(c0 INT);
CREATE TEMPORARY TABLE t1(c0 INT, c1 INT);
CREATE INDEX t1_c0 ON t1(c0);
INSERT into t0(c0) VALUES (1), (-1), (2);
INSERT INTO t1(c0,c1) VALUES (1, 2), (2, -1), (-1, 1);
.wheretrace
SELECT * FROM t0 CROSS JOIN t1 ON t1.c0 == t0.c0 AND t1.c1 == t0.c0;
```
I enable debugging and use the ".wheretrace" command to obtain wheretrace information, part of the output is as follows:
```
---- WHERE clause at start of analysis:
TERM-0 56425A4276C0 .E.. left={1:0} op=802 wtFlags=0008 prob=1 prereq=3,1
'-- EQ
|-- {1:0} pTab=56425A430150 fg.af=800000.n
'-- {0:0} pTab=56425A42E260 fg.af=800000.n
TERM-1 56425A4276F8 .E.. left={1:1} op=802 wtFlags=0008 prob=1 prereq=3,1
'-- EQ
|-- {1:1} pTab=56425A430150 fg.af=800000.n
'-- {0:0} pTab=56425A42E260 fg.af=800000.n
TERM-2 56425A427730 VE.. left={0:0} op=802 wtFlags=0003 prob=1 prereq=3,2 iParent=1
'-- EQ
|-- {0:0} pTab=56425A42E260 fg.af=800000.n
'-- {1:1} pTab=56425A430150 fg.af=800000.n
TERM-3 56425A427768 VE.. left={0:0} op=802 wtFlags=0003 prob=1 prereq=3,2 iParent=0
'-- EQ
|-- {0:0} pTab=56425A42E260 fg.af=800000.n
'-- {1:0} pTab=56425A430150 fg.af=800000.n
add: * 0.01.02 t0 f 04000 N 1 cost 271,53,43
TERM-0 56425A427730 VE.. left={0:0} op=802 wtFlags=0003 prob=1 prereq=3,2 iParent=1
'-- EQ
|-- {0:0} pTab=56425A42E260 fg.af=800000.n
'-- {1:1} pTab=56425A430150 fg.af=800000.n
skip: * 0.01.02 t0 f 04000 N 1 cost 271,53,43
TERM-0 56425A427768 VE.. left={0:0} op=802 wtFlags=0003 prob=1 prereq=3,2 iParent=0
'-- EQ
|-- {0:0} pTab=56425A42E260 fg.af=800000.n
'-- {1:0} pTab=56425A430150 fg.af=800000.n
add: * 0.01.00 t0 f 00100 N 0 cost 0,216,200
BEGIN t0.addBtreeIdx(), nEq=0, nSkip=0, rRun=216
END t0.addBtreeIdx(), nEq=0, rc=0
add: * 1.02.01 t1 f 04000 N 1 cost 271,53,43
TERM-0 56425A4276C0 .E.. left={1:0} op=802 wtFlags=0008 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 f 04000 N 1 cost 271,53,43
TERM-0 56425A4276F8 .E.. left={1:1} op=802 wtFlags=0008 prob=1 prereq=3,1
'-- EQ
|-- {1:1} pTab=56425A430150 fg.af=800000.n
'-- {0:0} pTab=56425A42E260 fg.af=800000.n
add: * 1.02.01 t1 f 00100 N 0 cost 0,216,180
BEGIN t1.addBtreeIdx(), nEq=0, nSkip=0, rRun=216
END t1.addBtreeIdx(), nEq=0, rc=0
BEGIN t1.addBtreeIdx(t1_c0), nEq=0, nSkip=0, rRun=216
replace: * 1.02.01 t1 f 04000 N 1 cost 271,53,43
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
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
delete: * 1.02.01 t1 f 00100 N 0 cost 0,216,180
BEGIN t1.addBtreeIdx(t1_c0), nEq=1, nSkip=0, rRun=62
END t1.addBtreeIdx(t1_c0), nEq=1, rc=0
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
BEGIN t1.addBtreeIdx(t1_c0), nEq=1, nSkip=0, rRun=62
END t1.addBtreeIdx(t1_c0), nEq=1, rc=0
END t1.addBtreeIdx(t1_c0), nEq=0, rc=0
0 0.01.02 t0 f 04000 N 1 cost 271,53,43
TERM-0 56425A427730 VE.. left={0:0} op=802 wtFlags=0003 prob=1 prereq=3,2 iParent=1
'-- EQ
|-- {0:0} pTab=56425A42E260 fg.af=800000.n
'-- {1:1} pTab=56425A430150 fg.af=800000.n
1 0.01.00 t0 f 00100 N 0 cost 0,216,200
2 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
```
It is weird that SQLite tries to use the index on t1.c0 for an equal search t1.c1 == t0.c0.
I can not find a test case to get a mistake result caused by this whereloop object, since this whereloop object usually dropped due to the same predict cost as a previous one.
```
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
```
I suspect that this is a misunderstanding on my side, rather than a bug. Looking forward to your explanation!