From 27166fc64f54ef82f1800988ffff431868377645 Mon Sep 17 00:00:00 2001
From: Magne Mahre <magne.mahre@oracle.com>
Date: Thu, 24 Feb 2011 12:23:38 +0100
Subject: Bug#11767480 - SPATIAL INDEXES ON NON-SPATIAL COLUMNS CAUSE CRASHES.

This is a backport of the patch for MySQL Bug#50574.

Adding a SPATIAL INDEX on non-geometrical columns caused a
segmentation fault when the table was subsequently
inserted into.

A test was added in mysql_prepare_create_table to explicitly
check whether non-geometrical columns are used in a
spatial index, and throw an error if so.

For MySQL 5.5 and later, a new and more meaningful error
message was introduced.  For 5.1, we (re-)use an existing
error code.
---
 mysql-test/r/gis.result | 33 +++++++++++++++++++++++++++++++++
 mysql-test/t/gis.test   | 47 +++++++++++++++++++++++++++++++++++++++++++++++
 sql/sql_table.cc        | 22 ++++++++++++++++------
 3 files changed, 96 insertions(+), 6 deletions(-)

diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index a9beb9631ae..151d0cfffa1 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -1034,4 +1034,37 @@ p
 NULL
 NULL
 drop table t1;
+CREATE TABLE t0 (a BINARY(32) NOT NULL);
+CREATE SPATIAL INDEX i on t0 (a);
+ERROR HY000: Incorrect arguments to SPATIAL INDEX
+INSERT INTO t0 VALUES (1);
+CREATE TABLE t1(
+col0 BINARY NOT NULL,
+col2 TIMESTAMP,
+SPATIAL INDEX i1 (col0)
+) ENGINE=MyISAM;
+ERROR HY000: Incorrect arguments to SPATIAL INDEX
+CREATE TABLE t1 (
+col0 BINARY NOT NULL,
+col2 TIMESTAMP
+) ENGINE=MyISAM;
+CREATE SPATIAL INDEX idx0 ON t1(col0);
+ERROR HY000: Incorrect arguments to SPATIAL INDEX
+ALTER TABLE t1 ADD SPATIAL INDEX i1 (col0);
+ERROR HY000: Incorrect arguments to SPATIAL INDEX
+CREATE TABLE t2 (
+col0 INTEGER NOT NULL,
+col1 POINT,
+col2 POINT
+);
+CREATE SPATIAL INDEX idx0 ON t2 (col1, col2);
+ERROR HY000: Incorrect arguments to SPATIAL INDEX
+CREATE TABLE t3 (
+col0 INTEGER NOT NULL,
+col1 POINT,
+col2 LINESTRING,
+SPATIAL INDEX i1 (col1, col2)
+);
+ERROR HY000: Incorrect arguments to SPATIAL INDEX
+DROP TABLE t0, t1, t2;
 End of 5.1 tests
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index bdbbfc7c064..b50df062d7e 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -754,4 +754,51 @@ insert into t1 values (geomfromtext("point(1 0)"));
 select * from (select polygon(t1.a) as p from t1 order by t1.a) d;
 drop table t1;
 
+#
+# Bug#11767480 - SPATIAL INDEXES ON NON-SPATIAL COLUMNS CAUSE CRASHES.
+#
+CREATE TABLE t0 (a BINARY(32) NOT NULL);
+--error ER_WRONG_ARGUMENTS
+CREATE SPATIAL INDEX i on t0 (a);
+INSERT INTO t0 VALUES (1);
+
+--error ER_WRONG_ARGUMENTS
+CREATE TABLE t1(
+  col0 BINARY NOT NULL,
+  col2 TIMESTAMP,
+  SPATIAL INDEX i1 (col0)
+) ENGINE=MyISAM;
+
+# Test other ways to add indices
+CREATE TABLE t1 (
+  col0 BINARY NOT NULL,
+  col2 TIMESTAMP
+) ENGINE=MyISAM;
+
+--error ER_WRONG_ARGUMENTS
+CREATE SPATIAL INDEX idx0 ON t1(col0);
+
+--error ER_WRONG_ARGUMENTS
+ALTER TABLE t1 ADD SPATIAL INDEX i1 (col0);
+
+CREATE TABLE t2 (
+  col0 INTEGER NOT NULL,
+  col1 POINT,
+  col2 POINT
+);
+
+--error ER_WRONG_ARGUMENTS
+CREATE SPATIAL INDEX idx0 ON t2 (col1, col2);
+
+--error ER_WRONG_ARGUMENTS
+CREATE TABLE t3 (
+  col0 INTEGER NOT NULL,
+  col1 POINT,
+  col2 LINESTRING,
+  SPATIAL INDEX i1 (col1, col2)
+);
+
+# cleanup
+DROP TABLE t0, t1, t2;
+
 --echo End of 5.1 tests
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index b919ea9eae7..c5fc037a49e 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -1,4 +1,4 @@
-/* Copyright 2000-2008 MySQL AB, 2008 Sun Microsystems, Inc.
+/* Copyright 2000-2011, Oracle and/or its affiliates. All rights reserved. 
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -11,7 +11,8 @@
 
    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
-   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
+   Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,  
+   MA 02110-1301  USA */
 
 /* drop and alter of tables */
 
@@ -3184,11 +3185,20 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
       {
 	column->length*= sql_field->charset->mbmaxlen;
 
-        if (key->type == Key::SPATIAL && column->length)
+        if (key->type == Key::SPATIAL)
         {
-          my_error(ER_WRONG_SUB_KEY, MYF(0));
-	  DBUG_RETURN(TRUE);
-	}
+          if (column->length)
+          {
+            my_error(ER_WRONG_SUB_KEY, MYF(0));
+            DBUG_RETURN(TRUE);
+          }
+
+          if (!f_is_geom(sql_field->pack_flag))
+          {
+            my_error(ER_WRONG_ARGUMENTS, MYF(0), "SPATIAL INDEX");
+            DBUG_RETURN(TRUE);
+          }
+        }
 
 	if (f_is_blob(sql_field->pack_flag) ||
             (f_is_geom(sql_field->pack_flag) && key->type != Key::SPATIAL))
-- 
cgit v1.2.1