Details
Major
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Fixed with:
9253064c0547b16b867565a05f8305780c2ecc34
Discussed the issue with Alexey Botchkov. Takeaways:
Conclusion: let's try to provide key_length() for geometry columns. Implementation should mention that it is done only for Hash Join.