User: Password:
Subscribe / Log in / New account

A report from OpenSQLCamp

A report from OpenSQLCamp

Posted Nov 3, 2010 10:22 UTC (Wed) by dgm (subscriber, #49227)
In reply to: A report from OpenSQLCamp by ringerc
Parent article: A report from OpenSQLCamp

what's so hard about doing SELECT FROM invoices WHERE customer = '1'?

(Log in to post comments)

A report from OpenSQLCamp

Posted Nov 3, 2010 16:16 UTC (Wed) by iabervon (subscriber, #722) [Link]

Updating the code that reads the result set to deal with the column that you added for a different feature entirely (since that gives you all of the columns in the order they were declared in the schema definition)? Dealing with the possibilities of single quotes in the string if it comes from a potential attacker? The fact that your customer column is probably actually a number?

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.

A report from OpenSQLCamp

Posted Nov 3, 2010 19:33 UTC (Wed) by nix (subscriber, #2304) [Link]

Also, as soon as joins turn up, you're into hell. WITH makes things a bit simpler (assuming your database supports it), but encoding knowledge of the frankly somewhat bizarre tree-walk you have to do in order to figure out what names your columns and tables have got in *this* particular subclause is not very pleasant.

But that's not why I hate SQL, at all. That's just icing on the bad-tasting cake.

A report from OpenSQLCamp

Posted Nov 3, 2010 16:27 UTC (Wed) by alankila (guest, #47141) [Link]

Apart from not being syntactically correct, using string quotes for field that is likely an integer, and hardcoding a variable value in query rather than using a host parameter? Nothing.

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.

A report from OpenSQLCamp

Posted Nov 3, 2010 17:46 UTC (Wed) by Cyberax (✭ supporter ✭, #52523) [Link]

You're in luck:

List%lt;SomeObject%gt; objects=sess.from(someObject)
  .where("Joe", "Smith", "Bubba")
or even:
HibernateQuery query=sess.from(someObject)

if (weNeedToLimitByName)
    query.where("Joe", "Smith", "Bubba"));

if (someAnotherCondition)

Secret sauce is QueryDSL. LINQ in C# has even more powerful capabilities.

A report from OpenSQLCamp

Posted Nov 3, 2010 22:10 UTC (Wed) by alankila (guest, #47141) [Link]

It looks reasonable on the surface, at least. I normally use the Criteria API when dealing with Hibernate on Java, but it is not especially smart or type safe. It's a pity that code generator is required for this.

A report from OpenSQLCamp

Posted Nov 4, 2010 1:02 UTC (Thu) by ringerc (subscriber, #3071) [Link]

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.

A report from OpenSQLCamp

Posted Nov 4, 2010 11:45 UTC (Thu) by alankila (guest, #47141) [Link]

Yeah, NULLs sound useful at first, but the only real place where I use them today is for indicating a missing value in a numeric field. For string fields, I find that empty strings are good substitutes for NULLs, and many dates, especially those that mark some start and end epoch can be set to year 0000 or year 9999 initially instead of NULL depending on what is the semantically most useful way to view the value.

A report from OpenSQLCamp

Posted Nov 3, 2010 17:49 UTC (Wed) by Cyberax (✭ supporter ✭, #52523) [Link]

Now suppose that you want to fetch 10 customer's address records, last 5 coupons, and 20 recent buys in the same request.

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.

A report from OpenSQLCamp

Posted Nov 3, 2010 19:40 UTC (Wed) by andresfreund (subscriber, #69562) [Link]

Why would you want to?

A report from OpenSQLCamp

Posted Nov 3, 2010 19:46 UTC (Wed) by Cyberax (✭ supporter ✭, #52523) [Link]

To minimize number of database roundtrips, as usual

A report from OpenSQLCamp

Posted Nov 7, 2010 13:27 UTC (Sun) by zorro (subscriber, #45643) [Link]

You can also send the three individual queries to the database in a single batch.

Copyright © 2018, Eklektix, Inc.
Comments and public postings are copyrighted by their creators.
Linux is a registered trademark of Linus Torvalds