summaryrefslogtreecommitdiff
path: root/mysql-test
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test')
-rw-r--r--mysql-test/include/gis_keys.inc46
-rw-r--r--mysql-test/r/bdb_gis.result39
-rw-r--r--mysql-test/r/gis-rtree.result4
-rw-r--r--mysql-test/r/gis.result39
-rw-r--r--mysql-test/r/innodb_gis.result39
-rw-r--r--mysql-test/t/bdb_gis.test1
-rw-r--r--mysql-test/t/gis.test2
-rw-r--r--mysql-test/t/innodb_gis.test1
8 files changed, 169 insertions, 2 deletions
diff --git a/mysql-test/include/gis_keys.inc b/mysql-test/include/gis_keys.inc
new file mode 100644
index 00000000000..295e0c48234
--- /dev/null
+++ b/mysql-test/include/gis_keys.inc
@@ -0,0 +1,46 @@
+--source include/have_geometry.inc
+
+#
+# Spatial objects with keys
+#
+
+#
+# Bug #30825: Problems when putting a non-spatial index on a GIS column
+#
+
+CREATE TABLE t1 (p POINT);
+CREATE TABLE t2 (p POINT, INDEX(p));
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+
+-- no index, returns 1 as expected
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+
+-- with index, returns 1 as expected
+-- EXPLAIN shows that the index is not used though
+-- due to the "most rows covered anyway, so a scan is more effective" rule
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+
+-- adding another row to the table so that
+-- the "most rows covered" rule doesn't kick in anymore
+-- now EXPLAIN shows the index used on the table
+-- and we're getting the wrong result again
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+EXPLAIN
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+
+EXPLAIN
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+
+DROP TABLE t1, t2;
+
+--echo End of 5.0 tests
diff --git a/mysql-test/r/bdb_gis.result b/mysql-test/r/bdb_gis.result
index d48b5a26e1d..6651421b51c 100644
--- a/mysql-test/r/bdb_gis.result
+++ b/mysql-test/r/bdb_gis.result
@@ -542,3 +542,42 @@ Overlaps(@horiz1, @point2)
0
DROP TABLE t1;
End of 5.0 tests
+CREATE TABLE t1 (p POINT);
+CREATE TABLE t2 (p POINT, INDEX(p));
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref p p 28 const 1 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+EXPLAIN
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL p NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+DROP TABLE t1, t2;
+End of 5.0 tests
diff --git a/mysql-test/r/gis-rtree.result b/mysql-test/r/gis-rtree.result
index 621402e87a9..99bfede3ee0 100644
--- a/mysql-test/r/gis-rtree.result
+++ b/mysql-test/r/gis-rtree.result
@@ -167,7 +167,7 @@ count(*)
150
EXPLAIN SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t1 range g g 32 NULL 8 Using where
+1 SIMPLE t1 range g g 34 NULL 8 Using where
SELECT fid, AsText(g) FROM t1 WHERE Within(g, GeomFromText('Polygon((140 140,160 140,160 160,140 160,140 140))'));
fid AsText(g)
1 LINESTRING(150 150,150 150)
@@ -301,7 +301,7 @@ count(*)
EXPLAIN SELECT fid, AsText(g) FROM t2 WHERE Within(g,
GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))'));
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE t2 range g g 32 NULL 4 Using where
+1 SIMPLE t2 range g g 34 NULL 4 Using where
SELECT fid, AsText(g) FROM t2 WHERE Within(g,
GeomFromText('Polygon((40 40,60 40,60 60,40 60,40 40))'));
fid AsText(g)
diff --git a/mysql-test/r/gis.result b/mysql-test/r/gis.result
index 55f42141adb..40c70721347 100644
--- a/mysql-test/r/gis.result
+++ b/mysql-test/r/gis.result
@@ -894,4 +894,43 @@ drop table t1, t2;
SELECT 1;
1
1
+CREATE TABLE t1 (p POINT);
+CREATE TABLE t2 (p POINT, INDEX(p));
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 system p NULL NULL NULL 1
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+EXPLAIN
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref p p 28 const 1 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+DROP TABLE t1, t2;
+End of 5.0 tests
End of 5.0 tests
diff --git a/mysql-test/r/innodb_gis.result b/mysql-test/r/innodb_gis.result
index 2c62537aa94..bfe8c984b7b 100644
--- a/mysql-test/r/innodb_gis.result
+++ b/mysql-test/r/innodb_gis.result
@@ -542,3 +542,42 @@ Overlaps(@horiz1, @point2)
0
DROP TABLE t1;
End of 5.0 tests
+CREATE TABLE t1 (p POINT);
+CREATE TABLE t2 (p POINT, INDEX(p));
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref p p 28 const 1 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+1
+INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
+INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
+EXPLAIN
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ref p p 28 const 1 Using where
+SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+EXPLAIN
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where
+SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
+COUNT(*)
+2
+DROP TABLE t1, t2;
+End of 5.0 tests
diff --git a/mysql-test/t/bdb_gis.test b/mysql-test/t/bdb_gis.test
index 88dcbb7cbe9..cb6d0683874 100644
--- a/mysql-test/t/bdb_gis.test
+++ b/mysql-test/t/bdb_gis.test
@@ -1,3 +1,4 @@
-- source include/have_bdb.inc
SET storage_engine=bdb;
--source include/gis_generic.inc
+--source include/gis_keys.inc
diff --git a/mysql-test/t/gis.test b/mysql-test/t/gis.test
index 730f046dd27..b7da7db1d76 100644
--- a/mysql-test/t/gis.test
+++ b/mysql-test/t/gis.test
@@ -598,4 +598,6 @@ SELECT AsText(GeometryFromText(CONCAT(
--enable_query_log
SELECT 1;
+-- source include/gis_keys.inc
+
--echo End of 5.0 tests
diff --git a/mysql-test/t/innodb_gis.test b/mysql-test/t/innodb_gis.test
index 142b526af92..024d17c5363 100644
--- a/mysql-test/t/innodb_gis.test
+++ b/mysql-test/t/innodb_gis.test
@@ -1,3 +1,4 @@
--source include/have_innodb.inc
SET storage_engine=innodb;
--source include/gis_generic.inc
+--source include/gis_keys.inc
View Lorry Controller Status