BETWEEN bug ?

------=_NextPart_000_0002_01C2F712.DB437B60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

Ver 8.23 Distrib 3.23.47, for Win95/Win98 on i32

OS: WIN98
EXE: mysqld

________________

SELECT pgrid, LEFT(pgrid,4) AS gridx, RIGHT(pgrid,4) AS gridy, crag
FROM crags
HAVING gridx > 5088 AND gridx < 5615 AND gridy > 982 AND gridy < 1508

c/f

SELECT pgrid, LEFT(pgrid,4) AS gridx, RIGHT(pgrid,4) AS gridy, crag
FROM crags
HAVING gridx BETWEEN 5088 AND 5615 AND gridy BETWEEN 982 AND 1508


pgrid is an 8 character string representing an 8 fig zero filled integer

the above statements should be identical

Only the latter returns correctly
The former returns nothing

eg a rows pgrid value of 55651392 is returned only by the latter.

I found changing the limits as so:

SELECT pgrid, LEFT(pgrid,4) AS gridx, RIGHT(pgrid,4) AS gridy, crag
FROM crags
HAVING gridx BETWEEN 5088 AND 5615 AND gridy BETWEEN 1000 AND 1508

then made it work fine. 999 error, 1000 success.

--
Mike Whittaker
Mike@HotPud.com
~~~~~~~~~~~
Rock Climbing Database at http://www.hotpud.com

------=_NextPart_000_0002_01C2F712.DB437B60--

Re: BETWEEN bug ?

Hi Mike,

On Mon, 2003-03-31 at 08:19, Mike Whittaker wrote:
> Ver 8.23 Distrib 3.23.47, for Win95/Win98 on i32
>
> OS: WIN98
> EXE: mysqld
>
> ________________
>
> SELECT pgrid, LEFT(pgrid,4) AS gridx, RIGHT(pgrid,4) AS gridy, crag
> FROM crags
> HAVING gridx > 5088 AND gridx < 5615 AND gridy > 982 AND gridy < 1508
>
> c/f
>
> SELECT pgrid, LEFT(pgrid,4) AS gridx, RIGHT(pgrid,4) AS gridy, crag
> FROM crags
> HAVING gridx BETWEEN 5088 AND 5615 AND gridy BETWEEN 982 AND 1508
>
>
> pgrid is an 8 character string representing an 8 fig zero filled integer
>
> the above statements should be identical
>
> Only the latter returns correctly
> The former returns nothing
>
> eg a rows pgrid value of 55651392 is returned only by the latter.
>
> I found changing the limits as so:
>
> SELECT pgrid, LEFT(pgrid,4) AS gridx, RIGHT(pgrid,4) AS gridy, crag
> FROM crags
> HAVING gridx BETWEEN 5088 AND 5615 AND gridy BETWEEN 1000 AND 1508
>
> then made it work fine. 999 error, 1000 success.

A couple of things:
- You should do LEFT(pgrid,4)+0 AS gridx to make it an integer.
- "x BETWEEN y AND z" is "x >= y AND x <= z", it's inclusive (>= and
<=).
- Why not store pgridx/pgridy directly, and only assemble "pgrid" when
doing a SELECT (or just in your application). With your current design
any query will be intrinsically inefficient, you can't use a WHERE
clause or indexes because the columns always have to be calculated.


Regards,
Arjen.
--
Training,Support,Licenses,T-shirts @ https://order.mysql.com/?marl
Sydney AUS: June 23-27, Using & Managing MySQL training
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Arjen G. Lentz
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer
/_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: BETWEEN bug ?

Hi!

On Mar 30, Mike Whittaker wrote:
> Ver 8.23 Distrib 3.23.47, for Win95/Win98 on i32
>
> OS: WIN98
> EXE: mysqld
>
> ________________
>
> SELECT pgrid, LEFT(pgrid,4) AS gridx, RIGHT(pgrid,4) AS gridy, crag
> FROM crags
> HAVING gridx > 5088 AND gridx < 5615 AND gridy > 982 AND gridy < 1508
>
> c/f
>
> SELECT pgrid, LEFT(pgrid,4) AS gridx, RIGHT(pgrid,4) AS gridy, crag
> FROM crags
> HAVING gridx BETWEEN 5088 AND 5615 AND gridy BETWEEN 982 AND 1508
>
> pgrid is an 8 character string representing an 8 fig zero filled integer
>
> the above statements should be identical
>
> Only the latter returns correctly
> The former returns nothing
>
> eg a rows pgrid value of 55651392 is returned only by the latter.

They are not identical.
What is different is implicit type conversion.
When you compare string to an integer, both arguments are converted to a
floating-point numbers.

gridy > 982 is the same as gridy+0.0 > 982.0

In BETWEEN, arguments are converted to the type of FIRST operand (in
MySQL 3.23.x, that is)

gridy BETWEEN 982 AND 1508 is the same as
gridy BETWEEN "982" AND "1508"

because gridy is a string. And, naturally,

RIGHT(pgrid,4) >= "982"

fails for pgrid="55651392".

Check the section "Comparison Operators" of the manual, where implicit
type conversion is described.

Note, that in 4.0, your BETWEEN would work as expected, as 4.0, when
comparing string to a number, would convert all operands to
floating-point numbers.

Regards,
Sergei

--
MySQL Development Team
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Sergei Golubchik
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/
/_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany
<___/



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org