Michael Wohlwend wrote:
But If I do "select data from pictures where (x between high_x and low_x)
and (y between high_y and low_y) then this takes ca. 8 seconds (!) on wince.
Michael,
If you are really writing your between clauses as above with the high
limit first, then they are not doing what you think. The low limit
should always be given first.
From the SQL:1999 standard:
8.3 <between predicate>
Function
Specify a range comparison.
Format
<between predicate> ::=
<row value expression> [ NOT ] BETWEEN
[ ASYMMETRIC | SYMMETRIC ]
<row value expression> AND <row value expression>
Syntax Rules
1) If neither SYMMETRIC nor ASYMMETRIC is specified, then ASYMMETRIC is
implicit.
2) Let X, Y, and Z be the first, second, and third <row value
expression>s, respectively.
3) ‘‘X NOT BETWEEN SYMMETRIC Y AND Z’’ is equivalent to ‘‘NOT ( X
BETWEEN SYMMETRIC
Y AND Z )’’.
4) ‘‘X BETWEEN SYMMETRIC Y AND Z’’ is equivalent to ‘‘((X BETWEEN
ASYMMETRIC Y AND
Z) OR (X BETWEEN ASYMMETRIC Z AND Y))’’.
5) ‘‘X NOT BETWEEN ASYMMETRIC Y AND Z’’ is equivalent to ‘‘NOT ( X BETWEEN
ASYMMETRIC Y AND Z )’’.
6) ‘‘X BETWEEN ASYMMETRIC Y AND Z’’ is equivalent to ‘‘X>=Y AND X<=Z’’.
Access Rules
None.
General Rules
one.
Conformance Rules
1) Without Feature T461, ‘‘Symmetric <between predicate>’’, conforming
SQL language shall not
specify SYMMETRIC or ASYMMETRIC.
2) Without Feature S024, ‘‘Enhanced structured types’’, no subfield of
the declared type of a <row
value expression> that is simply contained in a <between predicate>
shall be of a structured
type.
SQLite does not support symmetric between predicates so conformance rule
1 applies. This means that Syntax rule 1 also applies and asymmetric is
implied. This means that syntax rule 6 is used to translate the between
predicate.
In your case, this means your:
x between high_x and low_x
is equivalent to:
x >= high_x and x <= low_x
which will never be true.
Note that standard SQL does not reorder the limits in the between clause
if they are given in reverse order (even though it could).
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
