• (cs)

    select first from comments;

  • Arkady Bogdanov (unregistered)

    What The Fuuuuuhhhggggg

  • Crash Magnet (unregistered)

    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

  • Anonymous Coward (unregistered) in reply to Crash Magnet
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    It's multiple select statements when a single select will work just as well, and without all the declared variables, too.

  • rocksinger (unregistered) in reply to Crash Magnet
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    The TSQL function ISNULL(@variable,'') returns '' if @variable is null.

    You could also argue that it's the front-end/mid-layer's job to deal with nulls.

  • Bri (unregistered)

    Also note that isnull is not index-friendly, so it is probably a bit slower than it should be.

  • rocksinger (unregistered) in reply to rocksinger

    eeek, I meant

    The TSQL function ISNULL(YourFieldName,'') returns '' if YourFieldName is null.

  • Huh? (unregistered)

    select @w = w from wtf where id = @id select @t = t from wtf where id = @id select @f = f from wtf where id = @id

    select @w + @t + @f

  • (cs)
    declare @lnFirstName varchar(255) declare @lnLastName varchar(255) declare @lnEmailaddr varchar(255) declare @lnTitle varchar(255) declare @lnCompany varchar(255) declare @lnAddress varchar(255) declare @lnCity varchar(255) declare @lnState varchar(255) declare @lnZip varchar(255) declare @lnCountry varchar(255) declare @lnPhone varchar(255) declare @lnFax varchar(255)
    Ok, I know that the USPS isn't the most efficient, but I can't think of the last time I saw a zip code longer than 10 (5didgit, dash, +4). Phone number being 255? wow. I'd hate to see the phone bill associated with that long distance call.....

    I was going to go all witty with this, but there are too many WTFs associated with this.

  • Sandor (unregistered) in reply to Anonymous Coward
    Anonymous Coward:
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    It's multiple select statements when a single select will work just as well, and without all the declared variables, too.

    Yes, but then any non-developer who looks at the code thinks that writing code is easy.

    BTW anyone who names tables "Sitr0110" should be shot.

    CAPTCHA: ********

  • (cs) in reply to jonsjava
    jonsjava:
    Phone number being 255? wow.

    A pre-scripted phone banking session could get that long... Though I can't think of a legal reason to store that in a database with people's names and addresses...

  • (cs) in reply to Bri
    Bri:
    Also note that isnull is not index-friendly, so it is probably a bit slower than it should be.
    Um, no?

    There is actually no non-trivial function that is "index-friendly" if used as part of a WHERE clause - only the columns themselves are indexed, and if you chuck it into any function, the optimizer has to scan the whole index. But applying a function to the query's output has almost no effect on performance unless the function itself is extremely slow, which ISNULL definitely isn't.

    This entire hunk of junk could have been written as one SELECT statement with a bunch of ISNULLs, and would run in about 1/50th of the time. Of course, the fact that nulls are being turned into empty strings suggests an even worse WTF somewhere in the code, unless this is being directly used to feed a report (and nothing else). And then you have all the varchar(255) columns, but that's the least of our worries here...

  • Ron Moses (unregistered)

    To put it in simpler terms, let's say you want to buy a dozen donuts. You tell the clerk which donuts you want; he takes each donut from the case and places it in a separate bag. Once he's got all twelve bags lined up on the counter in front of him, each with a single donut in it, he opens each bag in turn and puts the donut into a dozen box. Once all twelve donuts are in the box, he closes it and hands it to you.

    Then his co-worker, noticing your puzzled expression, comes along and explains that it's better to do it this way in case you change your mind about one of the donuts and he has to swap it out. And you look at him, and the clerk, and realize the two of them have been doing crack all morning. The end.

  • (cs) in reply to Ron Moses
    Ron Moses:
    And you look at him, and the clerk, and realize the two of them have been doing crack all morning. The end.
    WIN
  • (cs) in reply to jonsjava
    jonsjava:
    I was going to go all witty with this, but there are too many WTFs associated with this.
    yeah exactly. besides the many many things wrong with this, what's with all the unnecessary begins and ends?
  • (cs) in reply to Crash Magnet
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    Um, where to start? Let's see, there's this mess

    select
@lnSitr0100Id      = max([Sitr0100Id])
    
from
Sitr0110
where
[Sitr0110Id]       = @lnSitr0110Id
    which does absolutely nothing.

    But the major WTF is that this whole page long crapfest could be written as:

    select @lnSitr0110Id as Response,
