Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL)
-
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.
Discussed the issue with holyfoot. Takeaways:
Conclusion: let's try to provide key_length() for geometry columns. Implementation should mention that it is done only for Hash Join.