Comment On The Query of Despair

Jeroen's colleague had the misfortune of being assigned to debug an intermittent, unspecified error in the one of the oldest of the legacy applications. "The good news is that I've isolated it to a database query," he told Jeroen, "the bad news is that I've isolated it to a database query." [expand full text]
« PrevPage 1 | Page 2 | Page 3 | Page 4 | Page 5Next »

Re: The Query of Despair

2011-10-24 10:35 • by boog
364211 in reply to 364210
The poop of DOOM:
boog:
On the plus side (for Jeroen's colleague), since this SQL was submitted as a CodeSOD instead of a Representative Line, I assume it implies some semblance of sanity in the rest of the source.

To apply for Representative Line, it should be a line, not six pages.
One line wrapped by the printer is still one line.

Re: The Query of Despair

2011-10-24 10:37 • by Ken B. (unregistered)
364212 in reply to 364168
Damien:
The problem is on page 4, line 27. Isn't that obvious to everyone?
You must be new around here. The problem is on line 42.

Re: The Query of Despair

2011-10-24 10:38 • by C-Octothorpe
364213 in reply to 364210
The poop of DOOM:
boog:
On the plus side (for Jeroen's colleague), since this SQL was submitted as a CodeSOD instead of a Representative Line, I assume it implies some semblance of sanity in the rest of the source.

To apply for Representative Line, it should be a line, not six pages.
Line breaks don't count...

EDIT: oops, boog beat me to it... DAMN YOU BOOG!!!

Re: The Query of Despair

2011-10-24 10:40 • by Bob (unregistered)
364214 in reply to 364187
I. G. E.:
The poop of DOOM:
frits:
C-Octothorpe:
frits:
C-Octothorpe:
frits:
Looking at that image gave me a similar feeling to watching VanDamme dancing in the movie Kickboxer.
What, nausea?
I think the colloquial term is "douche chills".
I totally forgot about that scene. Thanks for undoing hundreds of hours of therapy frits...
If you've had the mispleasure of seeing that scene, no matter how long and far you try to stuff the memory, the pain will always be there.

Seriously guys, I haven't seen that movie yet. Could you please put spoiler tags around this?

Then again, at least I've been warned now.

I have seen it, and I haven't a clue what scene they may be talking about. In a Van Damme movie, what kind of *censored* even notices anything except the fighting scenes?

Please try to show some sensitivity. I had a son who was *censored*, and let me assure you: it is no laughing matter.

Re: The Query of Despair

2011-10-24 10:44 • by Daniil S. (unregistered)
364215 in reply to 364206
trtrwtf:
Daniil S.:
Just out of sheer curiosity, I really wish to see this query in full.


As a wise man once said, "Just remember: what has been seen, cannot be unseen."


I wish I had a dime for each time I had that experience

Re: The Query of Despair

2011-10-24 10:47 • by chernobyl (unregistered)
As the BASIC interpreter would say: ?REDO FROM START

Re: The Query of Despair

2011-10-24 10:52 • by Me (unregistered)
They must be using Magento...

Re: The Query of Despair

2011-10-24 10:55 • by boog
364218 in reply to 364198
C-Octothorpe:
boog:
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...
That's exactly the direction I was headed, right down to the HA.

Re: The Query of Despair

2011-10-24 10:57 • by Nederlander (unregistered)
Looks like a great query for EXPLAIN.
What kind of database is configured with a statementbuffer this big?

Re: The Query of Despair

2011-10-24 11:05 • by Jon E. (unregistered)
Presented to you by the movement, "Occupy Query Engine."

Re: The Query of Despair

2011-10-24 11:05 • by steenbergh
This query is bigger than my mother-in-law...

Re: The Query of Despair

2011-10-24 11:09 • by Ratnoz Bassackwards (unregistered)
364223 in reply to 364159
You've been in programming 20 years and don't know what a *query* is?

I think I smell some BS, but just in case I'm wrong about that, here goes: It's ONE friggin' SQL statement that's MANY pages long.

Re: The Query of Despair

2011-10-24 11:10 • by Coyne
364224 in reply to 364197
boog:
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.


Yes. The only way it could possibly be worse is if they used column and table names that were keywords:

SELECT SELECT, INTO, FROM, ON FROM JOIN JOIN INTO ON SELECT = INTO AND ON = UNION

(DB2 SQL has no reserved words; given the right table definitions, the above is legal!)

Re: The Query of Despair

2011-10-24 11:11 • by boog
364225 in reply to 364221
steenbergh:
This query is bigger than my mother-in-law...
*ba dum tsh*

Re: The Query of Despair

2011-10-24 11:12 • by trtrwtf (unregistered)
364226 in reply to 364217
Me:
They must be using Magento...


I read "Magneto" at first, and I thought, yeah, he's the one who erases disks, isn't he? PERfect.

Re: The Query of Despair

2011-10-24 11:15 • by AdamJS (unregistered)
Is there anything you could do *other* than just rewriting the damn thing?

Re: The Query of Despair

2011-10-24 11:16 • by QJo
Pleasant little exercise. Great fun. On a par with replacing a DB table full of hardwired SQL statements, very similar but with subtle differences, with a stored procedure that built the SQL dynamically. Utterly unmaintainably impossible to read, but saved an entire table.

Re: The Query of Despair

2011-10-24 11:18 • by trtrwtf (unregistered)
364229 in reply to 364227
AdamJS:
Is there anything you could do *other* than just rewriting the damn thing?


Find the guy who wrote it and keelhaul him?

Re: The Query of Despair

2011-10-24 11:20 • by fardle (unregistered)
364230 in reply to 364167
dave:
I feel for the poor sap, his colleague.


FTTFY

captach: iusto... iusto care about efficiency, but now that nobody else does, why should I?

Re: The Query of Despair

2011-10-24 11:21 • by ObiWayneKenobi
364231 in reply to 364176
Steve The Cynic:
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?


We all know the reason why. Because someone wrote a huge query for some "complex" task and it became taboo to fix as the years went on out of fear of breaking something if someone decided to refactor it and pare it down.

Re: The Query of Despair

2011-10-24 11:26 • by Sadly, I've Seen This (unregistered)
364232 in reply to 364158
Matt:
Holy Fuck, no human could write that!


I doubt a human wrote this SQL. It looks like a program-generated query. I could list some programs at our company that make such crappy SQL.

We have a vendor whose software creates just that kind of SQL query. It has that "SELECT (SELECT COUNT(*)...)," sub-query in the SELECT-list pattern. It writes multiple JOINs to the same table to get (key, value) pairs.

We hired an Oracle consultant firm to tune that vendor's SQL. Their response was a firm "Re-write all the queries."

Re: The Query of Despair

2011-10-24 11:28 • by bob (unregistered)
Take off and nuke the site from orbit; it's the only way to be sure.

Re: The Query of Despair

2011-10-24 11:29 • by someone else (unregistered)
364235 in reply to 364186
someone:
Oh my god... it's full of stars.


My Stars.... Its full of Gods!!!

Re: The Query of Despair

2011-10-24 11:33 • by C-Octothorpe
364236 in reply to 364235
someone else:
someone:
Oh my god... it's full of stars.


My Stars.... Its full of Gods!!!
They must be crazy!

Re: The Query of Despair

2011-10-24 11:35 • by Tangoman (unregistered)
Came across a query like that once when asked to track down a bug in a report.

Spent a couple of hours reading through it before locating a comment with the Lead Dev's name on it - he was in the office the next day so I asked for his help on it.

"Oh don't try and debug THAT" he said. "I just sat down one night and dumped my brain out in one big splat of SQL - will probably have to do it all again to fix this issue".

Re: The Query of Despair

2011-10-24 11:40 • by The "Z-Guy" (unregistered)
364239 in reply to 364215
Daniil S.:
trtrwtf:
As a wise man once said, "Just remember: what has been seen, cannot be unseen."
I wish I had a dime for each time I had that experience
Then you could feel like a whore, too.

Re: The Query of Despair

2011-10-24 11:42 • by cappeca (unregistered)
That's no query. It's a space station.

Re: The Query of Despair

2011-10-24 11:44 • by Abdul Alhazred (unregistered)
Curses! Yet another failed attempt to summon the almighty Cthulhu.

Re: The Query of Despair

2011-10-24 11:45 • by justn (unregistered)
Ahh, so this is what happens when you use LINQ to SQL.

Re: The Query of Despair

2011-10-24 11:49 • by Steve The Cynic
364243 in reply to 364231
ObiWayneKenobi:
Steve The Cynic:
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?


We all know the reason why. Because someone wrote a huge query for some "complex" task and it became taboo to fix as the years went on out of fear of breaking something if someone decided to refactor it and pare it down.

That only explains why it is *still* that big. HTF did it get that big in the first place? Complex tasks aren't (shouldn't be) *that* complex. After all, how can you possibly know you have it right? And how do you manage to even write syntactically valid SQL for something that big?

Re: The Query of Despair

2011-10-24 11:52 • by L. (unregistered)
Meh .. why do people even try to debug that kind of crap .. you take the output, reproduce it with "saner" SQL and be done with it, it'll always be shorter and less of a mental health hazard than trying to deal with the squidmonster ...

Re: The Query of Despair

2011-10-24 11:53 • by kikito (unregistered)
Obviously, the solution is removing the query and starting again.

It's just one query.

Re: The Query of Despair

2011-10-24 12:16 • by Chris (unregistered)
364246 in reply to 364224
Coyne:

Yes. The only way it could possibly be worse is if they used column and table names that were keywords:

SELECT SELECT, INTO, FROM, ON FROM JOIN JOIN INTO ON SELECT = INTO AND ON = UNION

(DB2 SQL has no reserved words; given the right table definitions, the above is legal!)

Ow, my eyes. At least in SQL Server you have to enclose field names if they're reserved words, e.g.
select [select], [as] from [from]

Re: The Query of Despair

2011-10-24 12:17 • by GrammerSnarker (unregistered)
364247 in reply to 364205
Let Alex have a the life, we humans can parse it.

Re: The Query of Despair

2011-10-24 12:20 • by someone else (unregistered)
364248 in reply to 364245
Just one query? This thing has more selects than i care to count.

This might even reach a kind of perverse nirvana where it doesn't deviate at all from the median frequency of SQL keywords used in an average project for selects. All joined together.

Re: The Query of Despair

2011-10-24 12:21 • by boog
364249 in reply to 364243
Steve The Cynic:
ObiWayneKenobi:
Steve The Cynic:
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?

We all know the reason why. Because someone wrote a huge query for some "complex" task and it became taboo to fix as the years went on out of fear of breaking something if someone decided to refactor it and pare it down.

That only explains why it is *still* that big. HTF did it get that big in the first place? Complex tasks aren't (shouldn't be) *that* complex. After all, how can you possibly know you have it right? And how do you manage to even write syntactically valid SQL for something that big?
copy-paste-copy-paste-copy-paste

