`From` is a baffling name for the result of a CTE

[lf-]

I am filing this issue in light of some oopsies we did in production, because it's not very obvious when a value is a CTE versus a subquery and that these look basically the same and can be easily mixed up. If this is combined with reusing the query in multiple places, e.g. let x = someQuery; y <- from x vs x <- with someQuery; y <- from x, it can be really easy to accidentally cause a multiplicative blowup of the size of the output query without noticing that it's a subquery instead of a CTE.

I am not sure what the solution to this is, but it's an API footgun.

cc @ellie-hg @parsonsmatt

[parsonsmatt]

Can you post a more detailed example on what went wrong here?

With let x = someQuery in ... expression that refers to x multiple times ..., you're going to copy/paste the definition of x into every use-site of x, and not leverage any sort of sharing on the generated code. This will cause multiple subqueries to be created and used. If you do x <- with someQuery; ..., then you should be reusing the CTE, which hopefully has better performance characteristics. But if you're getting significantly worse performance, then that'd be useful to know and catalog so as to help build intuition around when let is preferable to with.

[lf-]

The mistake was in accidentally using a subquery and caused a brownout of mwb on a rather important endpoint; @ellie-hg can speak more to the actual circumstances (and can likely DM you on slack with full details). The issue here imo is that binding a subquery to a variable in a way that lands multiple times in the same query in the end is really easy and the api doesn’t make it especially obvious.

At the same time, the From newtype returned by with is not descriptive of what it is and thus just reads as “more junk in the type name to ignore” so also doesn’t serve as an especially good signal of what’s up with a query without sorta cursed knowledge (iirc the docs don’t make it especially obvious either).

[parsonsmatt]

From means "call from on this to refer to it in a query." The documentation could certainly be improved here.

I agree that this is a gotcha when working with esqueleto specifically and EDSLs in general. let x = someComputation a b c doesn't actually make a SQL-level variable and assign the result, but instead copy-pastes the expression. This almost feels like something that RebindableSyntax might help with, but it does not appear to allow you to override let.

This is actually the sort of thing that would be really useful to teach CodeRabbit or other AI tooling.

[belevy]

We can make a CommonTableExpression type that has a ToFrom instance but I don't actually see how that would help the issue other than maybe making the types more self documenting (a fine goal). A From is the common "interface" for all things that can be "fromed" and since there is nothing you can do with a CommonTableExpression other than call from on it a custom type seemed unwarranted, semantically the result of from $ table @A and b <- with $ from $ table @A; from b are equivalent so types of table @A and b are equivalent. As an aside I believe the documentation on with already calls out this specific thing.

From as an "interface"

The way I view the implementation of this stuff is fundamentally object oriented rather than data oriented. The reason for this is that we have a closed set of interpretations on our eDSL in that all you can expect an esqueleto query to do is render as SQL. Because the interpretations are closed it would be really nice if we could have open terms in our language. This means that you essentially have to flip the meanings of types and values from a traditional data oriented design where now the type is the evaluation strategy and the function is the term in the language. So in our case we have a term table which will bring a table into scope in something with interface inheritance you would represent this like class Table implements From. We could also have a term innerJoin which when given two instances of From will join the two of them together using INNER JOIN. This could be defined as something like InnerJoin(lhs From, rhs From) implements From (never mind the inconsistent pseudo code).

So why don't we just use individual types in Haskell?

We do, that is what the ToFrom class is for, however that adds further indirection that feels unnecessary in this case. We currently use ToFrom on SqlSetOperation which has its own type because we need to restrict the set operations to only complete queries or other set operations before we select from it but we cant just treat the operations as producing a SqlQuery, though the implementation of the set operations is a bit off topic. So we already have the machinery necessary to convert from a more specific type to From and the join family already depends on ToFrom. In fact when I initially implemented this approach to the new syntax I did use a bunch of data types Table, SubQuery, InnerJoin, etc.. but later removed them as it seemed that they provided no benefit but made the errors much worse because it all went through the ToFrom machinery.

In conclusion

I don't know much and have weird ideas and am pretty terrible at documenting them.

If you are interested on my take on this more object oriented flipping of evaluation strategy and terms I wrote about it at https://www.foxhound.systems/blog/final-tagless/