August 1, 2012 at 2:03 pm
Small details that are easy to forget because they're not implemented as their name implies. In this case BETWEEN doesn't really mean between. If you have "value BETWEEN a AND b", it really means "value >= a AND value <= b".
IF (0 BETWEEN -0.05 AND 0.05)
Print('true');
ELSE
Print('false');
IF (0 BETWEEN 0.05 AND -0.05)
Print('true');
ELSE
Print('false');
Will print,
true
false
Sigh. Why wouldn't this be changed to actually act as its name implies?
August 1, 2012 at 2:07 pm
I understand your point but the lowest value first makes sense. Would you tell somebody that you will come visit them sometime BETWEEN 7pm and 6pm? They would look at you like you were an alien. In that example does that mean the same day or 7pm one day and 6pm the next day? Can you start to see that is would somewhat depend on the context of what between means if you allow either value to be the "lowest"?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 2:07 pm
If you have "value BETWEEN a AND b", it really means "value >= a AND value <= b".
So your second statement is seeing if 0 >= 0.05 AND 0 <= -0.05, both of which are false. Which is the way SQL Server looks at it.
August 1, 2012 at 2:38 pm
Yes, that's my point. Between does not logically imply a comparison order, but SQL Server imposes one.
And consider if you want to supply variables instead of literal values for a and b. You might not be able to assume at any given time the relative value of each variable which would mean you would need add additional logic just to handle that.
August 1, 2012 at 2:42 pm
MarbryHardin (8/1/2012)
Yes, that's my point. Between does not logically imply a comparison order, but SQL Server imposes one.And consider if you want to supply variables instead of literal values for a and b. You might not be able to assume at any given time the relative value of each variable which would mean you would need add additional logic just to handle that.
That's why your application should sanitize inputs. Web pages do it all the time in the form, not in the database logic.
August 1, 2012 at 2:48 pm
Scott D. Jacobson (8/1/2012)
MarbryHardin (8/1/2012)
Yes, that's my point. Between does not logically imply a comparison order, but SQL Server imposes one.And consider if you want to supply variables instead of literal values for a and b. You might not be able to assume at any given time the relative value of each variable which would mean you would need add additional logic just to handle that.
That's why your application should sanitize inputs. Web pages do it all the time in the form, not in the database logic.
Well you pretty much have to the way that works don't you?
But the point was still that functionally "BETWEEN" in TSQL doesn't match what one would logically expect from the name. Which would be, "is value in the range defined by the endpoints a and b?"
August 1, 2012 at 2:52 pm
MarbryHardin (8/1/2012)
Scott D. Jacobson (8/1/2012)
MarbryHardin (8/1/2012)
Yes, that's my point. Between does not logically imply a comparison order, but SQL Server imposes one.And consider if you want to supply variables instead of literal values for a and b. You might not be able to assume at any given time the relative value of each variable which would mean you would need add additional logic just to handle that.
That's why your application should sanitize inputs. Web pages do it all the time in the form, not in the database logic.
Well you pretty much have to the way that works don't you?
But the point was still that functionally "BETWEEN" in TSQL doesn't match what one would logically expect from the name. Which would be, "is value in the range defined by the endpoints a and b?"
But it does perform exactly how BOL describes it, so it is a good lesson on reading the documentation if you aren't sure how an operator works.
August 1, 2012 at 3:00 pm
MarbryHardin (8/1/2012)
Scott D. Jacobson (8/1/2012)
MarbryHardin (8/1/2012)
Yes, that's my point. Between does not logically imply a comparison order, but SQL Server imposes one.And consider if you want to supply variables instead of literal values for a and b. You might not be able to assume at any given time the relative value of each variable which would mean you would need add additional logic just to handle that.
That's why your application should sanitize inputs. Web pages do it all the time in the form, not in the database logic.
Well you pretty much have to the way that works don't you?
But the point was still that functionally "BETWEEN" in TSQL doesn't match what one would logically expect from the name. Which would be, "is value in the range defined by the endpoints a and b?"
You're right it doesn't. SQL Server changes value BETWEEN a AND b into value >= a and value <= b. But what you are saying is that it should change it to (value >= a and value <= b) or (value >= b and value <= a). I'm curious (but not enough to test it right now) if you code a query like that, how does it compare to one that uses the BETWEEN functionality as designed?
August 1, 2012 at 3:04 pm
But the point was still that functionally "BETWEEN" in TSQL doesn't match what one would logically expect from the name. Which would be, "is value in the range defined by the endpoints a and b?"
I would disagree with that. As I explained in my first post. Between is used in math to describe an interval. That interval in math is
a <= y < = b
http://en.wikipedia.org/wiki/Interval_%28mathematics%29
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 1, 2012 at 8:36 pm
MarbryHardin (8/1/2012)
Well you pretty much have to the way that works don't you?
Yes, that's normal application development practice. You should learn to separate what is appropriate at the DB level, what is appropriate at the application level and how to separate business logic from each.
But the point was still that functionally "BETWEEN" in TSQL doesn't match what one would logically expect from the name. Which would be, "is value in the range defined by the endpoints a and b?"
I'm not sure I agree with the "logically" part of your argument. I'm sure I made this mistake at some point early in my experience with TSQL but I wasn't so incredulous to think that the behavior of the operator was wrong. Once I read the BOL documentation I said to myself, "Huh, that's where I went wrong."
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply