• Scott Stroz (unregistered)

    :|

  • sozin (cs)

    ...and somehow I doubt whoever coded it thought about the 255 max # of elems you can have in the IN( ... ) clause

  • shane (cs) in reply to Scott Stroz

    could someone please draw me a map back to sanity?  i seem to be lost...

    yeesh.

  • cm5400 (cs)

    [:S]  Please excuse me while I go speak with Ralph the driver of the porcelin bus.[:D]

  • asdf (unregistered)

    wow, do people just sit down and try to obfuscate?  I mean it's obvious he had to have enough mental faculty to know about all the aspects of the query he used.  a momentary lapse of reason I suppose

  • richleick (cs)

    select programmer.name from programmer inner join dba on
    programmer.name = dba.name where programmer.name not in
    (
        select programmer.name from programmer inner join dba on
            programmer.name = dba.name
            where programmer.name in ('moron', 'excusemaker') or
                  programmer.language in ('foxpro', 'scriptlanguages')
    )

    Sorry, had to get in my shots at the end there.

  • Jonathan B (unregistered)

    Well obviously this would be better then:

    <font>SELECT</font> <font>DISTINCT</font> SMS_G_System_SYSTEM.Name 
    <font>FROM</font> SMS_R_System
    <font>INNER</font> <font>JOIN</font> SMS_G_System_SYSTEM
    <font>ON</font> SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceID
    <font>WHERE</font> SMS_G_System_SYSTEM.Name <font>NOT</font> <font>IN</font> (
    <font>SELECT</font> SMS_G_System_SYSTEM.Name
    <font>FROM</font> SMS_R_System
    <font>INNER</font> <font>JOIN</font> SMS_G_System_SYSTEM
    <font>ON</font> SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId
    <font>WHERE</font> SMS_G_System_SYSTEM.Name <font>IN</font> (
             <font>    SELECT</font> SMS_G_System_SYSTEM.Name 
    <font> FROM</font> SMS_R_System
    <font>INNER</font> <font>JOIN</font> SMS_G_System_SYSTEM
    <font>ON</font> SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceId
    <font>WHERE</font> SMS_G_System_SYSTEM.Name <font>IN</font> (
    <font>'STC0017127'</font>, <font>'STC0015341'</font>, <font>'STC0015342'</font>, <font>'STC0015343'</font>,
    <font>'STC0015344'</font>, <font>'STC0015345'</font>, <font>'STC0015346'</font>, <font>'STC0015347'</font>,
    <font>'STC0015348'</font>, <font>'STC0017117'</font>, <font>'STC0017118'</font>, <font>'STC0017119'</font>,
    <font>'STC0017120'</font>, <font>'STC0017121'</font>, <font>'STC0017122'</font>, <font>'STC0017123'</font>,
    <font>'STC0017124'</font>, <font>'STC0017125'</font>, <font>'STC0017126'</font>, <font>'STC0017128'</font>,
    <font>'STC0019158'</font>, <font>'STC0019160'</font>
    )
    )
    )

  • Daruku (unregistered)

    Sombody please beat this guy with a rubber hose.  There are too many things wrong with this query I won't point them out.

  • fdsa (unregistered) in reply to sozin
    sozin:
    ...and somehow I doubt whoever coded it thought about the 255 max # of elems you can have in the IN( ... ) clause


    The fact that you know this means you probably weren't thinking it had any limits at some point in the past.
  • Adam (unregistered)

    I have a sample query that was done in MS Sql Server.  that is 600 lines.

    it executes in less than 1 second.  so it can't be Sql Server.  

    I still can't figure out why that query (the WTF) is slow.

     

  • Kazrael (cs) in reply to Adam

    I think I'm gonna be sick

  • noturavgdba (cs) in reply to shane

    Unfortunately, if you used a map provided by the writer of that query, you would have to circumnavigate the Earth to get to your next door neighbor's house.

  • John Smallberries (cs)

    <font size="2">looks like a case of 3am zombie syndrome, rather than a serious WTF.</font>

  • Maurits (cs) in reply to sozin
    sozin:
    ...and somehow I doubt whoever coded it thought about the 255 max # of elems you can have in the IN( ... ) clause


    Easily worked around...

    CREATE Table Systems_With_Some_Characteristic
    (   [Name] AS varchar(20)
    )

    -- create foreign key

    INSERT INTO Systems_With_Some_Characteristic ([Name])
    VALUES ('STC0017127')

    INSERT INTO Systems_With_Some_Characteristic ([Name])

    VALUES (...)

    ...
    WHERE SMS_G_System_SYSTEM.Name IN (
        SELECT [Name] FROM Systems_With_Some_Characteristic
    )

  • Martin Carolan (unregistered) in reply to noturavgdba

    At least he didn't use SELECT *[;)]

  • christoofar (unregistered) in reply to Scott Stroz

    <font>//Fixed

    SELECT</font> <font>DISTINCT</font> SMS_G_System_SYSTEM.Name
    <font>FROM</font> SMS_R_System
    <font>INNER</font> <font>JOIN</font> SMS_G_System_SYSTEM
    <font>ON</font> SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceID
    <font style="font-weight: bold;" size="4">WHERE</font><font size="4"> SMS_G_System_SYSTEM.Name </font><font style="font-weight: bold;" size="4">NOT</font><font size="4"> </font><font style="font-weight: bold;" size="4">IN</font> (
    <font>'STC0017127'</font>, <font>'STC0015341'</font>, <font>'STC0015342'</font>, <font>'STC0015343'</font>,
    <font>'STC0015344'</font>, <font>'STC0015345'</font>, <font>'STC0015346'</font>, <font>'STC0015347'</font>,
    <font>'STC0015348'</font>, <font>'STC0017117'</font>, <font>'STC0017118'</font>, <font>'STC0017119'</font>,
    <font>'STC0017120'</font>, <font>'STC0017121'</font>, <font>'STC0017122'</font>, <font>'STC0017123'</font>,
    <font>'STC0017124'</font>, <font>'STC0017125'</font>, <font>'STC0017126'</font>, <font>'STC0017128'</font>,
    <font>'STC0019158'</font>, <font>'STC0019160'</font>
    )

    And my guess is that he doesn't need to do the inner join... unless he's really sure he needs records that are both in SMS_G and SMS_R, which I think are fucked up table names.


    The "SELECT DISTINCT" is fun; you almost always start to see SELECT DISTINCT pop up a lot in queries after a system has aged for a while (nobody goes to the trouble of normalization).


    Fun fun fun!

  • dan jones (unregistered)

    "and for my next number, I will have the database run laps around the moon after retrieving each record!"

  • Rick (cs) in reply to fdsa

    At an interview recently, I was asked, "How many tables can you join in one Sybase query?"
    I responded, "More than you should use, but it used to be 16. I don't know what the current limit is."
    I wasn't impressed with the interviewer.

    Anonymous:
    sozin:
    ...and somehow I doubt whoever coded it thought about the 255 max # of elems you can have in the IN( ... ) clause


    The fact that you know this means you probably weren't thinking it had any limits at some point in the past.
  • mizhi (cs) in reply to Rick
    Rick:
    At an interview recently, I was asked, "How many tables can you join in one Sybase query?"
    I responded, "More than you should use, but it used to be 16. I don't know what the current limit is."
    I wasn't impressed with the interviewer.


    Ooh... trivia questions.  My rule of thumb is to treat those kinds of limits like engineers treat max load limits on bridges and elevators: you can hit the limits if you absolutely have to, but it's better to stay well below them. [:)]
  • DZ-Jay (cs)

    OMG! er... hum... ah.... OMG!!!  ................. O. M. G. ! ! !

        -dZ.

  • Earp (unregistered) in reply to sozin

    sozin:
    ...and somehow I doubt whoever coded it thought about the 255 max # of elems you can have in the IN( ... ) clause

     

    Microsoft SQL Server has no such limitation.

  • Rhett (unregistered) in reply to Adam
    Anonymous:

    I have a sample query that was done in MS Sql Server.  that is 600 lines.

    it executes in less than 1 second.  so it can't be Sql Server.  

    I still can't figure out why that query (the WTF) is slow.

     

    Indexing strategies and table row counts could fully account for the slow performance in this case.  The number of lines in the query is not a good indicator.  This query, although inane, would probably run well if there was indexes on the columns of interest. 

  • John Smallberries (cs) in reply to christoofar
    christoofar:
    The "SELECT DISTINCT" is fun; you almost always start to see SELECT DISTINCT pop up a lot in queries after a system has aged for a while (nobody goes to the trouble of normalization).


    The use of SELECT DISTINCT does not necessarily indicate poor normalization.
    If I want to know which of the 50 states I have customers in,

    select distinct state from customer

    works fine. Typically I would use GROUP BY (obviously if  want to know how many customers in each state) but SQL Server usually generates the same query plan.

    I usually see SELECT DISTINCT when somebody has a bad join condition and can't figure out how to fix it.
  • [humanBob breathing: NO]; (unregistered) in reply to Rhett

    Isn't funny how pretty much all WTFs are written in Microsoft products?

  • David Roberts (unregistered) in reply to christoofar
    Anonymous:
    <FONT size=+0>//Fixed

    SELECT</FONT> <FONT size=+0>DISTINCT</FONT> SMS_G_System_SYSTEM.Name
    <FONT size=+0>FROM</FONT> SMS_R_System
    <FONT size=+0>INNER</FONT> <FONT size=+0>JOIN</FONT> SMS_G_System_SYSTEM
    <FONT size=+0>ON</FONT> SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceID
    <FONT style="FONT-WEIGHT: bold" size=4>WHERE</FONT><FONT size=4> SMS_G_System_SYSTEM.Name </FONT><FONT style="FONT-WEIGHT: bold" size=4>NOT</FONT><FONT size=4> </FONT><FONT style="FONT-WEIGHT: bold" size=4>IN</FONT> (
    <FONT size=+0>'STC0017127'</FONT>, <FONT size=+0>'STC0015341'</FONT>, <FONT size=+0>'STC0015342'</FONT>, <FONT size=+0>'STC0015343'</FONT>,
    <FONT size=+0>'STC0015344'</FONT>, <FONT size=+0>'STC0015345'</FONT>, <FONT size=+0>'STC0015346'</FONT>, <FONT size=+0>'STC0015347'</FONT>,
    <FONT size=+0>'STC0015348'</FONT>, <FONT size=+0>'STC0017117'</FONT>, <FONT size=+0>'STC0017118'</FONT>, <FONT size=+0>'STC0017119'</FONT>,
    <FONT size=+0>'STC0017120'</FONT>, <FONT size=+0>'STC0017121'</FONT>, <FONT size=+0>'STC0017122'</FONT>, <FONT size=+0>'STC0017123'</FONT>,
    <FONT size=+0>'STC0017124'</FONT>, <FONT size=+0>'STC0017125'</FONT>, <FONT size=+0>'STC0017126'</FONT>, <FONT size=+0>'STC0017128'</FONT>,
    <FONT size=+0>'STC0019158'</FONT>, <FONT size=+0>'STC0019160'</FONT>
    )

    And my guess is that he doesn't need to do the inner join... unless he's really sure he needs records that are both in SMS_G and SMS_R, which I think are fucked up table names.


    The "SELECT DISTINCT" is fun; you almost always start to see SELECT DISTINCT pop up a lot in queries after a system has aged for a while (nobody goes to the trouble of normalization).


    Fun fun fun!

    lol, gee, your like 1 order smarter.

  • Ross Day (cs) in reply to Rhett

    I would imagine that if the query were analyzed and re-written with joins rather than many IN and NOT IN clauses that it woudl be much faster.  IN and NOT IN seem inherently slow, and so are subqueries.

    I know it's a different DBMS, but I once helped optimize an Access query from about 15 seconds down to 1.5 just by rewriting IN and NOT IN subqueries as good joins that did the same thing.  I've seen similar optimizations make things faster in some brief experiences with Oracle, however YMMV.

  • OMG (cs)

    Wow - Ouch, that hurts. I hope it isn't contagious.

  • Salandur (unregistered)

    [+o(] never seen anything horreble than this [8-)]

  • whojoedaddy (cs)

    I look at this and think ... what on earth? and then I start to cry... [:'(]

  • bugsRus (cs) in reply to Martin Carolan
    Anonymous:
    At least he didn't use SELECT *[;)]


    and they did use a where clause
  • bugsRus (cs) in reply to Salandur
    Anonymous:
    [+o(] never seen anything horreble than this [8-)]


    Then you haven't been paying attention.
  • El Duderino (cs) in reply to OMG

    I'll probably get flamed for this but:  Without proper insight into the database design and the restrictions placed on the developers it's tough to say if the WTF is on the developers or the database.

    Depending on the the structure or lack-there-of of the 2 tables and the business rules imposed on them, a query like this may be required to get the proper result. 

    Now I'm not saying that the IN list shouldn't have been stored in a table somewhere, but the developers may not have been allowed to alter the schema in this case.

  • El Duderino (cs) in reply to christoofar
    Anonymous:
    <FONT size=+0>//Fixed

    SELECT</FONT> <FONT size=+0>DISTINCT</FONT> SMS_G_System_SYSTEM.Name
    <FONT size=+0>FROM</FONT> SMS_R_System
    <FONT size=+0>INNER</FONT> <FONT size=+0>JOIN</FONT> SMS_G_System_SYSTEM
    <FONT size=+0>ON</FONT> SMS_G_System_SYSTEM.ResourceID = SMS_R_System.ResourceID
    <FONT style="FONT-WEIGHT: bold" size=4>WHERE</FONT><FONT size=4> SMS_G_System_SYSTEM.Name </FONT><FONT style="FONT-WEIGHT: bold" size=4>NOT</FONT><FONT size=4> </FONT><FONT style="FONT-WEIGHT: bold" size=4>IN</FONT> (
    <FONT size=+0>'STC0017127'</FONT>, <FONT size=+0>'STC0015341'</FONT>, <FONT size=+0>'STC0015342'</FONT>, <FONT size=+0>'STC0015343'</FONT>,
    <FONT size=+0>'STC0015344'</FONT>, <FONT size=+0>'STC0015345'</FONT>, <FONT size=+0>'STC0015346'</FONT>, <FONT size=+0>'STC0015347'</FONT>,
    <FONT size=+0>'STC0015348'</FONT>, <FONT size=+0>'STC0017117'</FONT>, <FONT size=+0>'STC0017118'</FONT>, <FONT size=+0>'STC0017119'</FONT>,
    <FONT size=+0>'STC0017120'</FONT>, <FONT size=+0>'STC0017121'</FONT>, <FONT size=+0>'STC0017122'</FONT>, <FONT size=+0>'STC0017123'</FONT>,
    <FONT size=+0>'STC0017124'</FONT>, <FONT size=+0>'STC0017125'</FONT>, <FONT size=+0>'STC0017126'</FONT>, <FONT size=+0>'STC0017128'</FONT>,
    <FONT size=+0>'STC0019158'</FONT>, <FONT size=+0>'STC0019160'</FONT>
    )

    And my guess is that he doesn't need to do the inner join... unless he's really sure he needs records that are both in SMS_G and SMS_R, which I think are fucked up table names.


    The "SELECT DISTINCT" is fun; you almost always start to see SELECT DISTINCT pop up a lot in queries after a system has aged for a while (nobody goes to the trouble of normalization).


    Fun fun fun!

    If case sensitivity is turned on on the database and the field name "ResourceId" in the sub-select of the original query isn't a typo from Alex's anonymizer the query may be joining to a completely seperate (albeit poorly named) field. 

    I know there's a WTF in there but without more information we can't say where it is.

  • John Smallberries (cs) in reply to Ross Day
    Ross Day:

    ...optimize an Access query...

    Now that's funny!

  • mizhi (cs) in reply to David Roberts
    Anonymous:

    lol, gee, your like 1 order smarter.



    I love it when jackasses make fun of the intelligence of others and they can't even get simple contractions correct.

    your != you're

    Or perhaps you're (see?  you are = you're) using the code from yesterday's wtf to remove apostrophes.
  • boingy baxter (unregistered) in reply to mizhi

    I remember when Unify only allowed a join of 5 tables... but wouldn't tell you not to use more.[<:o)]

  • boingy baxter (unregistered) in reply to mizhi

    I remember when Unify only allowed a join of 5 tables... but wouldn't tell you not to use more.[<:o)]

  • Hank Miller (unregistered) in reply to mizhi
    mizhi:

    Ooh... trivia questions.  My rule of thumb is to treat those kinds of limits like engineers treat max load limits on bridges and elevators: you can hit the limits if you absolutely have to, but it's better to stay well below them. [:)]


    Yeah, but you are an engineer (at heart anyway, for all I know your are a musican who programs on the side).   In the real world limits are meaningless, if it looks like it will fit, it must fit.  Sql might specify a limit, but your text editor will let your enter as much as you want, so you enter as much as you want.  

    This is also the cause of a lot of buffer overflow security bugs.  Engineers tend to forget that people are not reasonable about obeying limits.

    I too am an engineer, which is why in college I would not get on one elevator - I never could be sure that 15 people wouldn't get on behind me and overload it.   (Several people got Fs on one final because being stuck in an overloaded elevator is not an excuse to miss a final test)   When the elevator opened I just waited for the doors to close, and it to move on a little before pressing the button again.
  • Mung Kee (unregistered) in reply to [humanBob breathing: NO];
    Anonymous:
    Isn't funny how pretty much all WTFs are written in Microsoft products?


    I beg to differ.  I see much WTF-ery in Java, Perl and tools like Ant pretty regularly.  Programming languages don't kill applications...$hitty programmers kill applications.  Maybe I'll port "theDaily" to Java.
  • mizhi (cs) in reply to Hank Miller
    Anonymous:

    This is also the cause of a lot of buffer overflow security bugs.  Engineers tend to forget that people are not reasonable about obeying limits.


    I think one trick engineers use is to tell people that an elevator can handle only 10 people and design it to work with 20.  [:)]
  • Maurits (cs) in reply to [humanBob breathing: NO];
    Anonymous:
    Isn't funny how pretty much all WTFs are written in Microsoft products?


    I'd say the percentage of WTFs written in Microsoft products is roughly the same as the percentage of code-in-general written in Microsoft products.
  • mizhi (cs) in reply to Mung Kee
    Anonymous:

    I beg to differ.  I see much WTF-ery in Java, Perl and tools like Ant pretty regularly.  Programming languages don't kill applications...$hitty programmers kill applications.  Maybe I'll port "theDaily" to Java.


    Put another way, any language that is turing complete can be used to create a wtf. [:)]
  • phelyan (cs)

    I like the process of blaming everybody else before somebody finally goes to check what's actually happening...

  • rogthefrog (cs)

    About the abuse of "distinct" - sometimes businesses grow too fast and their IT teams are overstretched. Tool/table/db ownership is passed around amongst the IT team as some folks stop having the time to keep an eye on every single thing. Documentation never gets written because nobody has the time. Software systems grow and grow and nobody really knows what all affects what. So inevitably you wind up with seemingly innocuous changes or new tools or updates or whatnot that wind up fucking up the erstwhile integrity of your data, and once-unique keys become no longer unique, normalization gets lost, etc.

    It's no excuse, but it happens in small IT shops, even when the average IQ is well above 150.

  • tufty (cs) in reply to rogthefrog

    rogthefrog wrote:

    About the abuse of "distinct" - sometimes businesses grow too fast...but it happens in small IT shops...

    s/sometimes/pretty much always/ s/small/nearly all/

    In my experience, anyway.

    Simon

  • felix (cs) in reply to tufty

    Have you noticed that all SQL-related WTFs seem to be about ridiculously long queries? I mean, come on, an 175-line SELECT? That's bound to perform poorly and have at least one bug lost in there. Are you going to tell me MS SQL Server has no views? And even if it doesn't (MySQL had this limitation up to version 4.0), what about temporary tables? What about simulating the join in code? As a wise man once said, there are only solutions.

    I did the same kind of mistake once with PosgreSQL. I was still learning, and though the server did support CREATE VIEW, I cheerfully ignored this feature. My little monster query was about 50 lines long, took 15 minutes to run and filled most of the server's memory. After breaking it in parts, the time went down to 30 secs, and the resulting data, once put together in PHP, was more accurate (the specs were odd, but that's a different story).

    Morale: do your job and let the computer do its own. Then everything will work better.

  • Uriel (unregistered) in reply to felix

    The problem is you can never tell what the specs will be.. I worked on a project that was required to generate it's SQLs in runtime. the output was from a 10 line to 15 pages of SQL. However, with the help of a good DBA and some common sense it executed in reasnoble times (< 1 sec).

  • noone (unregistered) in reply to sozin
    sozin:
    ...and somehow I doubt whoever coded it thought about the 255 max # of elems you can have in the IN( ... ) clause


    What crappy database is that then?  Oracle can cope with 1000 items (now there's a number clearly pulled out of nowhere), and SQL Server doesn't advertise any limit so is probably limited to the maximum length of a SQL statement (default 256KB)
  • jzlondon (unregistered) in reply to [humanBob breathing: NO];

    Anonymous:
    Isn't funny how pretty much all WTFs are written in Microsoft products?

    Oh shoot the tool maker.  That makes sense.

    What is it with people who confuse a dislike of Microsoft with an opinion that their technology is poor?  Sloppy thinking.

    SQL Server is actually a seriously good product, albeit one written by an arguably evil company.

     

  • SteveM (cs) in reply to [humanBob breathing: NO];
    Anonymous:
    Isn't funny how pretty much all WTFs are written in Microsoft products?


    Has anyone done any analysis on the average time it takes a DailyWTF thread to move from 'this particular piece of code is a WTF' to 'Microsoft is evil?'

    Or is the real explanation that the Daily WTF forums are actually just being used in a techie version of Mornington Crescent?

Leave a comment on “Are You IN Or NOT IN?”

Log In or post as a guest

Replying to comment #:

« Return to Article