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

"WHERE CONTAINS(indexed_geometry_column,1)" causes full table scan

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1
    • Fix Version/s: 10.1.7
    • Component/s: Optimizer
    • Labels:
      None

      Description

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1  (a GEOMETRY NOT NULL, SPATIAL KEY(a)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES (Point(1,1)),(Point(2,2)),(Point(3,3));
      EXPLAIN SELECT * FROM t1 WHERE CONTAINS(a,1);

      returns

      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      | id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+
      |    1 | SIMPLE      | t1    | ALL  | a             | NULL | NULL    | NULL |    3 | Using where |
      +------+-------------+-------+------+---------------+------+---------+------+------+-------------+

      This is wrong. CONTAINS() and other spatial relations should report "Impossible WHERE" on attempt to use non-geometry values. There is no a need to do the full table scan, the result will be empty set anyway.

        Attachments

          Activity

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: