Comment On A Confusing SELECTion

select first from comments;

What The Fuuuuuhhhggggg

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

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.

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.

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

eeek, I meant

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

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

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.

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: ********

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

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

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.

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

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?

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

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.

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?

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.

I like donuts... and crack...

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.

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.

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

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

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

That's dafter than tables starting tbl.

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

$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;

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!

ubersoldat:

I like donuts... and crack...

Donuts with powdered crack - yummy!

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.

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.

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.

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.

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

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

  }

}

ubersoldat:

I like donuts... and crack...

Then you probably love plumbers on their lunch break. :-)

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

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.

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.

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.

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!

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"

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.

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.

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

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

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

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

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*

ubersoldat:

I like donuts... and crack...

Here ya go.