May 15, 2013 at 2:05 am
Raghavendra Mudugal (5/14/2013)
Really Good one, Hugo, thank you for posting (so basic and so much of valuable point)(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 .... 🙂 )
SELECT KeyCol, ValueCol, CASE
WHEN ValueCol < -10 THEN 'Very negative'
WHEN ValueCol BETWEEN -10 AND -1 THEN 'Bit negative'
WHEN ValueCol = 0 THEN 'Zero'
WHEN ValueCol BETWEEN 1 AND 10 THEN 'Bit positive'
ELSE 'Very positive'
END AS Category
FROM
(
SELECT 1 [KeyCol], 0 [ValueCol]
UNION ALL
SELECT 2, 4
UNION ALL
SELECT 3, -15
UNION ALL
SELECT 4, 14
UNION ALL
SELECT 5, -2
) A
ORDER BY 2
+1
Malleswarareddy
I.T.Analyst
MCITP(70-451)
May 15, 2013 at 2:30 am
Thanks, all for the kind comments and the valuable discussion.
It's interesting to see how different people have different opinions about BETWEEN. In my experience, this sometimes goes wrong when variables are used in a BETWEEN, and often goes wrong with constants that represent negative numbers. With positive numbers, people always tend to put the smaller number first. With negative numbers, some people still do that, but other people tend to reverse the order, putting the number closest to zero first.
Makking this mistake may not be very common, but it can be extremely hard to troubleshoot. In this question, people expect there is probably a catch and there is only very little code to look at - and still 44% (currently) overlook the inverted BETWEEN clause. When this error is embedded in a 300-line stored procedure, it is even harder to spot.
May 15, 2013 at 6:56 am
Nice question Hugo and valid point.
One part of your explination is very puzeling
SQL Server does not consider this an error, nor will it raise a warning.
What kind of math or laguage would consider this an error or raise a warning?
It seems like you hate on SQL server every chance you get and it really robs from the good parts of what you have to say. 😉
May 15, 2013 at 7:07 am
PHYData DBA (5/15/2013)
Nice question Hugo and valid point.One part of your explination is very puzeling
SQL Server does not consider this an error, nor will it raise a warning.
What kind of math or laguage would consider this an error or raise a warning?
It seems like you hate on SQL server every chance you get and it really robs from the good parts of what you have to say. 😉
Hate SQL Server? Me?
Oh no, not at all. I love it! I'm sorry if I come across as thrasing it, that's not my intention at all! 😉
To answer your question - since "Value BETWEEN -1 AND -10" is equivalent to "Value >= -1 AND Value <= -10", which can never be true for any value, I personally would have liked if SQL Server gives a warning in this case. Because those who are unaware of the exact definition of BETWEEN in SQL might think that it works like the English word "between" (and if you go out in the street and ask people if -5 is between -1 and -10, I'm willing to bet that 99% of all people will say "yes").
May 15, 2013 at 7:14 am
Hugo Kornelis (5/15/2013)
(and if you go out in the street and ask people if -5 is between -1 and -10, I'm willing to bet that 99% of all people will say "yes").
You have more faith in the mathematical skills of the populace than I do, I'll take that bet. 😀
I agree completely on the semantic meaning, and lack of a compelling reason for that difference. That said, we don't want to let semantic English creep into SQL to prevent OR from being defined as XOR.
May 15, 2013 at 7:21 am
srienstr (5/15/2013)
Hugo Kornelis (5/15/2013)
(and if you go out in the street and ask people if -5 is between -1 and -10, I'm willing to bet that 99% of all people will say "yes").You have more faith in the mathematical skills of the populace than I do, I'll take that bet. 😀
I agree completely on the semantic meaning, and lack of a compelling reason for that difference. That said, we don't want to let semantic English creep into SQL to prevent OR from being defined as XOR.
You can easily win. It's possible from 100 men, one says "No", one "Uggh", and on "Millennium, Hand And Shrimp". :hehe:
May 15, 2013 at 7:26 am
Hugo Kornelis (5/15/2013)
Hate SQL Server? Me?Oh no, not at all. I love it! I'm sorry if I come across as thrasing it, that's not my intention at all! 😉
To answer your question - since "Value BETWEEN -1 AND -10" is equivalent to "Value >= -1 AND Value <= -10", which can never be true for any value, I personally would have liked if SQL Server gives a warning in this case. Because those who are unaware of the exact definition of BETWEEN in SQL might think that it works like the English word "between" (and if you go out in the street and ask people if -5 is between -1 and -10, I'm willing to bet that 99% of all people will say "yes").
Thanks for clearing that up. Your wording in this QOTD, other QOTD, and your MS Connect submission comments have confused more than just one person about your opinion of MS Sql Server.
Still not getting from this post why anyone as you said "should" expect a warning or error when writing valid, although odd, code statements but 'ce la vie.
I know I like this behavior when my code is something like Between @var1 and @var2 and the expresion needs to evaluate the variables and continue without errors or warnings. Call me weird but if it stopped doing this and started returning errors or warnings wouldn't that be a hugo mess.... errr... huge mess. :hehe:
May 15, 2013 at 7:29 am
srienstr (5/15/2013)
I agree completely on the semantic meaning, and lack of a compelling reason for that difference. That said, we don't want to let semantic English creep into SQL to prevent OR from being defined as XOR.
Well said sir thier is a wide gab between a semantic Gramatical error and a syntax error.
^
On purpose example of semantic gramatical style error that spellcheck allowed. 😎
May 15, 2013 at 8:02 am
PHYData DBA (5/15/2013)
Thanks for clearing that up. Your wording in this QOTD, other QOTD, and your MS Connect submission comments have confused more than just one person about your opinion of MS Sql Server.
Hmmm, that's unfortunate.
The reason I post QotD's, blogs, forum posts, and so on about things I perceive as weird is that I want people using SQL Server to know about how it behaves. Both the good and the slightly-less-good parts.
The reason I sometimes make a racket on Connect (and then you do not even get to see the what I wrote to Microsoft in private mails...) is that it annoys me if the product that I love so much has flaws and they are not fixed. I don't stop loving the product, but I want more people to love it, and I think that perfecting all the flaws could help.
There are lots of other products that I do not love. Some Microsoft, some not. Some regularly used by me, some not. Try to find evidence on the internet of me ranting about some weird behaviour in MS Excel, MS Visio, Oracle, MySQL, ERWin Data Modeler, or any other product. You won't find anything. That's not because I consider those products perfect (They are not. Far from!) It is because I don't care a flying hoot about those products. SQL Server is the only product that I care so much about that I take the time and energy to investigate its rough edges, to warn people about it, and to shout and scream to Microsoft in the hope of getting them smoothed out.
I *love* SQL Server. I really do!
May 15, 2013 at 2:01 pm
Hugo Kornelis (5/15/2013)
Thanks, all for the kind comments and the valuable discussion.It's interesting to see how different people have different opinions about BETWEEN. In my experience, this sometimes goes wrong when variables are used in a BETWEEN, and often goes wrong with constants that represent negative numbers. With positive numbers, people always tend to put the smaller number first. With negative numbers, some people still do that, but other people tend to reverse the order, putting the number closest to zero first.
Makking this mistake may not be very common, but it can be extremely hard to troubleshoot. In this question, people expect there is probably a catch and there is only very little code to look at - and still 44% (currently) overlook the inverted BETWEEN clause. When this error is embedded in a 300-line stored procedure, it is even harder to spot.
I've been bitten by the BETWEEN bug with negative numbers before. Now that I do a fair amount of work with north latitude (0 < n < 90) and west longitude (range -180 < n < 0) values, I pay very close attention. Is -120.021832 less than or greater than -120.021850? Is 33.338232 greater than or less than 33.338132? It requires some concentration to keep it straight sometimes, especially since one range is positive and one is negative.
Jason Wolfkill
May 15, 2013 at 8:36 pm
wolfkillj (5/15/2013)
I've been bitten by the BETWEEN bug with negative numbers before. Now that I do a fair amount of work with north latitude (0 < n < 90) and west longitude (range -180 < n < 0) values, I pay very close attention. Is -120.021832 less than or greater than -120.021850? Is 33.338232 greater than or less than 33.338132? It requires some concentration to keep it straight sometimes, especially since one range is positive and one is negative.
Ditto here "Down Under", but (at least in my corner of the world) it is usually the latitude figures that catch me out.
For example, people looking at a map might think that Canberra (-35.3, 149.1) is between Sydney (-33.8, 151.2) and Melbourne (-37.8, 145.0) (all values approximate) but SQL thinks it is between Melbourne and Sydney.
Even more confusing, without proper regard for the order of the values when you put the queries together, SQL will tell you that Townsville (-19.2, 146.8) is neither between Cairns (-16.9, 145.7) and Mackay(-21.1, 149.1), nor between Mackay and Cairns.
May 15, 2013 at 10:33 pm
Excellent question Hugo....
May 16, 2013 at 5:44 am
I totally failed reading the "BETWEEN -1 AND -10" part.
Thank you, Hugo. Awesome question.
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
May 17, 2013 at 3:20 am
Great question, thanks Hugo.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 17, 2013 at 8:35 am
srienstr (5/15/2013)
Hugo Kornelis (5/15/2013)
(and if you go out in the street and ask people if -5 is between -1 and -10, I'm willing to bet that 99% of all people will say "yes").You have more faith in the mathematical skills of the populace than I do, I'll take that bet. 😀
I agree completely on the semantic meaning, and lack of a compelling reason for that difference. That said, we don't want to let semantic English creep into SQL to prevent OR from being defined as XOR.
You are demonstrating a lack of historical knowledge here.
SQL became "SQL" because someone other than IBM owned the rights to the original name the IBM developers who invented SQL chose, which was SEQUEL - for Structured English QUEry Language.
And anyone who thinks English OR means the same as XOR needs a remedial English course.
Tom
Viewing 15 posts - 31 through 45 (of 71 total)
You must be logged in to reply to this topic. Login to reply