An SQL WTF from the Department of Redundancy Dept.



  •  I ran across this gem while trying to track down a bug today. 


                  SELECT *
    FROM bm_attribute_sets_l
    WHERE bm_attribute_set_id IN (
    SELECT bm_attribute_set_id
    FROM bm_attribute_sets_l
    WHERE bm_substrand_id = ?
    AND bm_strand_id = ?
    AND bm_attribute_id = ?
    AND bm_attribute_sets_l.status_id = 1
    )

    Yes, you read that correctly. Instead of just specifying the columns in the WHERE clause directly, it uses an IN with a subselect. For no conceivable reason at all. It works, though, for a certain definition of "works." My co-worker helpfully suggested the following revision:


                  SELECT *
    FROM bm_attribute_sets_l
    WHERE bm_attribute_set_id IN (
    SELECT bm_attribute_set_id
    FROM bm_attribute_sets_l
    WHERE bm_attribute_set_id IN (
    SELECT bm_attribute_set_id
    FROM bm_attribute_sets_l
    WHERE bm_substrand_id = ?
    AND bm_strand_id = ?
    AND bm_attribute_id = ?
    AND bm_attribute_sets_l.status_id = 1
    )
    )

    Har har har.



  •  



  • Useless use of subqueries. Wow.

    Kind of the opposite of the other kind of abuse: sending tons of queries to "simulate" subqueries in the subquery-deprived MySQL < 4.x versions.

    But why stop there?You could put one of the WHERE statements in each nested IN level!



  • @danixdefcon5 said:

    Kind of the opposite of the other kind of abuse: sending tons of queries to "simulate" subqueries in the subquery-deprived MySQL < 4.x versions.

    What the hell are you talking about?  Why would you need anything other than the subquery executed on its own with the results pulled back to the client software to generate the outer query?  I'm glad real subqueries were added to MySQL but it's not like my life was horribly difficult before then. 



  •  Ah yes, the classic Nested Where In Anti-Pattern.



  •  I think this is a testament to the query optimisers in modern databases. They take all sorts of bantha pudu SQL code and turn it into something that runs great. Of course, that's not an excuse for database programmers to be writing such pudu in the first place.

     B



  • I've been guilty of leaving this sort of shit behind one or twice (although never so staringly obvious as this example).  It often arises from needlessly overcomplicating things (for example when you are investigating a new database) then subsequently simplifying.

    Not excusing it, but it can happen.  Usually after a request like "you know the <database you've never looked at> database?  I need a [stupidly complex] report out of it for yesterday"



  • @Jeff S said:

     Ah yes, the classic Nested Where In Anti-Pattern.

    No, it's worse than that. The "Nested Where-In" Anti-pattern comes about because people are scared of JOINs. (I can sort of understand that for a programmer with no DB experience who has been thrown into the deep end to do some SQL programming).

    In this case, no join is needed, you just need to delete the "WHERE bm_attribute_set_id IN (SELECT bm_attribute_set_id FROM bm_attribute_sets_l WHERE " text (and the final ")") and it will still work as you want.



  • @havokk said:

     I think this is a testament to the query optimisers in modern databases. They take all sorts of bantha pudu SQL code and turn it into something that runs great. Of course, that's not an excuse for database programmers to be writing such pudu in the first place.

     B

     

    As SQL is not a programming language, how can you justify the use of the word "programmers" in your comment?



  • @mrprogguy said:

    As SQL is not a programming language, how can you justify the use of the word "programmers" in your comment?
    Stop trolling.

    I seem to recall a number of people stating that thinking aboot SQL in terms of programs that extract data really helps to avoid a lot of the usual WTFery.

    More importantly, SQL chunks seem to fit the "program - noun (computer science) a sequence of instructions that a computer can interpret and execute;" definition well enough, Turing-complete or not.



  •  possible artififact of old functionality?



  • @mrprogguy said:

    As SQL is not a programming language, how can you justify the use of the word "programmers" in your comment?

    I'm just curious, what do you consider the criteria for being a programming language?  I have a pretty broad definition of programming language, one that includes languages like SQL and HTML.  I suppose it is ultimately quibbling over minor points.



  • @friedo said:

    It works, though, for a certain definition of "works."
     

    Sure, if that definition of "works" is "slower than molasses, hard to maintain, and written by an idiot". 

    Hopefully for you, the person who wrote that is no longer "working" there. :-) 



  • @mrprogguy said:

    @havokk said:

     I think this is a testament to the query optimisers in modern databases. They take all sorts of bantha pudu SQL code and turn it into something that runs great. Of course, that's not an excuse for database programmers to be writing such pudu in the first place.

     B

     

    As SQL is not a programming language, how can you justify the use of the word "programmers" in your comment?

     

    Even LOGO is a programming language.  (Anyone remember that one?)




  •  @Kazan said:

     possible artififact of old functionality?

    Potentially. Or, cut and paste and modified from elsewhere because it just "works".



  • @Jeff S said:

    Even LOGO is a programming language.  (Anyone remember that one?)

    Imma chargin mah turtle! 



  • @morbiuswilters said:

    I have a pretty broad definition of programming language, one that includes languages like SQL and HTML.

     

    I'm curious about what your definition is that would include HTML.  SQL I can understand - it has at least the basic elements of statements, variables, and functions - but HTML is more like a document format.  Would you also refer to CSS and XML as programming languages?  What about INI and other configuration files?



  • @Aaron said:

    I'm curious about what your definition is that would include HTML.  SQL I can understand - it has at least the basic elements of statements, variables, and functions - but HTML is more like a document format.  Would you also refer to CSS and XML as programming languages?  What about INI and other configuration files?

    HTML is on the edge, as is CSS and XML.  I wouldn't consider INI a programming language.  The difference, I think, is that you are instructing the computer on how to perform some action. 



  • @morbiuswilters said:

    @mrprogguy said:

    As SQL is not a programming language, how can you justify the use of the word "programmers" in your comment?

    I'm just curious, what do you consider the criteria for being a programming language? I have a pretty broad definition of programming language, one that includes languages like SQL and HTML.  I suppose it is ultimately quibbling over minor points.

    SQL can be called a programming language, even if it technically is a "Query Language". Then there is PL/SQL and the other variant that Sybase and SQL Server use which are also "programming" languages. Anyway, PROLOG is also considered a programming language, even when it is basically a query language, much like SQL.

    HTML, however, doesn't look like a programming language; take out JS and its pretty much a markup language for text formatting.



  • @danixdefcon5 said:

    Then there is PL/SQL and the other variant that Sybase and SQL Server use which are also "programming" languages.

    We're not including procedural add-ons that are not part of the SQL standard.  Obviously those are programming languages. 



  • @morbiuswilters said:

    HTML is on the edge, as is CSS and XML.  I wouldn't consider INI a programming language.  The difference, I think, is that you are instructing the computer on how to perform some action. 

    INI files instruct the computer regarding how you want a particular application to perform. It's actually quite similar, in that regard.

    My personal definition of programming language requires some logical constructs. If it can do conditional evaluation, it's probably a programming language - so Logo and sed are in. If it can't do conditional evaluation, it's out - so SGML (including HTML and XML) and INI files are out. I don't know CSS very well, but I've gotten the impression they have some limited conditional evaluation, so they're borderline. I don't think they count, still, but to show why I'd need to know them better, and I'd need to reveal more required capabilities to be a programming language. (Things like being able to do some form of arithmetic evaluation - sed and INTERCAL, unfortunately, count, as the requirement isn't being able to do arithmetic evaluation well.)



  • Wikipedia says SQL is a programming language, so it must be :)

    Personally, I'd class it as a programming language as well. Not a procedural programming language like C++/Java/etc, but a programming language nonetheless. Wikipedia classes it as a 'Declarative' language (like Prolog, with which I think it has quite a few similarities) as well as a Data-oriented language



  • @tgape said:

    I don't know CSS very well, but I've gotten the impression they have some limited conditional evaluation, so they're borderline.
     

    In current versions (as defined by the W3C), CSS has some conditional evaluation.  In real-world current versions, CSS is not at all capable in that regard. Anything approaching such capabilities are third-party browser-wars style extensions.

     I can't want for the time, in 30 years, when the W3C-current versions are well supported. *sigh*



  • Hate to break the trend and actually reply to the OP, but what's wrong with the SQL?

    Looks like it's just using the subselect to compile a list of "set_id"s which have at least one record matching the subselect where clause, then returning all records matching that list (whether they match the subselect where or not).

    Something like "show me all student grade records of students who have at least one "F"".

    The subselect would be superfluous if bm_attribute_set_id is a unique value (primary key), but that's not stated ...



  • @negativeview said:

     I can't want for the time, in 30 years, when the W3C-current versions are well supported. *sigh*

    I can't wait for the time -- in 7 years or so -- when the W3C is completely irrelevent and we can storm their offices and take their things and burn it to the ground. 



  • @morbiuswilters said:

    I can't wait for the time -- in 7 years or so -- when the W3C is completely irrelevent and we can storm their offices and take their things and burn it to the ground.
     

    I can easily count a multitude of things that the W3C could do better. But to be clear are you advocating the W3C being replaced by a group that would presumably do a better job, do you believe that browsers can play nice, or do you welcome another browser war?

    Or alternatively, are you just a pyro?



  •  As you can't understand how SQL is a programming language, how can you call yourself a programmer?

    Lame.



  • @sqlblindman said:

    As you can't understand how SQL is a programming language, how can you call yourself a programmer?

    Lame.

    Let's avoid pointlessly flaming, please.  I asked him to provide more information because sometimes people consider languages that aren't Turing complete to not technically be programming languages. 



  • @rbriem said:

    Hate to break the trend and actually reply to the OP, but what's wrong with the SQL?


    Looks like it's just using the subselect to compile a list of "set_id"s which have at least one record matching the subselect where clause, then returning all records matching that list (whether they match the subselect where or not).


    Something like "show me all student grade records of students who have at least one "F"".


    The subselect would be superfluous if bm_attribute_set_id is a unique value (primary key), but that's not stated ...

     

     

    bm_attribute_set_id is indeed a unique pkey. On this table, the query is exactly equivalent to:

     

     

                  SELECT *
    FROM bm_attribute_sets_l
    WHERE bm_substrand_id = ?
    AND bm_strand_id = ?
    AND bm_attribute_id = ?
    AND bm_attribute_sets_l.status_id = 1



  • I know it was you Friedo.  You broke my heart.  You broke my heart! 



  • @morbiuswilters said:

    I know it was you Friedo.  You broke my heart.  You broke my heart! 

     

     That wasn't me. It was Fredo. I swear.



  • @friedo said:

    @morbiuswilters said:

    I know it was you Friedo.  You broke my heart.  You broke my heart! 

     

     That wasn't me. It was Fredo. I swear.

    He got an offer he couldn't refuse...


  • @morbiuswilters said:

    @mrprogguy said:

    As SQL is not a programming language, how can you justify the use of the word "programmers" in your comment?

    I'm just curious, what do you consider the criteria for being a programming language?  I have a pretty broad definition of programming language, one that includes languages like SQL and HTML.  I suppose it is ultimately quibbling over minor points.

    Consider program-data duality. Both are just inputs for the processor; in the end they produce some meaningful/less output. You can see it from the point that data controls sequence of instruction fetch and produces processor state change. By combining processor and program, you produce a virtual machine that takes some data as its input language and produces output according to that language. Java bytecode interpreter is a virtual machine that takes its data (bytecode) which is at the same time its program.

     In this sense, HTML is programming language for a "HTML renderer" virtual machine. INI file not much of a programming language, but a REG file (regedit text input) can be very remotely considered one.

     



  •  I consider any language that is turning complete to be a programming language.

    Depending on the implementation, SQL may or may not be  turning complete (I believe MS-SQL T-SQL and Oracle's PL/SQL are turning complete, while MySQL isn't).

    That being said, I can't see HTML being a programming language, it is by defintion a markup language, but JavaScript is since it is turning complete.



  • In my world, if it's got verbs, it's a programming language.

    CSS, HTML and INI describe data or settings . They don't do anything until you plug them into something more intelligent. As soon as it describes behaviour, it's programming. XML is dead code. XSLT is programming. SQL is programming. CF is programming with ML syntax.

    The fun starts when you use programming languages to describe some great big machine, instead of prescribing its exact behaviour. I consider it pretty code if you program with as few verbs as possible. Just set the thing up, and let the data you feed to it roll through its natural paths, just like in hardware. Metaphorically: instead of instructing the machine to drive in a certain direction and adding code to keep it on the road, build some rails and give it a push.



  • @dhromed said:

    Metaphorically: instead of instructing the machine to drive in a certain direction and adding code to keep it on the road, build some rails and give it a push.
    Damn, that's some great wording right there, sir.


Log in to reply