• Rodnas (unregistered)

    ((((Frist) AND (Fist)) AND (Frits)) AND NOT (First))

  • (cs)

    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.

  • Dijkie (unregistered)

    Not only is this SQL nested way to deeply, but it's also unnecessarily nested way to deeply.

  • Burpy (unregistered)

    Wonder if the guy wrote this by hand...

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

  • EatenByAGrue (unregistered)

    Rule of Upper Limits: if you have to ask what the upper limit is, you're probably doing it wrong.

  • 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

  • (cs)

    That gave me scroll wheel finger fatigue.

  • Alekz (unregistered) in reply to Burpy
    Burpy:
    Wonder if the guy wrote this by hand...

    I bet it was left hand only...

  • 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?

  • Flavio de Sousa (unregistered)

    What F#%&ing ORM created this? LINQ? Hibernate?

    Do you have the calling code?

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

  • 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
  • Ben (unregistered)

    Wait... Wouldn't that return nothing unless all of the saved values were the same?

  • (cs)

    Holding down the Down arrow key on this post gave me a slowly growing fuzzy feeling.

  • LU (unregistered)

    This made me actually say "What Da F**k" out loud...

  • (cs) in reply to EatenByAGrue
    EatenByAGrue:
    Rule of Upper Limits: if you have to ask what the upper limit is, you're probably doing it wrong.
    In boring business apps, maybe. In interesting real-time apps, limits get pushed constantly. Especially ones relating to graphics processing.
  • (cs) in reply to Yazeran
    Yazeran:
    And bonus points, who in their right mind would create a stored procedure whti 500+ placeholders?? (smells of generated code)
    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.

  • 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 ?

  • it can be fixed (unregistered)

    Somebody shold to sprinkle this with salt. But who?

  • C.K. (unregistered) in reply to Zylon

    So is this a new technique for real-time graphics processing in SQL then?

  • (cs) in reply to DBMS developer
    DBMS developer:
    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 ?

    +1

  • Rob (unregistered)

    This looks like it was generated with LINQ to SQL

  • (cs) in reply to DBMS developer
    DBMS developer:
    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 ?
    It probably failed at the parse step. The server couldn't make an in-memory graph of the query to even begin optimizing it.
  • Henning Makholm (unregistered) in reply to DBMS developer
    DBMS developer:
    That's all SQL server fault, there's no reason why it couldn't flatten nested AND's
    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 ...
  • MikeCD (unregistered) in reply to DBMS developer
    DBMS developer:
    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 ?
    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.

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

  • (cs)

    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.

  • Bim Job (unregistered) in reply to Zylon
    Zylon:
    EatenByAGrue:
    Rule of Upper Limits: if you have to ask what the upper limit is, you're probably doing it wrong.
    In boring business apps, maybe. In interesting real-time apps, limits get pushed constantly. Especially ones relating to graphics processing.
    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.

  • Mike (unregistered)

    I bet the original coder was paid by the parenthesis. Frickin' metrics.

  • a former big-fiver (unregistered) in reply to Mike

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

  • (cs)

    What bothers me is, why would anyone test for @P2 == @P3 == ... == @P513 == Task.OwnerTeam ?

  • (cs)

    If you squint your eyes, it looks like ASCII art of two very tall buildings.

    Maybe it is a computer Rorschach test!

  • (cs) in reply to EatenByAGrue
    EatenByAGrue:
    Rule of Upper Limits: if you have to ask what the upper limit is, you're probably doing it wrong.

    FTW

  • (cs) in reply to EatenByAGrue
    EatenByAGrue:
    Rule of Upper Limits: if you have to ask what the upper limit is, you're probably doing it wrong.
    640k should be enough for you, eh?
  • Nobody (unregistered) in reply to rfsmit
    rfsmit:
    EatenByAGrue:
    Rule of Upper Limits: if you have to ask what the upper limit is, you're probably doing it wrong.
    640k should be enough for you, eh?
    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.
  • (cs) in reply to Rodnas

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

  • (cs)

    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:

    SELECT COUNT(*)
FROM TABLE
WHERE Task.OwnerTeam = 'REDUNDANT' 
  AND Task.OwnerTeam = 'REDUNDANT'
  AND Task.OwnerTeam = 'REDUNDANT'
  AND Task.OwnerTeam = 'REDUNDANT'
      ... (ad almost infinitum)

    If the variables are not identical then we can get the identical result with a much simpler query:

    SELECT COUNT(*)
FROM TABLE
WHERE 1 = 0

    Either way, a "code generator" intelligence upgrade is suggested.

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

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

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

  • hoodaticus (unregistered) in reply to Yazeran

    Yeah, that has to be generated code.

  • Pierre (unregistered) in reply to EatenByAGrue

    Excellent observation; I must agree with it.

    Also very humorous.

  • (cs) in reply to Zylon
    Zylon:
    EatenByAGrue:
    Rule of Upper Limits: if you have to ask what the upper limit is, you're probably doing it wrong.
    In boring business apps, maybe. In interesting real-time apps, limits get pushed constantly. Especially ones relating to graphics processing.

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

  • Anynomous Coward (unregistered)

    Even if it was auto generated... Use IN.

  • (cs) in reply to Anynomous Coward
    Anynomous Coward:
    Even if it was auto generated... Use IN.

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

  • (cs) in reply to EatenByAGrue
    EatenByAGrue:
    Rule of Upper Limits: if you have to ask what the upper limit is, you're probably doing it wrong.
    Rule of Outer Limits: We will control the horizontal. We will control the vertical.
  • 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.

  • (cs) in reply to tgape

    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.

  • MinorHavoc (unregistered) in reply to tgape
    tgape:
    (Actually, real-time graphics? WTF?)
    Think "glass cockpit".

Leave a comment on “SQL Error 191: Nested Way Too F#%&ing Deeply”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article