BAZOMBO: a discursive miscellany of topics

Falling between two stools

Tuesday, May 13 2008

I love a good bit of language lawyering as much as the next programmer, but perhaps not so much when I am the victim, as opposed to the smug bastard who remembers all the footnotes and can do a good Nelson Muntz-style “ha ha!”

It turns out that in SQL, the BETWEEN AND comparison operator does not work quite the way you would expect. Quoting the Postgres manual:

a BETWEEN x AND y

is equivalent to

a >= x AND a <= y

Similarly,

a NOT BETWEEN x AND y

is equivalent to

a < x OR a > y

Now, if perchance x is greater than y — for example, if you write SELECT foo FROM bar WHERE foo BETWEEN 200 AND 100 — SQL will treat that as if you had written SELECT foo FROM bar WHERE foo >= 200 AND foo <= 100, and return nothing, thus doing the complete opposite of what you might expect from the normal English meaning of  “between”.

This has tripped up both me and a colleague in the last couple of weeks.

Not so much syntactic sugar as syntactic Nutrasweet…

no comments

Tags: sql ~ programming ~ annoyance

---