Re: BETWEEN bug?

On Wed, 10 Jul 2002, Josh Berkus wrote:

> Folks,
>
> Why does BETWEEN only work for ascending criteria? For example:
>
> jwnet=> select '2002-06-07'::TIMESTAMP BETWEEN '2002-06-29'::TIMESTAMP and
> '2002-06-01'::TIMESTAMP;
> ?column?
> ----------
> f
> (1 row)
>
> jwnet=> select '2002-06-07'::TIMESTAMP BETWEEN '2002-06-01'::TIMESTAMP and
> '2002-06-29'::TIMESTAMP;
> ?column?
> ----------
> t
> (1 row)
>
> The above behaviour does not seem logical; is this a SQL spec thing, or a bug?

Spec thing.

In SQL92,
"X BETWEEN Y AND Z" is equivalent to "X>=Y AND X<=Z".

In SQL99, there's BETWEEN SYMMETRIC and ASYMMETRIC, but ASYMMETRIC is the
default which is the same as the SQL92 version afaics. Symmetric is an
optional feature that I think Christopher's been working on.