Welcome to WebmasterWorld Guest from 54.159.240.93

Forum Moderators: open

Mysql Select

Not returning expected results

   
5:48 pm on Oct 13, 2011 (gmt 0)

10+ Year Member



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.
6:32 pm on Oct 13, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
6:42 pm on Oct 13, 2011 (gmt 0)

10+ Year Member



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
6:46 pm on Oct 13, 2011 (gmt 0)

10+ Year Member



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 :(
6:56 pm on Oct 13, 2011 (gmt 0)

WebmasterWorld Administrator httpwebwitch is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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
7:01 pm on Oct 13, 2011 (gmt 0)

WebmasterWorld Administrator httpwebwitch is a WebmasterWorld Top Contributor of All Time 10+ Year Member



156758 does not fall between 156729 and 157043


yes it does
7:02 pm on Oct 13, 2011 (gmt 0)

10+ Year Member



Sorry httpwebwitch

I don't understand!
7:04 pm on Oct 13, 2011 (gmt 0)

10+ Year Member



httpwebwitch

My bad. I need some sleep.
 

Featured Threads

My Threads

Hot Threads This Week

Hot Threads This Month