IsNull(FirstName,'') as First,
IsNull(LastName,'') as Last,
IsNull(EmailAddress,'') as Email,
IsNull(Title,'') as Title,
IsNull(Company,'') as Company, 
IsNull(Address1,'') as Address, 
IsNull(City,'') as City, 
IsNull(State,'') as State,
IsNull(Zip,'') as Zip, 
IsNull(Country,'') as Country, 
IsNull(Phone,'') as Phone, 
IsNull(Fax,'') as Fax, 
@lnNew as New, 
@lnSitr0100Id as Sitr0100Id
where Sitr0100Id = @lnSitr0100Id
  • (cs) in reply to A Nonny Mouse
    A Nonny Mouse:
    yeah exactly. besides the many many things wrong with this, what's with all the unnecessary begins and ends?

    I always wrap my condiational code in BEGIN and END in SQL (and other where you only need it for multi-liners), even if it's a one liner.

    There is no performance issue and it means you are using the same convention for all condition statements.

    Not to mention the cut and paste issues I've seen with people who don't.

  • SR (unregistered) in reply to campkev
    campkev:
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    Um, where to start? Let's see, there's this mess

    select
@lnSitr0100Id      = max([Sitr0100Id])
    
from
Sitr0110
where
[Sitr0110Id]       = @lnSitr0110Id
    which does absolutely nothing.

    But the major WTF is that this whole page long crapfest could be written as:

    select @lnSitr0110Id as Response,
IsNull(FirstName,'') as First,
IsNull(LastName,'') as Last,
IsNull(EmailAddress,'') as Email,
IsNull(Title,'') as Title,
IsNull(Company,'') as Company, 
IsNull(Address1,'') as Address, 
IsNull(City,'') as City, 
IsNull(State,'') as State,
IsNull(Zip,'') as Zip, 
IsNull(Country,'') as Country, 
IsNull(Phone,'') as Phone, 
IsNull(Fax,'') as Fax, 
@lnNew as New, 
@lnSitr0100Id as Sitr0100Id
where Sitr0100Id = @lnSitr0100Id

    I agree to a certain extent, though I'd argue that the application should deal with nulls.

    Either way: W? T? F?

  • Engival (unregistered) in reply to campkev
    campkev:
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    Um, where to start? Let's see, there's this mess

    select
@lnSitr0100Id      = max([Sitr0100Id])
    
from
Sitr0110
where
[Sitr0110Id]       = @lnSitr0110Id
    which does absolutely nothing.

    You're missing the subtle difference between 0110 and 0100. Any interaction between those tables must be pure joy to work with.

  • (cs)

    I like donuts... and crack...

  • Anonymous (unregistered) in reply to Ron Moses
    Ron Moses:
    To put it in simpler terms, let's say you want to buy a dozen donuts. You tell the clerk which donuts you want; he takes each donut from the case and places it in a separate bag. Once he's got all twelve bags lined up on the counter in front of him, each with a single donut in it, he opens each bag in turn and puts the donut into a dozen box. Once all twelve donuts are in the box, he closes it and hands it to you.

    Then his co-worker, noticing your puzzled expression, comes along and explains that it's better to do it this way in case you change your mind about one of the donuts and he has to swap it out. And you look at him, and the clerk, and realize the two of them have been doing crack all morning. The end.

    So can I swap one of the donuts for a big bag of crack? Sounds like they're set up to accomodate such a situation and I don't really need all twelve donuts. Eleven donuts and a big bag of crack should keep me occupied just fine for the whole rest of the day.

  • RonMexico (unregistered) in reply to Bodestone
    Bodestone:
    A Nonny Mouse:
    yeah exactly. besides the many many things wrong with this, what's with all the unnecessary begins and ends?

    I always wrap my condiational code in BEGIN and END in SQL (and other where you only need it for multi-liners), even if it's a one liner.

    There is no performance issue and it means you are using the same convention for all condition statements.

    Not to mention the cut and paste issues I've seen with people who don't.

    Ditto. I use braces in C#/Javascript for single-line if and loop clauses also. Clarity, maintainability, all that.

  • Mike Dimmick (unregistered)

    It's rather like your partner giving you a shopping list and instead of driving to the shop, buying everything on the list and returning home, you instead drive to the shop, buy the first item, drive home, drop it off, drive back to the shop, buy the second item, drive home, etc.

    If you're using SQL you should aim to retrieve all the values you need in one statement. It helps if you understand that the database has to work pretty hard to find any particular row, but virtually no extra effort to retrieve additional values associated with that row.

    This particular query could have been rewritten as:

    SELECT TOP 1
   Sitr0100Id AS Response,
   FirstName AS First,
   LastName AS Last,
   EmailAddress AS Email,
   Title,
   Company,
   Address1 AS Address,
   City,
   State,
   Country,
   Phone,
   NULL AS New, -- @lnNew isn't set
   Sitr0100Id AS Sitr0100Id