In your over 20 years of programming experience, have you really never watched a new programmer clumsily "write" their first big SQL query?

Re: The Query of Despair

2011-10-24 12:23 • by boog
364250 in reply to 364224
Coyne:
The only way it could possibly be worse is if they used column and table names that were keywords:

SELECT SELECT, INTO, FROM, ON FROM JOIN JOIN INTO ON SELECT = INTO AND ON = UNION

(DB2 SQL has no reserved words; given the right table definitions, the above is legal!)
Thanks. Now I hate DB2 even more.

Re: The Query of Despair

2011-10-24 12:30 • by Mojo Monkeyfish (unregistered)
364251 in reply to 364176
Ironically, YAGNI.

Re: The Query of Despair

2011-10-24 12:32 • by Hortical (unregistered)
As a sociopath, I would like to imagine that this query returns a result set of two columns: the first column contains a unique id number and the second column contains some CSV or XML. The query collects all possible data you could be looking for in all possible forms you could want it. Any time one wants to look up anything in the database, just run this single query and get the row with the id number of the information you want. It will be right there, in easy-to-read CSV/XML form.

No more error-prone writing of multiple queries! Just one, solid query that will receive rigorous testing as everything depends on it! Brillant!

Re: The Query of Despair

2011-10-24 12:33 • by J.C. Vandamme (unregistered)
All you need are my butt cheeks to crack this nut.

