

SSCertifiable
Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 6,527,
Visits: 9,042


Alex Fekken (5/18/2013)
But then, I am not a native English speaker, so what do I know? I probably should have confessed earlier that neither is mine. But judging by your last name Hugo, it is probably the same as yours (dutch). Yup, you guessed right  I'm Dutch too.
(I hope this discussion isn't undermining future questions that Hugo has planned; I see there is a '1' at the end of the question title) No worries. Number 2 has already been piblished and doesn't use BETWEEN. No #3 planned at this time. (And even if I had planned another question that involves BETWEEN, I'd still not worry about it. The majority of QotD responders never visit the forums, so there's still value in having a question that covers something already beaten to death on the forums).
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis




SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 8:44 PM
Points: 8,758,
Visits: 10,586


Alex Fekken (5/18/2013)[hr.When I said I would exclude the endpoints I was thinking of the continuous case, because it is the more general one (e.g. pick a number between 0 and 1). In the continuous case you think it intuitive to assume the interval is open. I tend to think the opposite, because I think that "between a and b" refers to a 1ball (or a 2ball, when a and b are positions on a plane, and so on); a ball in a Euclidean space is compact (under the induced topology) and there's no reason for the definition of a ball in 1dimensional Euclidean space to be different from the definition in higher dimensions, and of those four intervals only the one including both endpoints is compact. But usually in the continuous case I write the interval as for example (0,1), (0,1], [0,1) or [0,1] depending on which endpoint(s) I intend to include, and it would be nice if SQL could have a similar simple notation instead of "between". For ranges (discrete case), I use 1..9 instead of [1,9] and 2..8 instead of (1,9) so I feel strongly that the notation suggests that named bounds are included in the range case. So my mathematical background tells me to include endpoints.
I think the best way of dealing with ranges (which is what I think BETWEEN is trying to do) would be in the way SQL partition functions are defined because there are often multiple contiguous ranges to deal with. The moment you try the single out a single one of them you've got an issue with (or at least decisions to make about) the endpoints. Hugo avoided this by using integers, but coming from a mathematical background I prefer approaches and solutions that also work in, and are (or can be seen as) intuitive from, a more general perspective.
Yes, a nice way of indicating what to do about endpoints without reverting to direct use of the conjunction of two predicates would be preferable to "between", at least for those of us who are used to using notations that don't directly represent words (and I guess others could get used to it over time).
Tom




SSCEnthusiastic
Group: General Forum Members
Last Login: Monday, December 15, 2014 3:15 PM
Points: 197,
Visits: 460


This could go on for a long time...
a ball in a Euclidean space is compact (under the induced topology) and there's no reason for the definition of a ball in 1dimensional Euclidean space to be different from the definition in higher dimensions The balls are no longer compact in infinite dimensional spaces (where many interesting applications, e.g. quantum mechanics, are) unless you use very special (weak) topologies. Compact sets are important precisely because they are so special, not general. So I don't think compactness is a good argument. Much of function theory is done on open sets to avoid having to deal with boundaries, which is probably where my preference for open sets comes from. But I do agree with you notation for discrete sets (which are always both open and closed under the discrete topology anyway).
I think (and hope) that I have made my point, which is not that my intuition is "better" than yours, just that you could argue either way, depending on context and/or background.




SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 8:44 PM
Points: 8,758,
Visits: 10,586


Alex Fekken (5/19/2013)
This could go on for a long time... yes, it could.
The balls are no longer compact in infinite dimensional spaces (where many interesting applications, e.g. quantum mechanics, are) unless you use very special (weak) topologies. we maybe don't agree on what topologies are "weak". (closed) Balls in an infinitedimensional complete metric space are compact. Balls in an incomplete metric space are not (even in a simple 1dimensional case like the rational line, balls are not compact). I haven't looked at the topologies used in QM but I suspect it will be lack of completeness rather than infinite dimension that makes balls incompact. Of course Borel's proof of compactness of totally bounded closed sets in Euclidean space breaks down with infinite dimensions, and I think that even adding completeness in leaves one needing something analogous to the LowenheimSkolem theorem or Tychonoff's theorem (that might be the best starting point)  or maybe a weak version of the axiom of choice would be enough (anyway I'm confused as to whether Tychonoff's theorem implies AC or not  I think it doesn't, but I'm not sure, and all my maths books are in the UK, four hours flight away).
I think (and hope) that I have made my point, which is not that my intuition is "better" than yours, just that you could argue either way, depending on context and/or background.
Oh, yes, quite definitely  it is a matter of how one reacts to "between" and there's nothing that would make everyone react the same way. My point really was that there's nothing inherently counterintuitive in inclusion of bounds, and I didn't want to suggest that there would be something inherently counterintuitive in their exclusion (although in the discrete case there would be for me, there might not be for something else).
Tom




SSCEnthusiastic
Group: General Forum Members
Last Login: Monday, December 15, 2014 3:15 PM
Points: 197,
Visits: 460


You made me doubt myself for a moment, but:
http://planetmath.org/compactnessofclosedunitballinnormedspaces
[cool math]Pretty sure the same is true of any metric space: cover the ball with open neighbourhoods of the endpoints of a basis of unit vectors where each such neighbourhood covers only one such endpoint, as well as any open sets that you need to cover the rest of the ball but that do not cover those endpoints. If you have an infinite number of dimensions then you won't be able to refine the cover to a finite one because each endpoint is covered by only one open set.[/cool math]
Thanks for an interesting discussion. We should probably continue it somewhere else if you want to, given the direction this is taking...




SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 8:44 PM
Points: 8,758,
Visits: 10,586


Alex Fekken (5/19/2013) You made me doubt myself for a moment, but:
You are right. I should be more careful when speculating without references. AC implies that there are bounded infinite point sequences that have no limit points in any infinitedimensional normed space, so it can't be compact. I wish I'd been aware of that 46 years ago! http://planetmath.org/compactnessofclosedunitballinnormedspaces
[cool math]Pretty sure the same is true of any metric space: cover the ball with open neighbourhoods of the endpoints of a basis of unit vectors where each such neighbourhood covers only one such endpoint, as well as any open sets that you need to cover the rest of the ball but that do not cover those endpoints. If you have an infinite number of dimensions then you won't be able to refine the cover to a finite one because each endpoint is covered by only one open set.[/cool math]
That probably works for infinite dimensional metric vector spaces even if the metric isn't induced by a homogenous norm, so if you have the definition of dimensionality which requires a vector space it's fine. I don't recall whether that definition of dimensionality was generally accepted though, there used to be many other definitions, several of which didn't even require a metric, let alone a coordinate system. I'm not sure whether it works for nonvector space metric spaces (if we use a definition that allows them to have infinite dimensionality, and maybe a field other than the reals for the distance field). That's probably too difficult question for my (extremely rusty) knowledge of topology.
Tom




SSC Eights!
Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825,
Visits: 319


Good question. I'd hope I'd never write BETWEEN 1 AND 10 but it was easy to miss as an error/anomaly in someone else's code.




SSCrazy
Group: General Forum Members
Last Login: Thursday, June 25, 2015 3:43 PM
Points: 2,262,
Visits: 917


It's a shame that "Between 1 And 10" and "Between 10 and 1" aren't equivalent in TSQL.
I haven't read BOL re. "Between" but does it state that it has to be Lower to Higher ?
If it was an Exam Question then I would've accepted both Answers.
David




SSCertifiable
Group: General Forum Members
Last Login: Today @ 9:03 AM
Points: 6,527,
Visits: 9,042


David Conn (5/23/2013) It's a shame that "Between 1 And 10" and "Between 10 and 1" aren't equivalent in TSQL.
I haven't read BOL re. "Between" but does it state that it has to be Lower to Higher ? http://msdn.microsoft.com/enus/library/ms187922.aspx
If it was an Exam Question then I would've accepted both Answers. If the exam question was on English, I would agree. For an SQL exam, I would not. Examiners are supposed to test whether the candidate understands the subject matter as it is, not as the examiner would like it to be.
For an exam with open (not multiple choice) questions, if BETWEEN was used in an answer with begin and end parameters reversed but otherwise correct, I would only apply a small deduction.
Hugo Kornelis, SQL Server MVP Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis




SSC Eights!
Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825,
Visits: 319


David Conn (5/23/2013) It's a shame that "Between 1 And 10" and "Between 10 and 1" aren't equivalent in TSQL.
David
More to the point, it's a shame you don't get an error when BETWEEN <higher> AND <lower> is encountered.



