January 9, 2009 at 10:00 am
Hi All,
I come accross some queries where the "where" clause is as follows:
"WHERE 1 = 1"
Now these are not dynamic queries. Why should this "WHERE" clause exist?
When I see "WHERE 1 = 2" then it is understandable that they are trying to create a structure without data because 1 = 2 evaluates to false and so no records will be returned.
Thanks.
January 9, 2009 at 10:04 am
Looks like a debugging artifact left in place when deployed to prod. I resort to that in QA when I want to selectively test things that were previously disabled. (meaning it's easier to set it to 1=1 instead of commenting out the whole clause, only to revert it back to disabled in a few minutes).
Agreed however this probably shouldn't be in Production. Serves no actual purpose.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 9, 2009 at 10:18 am
Hi Matt,
But how long does it take to type the word "WHERE" ? Im I missing something? The queries are as simple as:
SELECT * FROM tblA WHERE 1 = 1
Thanks.
January 9, 2009 at 11:19 am
It could be generated code. A lot of the code generators put that in so that everything can be "AND" from that point forward.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 9, 2009 at 11:25 am
ramadesai108 (1/9/2009)
Hi Matt,But how long does it take to type the word "WHERE" ? Im I missing something? The queries are as simple as:
SELECT * FROM tblA WHERE 1 = 1
Thanks.
When I do it - the queries are substantially longer, and the WHERE will have other things going on. It also usually means I keep going back and forth between having it enabled or disabled (so after the 3rd or fourth time, I just put 1=1 instead of commenting).
Still - that should get yanked out during code review....
Grant's point sounds interesting. I can't say I've played with a lot of code generators, but it would make it easier to build criteria from.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 9, 2009 at 11:46 am
Matt's explanation makes sense now, should have read his previous posting properly. Grant mentioned about generated code, but this code is certainly not generated through any code generator. Anyway, as Matt mentioned, it should not have ended up in production. Thanks to both for your time.
January 9, 2009 at 12:33 pm
I've seen requests to Microsoft to make having a Where clause on every query be required, with the solution to unrestricted queries that they could have something like "Where 1 = 1". The purpose is to make it so you don't accidentally run an update/delete without a Where clause (which can be quite a bad thing).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2009 at 12:49 pm
I have also done this in T-SQL generators. It can make things simpler to have an expectation of a WHERE clause on every query.
January 9, 2009 at 12:51 pm
GSquared (1/9/2009)
I've seen requests to Microsoft to make having a Where clause on every query be required, with the solution to unrestricted queries that they could have something like "Where 1 = 1". The purpose is to make it so you don't accidentally run an update/delete without a Where clause (which can be quite a bad thing).
That could be good, but the default should then be WHERE 1=2. So that you could keep with the "above all don't harm data" principle.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 9, 2009 at 2:19 pm
GSquared (1/9/2009)
I've seen requests to Microsoft to make having a Where clause on every query be required, with the solution to unrestricted queries that they could have something like "Where 1 = 1". The purpose is to make it so you don't accidentally run an update/delete without a Where clause (which can be quite a bad thing).
BWAA-HAA!!! ... that reminds me... I'm going to propose to car manufacturers that people be required to wear their spare tires on their heads so they can tell that they actually have one... 😛
Man, what will they waste time on next? :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
January 9, 2009 at 2:42 pm
Matt Miller (1/9/2009)
GSquared (1/9/2009)
I've seen requests to Microsoft to make having a Where clause on every query be required, with the solution to unrestricted queries that they could have something like "Where 1 = 1". The purpose is to make it so you don't accidentally run an update/delete without a Where clause (which can be quite a bad thing).That could be good, but the default should then be WHERE 1=2. So that you could keep with the "above all don't harm data" principle.
No, the point isn't a default, it's that any query where you would normally intentionally leave off a Where clause (for whatever reason), you'd be required to put one on it, but it could be "Where 1 = 1".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2009 at 2:48 pm
Jeff Moden (1/9/2009)
GSquared (1/9/2009)
I've seen requests to Microsoft to make having a Where clause on every query be required, with the solution to unrestricted queries that they could have something like "Where 1 = 1". The purpose is to make it so you don't accidentally run an update/delete without a Where clause (which can be quite a bad thing).BWAA-HAA!!! ... that reminds me... I'm going to propose to car manufacturers that people be required to wear their spare tires on their heads so they can tell that they actually have one... 😛
Man, what will they waste time on next? :hehe:
Having had to deal with a few too many people forgetting to put a Where clause on something a few too many times, I have to say, I can sympathize with the desire to make it mandatory.
I don't think it will solve the problem, but at least it's an attempt to prevent casual idiocy (as opposed to professional, full-time idiocy).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 9, 2009 at 4:41 pm
Heh... But, it won't solve a bloody thing and it won't prevent the problems that you describe... people will simply get into the habit of adding WHERE 1=1 and still forget the rest of the query. And, it's gonna piss me off in the process because I hate typing crap just because someone says it's gotta be there. It's like INNER JOINs... I like the old way of doing it... it doesn't make the code run faster... contrary to popular belief, it doesn't keep people from writing accidental cross joins... and it sure as hell doesn't make it any easier to read. It just makes more typing.
Nah... what they need to spend some time on are things like getting the window aggregates right, adding a "Connect BY", bringing back some stuff they deprecated and removed like the {f4} function of QA, making it possible to pass and easily use delimited parameters, and maybe adding a couple of T-SQL features like a directory reader table valued system function to replace a set based version of combination of xp_DirTree and sp_GetFileDetails so people who don't want to use a silly-a$$ed GUI to do a simple job quickly can actually get it all done in T-SQL... 😉
Heh... sorry... you hit on a pet peeve. The pet peeve is "Let's protect the idiot that doesn't really know how to use SQL and doesn't ever want to take the time to actually learn it and make it more difficult for the ones that do in the process." 😉 Or, so it seems... to summarize, if you make something idiot proof, then only idiots will use it. :w00t:
--Jeff Moden
Change is inevitable... Change for the better is not.
January 12, 2009 at 7:19 am
Agree with you on most points.
I like the Inner Join syntax. Makes it easier for me to read (not for you, but it does for me). Otherwise, yeah on all points.
The idea of SQL having a better connection to the file system, without having to turn on xp_cmdshell or go through CLR would be nice.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 12, 2009 at 1:14 pm
GSquared (1/12/2009)
Agree with you on most points.I like the Inner Join syntax. Makes it easier for me to read (not for you, but it does for me).
That would also be an excellent point to Microsoft... why not give folks a choice? 🙂 I will admit, I hate the old *= and =* method for doing outer joins because I think they're much more difficult to read, but even that's a personal preference. Let people do it either way. Why do ANSI standards deprecate things that aren't harmful?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply