[MDEV-8610] "WHERE CONTAINS(indexed_geometry_column,1)" causes full table scan Created: 2015-08-13  Updated: 2015-08-13  Resolved: 2015-08-13

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.1
Fix Version/s: 10.1.7

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None


 Description    

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1  (a GEOMETRY NOT NULL, SPATIAL KEY(a)) ENGINE=MyISAM;
 
INSERT INTO t1 VALUES (Point(1,1)),(Point(2,2)),(Point(3,3));
 
EXPLAIN SELECT * FROM t1 WHERE CONTAINS(a,1);

returns

+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
 
|    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |    3 | Using where |
 
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+

This is wrong. CONTAINS() and other spatial relations should report "Impossible WHERE" on attempt to use non-geometry values. There is no a need to do the full table scan, the result will be empty set anyway.
Generated at Thu Feb 08 07:28:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.