> To be really equivalent I should have used UNION.
Nope. Even that is not equivalent. See below.
"where a or b" returns 3 rows
"where a union all where b" returns 4
"where a union where b" returns 2
SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table foo (a, b);
sqlite> insert into foo values (1, 1), (1, 0), (0, 1);
sqlite> .eqp on
sqlite> select a from foo where a or b;
QUERY PLAN
`--SCAN TABLE foo
a
1
1
0
sqlite> select a from foo where a union all select a from foo where b;
QUERY PLAN
`--COMPOUND QUERY
|--LEFT-MOST SUBQUERY
| `--SCAN TABLE foo
`--UNION ALL
`--SCAN TABLE foo
a
1
1
1
0
sqlite> select a from foo where a union select a from foo where b;
QUERY PLAN
`--COMPOUND QUERY
|--LEFT-MOST SUBQUERY
| `--SCAN TABLE foo
`--UNION USING TEMP B-TREE
`--SCAN TABLE foo
a
0
1
sqlite>