Limits with joins

[markdchurchill]

So, I recently had something along the lines of .Query<A>().Where(P).Fetch(...B).Take(n), and it behaved how I would've naively written the query, as A join B limit n - limiting the total number of joined rows. Semantically not the correct result, but understandable.

This is on second look a harder problem - but I did notice that:

.Query<A>().Where(P).Take(n).Fetch(...B) doesn't affect the behaviour.

However .Query<A>().Take(n).Where(P).Fetch(...B) results in a better query, roughly A join B where P and A in (A limit n). Not quite what I want.

However I can trick it to give the behaviour I want, inline with the initial semantics I was after with
.Query<A>().Where(P).Take(n).Where(x => true).Fetch(...B)

This results in sql along the lines of A join B where true and A in (A where P limit n) which is perfect.

So I guess, this is a bug? It's at least surprising. My case is pretty far from minimal, and I'm not sure how much the postgres provider factors into this internally. I figured I'd post my workaround here - because I wasn't even sure if this Take is a supported scenario - but it can clearly be made to work, and the resulting SQL is performant.