Postgres Pro
Downloads
Home   >   mailing lists

Thread: BETWEEN bug?

BETWEEN bug?

From
Josh Berkus
Date:
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?

--
-Josh Berkus

Re: BETWEEN bug?

From
Stephan Szabo
Date:
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.

Re: BETWEEN bug?

From
Josh Berkus
Date:
Stephan,

> 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.

Thanks.  Thought it was something like that.

Thank you guys, though, OVERLAPS is asymmetric.

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________                                       Josh Berkus  Complete
informationtechnology     josh@agliodbs.com   and data management solutions     (415) 565-7293  for law firms, small
businesses     fax 621-2533   and non-profit organizations.     San Francisco

Re: BETWEEN bug?

From
Josh Berkus
Date:
Stephan,

> Thanks.  Thought it was something like that.
>
> Thank you guys, though, OVERLAPS is asymmetric.

Err ... I meant "symmetric".

--
-Josh BerkusAglio Database SolutionsSan Francisco