FROM
   Sitr0100
ORDER BY Sitr0100Id DESC

    (ordering by the ID in reverse and taking the top result is the same as selecting MAX(Sitr0100Id))

  • (cs) in reply to Engival
    Engival:
    campkev:
    Crash Magnet:
    For those of us who don't speak sql, what the Fuuuuuhhhggggg is wrong with this?

    Um, where to start? Let's see, there's this mess

    select
@lnSitr0100Id      = max([Sitr0100Id])
    
from
Sitr0110
where
[Sitr0110Id]       = @lnSitr0110Id
    which does absolutely nothing.

    You're missing the subtle difference between 0110 and 0100. Any interaction between those tables must be pure joy to work with.

    Crap, you're right. Add dumbass table names to the list of WTF's

  • (cs)

    And why do all the variables start @ln, or is it @In?

    That's dafter than tables starting tbl.

  • Dana (unregistered) in reply to Bodestone

    It's standard practice to name SQL variables beginning with @.

  • Patrick (unregistered)

    $sp=' '; $comment1='this'.$sp; $comment2='way'.$sp; $comment3='you'.$sp; $comment4='can'.$sp; $comment5='swap'.$sp; $comment6='words'.$sp; $comment7='around'; $comment=$comment1.$comment2.$comment3.$comment4.$comment5.$comment6.$comment7.'.'; echo $comment;

  • (cs) in reply to jonsjava
    jonsjava:
    declare @lnFirstName varchar(255) declare @lnLastName varchar(255) declare @lnEmailaddr varchar(255) declare @lnTitle varchar(255) declare @lnCompany varchar(255) declare @lnAddress varchar(255) declare @lnCity varchar(255) declare @lnState varchar(255) declare @lnZip varchar(255) declare @lnCountry varchar(255) declare @lnPhone varchar(255) declare @lnFax varchar(255)
    Ok, I know that the USPS isn't the most efficient, but I can't think of the last time I saw a zip code longer than 10 (5didgit, dash, +4). Phone number being 255? wow. I'd hate to see the phone bill associated with that long distance call.....

    I was going to go all witty with this, but there are too many WTFs associated with this.

    You should see the phone bills we get here at StarFleet!

  • (cs) in reply to ubersoldat
    ubersoldat:
    I like donuts... and crack...
    Donuts with powdered crack - yummy!
  • (cs) in reply to Mike Dimmick
    Mike Dimmick:
    It's rather like your partner giving you a shopping list and instead of driving to the shop, buying everything on the list and returning home, you instead drive to the shop, buy the first item, drive home, drop it off, drive back to the shop, buy the second item, drive home, etc.

    If you're using SQL you should aim to retrieve all the values you need in one statement. It helps if you understand that the database has to work pretty hard to find any particular row, but virtually no extra effort to retrieve additional values associated with that row.

    This particular query could have been rewritten as:

    SELECT TOP 1
   Sitr0100Id AS Response,
   FirstName AS First,
   LastName AS Last,
   EmailAddress AS Email,
   Title,
   Company,
   Address1 AS Address,
   City,
   State,
   Country,
   Phone,
   NULL AS New, -- @lnNew isn't set
   Sitr0100Id AS Sitr0100Id
FROM
   Sitr0100
ORDER BY Sitr0100Id DESC

    (ordering by the ID in reverse and taking the top result is the same as selecting MAX(Sitr0100Id))

    Not to mention the chance that the data in the row may change between calls.

  • Beaker (unregistered) in reply to Bodestone

    I originally thought it could be some sort of Hungarian Notation (some find that to be a bad practice too), but there's no rhyme or reason to the prefix.

    As for the isNull function, the code base that is calling the SQL statement should be handling those nulls. It's one thing if you have to concatenate values in the query (concatenating a string and a null in SQL yields a null), but that's not the case here.

  • SR (unregistered) in reply to Dana
    Dana:
    It's standard practice to name SQL variables beginning with @.

    Although you have to admit an overuse of "In" and "ln" is WTFery of the highest order.

  • Patrick (unregistered) in reply to Bodestone
    Bodestone:
    And why do all the variables start @ln, or is it @In?

    That's dafter than tables starting tbl.

    That's nothing, I have to deal with all tables starting with "table_", which gets picked up by the syntax checker before I'm finished. Prefixing names (or Hungarian Notation, in this case) is useful only for form controls when you want to tell the difference between txtSearch and btnSearch. Other than that, it's usually a sign of an incoming wtf-bomb.

  • Ken B (unregistered) in reply to Ron Moses
    Ron Moses:
    To put it in simpler terms, let's say you want to buy a dozen donuts. You tell the clerk which donuts you want; he takes each donut from the case and places it in a separate bag. Once he's got all twelve bags lined up on the counter in front of him, each with a single donut in it, he opens each bag in turn and puts the donut into a dozen box. Once all twelve donuts are in the box, he closes it and hands it to you.
    Close, but not quite.

    It's more like the clerk takes a bag, goes to the donuts, places one in the bag, and then puts the bag in a table in back. He then gets another bag, goes to the donuts, places one in the bag, and then puts the bag in a table in back. Repeat.

    (ie: Your version could be done by taking twelve bags and placing one donut in each, whereas the entire "take a bag, put a donut in it, and put the bag in a temporary storage area" needs to be completely done, from start to finish, twelve separate times.)

  • (cs)

    I don't know if this is better or worse than:

    $rs = $myconn.execute("Select * from sitr0100");

