Bug #48258 Assertion failed when using a spatial index
Submitted: 23 Oct 2009 7:40 Modified: 20 Jun 2010 22:43
Reporter: Ramil Kalimullin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Triage: Triaged: D1 (Critical) / R1 (None/Negligible) / E3 (Medium)

[23 Oct 2009 7:40] Ramil Kalimullin
Description:
mysql> CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 VALUES
    -> (GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')),
    -> (GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)'));
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT 1 FROM t1 WHERE a=GEOMFROMTEXT('LINESTRING(-1 -1,1 -1,-1 -1,-1 1,1 1)');

#0  0x0000003caea32f05 in raise () from /lib64/libc.so.6
#1  0x0000003caea34a73 in abort () from /lib64/libc.so.6
#2  0x0000003caea2bef9 in __assert_fail () from /lib64/libc.so.6
#3  0x0000000000971035 in rtree_key_cmp (keyseg=0x1896138, b=0x1896ef6 "��", a=0x7ffff48bf062 "��", key_length=32, nextflag=1)
    at rt_mbr.c:138
#4  0x000000000096e626 in rtree_estimate (info=0x1896318, keynr=0, key=0x1896ef6 "��", key_length=32, flag=1) at rt_index.c:1100
#5  0x0000000000950c4d in mi_records_in_range (info=0x1896318, inx=0, min_key=0x7ffff48bf6d0, max_key=0x7ffff48bf6c0)
    at mi_range.c:92
#6  0x0000000000735080 in ha_myisam::records_in_range (this=0x1868400, inx=0, min_key=0x7ffff48bf6d0, max_key=0x7ffff48bf6c0)
    at ha_myisam.cc:1903
#7  0x0000000000712503 in check_quick_keys (param=0x7ffff48bf930, idx=0, key_tree=0x18944d0, min_key=0x7ffff48bfc88 "",
    min_key_flag=0, max_key=0x7ffff48c0b86 "", max_key_flag=0) at opt_range.cc:6109
#8  0x00000000007127df in check_quick_select (param=0x7ffff48bf930, idx=0, tree=0x18944d0) at opt_range.cc:5905
#9  0x000000000071d487 in get_key_scans_params (param=0x7ffff48bf930, tree=0x1894260, index_read_must_be_used=false,
    read_time=4.55078125) at opt_range.cc:3546
#10 0x0000000000721356 in SQL_SELECT::test_quick_select (this=0x18a8da0, thd=0x1857b48, keys_to_use={map = 1}, prev_tables=0,
    limit=18446744073709551615, force_quick_range=false) at opt_range.cc:2186
#11 0x00000000006a7476 in get_quick_record_count (thd=0x1857b48, select=0x18a8da0, table=0x18758c8, keys=0x18a8aa8,
    limit=18446744073709551615) at sql_select.cc:2368
#12 0x00000000006a8cb3 in make_join_statistics (join=0x18985a0, tables_arg=0x1897e20, conds=0x18999a8, keyuse_array=0x1899780)
    at sql_select.cc:2766
#13 0x00000000006a9dfd in JOIN::optimize (this=0x18985a0) at sql_select.cc:912
#14 0x00000000006addf1 in mysql_select (thd=0x1857b48, rref_pointer_array=0x18595d8, tables=0x1897e20, wild_num=0,
    fields=@0x18594b8, conds=0x1898390, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2156153344,
    result=0x1898580, unit=0x1859008, select_lex=0x18593b0) at sql_select.cc:2305
#15 0x00000000006b2a81 in handle_select (thd=0x1857b48, lex=0x1858f78, result=0x1898580, setup_tables_done_option=0)
    at sql_select.cc:256
#16 0x0000000000639050 in mysql_execute_command (thd=0x1857b48) at sql_parse.cc:2899
#17 0x000000000064186c in mysql_parse (thd=0x1857b48,
    inBuf=0x1897c98 "SELECT 1 FROM t1 WHERE a=GEOMFROMTEXT('LINESTRING(-1 -1,1 -1,-1 -1,-1 1,1 1)')", length=78,
    found_semicolon=0x7ffff48c3e30) at sql_parse.cc:6459
...

How to repeat:
CREATE TABLE t1(a LINESTRING NOT NULL, SPATIAL KEY(a));
INSERT INTO t1 VALUES
  (GEOMFROMTEXT('LINESTRING(-1 -1, 1 -1, -1 -1, -1 1, 1 1)')),
  (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)');

Suggested fix:
Don't use spatial indexes for such queries.
[23 Oct 2009 11:27] 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/87922

2823 Ramil Kalimullin	2009-10-23
      Fix for bug#48258: Assertion failed when using a spatial index
      
      Problem: involving a spatial index for "non-spatial" queries
      (that don't containt MBRXXX() functions) may lead to failed assert.
      
      Fix: don't use spatial indexes in such cases.
     @ mysql-test/r/gis-rtree.result
        Fix for bug#48258: Assertion failed when using a spatial index
          - test result.
     @ mysql-test/t/gis-rtree.test
        Fix for bug#48258: Assertion failed when using a spatial index
          - test case.
     @ sql/opt_range.cc
        Fix for bug#48258: Assertion failed when using a spatial index
          - allow only spatial functions (MBRXXX) for itMBR keyparts.
[4 Nov 2009 9:16] Bugs System
Pushed into 5.0.88 (revid:joro@sun.com-20091104091355-hpz6dwgkrfmokj3k) (version source revid:ramil@mysql.com-20091023112648-gie6o3odj57cxh1e) (merge vers: 5.0.88) (pib:13)
[4 Nov 2009 9:26] 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:54] 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 7:02] 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:32] 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:45] Paul Dubois
Noted in 5.1.40sp1 changelog.
[8 Dec 2009 9:29] 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:40] 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:47] 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:54] 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:37] 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:52] 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:07] 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:21] 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)
[18 Dec 2009 13:19] MC Brown
Already noted in earlier changelogs.
[17 Feb 2010 16:26] 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:46] 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:46] 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:15] 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:31] 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:48] 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 11:59] 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 12:39] 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:25] 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)