Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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. –  Jim Thio 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. –  Jim Thio 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

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks. This seems to make more sense. –  Jim Thio Jun 25 '12 at 16:13
    
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
    on TA.BusinessID=TB.ID
    ORDER BY
        Distance
) AA LIMIT 0, 20;
share|improve this answer
1  
I was on the verge of commenting on the question 'weird'. Now I must say that this is the weirdest of all... Can't imagine anything like that in other RDBMSes I'm more or less familiar with. –  dezso Jun 21 '12 at 18:24
    
I agree with you. The answer is even more bizare than the question. I will give it a try. –  Jim Thio Jun 22 '12 at 15:18
    
The problem with your solution is that the query with the fulltext search behave as expected. I want the query without the full text search to work too. I am displaying the query with the full text search to show that there are indeed points within the rectangle I want. –  Jim Thio Jun 23 '12 at 15:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.