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

Different results of GIS functions on NULL vs NOT NULL columns

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1
    • 10.1.8
    • GIS

    Description

      Test case

      CREATE TABLE gis_geometrycollection (fid int, g GEOMETRYCOLLECTION NOT NULL) ENGINE=MyISAM;
       
      INSERT INTO gis_geometrycollection (fid,g) VALUES
      (120, GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
      (122, GeomFromText('GeometryCollection()'));
       
      SELECT g1.fid as first, g2.fid as second,
      Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
      Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
      Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
      FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
       
      ALTER TABLE gis_geometrycollection MODIFY g GEOMETRYCOLLECTION NULL;
       
      SELECT g1.fid as first, g2.fid as second,
      Within(g1.g, g2.g) as w, Contains(g1.g, g2.g) as c, Overlaps(g1.g, g2.g) as o,
      Equals(g1.g, g2.g) as e, Disjoint(g1.g, g2.g) as d, Touches(g1.g, g2.g) as t,
      Intersects(g1.g, g2.g) as i, Crosses(g1.g, g2.g) as r
      FROM gis_geometrycollection g1, gis_geometrycollection g2 ORDER BY first, second;
       
      DROP TABLE gis_geometrycollection;

      Output on 10.1

      # First SELECT 
      +-------+--------+---+---+---+---+---+---+---+---+
      | first | second | w | c | o | e | d | t | i | r |
      +-------+--------+---+---+---+---+---+---+---+---+
      |   120 |    120 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 0 |
      |   120 |    122 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 0 |
      |   122 |    120 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 0 |
      |   122 |    122 | 1 | 1 | 0 | 1 | 0 | 0 | 1 | 0 |
      +-------+--------+---+---+---+---+---+---+---+---+
       
      # Second SELECT
      +-------+--------+------+------+------+------+------+------+------+------+
      | first | second | w    | c    | o    | e    | d    | t    | i    | r    |
      +-------+--------+------+------+------+------+------+------+------+------+
      |   120 |    120 |    1 |    1 |    0 |    1 |    0 |    0 |    1 |    0 |
      |   120 |    122 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
      |   122 |    120 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
      |   122 |    122 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
      +-------+--------+------+------+------+------+------+------+------+------+

      I am not even sure which result is correct, but in any case they cannot possibly be different.

      Note: storage_engine.type_spatial_indexes fails because of it.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              elenst Elena Stepanova
              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.