Comment On The Query of Despair

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.

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.

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!!!

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.

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

As the BASIC interpreter would say: ?REDO FROM START

They must be using Magento...

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.

Looks like a great query for EXPLAIN.
What kind of database is configured with a statementbuffer this big?

Presented to you by the movement, "Occupy Query Engine."

This query is bigger than my mother-in-law...

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.

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!)

steenbergh:

This query is bigger than my mother-in-law...

*ba dum tsh*

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.

Is there anything you could do *other* than just rewriting the damn thing?

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.

AdamJS:

Is there anything you could do *other* than just rewriting the damn thing?

Find the guy who wrote it and keelhaul him?

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?

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.

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."

Take off and nuke the site from orbit; it's the only way to be sure.

someone:

Oh my god... it's full of stars.

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

someone else:

someone:

Oh my god... it's full of stars.

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

They must be crazy!

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".

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.

That's no query. It's a space station.

Curses! Yet another failed attempt to summon the almighty Cthulhu.

Ahh, so this is what happens when you use LINQ to SQL.

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?

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 ...

Obviously, the solution is removing the query and starting again.

It's just one query.

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]

Let Alex have a the life, we humans can parse it.

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.

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?

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.

Ironically, YAGNI.

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!

All you need are my butt cheeks to crack this nut.

Abdul Alhazred:

Curses! Yet another failed attempt to summon the almighty Cthulhu.

How do you know it failed?

Seriously? You can't figure out why an SQL query that is 6 full pages when printed would be a problem?

Why are the worst WTFs on this site always in Dutch...

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.

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? ;)

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

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.

Spivonious:

captcha: populus - boring game

Could be. Populous on the other hand is awesome!

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).

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.