May 14, 2013 at 4:49 am
Nice question, thank you very much for two very valuable points :hehe:
May 14, 2013 at 5:00 am
honza.mf (5/14/2013)
Raghavendra Mudugal (5/14/2013)
(Not sure and can't think of why one would use as "BETWEEN -1 AND -10" with "start" as the higher value in any practical case. When I saw -1 as the start value.. I thought that might be the catch here and .... 🙂 )I can think one can copy-paste positive thresholds and write minus signs afterwards.
I see you missed the <joke></joke> tags :rolleyes:
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 14, 2013 at 5:02 am
Nicely posed and explained. I suspected the BETWEEN might cause a problem, but went for the wrong answer anyway.
I lost those two very valuable points, delaying my retirement by at least five years. Fortunately I have my mutual funds to fall back on. 😉
[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn
May 14, 2013 at 5:38 am
Thomas Abraham (5/14/2013)
I lost those two very valuable points
I hadn't noticed it was two points.
Woo hoo - Barbados here I come!
😎
May 14, 2013 at 6:29 am
declare @test-2 int
set @test-2=-2
select case when @test-2 between -1 and -10 then 'Order does not matter'
else 'Order matters' end as Conclusion
It would seem to me that order shouldn't matter. As it is, if you are using parameters for the comparison and don't know which will be larger, you would need the below:
select case when (@test >= @compare1 and @test-2 <= @compare2) OR (@test >= @compare2 and @test-2 <= @compare1) then 'Sheesh, what a long way around.' end
I saw it was from Hugo, and checked carefully for tricks. Not carefully enough. 😀
May 14, 2013 at 6:53 am
Perhaps I did not make myself very clear.
Of course you can take a definition and pretend that that is how everything "is supposed to work". But I am pretty sure you wouldn't want the meanings of AND and OR reversed, or ORDER BY and GROUP BY, regardless of how well documented and defined that would be in Books Online.
What this shows is that there is something more to a computer language than a set of arbitrary definitions: it needs to align with the a priori expectations of most developers and have a high level of consistency. At least that how I like my languages and what most languages are like.
But I think that BETWEEN as a language feature does not quite meet those criteria, or this would not be "Question of the Day" and we would not be having this discussion. Since it also does not provide a lot of benefit (unless the expression to check is very long), my choice is to avoid it and try to use more unambiguous alternatives.
my concern is with the start expr with the higher value and then put lower value to the end expr and I don't see any practical case where and how it is used to make the code work
Variable bounds? I would argue that constant bounds is something you would rarely see in a practical case.
In fact, I would be much more likely to use BETWEEN if it wouldn't care in which order the bounds are because then there would be a much more significant benefit against the cost of learning that other arbitrary detail.
May 14, 2013 at 7:22 am
@alex Fekken
I see what you mean... but in general if used... "pick a number between 1 and 100" and nobody goes "pick a number between 100 and 1"; I guess the creator meant it to be used in general aspect; may be they will change it to "RANGE a TO b" (as you mentioned)... to make it more consistent in future releases.
If considering or making this "BETWEEN 100 AND 1" as legal in SQL, then it seems a lot work to the SELECT statement
(OR may be they might come up with - no matter to what range you mention in any order, parser will consider the value/s and then possibly it arranges itself internally suit its need)
hmmm... Interesting.
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 14, 2013 at 7:43 am
Thanks for the excellent question Hugo.
At first I thought the trick was that we don't know if the ValueCol was an integer or varchar data type. I almost over-thought myself into getting it wrong.
Looking forward to the rest of the series...
Enjoy!
May 14, 2013 at 7:43 am
Great question and explanation Hugo..
Thanks.
May 14, 2013 at 8:06 am
This is another interesting language ambiguity.[1]
Of course it makes sense when you rewrite the between operator as: val >= p1 AND val <= p2
I would have expected "between" to have been implemented to internally reverse those parameters when p2 < p1
My takeaway from this lesson is just don't use between.
[1] by language ambiguity, I mean English language - SQL's definition/implementation of "between" is documented and could be expected by reading BOL. The English definition of between may or may not exactly match that expectation.
ex: "between a rock and a hard place" is the same intent as "between a hard place and a rock"
(I think I'm going to reverse the order in that expression going forward because it sounds weirder)
May 14, 2013 at 9:05 am
Nice question. This falls into that category where if you inadvertently did this in a real piece of production code, you would probably stare at it for a long time trying to figure out why it wasn’t returning the expected result. I hate it when that happens.
May 14, 2013 at 9:09 am
It's clear if you look at the ANSI documentation, which says
"X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".
May 14, 2013 at 9:13 am
Cliff Jones (5/14/2013)
Nice question. This falls into that category where if you inadvertently did this in a real piece of production code, you would probably stare at it for a long time trying to figure out why it wasn’t returning the expected result. I hate it when that happens.
My initial suspicion on most QotD is that it came from someone debugging their own code.
May 14, 2013 at 11:03 am
Nice, very nice. Thanks, Hugo!
May 14, 2013 at 1:29 pm
Good question ... looked for the catch, but completely missed it, where's that coffee
Viewing 15 posts - 16 through 30 (of 71 total)
You must be logged in to reply to this topic. Login to reply