Bug #47019 Assertion failed: 0, file .\rt_mbr.c, line 138 when forcing a spatial index
Submitted: 31 Aug 2009 13:02 Modified: 20 Jun 2010 22:38
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.0.86-debug, 5.1.39-debug,5.4.4 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Triage: Triaged: D1 (Critical)

[31 Aug 2009 13:02] Shane Bester
Description:
Version: '5.1.37-community-debug'  socket: ''  port: 3306  MySQL Community Server - Debug (GPL)
Assertion failed: 0, file .\rt_mbr.c, line 138

 mysqld-debug.exe!my_sigabrt_handler()[mysqld.cc:2014]
 mysqld-debug.exe!raise()[winsig.c:597]
 mysqld-debug.exe!abort()[abort.c:78]
 mysqld-debug.exe!_wassert()[assert.c:212]
 mysqld-debug.exe!rtree_key_cmp()[rt_mbr.c:138]
 mysqld-debug.exe!rtree_find_req()[rt_index.c:113]
 mysqld-debug.exe!rtree_find_first()[rt_index.c:198]
 mysqld-debug.exe!mi_rkey()[mi_rkey.c:84]
 mysqld-debug.exe!ha_myisam::index_read_map()[ha_myisam.cc:1664]
 mysqld-debug.exe!join_read_always_key()[sql_select.cc:11694]
 mysqld-debug.exe!sub_select()[sql_select.cc:11141]
 mysqld-debug.exe!evaluate_join_record()[sql_select.cc:11257]
 mysqld-debug.exe!sub_select()[sql_select.cc:11142]
 mysqld-debug.exe!do_select()[sql_select.cc:10898]
 mysqld-debug.exe!JOIN::exec()[sql_select.cc:2199]
 mysqld-debug.exe!mysql_select()[sql_select.cc:2390]
 mysqld-debug.exe!handle_select()[sql_select.cc:268]
 mysqld-debug.exe!execute_sqlcom_select()[sql_parse.cc:5012]
 mysqld-debug.exe!mysql_execute_command()[sql_parse.cc:2207]
 mysqld-debug.exe!mysql_parse()[sql_parse.cc:5936]
 mysqld-debug.exe!dispatch_command()[sql_parse.cc:1215]
 mysqld-debug.exe!do_command()[sql_parse.cc:854]
 mysqld-debug.exe!handle_one_connection()[sql_connect.cc:1127]
 mysqld-debug.exe!pthread_start()[my_winthread.c:85]
 mysqld-debug.exe!_callthreadstart()[thread.c:295]
 mysqld-debug.exe!_threadstart()[thread.c:277]
 kernel32.dll!BaseThreadStart()

How to repeat:
drop table if exists t1;
create table t1(`a` linestring not null, spatial key(`a`))engine=myisam;
insert into t1 values (geomfromtext('linestring(-1 -1,1 -1,-1 -1,-1 1,1 1)'));
insert into t1 values (geomfromtext('linestring(-1 -1,1 -1,-1 -1,-1 1,1 1)'));
select 1 from `t1` natural left join `t1` as `t2` force index (`a`);
[31 Aug 2009 13:10] Miguel Solorzano
Thank you for the bug report. Verified as described.
[31 Aug 2009 13:56] Miguel Solorzano
miguel@luz:~/dbs/mysql-next$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.4.4-alpha-debug Source distribution

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

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t1(`a` linestring not null, spatial key(`a`))engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values (geomfromtext('linestring(-1 -1,1 -1,-1 -1,-1 1,1 1)'));
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values (geomfromtext('linestring(-1 -1,1 -1,-1 -1,-1 1,1 1)'));
Query OK, 1 row affected (0.00 sec)

mysql> select 1 from `t1` natural left join `t1` as `t2` force index (`a`);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[4 Sep 2009 10:33] Sergey Vojtovich
Queries even with no join failing with the same back trace:
SELECT 1 FROM t1 WHERE a=GEOMFROMTEXT('LINESTRING(-1 -1,1 -1,-1 -1,-1 1,1 1)');
SELECT 1 FROM t1 WHERE a>GEOMFROMTEXT('LINESTRING(-1 -1,1 -1,-1 -1,-1 1,1 1)');
etc...
[21 Oct 2009 9:04] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87598

