HAVING a Blunderful Time (or Wish You Were WHERE) (2007)

HAVING a Blunderful Time (or Wish You Were WHERE) (2007)(dcs.warwick.ac.uk)

31 points by shangxiao 7 years ago | 6 comments

cafard 7 years ago |

The master pattern for this sort of thing is probably Babbage arguing with Stephenson about whether a broader gauge would have been better for railroads. Undoubtedly Babbage had the right of it in the sense that if one were starting a railroad system from scratch the broader gauge would have been better. But beyond some established base of rail mileage, it really didn't matter.

blattimwind 7 years ago |

WHERE filters input rows

HAVING filters produced rows

Questions?

spthorn60 7 years ago | |

But he has a valid point in HAVING not being able to reference an aggregate column by the AS name in the SELECT.

blattimwind 7 years ago | | |

Yeah, I always considered that quite strange, given the order of the query (and even though forward references are found almost everywhere in SQL, making it even more strange)

ORM-wise it's not a huge issue, I tend to move them outside the query definition to avoid the duplication. E.g.

    comment_count = func.count(Comment.id).label('comment_count')
    query = (session.query(User, comment_count)
             .join(User.comments)
             .group_by(User)
             .having(comment_count > 10))

asah 7 years ago |

Misleading title. Should be more like, "random whining about SQL"

SQL is like democracy: it's the worst form, except for every other.

Go try writing non-trivial queries in any other database query language, then come back and whine to me about SQL.

Notably, SQL hides the implementation in a way that allows for 1000+x speedups from index selection, JOIN order optimization, execution method and parallelism. Typically, even hand-coded queries can't touch the performance because of gonzo low level optimizations nobody would bother writing for one query, such as optimizing batch size to match cache size.

Modern SQL (e.g. Postgres) even does this while allowing the user to include unmodified business logic written in a variety of high level languages. For example, if you want to search for employees that match some crazy predicate written in (JavaScript/Python/etc) no problem, it fits right into the WHERE clause, and is executed in parallel on each core of each node. You can even index the results of this function, so the storage system doesn't even look at non-matching records... assuming this index is the more selective of the options, which it dynamically decides per query.

tl;dr: quit your whining

randomdata 7 years ago | |

> Go try writing non-trivial queries in any other database query language

Which query languages (other than ORMs) are the top contenders when staying within the rational model to compare with?

While there are notable examples of non-relational databases that use SQL as their native query language, I was recently looking for a relational database that natively uses a language other than SQL and came up short. I imagine there is something out there, but SQL dominates the landscape.

I am familiar with a few alternative languages, some of which compile to SQL. But it is pretty bleak out there, which is interesting given how many general purpose programming languages we have. I expect because ORMs have become the language people use to query databases, all of the attention have gone into them, rather than improving native query languages.

comment_count = func.count(Comment.id).label('comment_count') query = (session.query(User, comment_count) .join(User.comments) .group_by(User) .having(comment_count > 10))