1
--source include/have_geometry.inc
4
# Spatial objects with keys
8
# Bug #30825: Problems when putting a non-spatial index on a GIS column
11
CREATE TABLE t1 (p POINT);
12
CREATE TABLE t2 (p POINT, INDEX(p));
13
INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
14
INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
16
# no index, returns 1 as expected
17
SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
19
# with index, returns 1 as expected
20
# EXPLAIN shows that the index is not used though
21
# due to the "most rows covered anyway, so a scan is more effective" rule
23
SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
24
SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
26
# adding another row to the table so that
27
# the "most rows covered" rule doesn't kick in anymore
28
# now EXPLAIN shows the index used on the table
29
# and we're getting the wrong result again
30
INSERT INTO t1 VALUES (POINTFROMTEXT('POINT(1 2)'));
31
INSERT INTO t2 VALUES (POINTFROMTEXT('POINT(1 2)'));
33
SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
34
SELECT COUNT(*) FROM t1 WHERE p=POINTFROMTEXT('POINT(1 2)');
37
SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
38
SELECT COUNT(*) FROM t2 WHERE p=POINTFROMTEXT('POINT(1 2)');
41
SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
42
SELECT COUNT(*) FROM t2 IGNORE INDEX(p) WHERE p=POINTFROMTEXT('POINT(1 2)');
46
--echo End of 5.0 tests
50
--echo # Test for bug #58650 "Failing assertion: primary_key_no == -1 ||
51
--echo # primary_key_no == 0".
54
drop table if exists t1;
56
--echo # The minimal test case.
57
create table t1 (a int not null, b linestring not null, unique key b (b(12)), unique key a (a));
59
--echo # The original test case.
60
create table t1 (a int not null, b linestring not null, unique key b (b(12)));
61
create unique index a on t1(a);