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

Incorrect use of SPATIAL KEY for query plan

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL)
    • 5.5.43
    • GIS, Optimizer
    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.