May be WAD may be bug. I suddenly thought is BETWEEN between or between and including (amazing what goes through your mind isn't it) so I ran a test.

I also thought I'd test something else out. This

select _ID from contacts where _ID between 10 and 100001

produces a 1 row result

whereas this

select _ID from contacts where _ID between 100001 and 10

returns a null row.

The manual doesn't say the parameters have to be in ascending order, and whilst it may be required I don't understand why that would be so.

Roy Lambert

Roy,

It's the standard definition for the "between" predicate.
Here is an excerpt of the relevant part in the SQL2003 standard docs:

-----------------------------------------------------------------------
1) If neither SYMMETRIC nor ASYMMETRIC is specified, then ASYMMETRIC is implicit.

(...)

6) “X BETWEEN ASYMMETRIC Y AND Z” is equivalent to “X>=Y AND X<=Z”.
-----------------------------------------------------------------------


--
Fernando Dias
[Team Elevate]

Fernando


OK so what's

X BETWEEN SYMMETRIC Y AND Z

Roy Lambert

Roy,

X BETWEEN SYMMETRIC Y AND Z is equivalent to (X>=Y AND X<=Z) OR (X>=Z AND X<=Y).

IOW, it's a short way to say:
X BETWEEN Y AND Z OR X BETWEEN Z AND Y

But AFAIK it's not implemented in EDB.

--
Fernando Dias
[Team Elevate]

Fernando


So SYMMETRIC sounds as though it means what almost any human being would translate BETWEEN to mean. Yet again I am astounded by the imbecility of the standard setters.

Roy Lambert

Roy,

> So SYMMETRIC sounds as though it means what almost any human being would translate BETWEEN to mean. Yet again I am astounded by the imbecility of the standard setters.

Ok, but don't kill the messenger, please

--
Fernando Dias
[Team Elevate]

Fernando

>Ok, but don't kill the messenger, please

Don't worry. I prefer to keep them alive so I can keep on torturing them.

Roy Lambert

Roy

So SYMMETRIC sounds as though it means what almost any human being would
translate BETWEEN to mean. Yet again I am astounded by the imbecility of the
standard setters.

So "the days between Monday and Thursday" is the same as "the days between
Thursday and Monday" ? <bg>

John

John

> So SYMMETRIC sounds as though it means what almost any human being would
>translate BETWEEN to mean. Yet again I am astounded by the imbecility of the
>standard setters.
>
>So "the days between Monday and Thursday" is the same as "the days between
>Thursday and Monday" ? <bg>

Yes and No

The days between Monday 1st and Thursday 4th are the same as the days between Thursday 4th and Monday 1st but not the same as the days between Thursday 4th and Monday 8th.

I brilliant example of partial definition of the parameters.

Roy Lambert

Roy
> Yes and No
>
> The days between Monday 1st and Thursday 4th are the same as the days
between Thursday 4th and Monday 1st but not the same as the days between
Thursday 4th and Monday 8th.
>
> I brilliant example of partial definition of the parameters.

With you on their case the standard setters are between a rock and a hard
place or was that between.....

John