Not logged in
Log in now
Create an account
Subscribe to LWN
An unexpected perf feature
LWN.net Weekly Edition for May 16, 2013
A look at the PyPy 2.0 release
PostgreSQL 9.3 beta: Federated databases and more
LWN.net Weekly Edition for May 9, 2013
A report from OpenSQLCamp
Posted Nov 3, 2010 16:16 UTC (Wed) by iabervon (subscriber, #722)
But the real issue is that applications will generally need some sort of search, and that will often involve optional clauses, which means that a fixed SQL statement, even with bind variables, will not be sufficient to implement some of the queries the application needs to do. And once you do pretty much anything dynamic, you're into a lot of annoyance with the need to have one less item of punctuation than list item, and needing to understand how join order can matter. And it's not long before you have to join the same table for two different purposes, getting different rows for each, and you have to worry about what you're naming the table in the query, and getting the right names for the columns from the two copies. The problem with a lot of the convenience features of SQL is that you can't use them with the queries that most need to be comprehensible.
Posted Nov 3, 2010 19:33 UTC (Wed) by nix (subscriber, #2304)
But that's not why I hate SQL, at all. That's just icing on the bad-tasting cake.
Posted Nov 3, 2010 16:27 UTC (Wed) by alankila (subscriber, #47141)
From viewpoint of someone who constructs a query dynamically based on, say a hash-map's keys, the idiocy already starts from the fact that the first condition needs to start with "WHERE" and the other conditions need to start with "AND", just to construct a query that says "WHERE col1 = :v1 AND col2 = :v2". Lazy people type code like "WHERE 1=1" just to avoid having to deal with that issue.
Some other things that I hate personally are having to deal with the NULL value specifically; "foo = :some_value", but "foo IS NULL" instead of "foo = NULL". I'm sure there is some tired reason for this, but I don't care anymore. Additionally, if you want to negate a condition generally, you have to come up with schemes like (NOT foo = x) or (NOT foo IS NULL) because otherwise you have to generate foo != x vs. foo IS NOT NULL. (At least that is possible generically, though. A small victory, if you accept the use of different operators to begin with.)
Finally, the actual value is often presenting complexities of its own. Many databases flat out refuse a comparison like id = '5' if the id is in fact numerical. This is mitigated by host variables and intelligent cooperation between a dynamic language and the underlying SQL engine so automatic type conversions become possible, and in case of a static language it is a feature that the query crashes if wrong type of parameter is bound to a host variable.
It is this type of thoughtless hostility that the GP is complaining about, I believe. I have often wished having a programmable API for query construction that wouldn't require laying out a complex query out of tiny bricks. When designing one, I decided that such a thing must have a SQL parser because usually there is no way better to represent a SQL query than to spell it out in a string, and despite starting out in this way you still want to add and remove conditions later on without having to care about whether the condition must start with "WHERE" or "AND" or if the null value needs to be compared with operator = or IS, or whether the field in question needs a value that is a string or integer type, etc.
Posted Nov 3, 2010 17:46 UTC (Wed) by Cyberax (✭ supporter ✭, #52523)
.where(someObject.name.in("Joe", "Smith", "Bubba")
query.where(someObject.name.in("Joe", "Smith", "Bubba"));
Posted Nov 3, 2010 22:10 UTC (Wed) by alankila (subscriber, #47141)
Posted Nov 4, 2010 1:02 UTC (Thu) by ringerc (subscriber, #3071)
Null handling isn't too bad if you're not dealing with brain-dead databases where you have to severely limit the used feature set. You can use "NOT (A IS DISTINCT FROM B)" to cover equality and nullity in one expression. It's a wee bit verbose, but better than a properly null-safe equality comparison, which is just horrid.
I'm of two minds on NULL. On one hand, it's semantically different to the null/NULL/None/undef/whatever in most programming languages, and behaves differently. On the other hand, it's a really useful concept that's intellectually appealing. It's such a PITA to actually work with that I'm not sure it's worth it, though, especially once you get to the inconsistencies in its application in various parts of the spec.
Posted Nov 4, 2010 11:45 UTC (Thu) by alankila (subscriber, #47141)
Posted Nov 3, 2010 17:49 UTC (Wed) by Cyberax (✭ supporter ✭, #52523)
You'll get hit by Cartesian product in the form of 10*5*20=1000 records. Good hierarchical query should return a tree with 35 nodes.
Posted Nov 3, 2010 19:40 UTC (Wed) by andresfreund (subscriber, #69562)
Posted Nov 3, 2010 19:46 UTC (Wed) by Cyberax (✭ supporter ✭, #52523)
Posted Nov 7, 2010 13:27 UTC (Sun) by zorro (subscriber, #45643)
Copyright © 2013, Eklektix, Inc.
Comments and public postings are copyrighted by their creators.
Linux is a registered trademark of Linus Torvalds