5 ms·
At an old job, me and another person wrote an in-house SQL-like dialect that compiled to access our C++ data structures. It was kind of nice just throwing whate
by MichaelBurge 10y ago
At an old job, me and another person wrote an in-house SQL-like dialect that compiled to access our C++ data structures. It was kind of nice just throwing whatever junk you wanted into your SQL without having to worry about any standards.
Want a filter clause? Got it.
Need a weighted distinct count? Now it's part of the language.
Also, you can rephrase a lot of these SQL features as subqueries. It's surprising how many database bugs you can find when you do it. Not so much in Postgres, but I probably found a dozen in Redshift. I mean rephrasing this:
select
sum(x) filter (where x < 5),
sum(x) filter (where x < 7)
from
generate_series(1,10,1) s(x)
;
as this(where t is either a permanent table, view, or CTE as appropriate):
with t as (
select * from generate_series(1,10,1) s(x)
)
select
(select sum(x) from t where x < 5),
(select sum(x) from t where x < 7)
;
Though for Postgres the filter will likely have better performance.
- goldenkey 10y agoArent subselects better performing than comparable joins and aggregates when the query optimizer screws up? I remember reading an HN post not too long ago.
- MichaelBurge 10y agoIf you're talking specifically about the comment at the end, that was because the example query will do a seq scan on the table twice. The filter equivalent will only do one scan. I find subselects are more predictable, but I wouldn't say they're generally more performant. The explain plan's tree maps more closely to the subquery dependency tree with selects, so someone tweaking their query and staring at the explain plan to get it how they want will have an easier time with the subselect strategy. Be careful of CTEs if you try that, though. A long chain of CTEs with the transformation I gave is almost guaranteed to give a bad plan(unless you want seq scans all the way down).
- brudgers 10y agoDepending on the number of years ago, it may have been ahead of its time. C# and Scala have SQL like language abstractions a few years ago via LINQ. Scala has something similar.
- duaneb 10y agoScala doesn't have anything like this where the query is compiled monadically before execution, although the data structures do expose excellent ways to chain functional combinators. Squeryl (and there's a similar competitor) implement this for SQL, and I'd say it's 80% there--still a lot of rough edges fitting dynamic queries (e.g. number of tables joinable in a single statement) into a static language.
- rm999 10y agoWould Catalyst in Spark SQL count? http://people.csail.mit.edu/matei/papers/2015/sigmod_spark_sql.pdf http://people.csail.mit.edu/matei/papers/2015/sigmod_spark_s...
- virtualwhys 10y ago> Scala doesn't have anything like this where the query is compiled monadically before execution Nonsense, Scala is arguably leading the way in the statically typed composable query dsl department; see Slick [1] and Quill [2] among others. Outside of Haskell's Esqueleto I am not aware of any statically typed query dsl that comes remotely close to the aforementioned. LINQ to SQL/Objects provides static query generation but doesn't compose; beyond that, what is there? [1] https://github.com/slick/slick https://github.com/slick/slick [2] https://github.com/getquill/quill https://github.com/getquill/quill
- Arnavion 10y ago>LINQ to SQL/Objects provides static query generation but doesn't compose How do you mean? The result of a LINQ query is a sequence that can be used as the input of another LINQ query.
- virtualwhys 10y agoBy LINQ query I mean LINQ to SQL, not the basic LINQ collections functionality (the equivalent in Scala being for comprehensions, BTW). If you have an example of .NET based query composition to share I'd like to see it; last I checked there was no such functionality available.
- snissn 10y agoCould you give a quick sketch of how you designed your sql parser (traspiler?)? Thank you!