Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178])
	by postgresql.org (Postfix) with ESMTP id 9E1E847595B
	for <pgsql-sql@postgresql.org>; Wed, 10 Jul 2002 16:10:05 -0400 (EDT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 9F735D5F7; Wed, 10 Jul 2002 13:10:06 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 9531B5C02; Wed, 10 Jul 2002 13:10:06 -0700 (PDT)
Date: Wed, 10 Jul 2002 13:10:06 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Josh Berkus <josh@agliodbs.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: BETWEEN bug?
In-Reply-To: <200207101235.53848.josh@agliodbs.com>
Message-ID: <20020710125904.O53979-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII
X-Archive-Number: 200207/145
X-Sequence-Number: 8524


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.