Mysql Select

Not returning expected results

[gosman]

I'm running the following statement

select minx,maxx,miny,maxy from TABLE where 523892 between minx AND maxx AND 156758 between miny AND maxy

There are 2 rows being returned. (below)

minx | maxx | miny | maxy

---

522962 | 524063 | 156729 | 157043
523867 | 523892 | 156742 | 156797

---

I can't understand why row 1 is being returned.

156758 does not fall between 156729 and 157043

It's driving me crazy, help much appreciated.

[rocknbil]

Fascinating . . . .yes I'm a nerd . . .

create table minmax (id int(11) primary key auto_increment, minx int(11),maxx int(11), miny int(11), maxy int(11));

insert into minmax (minx,maxx,miny,maxy) values (522962,524063,156729,157043);
insert into minmax (minx,maxx,miny,maxy) values (523867,523892,156742,156797);

select minx,maxx,miny,maxy from minmax where 523892 between minx AND maxx AND 156758 between miny AND maxy;

FAIL!

select minx,maxx,miny,maxy from minmax where (523892 between minx AND maxx) AND (156758 between miny AND maxy);

FAIL! (and knew it would, but hadda try)

select minx,maxx,miny,maxy from minmax where 523892 > minx AND 523892 < maxx AND 156758 > miny AND 156758 < maxy;

WORKS!

I've no idea why between fails, the docs say it should be synonymous with the last statement. It must be that it's converting the data type or something, though explain doesn't tell us much.

[gosman]

Hi rocknbil

Your solution doesn't work either. It returns

522962 | 524063 | 156729 | 157043

The result I'm looking for is

523867 | 523892 | 156742 | 156797

Regards

gosman

[gosman]

Also rocknbil

if the value matches the min or max they are valid so I added the = sign to your statement and it produces the exact same result as my original statement

select minx,maxx,miny,maxy from ap_roads where 523892 >= minx AND 523892 <= maxx AND 156758 >= miny AND 156758 <= maxy;

It's driving me nuts now :(

[httpwebwitch]

I added a row with 1,3,1,3 to illustrate

select minx,maxx,miny,maxy,(523892 between minx AND maxx) as x, (156758 between miny AND maxy) as y from minmax;

minx |maxx |miny |maxy |x|y 
----------------------------------------- 
1  |3  |1  |3  |0|0 
522962|524063|156729|157043|1|1 
523867|523892|156742|156797|1|1 

[httpwebwitch]

156758 does not fall between 156729 and 157043

yes it does

[gosman]

Sorry httpwebwitch

I don't understand!

[gosman]

httpwebwitch

My bad. I need some sleep.