| « Prev | Page 1 | Page 2 | Next » |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 09:03
•
by
Rodnas
(unregistered)
|
|
((((Frist) AND (Fist)) AND (Frits)) AND NOT (First))
|
|
I assume Ben just removed all ( and ) characters in the WHERE clause, and used them to build a Lisp app that commands a robot to slap the original developer.
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 09:09
•
by
Dijkie
(unregistered)
|
|
Not only is this SQL nested way to deeply, but it's also unnecessarily nested way to deeply.
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 09:11
•
by
Burpy
(unregistered)
|
|
Wonder if the guy wrote this by hand...
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 09:13
•
by
Anonymous
(unregistered)
|
|
The obvious solution is to build a balanced binary tree out of the conditions. That way, the nesting depth drops to log n.
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 09:14
•
by
EatenByAGrue
(unregistered)
|
|
Rule of Upper Limits: if you have to ask what the upper limit is, you're probably doing it wrong.
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 09:15
•
by
Yazeran
(unregistered)
|
|
Oh my...
Not only nested way too deeply (and pointlessly nested as pointed out above) but how would that code ever produce a 'true' return (that is any record at all) with all those AND's on that Task.OwnerTeam row ??? The condition would only return true if all those placeholders were equal, in which case the whole exercise is pointless (that could presumably have been determined by the application layer instead. And bonus points, who in their right mind would create a stored procedure whti 500+ placeholders?? (smells of generated code) Yours Yazeran Plan; To go to Mars one day with a hammer |
|
That gave me scroll wheel finger fatigue.
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 09:44
•
by
Alekz
(unregistered)
|
I bet it was left hand only... |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 09:56
•
by
krupa
(unregistered)
|
|
How frickin' lazy do you have to be to write something like that, look at it, and not come up with a better way to do it?
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 10:07
•
by
Flavio de Sousa
(unregistered)
|
|
What F#%&ing ORM created this? LINQ? Hibernate?
Do you have the calling code? |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 10:11
•
by
Alin
(unregistered)
|
|
Can i have the values of @P1 to @P5xx ? I'd like to write a test plan for this...
This must be auto generated code, if not then I can only recommend a "SQL for dummies" book... |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 10:16
•
by
chlb
(unregistered)
|
|
The code actually looks like a hammer.
"If you only carry a hammer then all problems are nails." - from How To Write Unmaintainable Code by Roedy Green |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 10:18
•
by
Ben
(unregistered)
|
|
Wait... Wouldn't that return nothing unless all of the saved values were the same?
|
|
Holding down the Down arrow key on this post gave me a slowly growing fuzzy feeling.
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 10:25
•
by
LU
(unregistered)
|
|
This made me actually say "What Da F**k" out loud...
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 10:48
•
by
Zylon
|
In boring business apps, maybe. In interesting real-time apps, limits get pushed constantly. Especially ones relating to graphics processing. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 10:55
•
by
dkf
|
In fact, there's 512 useless parameters. Since that's 2⁹, it smells very fishy indeed. I'd guess that the form grid that this was built on had a maximum width of 256 and (everyone's favorite arbitrary limit) and when making the binding, two whole rows were accidentally selected. The poor table field is probably just an innocent bystander that happened to be first in the selection box in the binding dialog. The fix is to just slap someone and throw the code away. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 10:56
•
by
DBMS developer
(unregistered)
|
|
That's all SQL server fault, there's no reason why it couldn't flatten nested AND's - the depth doesn't matter. If applications would always generate perfect queries, why would anybody bother to work on a query optimizer ?
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 10:57
•
by
it can be fixed
(unregistered)
|
|
Somebody shold to sprinkle this with salt. But who?
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 11:00
•
by
C.K.
(unregistered)
|
|
So is this a new technique for real-time graphics processing in SQL then?
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 11:06
•
by
Mason Wheeler
|
+1 |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 11:26
•
by
Rob
(unregistered)
|
|
This looks like it was generated with LINQ to SQL
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 11:31
•
by
Jaime
|
It probably failed at the parse step. The server couldn't make an in-memory graph of the query to even begin optimizing it. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 11:37
•
by
Henning Makholm
(unregistered)
|
Before it can flatten the expression, it needs to parse it. Many LALR parser generators hardcode size limits for the parser stack. Google suggests that some legacy yacc implementation default to 150 ... |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 11:40
•
by
MikeCD
(unregistered)
|
Could be anti-DoS code... eg. imagine for whatever reason that each level of nesting created a recursive call in the parser. If you allowed users to use infinite nesting, you could exceed the stack limit and crash the program. I don't begrudge a programmer who puts a (obnoxiously high) limit on a situation he can imagine being misused by a client. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 11:43
•
by
Zica
(unregistered)
|
|
Wow. That SQL query made be go blind for a few minutes!
I guess the person (or entity) has never heard of regular expressions, the LIKE keyword, or other methods that actually make sense. Also, shouldn't those be OR conditions instead of AND conditions (since Task.OwnerTeam can be equal to only 1 value at a time)? Oh wait, this must be one of those new-fangled "expert systems" where "The business users can just tell the system what to do". Bah, who needs logic anyway. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 11:52
•
by
jmucchiello
|
|
This is my favorite part:
WHERE (Task.TaskType = @P514) AND (Task.Status = @P1) This existed as a procedure with a status parameter followed by the useless 512 parameters. Someone then went in and added the TaskType parameter to the END of the parameter list. I'd love to see the code where this monstrosity is called. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 12:14
•
by
Bim Job
(unregistered)
|
I'd assume you've never had to deal with a "boring business app" that was specified with boring business limits like service levels and was architected on a 24 cpu Solaris based on a minimum throughput of 4,000 transactions a second, then. (Not stupid database transactions. Actual online transactions.) Some of us in the business world also have to count cpu cycles and kernel calls, you know. But you're right. Business or scientific or real time or whatever, Mr Grue is being, at the kindest, disingenuous. (1) If you don't have a grue what the upper limit is, then you're probably programming in PHP. (2) If you don't even know that an upper limit exists, then you should go back to CompSci 101. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 12:19
•
by
Mike
(unregistered)
|
|
I bet the original coder was paid by the parenthesis. Frickin' metrics.
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 12:35
•
by
a former big-fiver
(unregistered)
|
|
Probably a consultant, paid by the line.
Remember, the calling code has to set up all those parameters, too, at one per line for 'readability'. Captcha: Similis I did stuff similis to this when I was contracting... |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 13:12
•
by
Resistance
|
|
What bothers me is, why would anyone test for @P2 == @P3 == ... == @P513 == Task.OwnerTeam ?
|
|
If you squint your eyes, it looks like ASCII art of two very tall buildings.
Maybe it is a computer Rorschach test! |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 13:50
•
by
SQLDave
|
FTW |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 14:51
•
by
rfsmit
|
640k should be enough for you, eh? |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 15:06
•
by
Nobody
(unregistered)
|
Grue is absolutely right. He didn't say you ARE doing it wrong, but that you PROBABLY ARE doing it wrong. Big difference. It means that if you run into these limits that you should take a step back and think and make sure that this is really the best way to do it. Then is it is, by all means, full speed ahead. All of you bashing grue are dunderheads if you don't do this. I've fixed code written by people like you and it's not a pretty sight. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 15:15
•
by
Grovesy
|
|
Seen something like this before generated by an ORM...
an AND expression wrapped all right hand operands with brackets.. so you ended up with crazy stuff like.. <expression> AND (<expression AND (<expression> AND (<expression>))) |
|
Well, (A), it looks to be generated code (TM)(R)(C) by a generator that is bright as your average gatepost...
...and (B) how many times do we really need to test a column to make sure it contains a particular value? If on the one hand, the variables are all identical, then we have this:
If the variables are not identical then we can get the identical result with a much simpler query:
Either way, a "code generator" intelligence upgrade is suggested. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 15:36
•
by
bunnyman
(unregistered)
|
|
Anyone who can create code with so many left parens without making an ASCII art fish is missing the point.
That's not even close to the 511 in the code sample. It could be so much better. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 15:45
•
by
silent d
(unregistered)
|
|
This query is wrong from the first line, even before it gets to all the parameter nonsense.
SELECT DISTINCT COUNT(Task.RecId) The COUNT function returns a single value, so the DISTINCT keyword is redundant in this case. If the intended goal was to count unique instances of RecID within the result set, then the correct syntax would be: SELECT COUNT(DISTINCT Task.RecId) |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 16:11
•
by
Ben
(unregistered)
|
|
Operations in SQL that are associative:
Any associative scalar operations, either in calculated columns or in WHERE cluases. So all boolean operations in the WHERE clause are associative. Implicit and explicit Cartesian products, natural join, are all associative, so FROM A, B, C or FROM A CROSS JOIN B CROSS JOIN C or FROM A NATURAL JOIN B NATURAL JOIN C. Inner join is associative, though you have to move the ON clause. (A JOIN B ON A.x = B.x) JOIN C ON B.x = C.x is the same as A JOIN (B JOIN C ON B.x = C.X) ON A.X = B.X Left and right outer joins aren't. I think full outer joins are with the same caveats as inner joins. UNION DISTINCT and UNION ALL are associative because the two equivalents from the bag algebra are associative. (Some DBMSs will do weird things if column names are different, though, when they ought to throw an error.) INTERSECT is associative. EXCEPT is not. Now, admittedly, it can get hairy. But those are pretty much the rules for associativity. So why can't SQL generators clean up all the damned parens?! |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 16:38
•
by
hoodaticus
(unregistered)
|
|
Yeah, that has to be generated code.
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 17:54
•
by
Pierre
(unregistered)
|
|
Excellent observation; I must agree with it.
Also very humorous. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 19:35
•
by
tgape
|
From my (admittedly very limited) exposure to real-time apps, I believe it *especially* applies there, so long as it's generic programming language limits, rather than CPU limits or a real-time programming language. Admittedly, I've only worked a bit with real-time data processing and real-time machine control, not real-time graphics. (Actually, real-time graphics? WTF?) Pushing CPU limits (for example, wanting to use more registers than the CPU has) is kinda to be expected with real-time. Both of the real-time programming languages I've worked with were exceedingly difficult to work within their limits - as if the people designing them had no intention of letting anyone try anything complicated with their language. The one time I had to do actual real-time programming, I attempted to use the suggested language for about a week before I took a step back, read up a bit on the embedded hardware, found it could take C programs compiled for an ARM processor linked against their SDK, and I had a program that worked within a couple of hours that ran in less time than the existing code with the additional functionality I was adding included. (This is not claiming that the real-time language was slow, just that I was unfamiliar with it and it was restrictive; also, I'm fairly good with C.) It *is* possible that it also applies to stuff in some specific real-time languages, because it may be just using those languages constitutes "doing it wrong". (I do not have any languages in mind for this comment, I'm just offering it as a theoretical statement. Just like people who write other software can be TRWTF, people who write languages can be also.) |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 19:39
•
by
Anynomous Coward
(unregistered)
|
|
Even if it was auto generated... Use IN.
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 20:19
•
by
tgape
|
If you want what the coder almost certainly wanted - yes. Also, store the items in a result set, so you can just specify a single variable. However, that's not what was coded. What they coded was more of an "EQUALS ALL OF THESE THINGS". |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 22:24
•
by
DaveK
|
Rule of Outer Limits: We will control the horizontal. We will control the vertical. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 22:36
•
by
JV
(unregistered)
|
|
This is a pretty standard WTF...
Obviously, the programmer was adding a parameter every time a new 'OwnerTeam' was created to be able to pass the new value in his query. This only broke when he exceeded 512 parameters, at which point he had to call the help desk. |
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 22:51
•
by
frits
|
|
C is the de facto language of real time apps, so your instincts were correct. Many vendors supply C++ compilers as well, which is what I prefer. Real-time graphics are used for electronic instruments such as digital oscilloscopes, spectrum analyzers, and other equipment that have to display transient and dynamic signals.
|
Re: SQL Error 191: Nested Way Too F#%&ing Deeply
2009-12-28 23:16
•
by
MinorHavoc
(unregistered)
|
|
|
| « Prev | Page 1 | Page 2 | Next » |