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

Using spatial index changes type from point to geometry

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.2.3, 10.2.4
    • Fix Version/s: 10.2.5
    • Labels:
      None
    • Environment:
      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.
    • Sprint:
      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

              • Assignee:
                holyfoot Alexey Botchkov
                Reporter:
                wallneradam Adam Wallner
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: