Incorrect NLJ plan due to index fetch

[hatyo]

In certain scenarios, the optimizer chooses a covering index scan for NLJ inner leg, however it is also putting a fetch between the NLJ operator and the (correlated) predicate filter operator, this causes plan execution to fail with a missing correlation error.

com.apple.foundationdb.record.RecordCoreException: com.apple.foundationdb.record.RecordCoreException: Missing binding for __corr_q4

The optimizer should either push the fetch down (rendering the covering scan less appealing) or choose a different access path all together I think.

Here is an example:

create table t1(id bigint, parent bigint, primary key(id))
-- create a secondary index childIdx on id and parent

now:

select b.id, b.parent from t1 as a, t1 as b where a.parent = b.id and b.id > 40

ends up with this incorrect plan: