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

Question

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.

Answer 1

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.

Answer 2

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

• https://stackoverflow.com/a/6092216/491757 (May 23, 2011)
• FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional (Oct 25, 2011)
• Mysql fulltext search my.cnf optimization (Jan 26, 2012)
• MySQL EXPLAIN doesn't show 'use index' for FULLTEXT (May 07, 2012)

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
    on TA.BusinessID=TB.ID
    ORDER BY
        Distance
) AA LIMIT 0, 20;