- Feature Articles
- CodeSOD
- Error'd
-
Forums
-
Other Articles
- Random Article
- Alex's Soapbox
- Announcements
- Best of…
- Best of Email
- Best of the Sidebar
- Bring Your Own Code
- Coded Smorgasbord
- Mandatory Fun Day
- Off Topic
- Representative Line
- News Roundup
- Editor's Soapbox
- Software on the Rocks
- Souvenir Potpourri
- Sponsor Post
- Tales from the Interview
- The Daily WTF: Live
- Virtudyne
Admin
... and for not using the 'using' statement but manually calling close() (or not, when an exception happens).
Admin
Unioning the Ununion
Wait a second??? Isn't this an Onion article? [:D]
Admin
Obviously, the records are sorted in the database by inactiveDate, which makes it faster to query for the non-null records separately from the null records. Then, once the application has the data, they need to be sorted, and since the ORDER BY has made the list mostly sorted already, it is fast to sort the mostly-sorted list.
Microsoft was just too dumb to make SQL server able to sort by two things at once in the ORDER BY clause. Typical MSFT.
Admin
Looks to me more like it was designed differently and at one point was changed to what we see here.
The real WTF is why didn't he/she clean up the code... or this is another cut-n-paste example what someone without any clue what's going on is doing from getting already working functions from another place in the program.
Admin
And more points off for having the same exact loop twice in the same method.
Admin
Which is all the more surprising when you consider that they were bright enough to implement procedures holding >1 SQL statement, without a ; marking the end of them, and a database server that can remember which statement within the procedure was previously called for that connection, and execute the next one, smartly.
Admin
Someone help me out here -- are these supposed to be funny/sarcastic or do I need to take some people out behind the toolshed for a beating?
Admin
The *real* WTF here is the name of the property "Deptartment".
Admin
You can order by on multiple fields in sqlserver, so that's not it.
Say, you want to have the records in one set with two areas (in a grid for example): first the rows with no inactive date and then the rows with an active date. If you want to have the inactivedate's without a value on top, you can only order ascending, as descending order by on that column will place the NULLs at the bottom.
Perhaps that's not what the programmer wanted, so you need two sets and combine them.
That's with WTF's... they might look like stupid code, but there also might be a context in which the code makes sense.
Admin
Your post is a WTF. I'm going to invoke Chris Date's Incoherence Principle:
It is difficult to treat coherently that which is incoherent.
Sorting in what fashion? It was indicated that the application is doing some additional sorting but doesn't say on what. “Mostly sorted” – huh??
If you're referring to ORDER BY vendorNum then your statement is also nonsensical. If the clustered index was on inactiveDate then ORDER BY inactiveDate, vendorNum would be just as "fast" as the two-parter.
If the clustered index is on inactiveDate, vendorNum then it will be even *faster* because then no sorting will occur.
SQL Server (and every other SQL DBMS I've used) can easily order by two things.
If it’s indeed SQL Server, then Frans’ supposition is probably correct. As far as I know SQL Server doesn’t have the ability to say where NULLs go in the ORDER BY, so unless you want to do some magic inside your application you’d have to do it like that.<o:p> </o:p>
Of course, in that case why not just <st1:place>UNION</st1:place> the two so you don’t have to manage two result sets?
<o:p></o:p>Oracle has ORDER BY foobar {NULLS (FIRST/LAST)} to instruct the DBMS to return nulls at the beginning or end of the result “set” (true sets have no order, etc.). It would be nice to see this added to additional DBMS products and/or the SQL standard.
Admin
Speaking TSQL, could one not simply use the ISNULL function on that column, and return a carefully crafted magic value that makes all such rows sort to the top (or bottom, if desired) of the list?
Then the app wouldn't have to do any of this.
Admin
Firstly, you should use COALESCE. I'm not one of those anti-propietary guys, but COALESCE is standard and allows for N arguments.
Anyway, I wouldn't use that for ordering since you'll be forced to order by that column when its not null. here's what I do:.
ORDER BY
CASE WHEN SomeCol IS NULL THEN 0 ELSE 1 END, SomeOtherCol, ...
Admin
It's dangerous to assign 'fake' values to something that is null -- after all, that's what NULL is there for.
Admin
Exactly. Make the DB code work a little for all those fees you pay for it.
I love the way the code uses numeric columns. I'm sure in those tight while loops he had to squeeze every drop of perf out of the machine.
Can't you just bind a data structure to a query and just let the system flesh out the array? I was sure some language allowed that, can't remember if it was .Net.
Admin
I can't really tell but I'll get the belt, you call the children and we'll sort things out afterwards.
As to the original source code: I especially like how after limiting the two sets of records to be either Null or Non-Null dates, they check in each loop if the date is Null or not. It's just so precious...
Admin
For all we know this guy is crosseyed and couldn't properly see the complete code so he types them down twice....
Admin
I thought you had something there for a minute -- I copied the code into an editor window, moved the second loop just to the right of the first loop, and crossed my eyes "Magic Eye" style, looking for a hidden stereogram to jump out at me. Unfortunately, no, the loops really are identical.
Admin
I agree. The real WTF is the posts assigning some value to a null field.
Admin
UNION two queries for WHERE inactiveDate is NULL and WHERE inactiveDate is NOT NULL?
I guess then your post is also a WTF and you couldn't possible be a DBA :)
Admin
You must be an American. That's the only explanation I can see for the complete failure to recognise irony when it hits you round the head.
Admin
Shouldn't that be Deptardment?
Seriously, I could see a situation where the same proc is used in other data layers to get only NULL or nonNULL values by using only one of the result sets. Sure it's a lazy and dangerous implementation but it would at least explain the decision.
Admin
You are all forgetting that it might be as simple as this. Some crazy THE (pun intended) Architect decided that sproc should return two result sets without taking into consideration what UI or even BL layer needs or even what requirements/customer wants so then UI/BL have to merge two result sets into one so that it will suit their requirments. It just reminds me our current project and yes we have same issues. :'(
Admin
2. use ExecuteXmlReader on the SqlCommand
XmlSerializer serializer = new XmlSerializer(typeof(Vendor));
while(!reader.EOF)
{
Vendor vendor = (Vendor)serializer.Deserialize(reader);
myVendorList.Add(vendor);
}
Admin
That would be even worse -- having SQL Server process and return two resultsets and then just ignore one of them depending on what you need? If this is needed, wouldn't a simple bit parameter for the proc (1=Active, 0 =Inactive) would make more sense?
Hey "DBA" -- They could have used a UNION? yikes ! And that still wouldn't provide any kind of guaranteed sorting, as I am sure you are aware.
Admin
The real WTF here is that:
Admin
The biggest WTF here is that the ANSI standard allows a database implementation to sort NULLs high or low, or to always put them at the beginning or end of the result set (regardless of ascending or descending sequence). The SelectVendors code is a fairly straightforward way to get a specific sequence in a product independent manner. Of course such a technique requires documentation, which apparently was not there.
The GetVendors code shows a little "one foot on the boat, one on the pier" tendency. The repeated code should have been broken out to a separate method or modified to avoid the "if null" tests.
Of course, the lack of exception handling is very dangerous if not covered in the invoking methods, but that hardly rises to the level of a WTF. That's just common, garden variety, sloppy coding.
Admin
The very simple, database-independent solution is to not have a nullable column in the first place. You could use a specific date that can't really occur (such as 1/1/1901). If there are no dates that can't logically occur, add a flag and only use the date to record when the flag was set. Nullable columns are bad and very rarely actually necessary.
Admin
Why are people getting all confused about "assigning magic numbers to Null" or the sort order of NULLS ? That has absolutely nothing to do with anything. As Alex points out, there are two things here we are sorting on:
#1 -- is the inActiveDate column Null? That is boolean, either yes or no, a 1 or a 0 if you like. Nothing to do with how Null's sort, or assigning Null values to some other value to ensure the sort order looks good.
#2 -- VendorNum
That's it ... Alex has provided the solution that implements this quite well. No UNIONs, no COALESCE() or ISNULL(), no Null sort order implementations to worry about, no multiple recordsets, no "magic numbers" ...
Don't overcomplicate things here, guys and gals -- that's what causes 9 out of 10 WTF's.
Admin
Regarding Sorting, Nulls, and Magic numbers - my replies on these topics were to other comments in the thread. While it may come as a surprise, not every comment in the thread is directly in reference only to the original post. :)
And, I did see a post from Alex that did in fact use "IS NULL" in the ORDER BY clause in response to mine.
Admin
Blue -- I wasn't singling you out. But I will here -- there is a HUGE difference betwen the ISNULL() function and the "Is Null"; I made no reference at all to using an "is null" comparison which is indeed what Alex uses in his solution.
Admin
Disagree, NULL means that there is no data that satisfies the field definition. It's an easy value to understand. When you start using replacement values in your database you will invariably raise questions about data integrity. is this legitimate data? did the user intend to enter something but mis-keyed it?
Additionally, even though it makes joining and query conditions simpler, that NULL marker usually needs to be reinterpreted for display. The net gain is negligable and the risks aren't worth it.
In the case at hand there should be an inactive flag that is a not null boolean used to ID inactive and active vendors but a NULL inactive date should be considered valid for active records.
Admin
I agree that most of the time columns should be "not null" - but there are times when nulls are good. Consider an Address.State field, where State is a two-letter abbreviation foreign key-ed to a States.Abbreviation table. If the address is in a country other than the US, the State field should be null (this is legal for the foreign key.)
Admin
Didn't feel singled out, just a little defensive, I guess. :) no harm, no foul.
I can't resist being nitpicky though. I understand and agree that there is a difference between ISNULL() and IS NULL, and that Alex didn't use ISNULL() in his response to me. However, a part of your reply to me mentions that there are "no Null sort order implementations" in his response to me.
What do you call "ORDER BY CASE WHEN SomeCol IS NULL THEN 0 ELSE 1 END", then? It appears to involve an ORDER BY clause, which sorts result sets, as well as a CASE statement that performs differently depending on whether the column is null or not. By definition, that would be a "null sort order implementation", the way I'm seeing this... I know this is really splitting hairs. I just couldn't let it go, I suppose..
Admin
>>"no sort order implementations to worry about"
What I meant was: several people talked about how Nulls sort, before or after non-null values, and that the ANSI specification doesn't define this, and that in Oracle you can indicate how this should work. But how NULLS sort on a particular implementation of SQL is irrelevant to this problem -- at no point is a column being sorted (or does a column need to be sorted) with NULL values.
Admin
Alex's solution, as presented, is not portable. The ANSI SQL standard requires that the ORDER BY clause reference only columns from the result set (expressions are not allowed); however, if you move the CASE expression up to the select column list and give it a name, it would work every bit as well as the original code. A good case can be made that it would be less confusing than the original code. Does the difference really merit WFT status, though? I think that a one-line comment on the reason for the doing it would definitely take it out of that category.
Admin
Presentation layer mixed into business logic. how is that a wtf in anyone's imagination?? Wtf is how anyone could possibly miss that.
Admin
WTF are you talking about? Presentation layers - this is the first time I hear that term?
Off topic: how many layers do you use when you're building an application. Database, Application Server and Client - that's what I do. Where in this schema would possibly go this presentation layer and how many layers do you have on the server and client application?
Admin
he wants his data to be sorted by vendor num, but he wants alll those NOT inactive first, then he has all the inactive ones listed.
SQL can get a little funny and inconsitant when dealing with null values, so he's elected to do 2 different selects, ordered each and then made a union.
How is that hard to understand? It is presentation layer logic (how to display the data), but it is mixed into the business logic (management of data).
The presentation layer is that part of the client which actually controls how data is presented. It is a major component of any client application. It is not uncommon to mix part of this functionality into the business logic layer, such as using order by in sql, but realisitically this is the least useful way to organize output - preferably you would want to use a control in your client UI which allows the user to select how they want the data sorted (such as when you click on headers in explorer to sort by filename, date, size etc. IN fact, explorer has the same kind of 2 teir sorting which you can see by the way to keeps folders and files seperate when you sort directories by filename)
All your programs likely has presentation layers, but likely mashed together with other functionality. Sometimes this is unavoidable, sometimes useful, but more often it is a reflection of poor technique.
Once you get use to thinking about your applications in terms of tasks and roles, it becomes much easier to write maintainable code.
Admin
Actually, this is an example of why you don't have null columns. Most people will inner join the State table and inadvertantly exclude all the rows with no state. I never allow nulls on a FK column. You can always add a row to represent no state information and set that as the default value in the referencing table. This is much cleaner than allowing a nullable FK column.
Admin
"NULL means that there is no data that satisfies the field definition. It's an easy value to understand."
No it's not. For instance, explain why null <> null and null = null are both false? That's not easy to understand and it doesn't lead to simple, understandable code either.
I already pointed out to another poster the problem with nullable FKs.
You can argue from a theoretical standpoint about the merits of null all day long. I know from personal experience because I've done it both ways and seen the results over the long term. I never allow null varchar or char columns because you can always use '' as the default. I never allow nulls for id columns. For dates, if there is a value that can't logically occur (01/01/0001 for example) I will use that as the default. True numbers (as opposed to ids) are the one case that you may need to allow nulls for. However, many times the field must logically be positive or non-negative so you can use either 0 or -1 as the default.
Admin
Gotcha. Stupid me - brain was stuck on dealing with null sorts, and not thinking of the bigger picture.
Admin
null <> null and null = null are both false because, "null" means "i don't know the value"
so:
It is impossible to say if 2 unknown values are different so (null <> null) is false
and
It is impossible to say if 2 unknown values are the same so (null = null) is false
Of course.. we the other answer could be.. instead of true, of false, "null", because we dont really know the answer
Admin
The top stored procedure was probably done by someone who used to fix cars for a living. His garage's sign must have read "Imported and Domestic Cars"... sigh...
Alex
PS: the captcha thing was unreadable (no contrast whatsoever) when I first tried to post this reply.
Admin
My favorite one is, "Specializing in imported and domestic cars."