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

Using spatial index changes type from point to geometry

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.2.3, 10.2.4
    • 10.2.5
    • None
    • Tested On Mac OS X compiled from git 10.2 branch, And tested on Alpine Linux Docker container compiled from 10.2.3 release source.
    • 10.2.5-1

    Description

      If we use a geospatial function e.g. ST_Intersects on a field which has spatial index the original POINT type will automatically be changed to GEOMETRY. This could cause problems in applications where this field is expected to be POINT.

      We can reproduce the bug with the following script:

      DROP TABLE IF EXISTS test;
       
      CREATE TABLE test (
        coordinate point NOT NULL,
        SPATIAL KEY coordinate (coordinate)
      ) ENGINE=Aria DEFAULT CHARSET=ascii PAGE_CHECKSUM=1;
       
      SHOW COLUMNS FROM test;
       
      INSERT INTO test (coordinate) VALUES(ST_PointFromText("POINT(0 0)"));
      INSERT INTO test (coordinate) VALUES(ST_PointFromText("POINT(10 0)"));
      INSERT INTO test (coordinate) VALUES(ST_PointFromText("POINT(10 10)"));
      INSERT INTO test (coordinate) VALUES(ST_PointFromText("POINT(0 10)"));
      INSERT INTO test (coordinate) VALUES(ST_PointFromText("POINT(5 5)"));
       
      SELECT * FROM test WHERE ST_Intersects(ST_LineFromText("LINESTRING(0 0, 10 0, 10 10, 0 10)"), coordinate);
       
      SHOW COLUMNS FROM test;
       
      DROP TABLE test;
      

      Attachments

        Issue Links

          Activity

            People

              holyfoot Alexey Botchkov
              wallneradam Adam Wallner
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.