Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-7682

Incorrect use of SPATIAL KEY for query plan

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 5.5
    • Fix Version/s: 5.5.43
    • Component/s: GIS, Optimizer
    • 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.

        Attachments

          Activity

            People

            • Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              cvicentiu Vicențiu Ciorbaru
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: