tomjaguarpaw / haskell-opaleye

Other
602 stars 115 forks source link

Add `withRecursiveDistinct` which uses `UNION` instead of `UNION ALL` #571

Closed shane-circuithub closed 1 year ago

shane-circuithub commented 1 year ago

I should have added this originally, but I omitted it at the time because I erroneously thought that because union a b is the same as distinct $ unionAll a b, therefore distinct $ withRecursive s f could always be used instead of withRecursiveDistinct, rendering the latter redundant.

I've added some comments explaining operationally what is happening in each of these calls that should hopefully explain why these are not the same.

In short, using UNION instead of UNION ALL means that at each recursive step, you can consider only the rows that don't already exist in the result set as opposed to all the rows returned by that step. This is useful because there are queries that terminate quickly when written with withRecursiveDistinct but which run forever (because the same rows are being fed back in over and over again) when written with withRecursive, even if you try to wrap it in a distinct.

tomjaguarpaw commented 1 year ago

Thanks! Published as https://hackage.haskell.org/package/opaleye-0.10.1.0