# Less restrictive query return less result due to simple removing one additional constraint

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)


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.

• How can the first query return 22 rows when you've said to limit to 20? – Cade Roux Jun 21 '12 at 18:46
• I think I once unlimit the row and it says there are 22. – user4951 Jun 22 '12 at 8:40
• did the first query you gave in the question above (as it is written) return 20 or 22? If it is different from the unlimited one which did return 22, are you 100% sure there were no other differences? – Cade Roux Jun 22 '12 at 14:52
• I'll try again to make sure. – user4951 Jun 22 '12 at 15:17
• @Jim: WHat happens when you remove the DISTINCT and the LIMIT (from both queries)? – ypercubeᵀᴹ Jun 25 '12 at 12:36

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 the ORDER 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.

• If you really want to try identifying the cause, you could try several versions of the query (rewriting it) and several versions of similar queries (like my suggestion, removing/adding stuff) and checking which give consistent and which inconsistent results. – ypercubeᵀᴹ Jun 25 '12 at 16:17
• I have a hunch that it may be related to the use of both a spatial function/index (MBRContains) and a Full Text index (and possibly the joining). – ypercubeᵀᴹ Jun 25 '12 at 16:18

The FULLTEXT index acts very funny with regard to the MySQL Query Optimizer. I have written about this before:

Try refactoring your first query so that the FULLTEXT clause and the Spatial clause are not performed in the same level of the EXPLAiN plan.

SELECT * FROM
(
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
(
SELECT * FROM tablebusiness
WHERE MATCH (FullTextSearch) AGAINST
('kucing*' IN BOOLEAN MODE)
) AS TB
join
(
SELECT ID FROM tableauxiliary
WHERE MBRContains(GeomFromText ('MULTIPOINT
(-6.2317830813328 106.72621691867,-6.1382169186672 106.81978308133)'),TA.Latlong)
) as TA