MySQL Bugs: #68001: Server version: 5.6.8-rc-log query does not return right resultset

Bug #68001	Server version: 5.6.8-rc-log query does not return right resultset
Submitted:	31 Dec 2012 18:00	Modified:	24 Jan 2014 17:39
Reporter:	steven tang	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.6.15 (GA)	OS:	Any
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
Somehow the join query with spatial don’t return expected result. The highlight one (in red) should be the result for the last query, but it return empty.

We test the similar query in 5.5.25a, it works fine. 

I think the issue is the new MySQL 5.6 RC. 

Regards,

Steven

C:\>mysql -uwriter -pwriter -h192.168.169.82 quadrant
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5619
Server version: 5.6.8-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select c.id, b.csd_id,b.csd_s_id  
       from csd_binding b,csd c  
       where c.id=b.csd_id 
       and within(geomfromtext('point(64.849551 -147.706117)'),landmark);

+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8782782 | 8782782 |    25943 |
| 8786599 | 8786599 |    26008 |
| 8786599 | 8786599 |    26010 | <-- the record wanted!
+---------+---------+----------+
3 rows in set (0.00 sec)

mysql> select c.id,b.csd_id,b.csd_s_id 
       from csd_binding b,csd c  
       where b.csd_id = c.id and b.csd_s_id =26010;
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786602 | 8786602 |    26010 |
| 8786601 | 8786601 |    26010 |
| 8786600 | 8786600 |    26010 |
| 8786598 | 8786598 |    26010 |
| 8786599 | 8786599 |    26010 | <-- the record is here to return
| 8786603 | 8786603 |    26010 |
+---------+---------+----------+
6 rows in set (0.00 sec)

mysql> select c.id, b.csd_id,b.csd_s_id  
       from csd_binding b,csd c  
       where c.id=b.csd_id and b.csd_s_id=26010 
       and within(geomfromtext('point(64.849551 -147.706117)'),landmark);

Empty set (0.00 sec)

If I changed the above query to following, it returns the correct result!!!!

mysql> select * from (
    -> select c.id, b.csd_id,b.csd_s_id
    -> from csd_binding b,csd c
    -> where c.id=b.csd_id -- and b.csd_s_id=26010
    -> and within ( geomfromtext('point(64.849551 -147.706117)'), landmark)
    -> ) d where d.csd_s_id=26010;
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786599 | 8786599 |    26010 |
+---------+---------+----------+
1 row in set (0.00 sec)

How to repeat:
got the same result all the times.

Suggested fix:
It is simply a MySQL bug in server 5.6 RC and must be fixed in order for MySQL 5.6 release.

Please provide the complete test case (create table, insert data and so on). Thanks.

I will upload the two mysql dump files.

I was not able to use your FTP.

C:\>ftp ftp.oracle.com/support/incoming/
Unknown host ftp.oracle.com/support/incoming/.

Steven:

  ftp ftp.oracle.com

with anonymous login, then

ftp> cd support/incoming

works for me.

still could not upload the file:
ftp> send bug-data-68001.rar
200 PORT command successful. Consider using PASV.
425 Failed to establish connection.
ftp> pwd
257 "/support/incoming"
ftp> send bug-data-68001.rar
200 PORT command successful. Consider using PASV.
553 Could not create file.
ftp>

Windows FTP client does not use passive mode by default. Try "QUOTE PASV"  before you "send" the file.

Found WinXP box, tested with default windows ftp client, got the same error.
Then tested ftp upload with http://www.farmanager.com, worked fine.

So I recommend to install a better ftp client than default Windows one.

I used Linux ftp client and got following error:

ftp> put  bug-data-68001.rar
local: bug-data-68001.rar remote: bug-data-68001.rar
227 Entering Passive Mode (141,146,44,21,214,69)
553 Could not create file.
ftp>

Steven,

what is the size of bug-data-68001.rar file?

-rw-r--r-- 1 root root 307894612 Dec 31 12:59 bug-data-68001.rar

given the ftp://ftp.oracle.com/FAQ_README.txt try a different filename, e.g. bug-data-68001-1.rar

Steven,

ncftp gives explaining  error message:

***********************
# ncftp -u anonymous ftp.oracle.com
Logging in...                                                                            
Login successful.
Logged in to ftp.oracle.com.                                                             
ncftp / > cd support/incoming/
Directory successfully changed.
ncftp /support/incoming > put bug-data-68001.rar

The remote file "bug-data-68001.rar" already exists.
       Local:             0 bytes, dated January 10, 2013 07:16:44 PM CET.
       Remote:            0 bytes, dated January  2, 2013 05:48:58 PM CET.

       [O]verwrite?  [A]ppend to?  [S]kip?  [N]ew Name?
       [O!]verwrite all?  [S!]kip all?  [C]ancel  > 
***********************

It seems a joke.

Can you delete it?

I have created for you on the FTP server a new folder. 
    support/incoming/bug68001

Please try uploading your .rar file there. 

-- 
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

uploaded the rar file.

226 File receive OK.
307894612 bytes sent in 83 seconds (3.6e+03 Kbytes/s)

Thank you for the file.

I can not repeat described behavior with current development sources.

Please try with current version 5.6.9 and if problem still exists please try with reduced dump which I upload shortly and inform us if problem is repeatable with reduced dump too.

Attention! Reduced dump uses same table names, so restore it into different database or edit table names prior restoring!

reduced dump

Attachment: bug68001_reduced.sql (application/octet-stream, text), 73.84 KiB.

Did not test against specified version 5.6.8-rc?

Thank you for the feedback.

We don't backport bug fixes, so there is no sense to test with version 5.6.8

However I just tested with reduced test case and could not repeat. This is why it is important to test with new version in your environment.

I just tested under 5.6.10 (GA) the resultset is still empty.
mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.10-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.5               |
+-------------------------+------------------------------+

I am just wondering how you tested it or even you actually tested it at all with all my effort to send you my big bug data!

C:\PROJECTS\SVN\Trunk\Database\Schemas\Oracle\Quadrant\patch>mysql -uroot -p -hdev82 quadrant
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 33
Server version: 5.6.10-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select c.id, b.csd_id,b.csd_s_id
    ->        from csd_binding b,csd c
    ->        where c.id=b.csd_id
    ->        and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8782782 | 8782782 |    25943 |
| 8786599 | 8786599 |    26008 |
| 8786599 | 8786599 |    26010 |
+---------+---------+----------+
3 rows in set (0.00 sec)

mysql> select c.id,b.csd_id,b.csd_s_id
    ->        from csd_binding b,csd c
    ->        where b.csd_id = c.id and b.csd_s_id =26010;
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786602 | 8786602 |    26010 |
| 8786601 | 8786601 |    26010 |
| 8786600 | 8786600 |    26010 |
| 8786598 | 8786598 |    26010 |
| 8786599 | 8786599 |    26010 |
| 8786603 | 8786603 |    26010 |
+---------+---------+----------+
6 rows in set (0.00 sec)

mysql> select c.id, b.csd_id,b.csd_s_id
    ->        from csd_binding b,csd c
    ->        where c.id=b.csd_id and b.csd_s_id=26010
    ->        and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
Empty set (0.00 sec)

mysql>

mysql> select * from (
    -> select c.id, b.csd_id,b.csd_s_id
    -> from csd_binding b,csd c
    -> where c.id=b.csd_id -- and b.csd_s_id=26010
    -> and within ( geomfromtext('point(64.849551 -147.706117)'), landmark)
    ->  ) d where d.csd_s_id=26010;
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786599 | 8786599 |    26010 |
+---------+---------+----------+
1 row in set (0.00 sec)

You MySQL Server 5.6.10 (GA) definitely has a bug that you have ignored to address!!!!

Hi Steven,

I have made several attempts at reproducing the wrong result problem using bug68001_reduced.sql without luck. Here's from 5.6.10:

------------------------------
show variables like 'version%';
Variable_name	Value
version	5.6.10-debug-log
version_comment	Source distribution
version_compile_machine	x86_64
version_compile_os	Linux

select c.id, b.csd_id,b.csd_s_id
from csd_binding b,csd c
where c.id=b.csd_id
and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
id	csd_id	csd_s_id
8786599	8786599	26008
8786599	8786599	26010            <----

# ^ BTW, the reduced test case does not contain a csd row with id=8782782