Re: The Query of Despair

2011-10-24 12:39 • by PedanticCurmudgeon
364254 in reply to 364241
Abdul Alhazred:
Curses! Yet another failed attempt to summon the almighty Cthulhu.
How do you know it failed?

Re: The Query of Despair

2011-10-24 12:43 • by seriously? (unregistered)
364255 in reply to 364159
Seriously? You can't figure out why an SQL query that is 6 full pages when printed would be a problem?

Re: The Query of Despair

2011-10-24 12:45 • by This is not my real name (unregistered)
Why are the worst WTFs on this site always in Dutch...

Re: The Query of Despair

2011-10-24 12:59 • by Rfoxmich (unregistered)
364257 in reply to 364199
WC:
Am I the only one here that wishes he (or she) could attempt to clean that up and then refactor it? :D

Inside that ugly mess is some beautiful code, just trying to come out.


I doubt it.

Captcha "inhibeo" I inhibeo you from writing crap like that.

Re: The Query of Despair

2011-10-24 12:59 • by frits
364258 in reply to 364256
This is not my real name:
Why are the worst WTFs on this site always in Dutch...
What do you expect from a country full of dikes that little boys have to save from bursting, by plugging them with their thumbs? ;)

Re: The Query of Despair

2011-10-24 13:03 • by I prefer both Microsoft's portable mp3 player and my sister (unregistered)
364260 in reply to 364258
frits:
This is not my real name:
Why are the worst WTFs on this site always in Dutch...
What do you expect from a cuntry full of dykes and little girl have them drolling, by plugging themselves with their thumbs? ;)
I <3 your ;)
Or maybe I 8=====D~~~ your E(o)3

