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

Different results of GIS functions on NULL vs NOT NULL columns

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1(EOL)
    • 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

          relates to

          Task - A task that needs to be done. MDEV-7069 Fix buildbot failures in main server trees

          • Major - Major loss of function.
          • Stalled

          Task - A task that needs to be done. MDEV-7509 Testing for GIS extensions

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8676 Some storage_engine tests fail on 10.1

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova added a comment -

            Reassigning to bar because the problem appeared in 10.1 tree from the following revision:

            commit ba3573cae8f0d269452cc6213f1a8243e455830e
            		 
            Author: Alexander Barkov <bar@mariadb.org>
            		 
            Date:   Thu Mar 12 13:40:52 2015 +0400
            		 
             
            		 
                Clean-up:
            		 
                - Renaming Item::is_bool_func() to is_bool_type(), to avoid assumption
            		 
                  that the item is an Item_func derivant.
            		 
                - Deriving Item_func_spatial_rel from Item_bool_func rather than Item_int_func
            		 
             

            elenst Elena Stepanova added a comment - Reassigning to bar because the problem appeared in 10.1 tree from the following revision: commit ba3573cae8f0d269452cc6213f1a8243e455830e Author: Alexander Barkov <bar@mariadb.org> Date: Thu Mar 12 13:40:52 2015 +0400   Clean-up: - Renaming Item::is_bool_func() to is_bool_type(), to avoid assumption that the item is an Item_func derivant. - Deriving Item_func_spatial_rel from Item_bool_func rather than Item_int_func  
            elenst Elena Stepanova made changes -
            Assignee Alexey Botchkov [ holyfoot ] Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov added a comment - - edited

            It seems that the problem happens because Item_func_spatial_rel does not set its maybe_null member correctly:

            DROP TABLE IF EXISTS t1,t2;
            		 
            CREATE TABLE t1 (g1 GEOMETRY NOT NULL,g2 GEOMETRY NULL);
            		 
            CREATE TABLE t2 AS SELECT WITHIN(g1,g1) as w1,WITHIN(g2,g2) AS w2 FROM t1;
            		 
            SHOW CREATE TABLE t2;

            returns

            +-------+---------------------------------------------------------------------------------------------------------------+
            		 
            | Table | Create Table                                                                                                  |
            		 
            +-------+---------------------------------------------------------------------------------------------------------------+
            		 
            | t2    | CREATE TABLE `t2` (
            		 
              `w1` int(1) NOT NULL,
            		 
              `w2` int(1) DEFAULT NULL
            		 
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            		 
            +-------+---------------------------------------------------------------------------------------------------------------+

            Notice, NULL-ability of WITHIN() depends on NULL-ability of its arguments, which is not correct.
            Spatial relations should always be NULL-able.

            bar Alexander Barkov added a comment - - edited It seems that the problem happens because Item_func_spatial_rel does not set its maybe_null member correctly: DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (g1 GEOMETRY NOT NULL,g2 GEOMETRY NULL); CREATE TABLE t2 AS SELECT WITHIN(g1,g1) as w1,WITHIN(g2,g2) AS w2 FROM t1; SHOW CREATE TABLE t2; returns +-------+---------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------------------+ | t2 | CREATE TABLE `t2` ( `w1` int(1) NOT NULL, `w2` int(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+---------------------------------------------------------------------------------------------------------------+ Notice, NULL-ability of WITHIN() depends on NULL-ability of its arguments, which is not correct. Spatial relations should always be NULL-able.
            bar Alexander Barkov made changes -
            Fix Version/s 10.1.8 [ 19605 ]
            Fix Version/s 10.1 [ 16100 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 71210 ] MariaDB v4 [ 149513 ]

            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.