select c.id,b.csd_id,b.csd_s_id
from csd_binding b,csd c
where b.csd_id = c.id and b.csd_s_id =26010;
id	csd_id	csd_s_id
8786598	8786598	26010
8786599	8786599	26010               <----
8786600	8786600	26010
8786601	8786601	26010
8786602	8786602	26010
8786603	8786603	26010

select c.id, b.csd_id,b.csd_s_id
from csd_binding b,csd c
where c.id=b.csd_id and b.csd_s_id=26010
and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
id	csd_id	csd_s_id
8786599	8786599	26010            <----

select * from (
select c.id, b.csd_id,b.csd_s_id
from csd_binding b,csd c
where c.id=b.csd_id and b.csd_s_id=26010
and within ( geomfromtext('point(64.849551 -147.706117)'), landmark)
) d where d.csd_s_id=26010;
id	csd_id	csd_s_id
8786599	8786599	26010             <----
------------------------------

That's the bad news. However, there is also some good news that indicates that you have indeed found a bug:

explain
select  b.csd_id,b.csd_s_id
from csd_binding b force index (csd_binding_landmark)
where within(geomfromtext('point(64.849551 -147.706117)'),landmark);
id	1
select_type	SIMPLE
table	b
type	range               <------
possible_keys	csd_binding_landmark
key	csd_binding_landmark <------
key_len	35
ref	NULL
rows	200
Extra	Using where

