[MDEV-7682] Incorrect use of SPATIAL KEY for query plan Created: 2015-03-09  Updated: 2015-03-20  Resolved: 2015-03-20

Status: Closed
Project: MariaDB Server
Component/s: GIS, Optimizer
Affects Version/s: 5.5
Fix Version/s: 5.5.43

Type: Bug Priority: Major
Reporter: Vicențiu Ciorbaru Assignee: Vicențiu Ciorbaru
Resolution: Fixed Votes: 0
Labels: None


 Description   

After the changes made in https://github.com/MariaDB/server/commit/45b6edb158f8101d641f550179ee15df363f686f

The testcase main.select_jcl6 shows a difference of key_length for the following query:

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;
 

 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      hash_ALL        a       #hash#$hj       14      test.t1.a       2       Using where; Using join buffer (flat, BNLH join)  // OLD VALUE IS 14
 
+1      SIMPLE  t2      hash_ALL        a       #hash#$hj       2       test.t1.a       2       Using where; Using join buffer (flat, BNLH join)  // NEW VALUE is 2

The difference is due to calling key_length for the spatial key in sql/table.cc:5996, which yields the value of 0. It seems that either the key_length value is incorrectly returned, or in case it is correct, the optimizer should not make use of that key in the plan.

 Comments   
Comment by Sergei Petrunia [ 2015-03-10 ]

Discussed the issue with holyfoot. Takeaways:

  • creating hash index over geom fields is not very meaningful, because has indexes compares values with memcmp(). However, identical geometries may have different binary-blob forms.
  • memcmp()-type comparison is used for handling SQL equalities like geom_column1=geom_column2. This comparison doesn't make much sense but we need to handle it.
  • All geometry columns have "field->type() == MYSQL_TYPE_GEOMETRY"
  • However, it is not nice to make geometry columns a special case

Conclusion: let's try to provide key_length() for geometry columns. Implementation should mention that it is done only for Hash Join.

Comment by Vicențiu Ciorbaru [ 2015-03-20 ]

Fixed with:
9253064c0547b16b867565a05f8305780c2ecc34

Generated at Thu Feb 08 07:21:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.