I completely trust that SQLite is computing it correctly. I apologize for being imprecise - yes I'm aware `UNION ALL` contains duplicates. To be really equivalent I should have used `UNION`. The point still stands though, there's practically no difference in duration between `UNION` & `UNION ALL` - they complete in a few milliseconds, compared to the problematic query taking seconds.
```
sqlite> select file.id from file left join event on file.id = event.file_id where event.new_name = 'aaa.txt' UNION select file.id from file left join event on file.id = event.file_id WHERE event.new_name IS NULL and file.name = 'aaa.txt';
QUERY PLAN
`--COMPOUND QUERY
|--LEFT-MOST SUBQUERY
| |--SCAN TABLE event
| `--SEARCH TABLE file USING INTEGER PRIMARY KEY (rowid=?)
`--UNION USING TEMP B-TREE
|--SEARCH TABLE file USING COVERING INDEX file_index (name=?)
`--SCAN TABLE event
Run Time: real 0.002 user 0.000000 sys 0.000000
```