The fix applied for
CALCITE-4206 was "too drastic" and it resulted in Sort with fetch/offset being impossible to decorrelate in all cases.
CALCITE-4333 addressed this issue but only partially (when the Sort with fetch/offset is on top on the plan). However, this solution is insufficient, because any Sort with fetch/offset that is not inside a Correlate can be decorrelated.
Check this test in SqlToRelConverterTest (same test as
CALCITE-4333, just with an extra LogicalProject on top of the LogicalSort):
The current plan is:
It can actually be decorrelated as: