Look at this query
SELECT DISTINCT
TB.ID,
TB.Latitude,
TB.Longitude,
111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
`tablebusiness` AS TB
join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672 106.81978308133)'),
TA.Latlong
)
AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
ORDER BY
Distance
LIMIT
0, 20
that's basically search all bizs whose TA.LatLong is in the box of 'MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672 106.81978308133)' and after that the box must contain kucing
This returns 22 rows.
Now compared that to this query
SELECT DISTINCT
TB.ID,
TB.Latitude,
TB.Longitude,
111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
`tablebusiness` AS TB
join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
GeomFromText ('MULTIPOINT(-6.2317830813328 106.72621691867,-6.1382169186672 106.81978308133)'),
TA.Latlong
)
ORDER BY
Distance
LIMIT
0, 20
It's the same query but LESS restrictive. Now I only want to check whether the business is in the box or not. That is it. Yes there are businesses in those box. Most of which do not contain the word "kucing*"
There should be more queries. Yet there are less. The second query return null. The LESS restrictive query return LESS result.
Rolando answer is good with one catch.
The one with
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE) is the one that works properly.
I am asking why the one without is problematic. Rolando seems to try to correct the correct ones.
Note: (I wonder whether this should be another question). After listening to others I tried:
After removing distinct
SELECT
TB.ID,
111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
`tablebusiness` AS TB
join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
GeomFromText (
'MULTIPOINT(-6.1939967464102 106.76400325359,-6.1760032535898 106.78199674641)'
),
TA.Latlong)
AND TA.Prominent >15
ORDER BY
Distance
LIMIT
0, 20
returning 0 rows
Then I tried removing the order by and limit
SELECT
TB.ID,
111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance
FROM
`tablebusiness` AS TB
join tableauxiliary as TA on TA.BusinessID=TB.ID
WHERE
MBRContains(
GeomFromText (
'MULTIPOINT(-6.1939967464102 106.76400325359,-6.1760032535898 106.78199674641)'
),
TA.Latlong)
AND TA.Prominent >15
Which also result in 0 rows finally I use the original query and make sure I only remove
AND
MATCH (FullTextSearch) AGAINST ('kucing*' IN BOOLEAN MODE)
to turn results from some results to no results
Finally I make the query really simple
SELECT BusinessID, Prominent
FROM
tableauxiliary as TA
WHERE
MBRContains(GeomFromText ('MULTIPOINT(-6.2209869856406 106.73701301436,-6.1490130143594 106.80898698564)'),TA.Latlong)
That's it the most basic query ever.
Still return no result
Now here's a miracle. I add 2 characters and it works again. The characters I add is =1
SELECT BusinessID
FROM tableauxiliary AS TA
WHERE MBRContains( GeomFromText( 'MULTIPOINT(-6.2209869856406 106.73701301436,-6.1490130143594 106.80898698564)' ) , TA.Latlong )
=1
I got result
I guess it's time to report bugs.
Best Answer
If the second (indeed less restrictive) query returns zero rows while the first returns more than zero rows, then this is a bug.
First check if you can repoduce the error with only table or not. If the error stays while you remove the
DISTINCT
and/or theORDER BY ... LIMIT
.Then try to write the set of statements (
CREATE
tables,INSERT
rows, and the 2 queries, as simplified as possible) that reproduces the error, so others can test.Then you can submit it to MySQL.