| Mysql Select
Not returning expected results |
gosman
msg:4374080 | 5:48 pm on Oct 13, 2011 (gmt 0) |
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
msg:4374094 | 6:32 pm on Oct 13, 2011 (gmt 0) |
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
msg:4374095 | 6:42 pm on Oct 13, 2011 (gmt 0) |
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
msg:4374096 | 6:46 pm on Oct 13, 2011 (gmt 0) |
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
msg:4374098 | 6:56 pm on Oct 13, 2011 (gmt 0) |
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
msg:4374100 | 7:01 pm on Oct 13, 2011 (gmt 0) |
| 156758 does not fall between 156729 and 157043 |
|
yes it does
|
gosman
msg:4374102 | 7:02 pm on Oct 13, 2011 (gmt 0) |
Sorry httpwebwitch
I don't understand!
|
gosman
msg:4374103 | 7:04 pm on Oct 13, 2011 (gmt 0) |
httpwebwitch
My bad. I need some sleep.
|
|
|