2822 Ramil Kalimullin	2009-10-21
      Fix for bug#47019: Assertion failed: 0, file .\rt_mbr.c, 
      line 138 when forcing a spatial index
      
      Problem: "Spatial indexes can be involved in the search 
      for queries that use a function such as MBRContains() 
      or MBRWithin() in the WHERE clause".
      Using spatial indexes for JOINs with =, <=> etc.
      predicates is incorrect.
      
      Fix: disable spatial indexes for such queries.
     @ mysql-test/r/select.result
        Fix for bug#47019: Assertion failed: 0, file .\rt_mbr.c, 
        line 138 when forcing a spatial index
          - test result.
     @ mysql-test/t/select.test
        Fix for bug#47019: Assertion failed: 0, file .\rt_mbr.c, 
        line 138 when forcing a spatial index
          - test case.
     @ sql/sql_select.cc
        Fix for bug#47019: Assertion failed: 0, file .\rt_mbr.c, 
        line 138 when forcing a spatial index
          - disable spatial indexes for queries which use 
        non-spatial conditions (e.g. NATURAL JOINs).
[4 Nov 2009 9:16] Bugs System
Pushed into 5.0.88 (revid:joro@sun.com-20091104091355-hpz6dwgkrfmokj3k) (version source revid:ramil@mysql.com-20091021090408-208mvwwrcroi2j8c) (merge vers: 5.0.88) (pib:13)
[4 Nov 2009 9:25] Bugs System
Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)
[11 Nov 2009 6:51] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109080109-7dxapd5y5pxlu08w) (merge vers: 6.0.14-alpha) (pib:13)
[11 Nov 2009 6:59] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105092041-sp6eyod7sdlfuj3b) (merge vers: 5.5.0-beta) (pib:13)
[11 Nov 2009 16:34] Paul Dubois
Noted in 5.0.88, 5.1.41, 5.5.0, 6.0.14 changelogs.

An assertion could fail if the optimizer used a SPATIAL index.
[7 Dec 2009 16:41] Paul Dubois
Noted in 5.1.40sp1 changelog.
[8 Dec 2009 9:30] Bugs System
Pushed into 5.1.43 (revid:build@mysql.com-20091208092611-pbno5awyb0v38hs7) (version source revid:build@mysql.com-20091208092611-pbno5awyb0v38hs7) (merge vers: 5.1.43) (pib:13)
[16 Dec 2009 8:39] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091215065750-5m04ogppd5l0pol5) (merge vers: 6.0.14-alpha) (pib:14)
[16 Dec 2009 8:46] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alik@sun.com-20091211070127-kl8uvlrv9cr11kva) (merge vers: 5.5.0-beta) (pib:14)
[16 Dec 2009 8:52] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)
[18 Dec 2009 10:32] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:48] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:03] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:17] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[17 Feb 2010 16:19] Paul Dubois
Noted in 5.0.87sp1 changelog.
[20 Feb 2010 17:11] Bugs System
Pushed into 5.0.91 (revid:build@mysql.com-20100220170835-5kr6ztsg25va7qzz) (version source revid:build@mysql.com-20100220170835-5kr6ztsg25va7qzz) (merge vers: 5.0.91) (pib:16)
[1 Mar 2010 8:44] Bugs System
Pushed into 5.1.45 (revid:joro@sun.com-20100301083827-xnimmrjg6bh33o1o) (version source revid:joro@sun.com-20100226131646-kpvzk740hxbtaexn) (merge vers: 5.1.45) (pib:16)
[2 Mar 2010 14:36] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100302142746-u1gxdf5yk2bjrq3e) (version source revid:alik@sun.com-20100301095421-4cz64ibem1h2quve) (merge vers: 6.0.14-alpha) (pib:16)
[2 Mar 2010 14:41] Bugs System
Pushed into 5.5.3-m2 (revid:alik@sun.com-20100302072233-t3uqgjzdukt1pyhe) (version source revid:alik@sun.com-20100301090215-63o2w2y16go8n53p) (merge vers: 5.5.3-m2) (pib:16)
[2 Mar 2010 14:45] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100302072432-k8xvfkgcggkwgi94) (version source revid:alik@sun.com-20100301094536-2zc4uqyy3os8san7) (pib:16)
[12 Mar 2010 14:12] Bugs System
Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)
[12 Mar 2010 14:28] Bugs System
Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)
[12 Mar 2010 14:44] Bugs System
Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)
[17 Jun 2010 12:16] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:03] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:martin.skold@mysql.com-20100609140708-52rvuyq4q500sxkq) (merge vers: 5.1.45-ndb-6.2.19) (pib:16)
[17 Jun 2010 13:44] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)