Welcome to WebmasterWorld Guest from 54.235.46.164

Forum Moderators: open

Message Too Old, No Replies

Mysql Select

Not returning expected results

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

Preferred Member

10+ Year Member

joined:Sept 7, 2003
posts:383
votes: 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.
6:32 pm on Oct 13, 2011 (gmt 0)

Senior Member

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

joined:Nov 28, 2004
posts:7999
votes: 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.
6:42 pm on Oct 13, 2011 (gmt 0)

Preferred Member

10+ Year Member

joined:Sept 7, 2003
posts:383
votes: 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
6:46 pm on Oct 13, 2011 (gmt 0)

Preferred Member

10+ Year Member

joined:Sept 7, 2003
posts:383
votes: 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 :(
6:56 pm on Oct 13, 2011 (gmt 0)

Moderator from CA 

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

joined:Aug 29, 2003
posts:4059
votes: 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
7:01 pm on Oct 13, 2011 (gmt 0)

Moderator from CA 

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

joined:Aug 29, 2003
posts:4059
votes: 0


156758 does not fall between 156729 and 157043


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

Preferred Member

10+ Year Member

joined:Sept 7, 2003
posts:383
votes: 0


Sorry httpwebwitch

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

Preferred Member

10+ Year Member

joined:Sept 7, 2003
posts:383
votes: 0


httpwebwitch

My bad. I need some sleep.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members