for ($i=0; $<$rs.length(); $i++)
{
  if ($rs["id"]==$myId)
  {
    $firstname = $rs["FirstName"];
    $lastname = $rs["LastName"];
    $email = $rs["EmailAddress"];
    etc..
  }
}
  • Ken B (unregistered) in reply to ubersoldat
    ubersoldat:
    I like donuts... and crack...
    Then you probably love plumbers on their lunch break. :-)
  • Ken B (unregistered) in reply to akatherder
    akatherder:
    I don't know if this is better or worse than: 
    $rs = $myconn.execute("Select * from sitr0100");

for ($i=0; $<$rs.length(); $i++)
{
  if ($rs["id"]==$myId)
  {
    $firstname = $rs["FirstName"];
    $lastname = $rs["LastName"];
    $email = $rs["EmailAddress"];
    etc..
  }
}
    "WHERE clause"? What's a "WHERE clause"?
  • (cs) in reply to RonMexico
    RonMexico:
    Bodestone:
    A Nonny Mouse:
    yeah exactly. besides the many many things wrong with this, what's with all the unnecessary begins and ends?

    I always wrap my condiational code in BEGIN and END in SQL (and other where you only need it for multi-liners), even if it's a one liner.

    There is no performance issue and it means you are using the same convention for all condition statements.

    Not to mention the cut and paste issues I've seen with people who don't.

    Ditto. I use braces in C#/Javascript for single-line if and loop clauses also. Clarity, maintainability, all that.
    And for the same reason, when I'm done testing with 
    [[ $? -ne 0 ]] && failed || success
    I unroll that into 
    if [ $? -ne 0 ]; then
