bzr commit into mysql-5.0 branch (ramil:2822) Bug#47019

[Ramil Kalimullin]

#At file:///home/ram/mysql/b47019-5.0-bugteam/ based on revid:azundris@stripped

 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).

    modified:
      mysql-test/r/select.result
      mysql-test/t/select.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/select.result'
--- a/mysql-test/r/select.result	2009-04-28 00:19:13 +0000
+++ b/mysql-test/r/select.result	2009-10-21 09:04:08 +0000
@@ -4401,4 +4401,33 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where ((`test`.`t1`.`a` = `test`.`t1`.`b`) and (`test`.`t1`.`a` > 1)) limit 2
 DROP TABLE t1;
+#
+# Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 
+# forcing a spatial index
+#
+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)'));
+EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	
+SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
+1
+1
+1
+1
+1
+EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	SIMPLE	t2	ALL	a	NULL	NULL	NULL	2	
+SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
+1
+1
+1
+1
+1
+DROP TABLE t1;
 End of 5.0 tests

=== modified file 'mysql-test/t/select.test'
--- a/mysql-test/t/select.test	2009-04-28 00:19:13 +0000
+++ b/mysql-test/t/select.test	2009-10-21 09:04:08 +0000
@@ -3750,5 +3750,21 @@ EXPLAIN EXTENDED SELECT a, b FROM t1 WHE
 EXPLAIN EXTENDED SELECT a, b FROM t1 WHERE a > 1 AND b = a LIMIT 2;
 
 DROP TABLE t1;
+
+
+--echo #
+--echo # Bug#47019: Assertion failed: 0, file .\rt_mbr.c, line 138 when 
+--echo # forcing a spatial index
+--echo #
+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)'));
+EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
+SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2;
+EXPLAIN SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
+SELECT 1 FROM t1 NATURAL LEFT JOIN t1 AS t2 FORCE INDEX(a);
+DROP TABLE t1;
+
  
 --echo End of 5.0 tests

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-10-14 08:16:04 +0000
+++ b/sql/sql_select.cc	2009-10-21 09:04:08 +0000
@@ -3447,7 +3447,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array
     {
       if (!(form->keys_in_use_for_query.is_set(key)))
 	continue;
-      if (form->key_info[key].flags & HA_FULLTEXT)
+      if (form->key_info[key].flags & (HA_FULLTEXT | HA_SPATIAL))
 	continue;    // ToDo: ft-keys in non-ft queries.   SerG
 
       uint key_parts= (uint) form->key_info[key].key_parts;


Attachment: [text/bzr-bundle] bzr/[e-mail]