Re: The Query of Despair

2011-10-24 13:31 • by Nagesh
none of you peepul have working experence with Kristal reports. That's one softwear that will write complex querees and mess with your mind all time.

Re: The Query of Despair

2011-10-24 13:40 • by Nagesh (unregistered)
364266 in reply to 364155
Spivonious:
captcha: populus - boring game

Could be. Populous on the other hand is awesome!

Re: The Query of Despair

2011-10-24 13:55 • by Tangurena (unregistered)
364267 in reply to 364176
Steve The Cynic:
HTF does an SQL query get complex enough to be spread over six pages?
I've done a couple report queries over the years that were about half this length. The worst took about 45 minutes to run as it hit multiple servers to do all sorts of aggregates for various columns. About a dozen columns were subqueries themselves. It was also used for billing so it had some crazy logic like "which customers of type X have not submitted a transaction via partner Q during the last calendar month" (with the reasoning being that partner Q would subsidize them if they did 2+ transactions in a month, otherwise we charged the customers).

Re: The Query of Despair

2011-10-24 13:58 • by Anketam
My only hope is that a program generated most of this query and not an actual person. I would laugh if the error is because the query is so long that the program running it is truncating part of the query off, and it just happens to truncate in such a way that the query can still be run.

On a side note I do not think I could even test or try debugging the sql query by running it from a cmd line without it running out of buffer.
« PrevPage 1 | Page 2 | Page 3 | Page 4 | Page 5Next »

Add Comment