failed
else
success
fi
    It's only polite.

  • Yazeran (unregistered) in reply to Mike Dimmick
    Mike Dimmick:
    (ordering by the ID in reverse and taking the top result is the same as selecting MAX(Sitr0100Id))

    Fail!

    In output yes, but in performance NO! Max() / Min() is O(n) whereas sort (ORDER) is at best O(n log(n))

    For small n it doesn't matter, but for large n.....

    In this particular case it likely doesn't matter (as the application will likely be slow as molasses anyways considering that SQL) :-)

    Yours Yazeran.

    Plan: To go to Mars one day with a hammer.

  • JoeKu (unregistered) in reply to campkev
    campkev:
    select @lnSitr0110Id as Response,

    IsNull(FirstName,'') as First,

    IsNull(LastName,'') as Last,

    IsNull(EmailAddress,'') as Email,

    IsNull(Title,'') as Title,

    IsNull(Company,'') as Company,

    IsNull(Address1,'') as Address,

    IsNull(City,'') as City,

    IsNull(State,'') as State,

    IsNull(Zip,'') as Zip,

    IsNull(Country,'') as Country,

    IsNull(Phone,'') as Phone,

    IsNull(Fax,'') as Fax,

    @lnNew as New,

    @lnSitr0100Id as Sitr0100Id

    where Sitr0100Id = @lnSitr0100Id

    Almost correct: if there are no rows where Sitr0100Id = @lnSitr0100Id, then your select will return an empty resultset. If there are multiple rows with the specified condition, then your select will return multiple rows. The original code will always return 1 row.

    Of course, it make sense for the caller to deal with the non-existing row as an empty resultset, but your code is not 100% compatible with the original code.

  • A DBA (unregistered) in reply to Sandor
    Sandor:
    BTW anyone who names tables "Sitr0110" should be shot.

    Okay! Okay! I'll change the name of the table to "babysitters_born_jan_10"! Don't shoot! Please!

  • (cs) in reply to Bri

    Isnull doesn't matter a whole lot unless it's used in the WHERE clause. In the SELECT clause, it only adds a "compute scalar" to the query plan.

    But still... this is why we need to actually test people who SAY they know SQL... because usually, they don't. In an interview I was given once, I was told I was the only person who ever passed their SQL test, and it was ridiculously easy, but I wasn't surprised that nobody did well on it because they were looking for C# programmers, who also "know SQL"

  • ppp (unregistered) in reply to jonsjava
    jonsjava:
    declare @lnFirstName varchar(255) declare @lnLastName varchar(255) declare @lnEmailaddr varchar(255) declare @lnTitle varchar(255) declare @lnCompany varchar(255) declare @lnAddress varchar(255) declare @lnCity varchar(255) declare @lnState varchar(255) declare @lnZip varchar(255) declare @lnCountry varchar(255) declare @lnPhone varchar(255) declare @lnFax varchar(255)
    Ok, I know that the USPS isn't the most efficient, but I can't think of the last time I saw a zip code longer than 10 (5didgit, dash, +4). Phone number being 255? wow.

    Meh. Our production db is full of "boolean" fields that are VARCHAR2(255). Not only that, we have one table (incrementally modified over the years by different developers) with four VARCHAR2(255) fields constrained to:

    1. "1" or "0"
    2. "TRUE" or "FALSE"
    3. "YES" or "NO"
    4. "Y" or "N"

    I'm not kidding. This isn't written for effect.

    As for me, I insist on using NUMBER(0, 1) for my booleans. Sure, it's another version, but at least it's right.

  • Ibi-Wan Kentobi (unregistered) in reply to Yazeran
    Yazeran:
    Mike Dimmick:
    (ordering by the ID in reverse and taking the top result is the same as selecting MAX(Sitr0100Id))

    Fail!

    In output yes, but in performance NO! Max() / Min() is O(n) whereas sort (ORDER) is at best O(n log(n))

    For small n it doesn't matter, but for large n.....

    If you were searching through a flat file yes, but in a database NO!

    Max(), Min(), and SELECT TOP 1 FROM ... ORDER BY are all O(1) in a table indexed by the field in question -- which this table certainly should be, given this usage.

  • (cs)

    The best part is that the coworker had no problem with it.

  • (cs) in reply to Anonymous
    Anonymous:
    Ron Moses:
    To put it in simpler terms, let's say you want to buy a dozen donuts. You tell the clerk which donuts you want; he takes each donut from the case and places it in a separate bag. Once he's got all twelve bags lined up on the counter in front of him, each with a single donut in it, he opens each bag in turn and puts the donut into a dozen box. Once all twelve donuts are in the box, he closes it and hands it to you.

    Then his co-worker, noticing your puzzled expression, comes along and explains that it's better to do it this way in case you change your mind about one of the donuts and he has to swap it out. And you look at him, and the clerk, and realize the two of them have been doing crack all morning. The end.

    So can I swap one of the donuts for a big bag of crack? Sounds like they're set up to accomodate such a situation and I don't really need all twelve donuts. Eleven donuts and a big bag of crack should keep me occupied just fine for the whole rest of the day.

    DOUBLE WIN

  • Ibi-Wan Kentobi (unregistered) in reply to ppp
    ppp:
    As for me, I insist on using NUMBER(0, 1) for my booleans. Sure, it's another version, but at least it's right.

    And the nominee for The New Real WTF is...

    (You don't think the first four people all thought "at least MINE is right"?)

  • Coward (unregistered) in reply to ppp
    ppp:
    (snip) As for me, I insist on using NUMBER(0, 1) for my booleans. Sure, it's another version, but at least it's right.

    Why would you use a decimal?

    Wouldn't it make more sense to use a NUMBER(1)?

  • Sandor (unregistered) in reply to A DBA
    A DBA:
    Sandor:
    BTW anyone who names tables "Sitr0110" should be shot.

    Okay! Okay! I'll change the name of the table to "babysitters_born_jan_10"! Don't shoot! Please!

    Ow great! now i can use SELECT * FROM babysitters_born_jan_10 in stead of:

    SELECT h.* FROM HUMANS INNER JOIN JobDescriptions j on j.ID = h.JobDescriptionID WHERE j.Description = 'Babysitter' AND DATEPART (m, h.BirthDay) = 1 AND DATEPART (d, h.BirthDay) = 10

    which allows for much faster coding :) and a lot of tables yeah

  • (cs) in reply to ubersoldat
    ubersoldat:
    I like donuts... and crack...
    Here ya go. [image]

Leave a comment on “A Confusing SELECTion”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article