I don't get it. So much crap posted on The Daily WTF is for initiates only with no explanations for those of us with over 20 years programming experience who lack that finite bit of knowledge that makes YOUR WTF understandable.

The worst part is I looked at that query and was like, oh hibernate probably generated that query. Then I looked again and from what I can see it doesn't look like it's aliasing tables/fields the way that hibernate would. This leads me to believe that someone probably wrote this...

Seen a query of this size that was generated by an alien logic that my colleague wrote... and strangely enough, it was the only possible way to get the desired result as the DB structure was just as alien.

I don't get it. So much crap posted on The Daily WTF is for initiates only with no explanations for those of us with over 20 years programming experience who lack that finite bit of knowledge that makes YOUR WTF understandable.

Well, I have over 20 years' programming experience, and evidently I have the necessary piece of knowledge. In fact, it's in the article. The picture shows a hard-copy of *one* SQL query. HTF does an SQL query get complex enough to be spread over six pages?

The worst part is I looked at that query and was like, oh hibernate probably generated that query. Then I looked again and from what I can see it doesn't look like it's aliasing tables/fields the way that hibernate would. This leads me to believe that someone probably wrote this...

I'm not even sure what kind of query you would have hibernate doing that it would do it like that. Hibernate seems to do it less in big queries now, but you might have more running to populate your objects if you don't have lazy loading turned on for relationships.

Regrettably, my second or third thought was, "I really hope that wasn't one I had to write." After another, closer look, it isn't (I never had to write anything against a schema with Dutch table names).

I wound up writing a hideous abomination of an 8+-way union, once. IIRC, it was a bunch of reporting queries that needed to be squished together, so the individual queries weren't terrible, there were just a lot of them.

It's also possible that it's a bunch of MS Access queries that have been strung together, which might explain the SELECT (SELECT thing at the start. Access has traditionally had some curiously quaint restrictions on query size that tends to turn any even moderately complex query into a maze of small, twisty queries, all alike.

Finally, it could just be that whoever created that thing had no idea what they were up to.

That's pretty bad, but in my experience such queries are usually a result of

lack of SQL know-how and

extensive CTRL+Cing-and-CTRL+Ving.

This massive beast is most likely a handful of copies of several smaller-but-ever-so-slightly-different beasts all JOINed and UNIONed together. Example:

SELECT (bunch of crappy columns)
FROM (bunch of crappy tables)
WHERE (bunch of crappy conditions)
AND item_type = "XYZZY"
UNION
SELECT (the same crappy columns)
FROM (the same crappy tables)
WHERE (the same crappy conditions)
AND item_type = "PLUGH"
UNION
...

I bet if you format it, it'd take (at most) only a couple months and all your remaining sanity to refactor it into something mentally-parsable.

That's pretty bad, but in my experience such queries are usually a result of
1) lack of SQL know-how and
2) extensive CTRL+Cing-and-CTRL+Ving.

This massive beast is most likely a handful of copies of several smaller-but-ever-so-slightly-different beasts all JOINed and UNIONed together. Example:

SELECT (bunch of crappy columns)
FROM (bunch of crappy tables)
WHERE (bunch of crappy conditions)
AND item_type = "XYZZY"
UNION
SELECT (the same crappy columns)
FROM (the same crappy tables)
WHERE (the same crappy conditions)
AND item_type = "PLUGH"
UNION
...

I bet if you format it, it'd take (at most) only a couple months and all your remaining sanity to refactor it into something mentally-parsable.

Which is exactly why he shouldn't try to RE the bitch and should just rewrite it from scratch based on specs (HA!) or expected behavior...

I once helped out on a project with a SQL query that long that took 70 seconds to complete on average (not good for a web app!). I fixed it by extracting the relevant data from the database and doing the search in RAM, reducing the query time to sub second. Some things are best not done with a relational database.