Range scan is not allowed on non-spatial indexes, but in the explain above MySQL does exactly that. See here: "A B-tree index on spatial values will be useful for exact-value lookups, but not for range scans." (http://dev.mysql.com/doc/refman/5.6/en/creating-spatial-indexes.html)

I suggest you try to rerun the queries in one of these ways: either remove the index over landscape or use the ignore index(csd_binding_landmark) syntax. If the bug goes away when you do this, your issue is a duplicate of BUG#67889. If you still get wrong result we need to investigate more. In that case, please provide EXPLAIN output of the failing queries. Also, please use the datadump in "bug68001_reduced.sql" so that we debug with the same data.

Well, I added the ignore index and the query returns the right resultset! 
Yes indeed you got a bug in 5.6.10!

mysql> explain select c.id, b.csd_id,b.csd_s_id
    -> from csd_binding b ignore index (csd_binding_landmark), csd c
    -> where c.id=b.csd_id and b.csd_s_id=26010
    -> and within(geomfromtext('point(64.849551 -147.706117)'),landmark)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: PRIMARY,csd_binding_csd_s_id,csd_binding_csd_id_idx,
               csd_binding_cidse_idx,csd_binding_csidse_idx
          key: csd_binding_csidse_idx
      key_len: 4
          ref: const
         rows: 3
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: quadrant.b.CSD_id
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

mysql> select c.id, b.csd_id,b.csd_s_id
    -> from csd_binding b ignore index (csd_binding_landmark), csd c
    -> where c.id=b.csd_id and b.csd_s_id=26010
    -> and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786599 | 8786599 |    26010 |
+---------+---------+----------+
1 row in set (0.00 sec)

Thank you for the feedback and confirming my suspicion.

With the additional info provided by bug reporter, I close this as a duplicate of BUG#67889. 

@steven: Please reopen this bug if you still get wrong result when MySQL 5.6.11 is out.

I just upgraded MySQL servers to 5.6.11 and the problem is still existing the same as before and as it is.

You did not fix the bug yet.

Without the bug being fixed, we can not use it as production server!!!!

Steven

C:\>mysql -uroot -p -hdev82 quadrant
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.6.11-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select c.id, b.csd_id,b.csd_s_id
    ->   from csd_binding b -- ignore index (csd_binding_landmark)
    ->        ,csd c
    ->   where c.id=b.csd_id and b.csd_s_id=26010
    ->   and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
Empty set (0.00 sec)

mysql> explain select c.id, b.csd_id,b.csd_s_id
    ->   from csd_binding b -- ignore index (csd_binding_landmark)
    ->        ,csd c
    ->   where c.id=b.csd_id and b.csd_s_id=26010
    ->   and within(geomfromtext('point(64.849551 -147.706117)'),landmark)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: index_merge
possible_keys: PRIMARY,csd_binding_csd_s_id,csd_binding_landmark,csd_binding_csd_id_idx,csd_binding_cidse_idx,csd_binding_csidse_idx
          key: csd_binding_landmark,csd_binding_csd_s_id
      key_len: 34,4
          ref: NULL
         rows: 1
        Extra: Using intersect(csd_binding_landmark,csd_binding_csd_s_id); Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: c
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: quadrant.b.CSD_id
         rows: 1
        Extra: Using index
2 rows in set (0.00 sec)

mysql> select c.id, b.csd_id,b.csd_s_id
    ->   from csd_binding b ignore index (csd_binding_landmark)
    ->        ,csd c
    ->   where c.id=b.csd_id and b.csd_s_id=26010
    ->   and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786599 | 8786599 |    26010 |
+---------+---------+----------+
1 row in set (0.00 sec)

mysql>

Hi,

That's really... peculiar. 5.6.11 rejects that index on my box:

-----------------
mysql> show variables like 'version%';
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.6.11-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+

mysql>explain
    -> select  b.csd_id,b.csd_s_id
    -> from csd_binding b force index (csd_binding_landmark)
    -> where within(geomfromtext('point(64.849551 -147.706117)'),landmark)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ALL
possible_keys: csd_binding_landmark
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: Using where
1 row in set (0.00 sec)

mysql> explain select c.id, b.csd_id,b.csd_s_id
    -> from csd_binding b -- ignore index (csd_binding_landmark)
    -> ,csd c
    -> where c.id=b.csd_id and b.csd_s_id=26010
    -> and within(geomfromtext('point(64.849551 -147.706117)'),landmark)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: ref
possible_keys: PRIMARY,csd_binding_csd_s_id,csd_binding_landmark,csd_binding_csd_id_idx,csd_binding_cidse_idx,csd_binding_csidse_idx
          key: csd_binding_csd_s_id
      key_len: 4
          ref: const
         rows: 6
        Extra: Using where
*************************** 2. row ***************************
 (... cut - not the problem) 

mysql> select c.id, b.csd_id,b.csd_s_id
    -> from csd_binding b -- ignore index (csd_binding_landmark)
    -> ,csd c
    -> where c.id=b.csd_id and b.csd_s_id=26010
    -> and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786599 | 8786599 |    26010 |
+---------+---------+----------+
1 row in set (0.00 sec)

----------------

And optimizer trace shows this for table 'b':

   "rows_estimation": [
     {
       "table": "`csd_binding` `b`",
       (... cut)
       "analyzing_range_alternatives": {
         "range_scan_alternatives": [
          {
            "index": "csd_binding_csd_s_id",
            "ranges": [
              "26010 <= CSD_S_id <= 26010"
            ] /* ranges */,
            "index_dives_for_eq_ranges": true,
            "rowid_ordered": true,
            "using_mrr": false,
            "index_only": false,
            "rows": 6,
            "cost": 8.21,
            "chosen": true
          },
          {
            "index": "csd_binding_csidse_idx",
            "ranges": [
              "26010 <= CSD_S_id <= 26010"
            ] /* ranges */,
            "index_dives_for_eq_ranges": true,
            "rowid_ordered": false,
            "using_mrr": false,
            "index_only": false,
            "rows": 12,
            "cost": 15.41,
            "chosen": false,
            "cause": "cost"
          }
        ] /* range_scan_alternatives */,

Notice how the index csd_binding_landmark isn't even considered, so on my box everything looks just fine.

* Did you compile the server yourself or download some binary?
* Did you use bug68001_reduced.sql in the latest test run? Please do :-)
* Please provide SHOW CREATE TABLE to verify that what I have is correct
* Please attach optimizer trace output for the failing query

Oh... forgot one thing - please provide output of:

mysql> explain
    -> select b.csd_id,b.csd_s_id
    -> from csd_binding b force index (csd_binding_landmark)
    -> where within(geomfromtext('point(64.849551 -147.706117)'),landmark)\G

Can I ask why you want a non-spatial index on a geometry data type? Have you considered switching to a spatial index instead?

mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 5.6.11-log                   |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.5               |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

I downloaded this GA file from MySQL: mysql-5.6.11-linux-glibc2.5-x86_64.tar.gz

What is bug68001_reduced.sql? The problem is what I reported in current database, so the test case must be against the problem data, not an altered dataset as that would not help solving this issue. 

Please send me how-to to do optimizer trace. 

mysql> explain
    -> select b.csd_id,b.csd_s_id
    -> from csd_binding b force index (csd_binding_landmark)
    -> where within(geomfromtext('point(64.849551 -147.706117)'),landmark)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
         type: range
possible_keys: csd_binding_landmark
          key: csd_binding_landmark
      key_len: 34
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

I do not understand your question: 'Can I ask why you want a non-spatial index on a geometry data type? Have you considered switching to a spatial index instead?' -- the initial query does not specify any index and MySQL 5.6 simply could not get the right result back, it is working fine in MySQL 5.5.25a.

To reproduce the problem, I suggest you use my original uploaded data dump and create a database and tables and then repeat what we have execised. You can not alter the data set and do any test.

Steven,

I have some pressing business to attend. I'll get back to you on this issue as soon as time permits.

Regards,
Jørgen

Hi Steven.

About reduced dump.

I tested full dump with current development sources at Jan, 16. I could not repeat described behavior with this dump. But testing full dump is usually not not final step in bugs fixing, because we have to create reduced test cases which we can put in our regression test suite and run before making new release. This is why I created reduced dump and asked you to test with it. See my comments from Jan, 16:

----<q>----
[16 Jan 2:02] Sveta Smirnova

Thank you for the file.

I can not repeat described behavior with current development sources.

Please try with current version 5.6.9 and if problem still exists please try with reduced dump which I upload shortly and inform us if problem is repeatable with reduced dump too.

Attention! Reduced dump uses same table names, so restore it into different database or edit table names prior restoring!

[16 Jan 2:03] Sveta Smirnova

reduced dump

Attachment: bug68001_reduced.sql (application/octet-stream, text), 73.84 KiB.
----</q>----

Have you tested with reduced dump in your environment?

This is very important to know if issue is repeatable with reduced dump on your machine or not, because the fact that I could not repeat the issue with *full* dump means there is a difference either in options we use or in how data stored.

So if you can repeat issue with reduced dump, we can ask for one kind of information and if you can not - we would look into other part of code.

Please try it somewhere: it should be much easier with few KBs than with 1.3G like your original dump and send us results.

OK I downloaded the reduced dump and found out following:

1. The reduced dump does not have the identical data set, the original csd table has record for id=8782782:

mysql> select * from csd where id= 8782782;
+---------+-----------+--------+---------------------------------------------+-------------+-----------+-------------+-------------+----------------+---------------------+---------+-------+------+---------------------+----------------+--------+
| ID      | CompanyID | UserID | sitename                                    | Description | Lat       | Lon         | Sensitivity | Address        | UploadTime          | started | ended | Icon | LastModified        | LastModifiedBy | ExtKey |
+---------+-----------+--------+---------------------------------------------+-------------+-----------+-------------+-------------+----------------+---------------------+---------+-------+------+---------------------+----------------+--------+
| 8782782 |     30462 |   2323 | United Charities Roger McCoy Players Choice | x           | 64.849551 | -147.706117 |         182 | 101 Charles St | 2012-10-31 17:27:10 | NULL    | NULL  | 1    | 2012-10-31 17:27:10 |           2323 | NULL   |
+---------+-----------+--------+---------------------------------------------+-------------+-----------+-------------+-------------+----------------+---------------------+---------+-------+------+---------------------+----------------+--------+
1 row in set (0.01 sec)

2. The reduced dump with the modified dataset, it indeed returns the right result:

mysql> select c.id, b.csd_id,b.csd_s_id
    ->        from csd_binding b,csd c
    ->        where c.id=b.csd_id
    ->        and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786599 | 8786599 |    26008 |
| 8786599 | 8786599 |    26010 |
+---------+---------+----------+
2 rows in set (0.00 sec)

mysql>
mysql> select c.id,b.csd_id,b.csd_s_id
    ->        from csd_binding b,csd c
    ->        where b.csd_id = c.id and b.csd_s_id =26010;
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786598 | 8786598 |    26010 |
| 8786599 | 8786599 |    26010 |
| 8786600 | 8786600 |    26010 |
| 8786601 | 8786601 |    26010 |
| 8786602 | 8786602 |    26010 |
| 8786603 | 8786603 |    26010 |
+---------+---------+----------+
6 rows in set (0.00 sec)

mysql> select c.id, b.csd_id,b.csd_s_id
    ->        from csd_binding b,csd c
    ->        where c.id=b.csd_id and b.csd_s_id=26010
    ->        and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786599 | 8786599 |    26010 |
+---------+---------+----------+
1 row in set (0.00 sec)

mysql>
mysql> select * from csd where id = 8782782;
Empty set (0.00 sec)

mysql>

Steven,

I've spent hours trying to reproduce the issue using the full dump. I am able to provoke the bug in 5.6.10 but not in 5.6.11. The reason is the fix for BUG#67889.

There are my findings:

5.6.10:
-------
Using a debugger, I tricked MySQL into choosing intersect(csd_binding_csd_s_id,csd_binding_landmark) [1]. Once I was able to trick MySQL to use index intersection, I got the empty result:

mysql> explain select c.id, b.csd_id,b.csd_s_id from csd_binding b ignore index(csd_binding_csidse_idx),csd c where c.id=b.csd_id and b.csd_s_id=26010 and within(geomfromtext('point(64.849551 -147.706117)'),landmark)\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: index_merge
possible_keys: PRIMARY,csd_binding_csd_s_id,csd_binding_landmark,csd_binding_csd_id_idx,csd_binding_cidse_idx
key: csd_binding_csd_s_id,csd_binding_landmark
key_len: 4,35
ref: NULL
rows: 4
Extra: Using intersect(csd_binding_csd_s_id,csd_binding_landmark); Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: c
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.b.CSD_id
rows: 1
Extra: Using index

mysql> select c.id, b.csd_id,b.csd_s_id from csd_binding b ignore index(csd_binding_csidse_idx),csd c where c.id=b.csd_id and b.csd_s_id=26010 and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
Empty set (17.93 sec)

The reason for this is clear: MySQL uses a geometry function on a non-spatial index. That does not work, and has been fixed in 5.6.11.

[1] Details: Both csd_binding_csd_s_id and csd_binding_landmark are valid indexes, but the optimizer estimates that csd_binding_landmark will output 6.3M rows and is therefore not used. The trick is to reduce the row and cost estimate for Index Merge Intersection so that intersection becomes cheaper than using only csd_binding_csd_s_id.

5.6.11:
-------
Due to the fix for BUG#67889, index csd_binding_landmark is no longer considered a valid index for this query. The reason is that the index is non-spatial while the query predicate is a geom function. Since the index is no longer valid, it is not possible to trick MySQL to use intersection either. The only way I can trigger this bug is by first removing the aforementioned bugfix. The same debugging sequence of manipulating costs (as done with 5.6.10) will then provoke the bug.

Conclusion: I am not able to reproduce in 5.6.11, and from all this debugging the fix for BUG#67889 is confirmed as a fix. It is a mystery to me how this can be a problem in 5.6.11, but in any case my suggested workaround is this: the index 'csd_binding_landmark' should be removed or changed to a spatial index since geom functions cannot be used with non-spatial indexes.

My comments to: "Conclusion: I am not able to reproduce in 5.6.11, and from all this debugging the fix for BUG#67889 is confirmed as a fix. It is a mystery to me how this can be a problem in 5.6.11, but in any case my suggested workaround is this: the index 'csd_binding_landmark' should be removed or changed to a spatial index since geom functions cannot be used with non-spatial indexes"

1. csd_binding_landmark is indeed a spatial index

mysql> show create table csd_binding\G
*************************** 1. row ***************************
       Table: csd_binding
Create Table: CREATE TABLE `csd_binding` (
  `CSD_id` int(11) NOT NULL DEFAULT '0',
  `CSD_S_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(80) COLLATE latin1_general_ci DEFAULT '' COMMENT 'Column name is the name of this landmark in the context of the set',
  `Ordinal` int(11) NOT NULL DEFAULT '0',
  `Sensitivity` int(11) DEFAULT '0',
  `started` datetime DEFAULT NULL,
  `ended` datetime DEFAULT NULL,
  `Icon` varchar(100) COLLATE latin1_general_ci DEFAULT '',
  `Description` varchar(255) COLLATE latin1_general_ci DEFAULT '',
  `CustID` varchar(32) COLLATE latin1_general_ci DEFAULT NULL,
  `RefNo` varchar(32) COLLATE latin1_general_ci DEFAULT NULL,
  `ETA` datetime DEFAULT NULL,
  `ETAVariance` int(11) DEFAULT '0',
  `Duration` int(11) DEFAULT '0',
  `DurationVariance` int(11) DEFAULT '0',
  `landmark` geometry NOT NULL,
  PRIMARY KEY (`CSD_id`,`CSD_S_id`,`Ordinal`),
  KEY `csd_binding_csd_s_id` (`CSD_S_id`),
  KEY `csd_binding_name` (`name`),
  SPATIAL KEY `csd_binding_landmark` (`landmark`),
  KEY `csd_binding_csd_id_idx` (`CSD_id`),
  KEY `csd_binding_cidse_idx` (`CSD_id`,`started`,`ended`),
  KEY `csd_binding_csidse_idx` (`CSD_S_id`,`started`,`ended`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci
1 row in set (0.01 sec)

So I am not sure what is going on. Have you got some false assumptions?

Steven

I have created the database using your dump file and the index is not spatial :-/

From the csd_binding.dump file:
------
CREATE TABLE `csd_binding` (
  `CSD_id` int(11) DEFAULT '0',
  `CSD_S_id` int(11) DEFAULT '0',
  `name` varchar(80) DEFAULT '',
  `Ordinal` int(11) DEFAULT '0',
  `Sensitivity` int(11) DEFAULT '0',
  `started` datetime,
  `ended` datetime,
  `Icon` varchar(100) DEFAULT '',
  `Description` varchar(255) DEFAULT '',
  `CustID` varchar(32),
  `RefNo` varchar(32),
  `ETA` datetime,
  `ETAVariance` int(11) DEFAULT '0',
  `Duration` int(11) DEFAULT '0',
  `DurationVariance` int(11) DEFAULT '0',
  `landmark` geometry,
  PRIMARY KEY (`CSD_id`,`CSD_S_id`,`Ordinal`),
  KEY `csd_binding_csd_s_id` (`CSD_S_id`),
  KEY `csd_binding_name` (`name`),
  KEY `csd_binding_landmark` (`landmark` (32)),
  KEY `csd_binding_csd_id_idx` (`CSD_id`),
  KEY `csd_binding_cidse_idx` (`CSD_id`,`started`,`ended`),
  KEY `csd_binding_csidse_idx` (`CSD_S_id`,`started`,`ended`)
)/*! engine=MyISAM */;
----

From MySQL client:
----
mysql> show create table csd_binding\G
*************************** 1. row ***************************
       Table: csd_binding
Create Table: CREATE TABLE `csd_binding` (
  `CSD_id` int(11) NOT NULL DEFAULT '0',
  `CSD_S_id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(80) DEFAULT '',
  `Ordinal` int(11) NOT NULL DEFAULT '0',
  `Sensitivity` int(11) DEFAULT '0',
  `started` datetime DEFAULT NULL,
  `ended` datetime DEFAULT NULL,
  `Icon` varchar(100) DEFAULT '',
  `Description` varchar(255) DEFAULT '',
  `CustID` varchar(32) DEFAULT NULL,
  `RefNo` varchar(32) DEFAULT NULL,
  `ETA` datetime DEFAULT NULL,
  `ETAVariance` int(11) DEFAULT '0',
  `Duration` int(11) DEFAULT '0',
  `DurationVariance` int(11) DEFAULT '0',
  `landmark` geometry DEFAULT NULL,
  PRIMARY KEY (`CSD_id`,`CSD_S_id`,`Ordinal`),
  KEY `csd_binding_csd_s_id` (`CSD_S_id`),
  KEY `csd_binding_name` (`name`),
  KEY `csd_binding_landmark` (`landmark`(32)),
  KEY `csd_binding_csd_id_idx` (`CSD_id`),
  KEY `csd_binding_cidse_idx` (`CSD_id`,`started`,`ended`),
  KEY `csd_binding_csidse_idx` (`CSD_S_id`,`started`,`ended`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Well, I really do not know what to say!

Steven,

Then there's no wonder why Sveta and I couldn't reproduce the bug :-)

Let me suggest that we do some team work:

If you can reproduce the issue with a simpler test case (e.g. by modifying the create table statements in the reduced test case uploaded by Sveta), I'll continue searching for the bug after you upload the new repro. How does that sound?

The correct table creation script

Attachment: bug68001_reduced_correct.sql (application/octet-stream, text), 74.31 KiB.

Now I have fixed the table creation scripts. I do not know how you guys could screw the original dump file to become this wrong file in the first place!

Please use the latest file I upload to do the investigation.

Thanks,
Stevenh

Now I tested the correct reduced dump and the result seems to be correct!

mysql> select c.id, b.csd_id,b.csd_s_id
    ->        from csd_binding b,csd c
    ->        where c.id=b.csd_id
    ->        and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786599 | 8786599 |    26008 |
| 8786599 | 8786599 |    26010 |
| 8782786 | 8782786 |    25943 |
| 8786295 | 8786295 |    24977 |
+---------+---------+----------+
4 rows in set (0.00 sec)

mysql>
mysql> select c.id,b.csd_id,b.csd_s_id
    ->        from csd_binding b,csd c
    ->        where b.csd_id = c.id and b.csd_s_id =26010;
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786598 | 8786598 |    26010 |
| 8786599 | 8786599 |    26010 |
| 8786600 | 8786600 |    26010 |
| 8786601 | 8786601 |    26010 |
| 8786602 | 8786602 |    26010 |
| 8786603 | 8786603 |    26010 |
+---------+---------+----------+
6 rows in set (0.00 sec)

mysql> select c.id, b.csd_id,b.csd_s_id
    ->        from csd_binding b,csd c
    ->        where c.id=b.csd_id and b.csd_s_id=26010
    ->        and within(geomfromtext('point(64.849551 -147.706117)'),landmark);
+---------+---------+----------+
| id      | csd_id  | csd_s_id |
+---------+---------+----------+
| 8786599 | 8786599 |    26010 |
+---------+---------+----------+
1 row in set (0.02 sec)

However I do not know why my original database would not work!

If you compare the reduced dump file database, the result for each query is not the same as original. I do not how you create the reduced dump file in the first place. 

Once again, I do not believe your reduced dump file at all; it has been problematic and waste us a few months of time!

Steven,

May I remind you that it is YOUR upload that has a non-spatial index? Sveta's attempt at reducing the test case was with the same schema. She reported early on that she couldn't reproduce the issue (you can hardly blame her since the original dump was incorrect) but asked kindly if you could try her reduced test case. You can hardly blame us for not finding the issue when your multi-GB dump has a wrong CREATE TABLE statement? I even asked you multiple times why you use a non-spatial index. 

Yesterday, I requested a reduced test case from you (not necessarily Sveta's upload) to speed development up. That's simply because working with the huge dump will take me a lot more time than working with a smaller test case. I'm trying to fix an issue for you and I think it's only fair that you as a minimum send me a test case that can actually reproduce the bug.

Well, I am very surprised that I had a MySQL dump generated by mysqldump utility to get a non-spatial index instead of actually spatial index. I do not have the original file any more so I could not verify that claim. It is MySQL utility mysqldump generated and not MINE.

It is a bug that exists in 5.6.11 (GA) and as I said if MySQL could not fix it, MySQL server 5.6.11 (GA) could not been used. And since we know there was mistake before, please try to fix the bug now.

Thanks,
Steven

Steven,

I changed the index on landmark to be spatial, and it still returns correct result here when I use the full dump. I am about to give up reproducing the issue with the attached data.

However, I have a candidate fix ready based on a suspicion. If you are able to patch and compile the server yourself, I can send the fix to you for verification. If not, you'll have to wait for a new server version with the fix in it (not sure which version that will be) before we can verify it.

No feedback whether or not the latest 5.6 release fixed the issue. I'm closing this bug, but please reopen if it is still an issue in the latest MySQL 5.6 version.

Just wonder what MySQL bug fix is related to this bug?

Hi Steven,

I believe it was this internally reported bug:

BUG#16960800: "RANGE OPTIMIZER BELIEVES THAT GEOM FUNCTIONS RETURN ROWID ORDERED RESULTS"

Today I upgraded my MySQL server to 5.6.15 and the result is finally correct.

Thanks.

Excellent! Thanks for the feedback.