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

Inconsistent Query Results Between Materialized Table and View with ST_AsGeoJSON and Spatial Data

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 11.6.2
    • 11.4
    • None
    • None

    Description

      A query using ST_AsGeoJSON to convert spatial data (MULTILINESTRING) produces inconsistent results when executed on a materialized table (t1) versus a view (t1).

      1. Create Table t0 and Insert Data

      CREATE TABLE t0 (c0 MULTILINESTRING, c1 MULTIPOLYGON);
      		 
      INSERT INTO t0 (c0, c1) VALUES (MultiLineStringFromText('MULTILINESTRING((143.478053 43.905409, 133.412416 73.864103), (-147.978948 -64.809239, -92.746688 -21.873515, 44.498933 -74.266897, 140.226441 33.07901, -86.113262 -80.435631))'), MultiPolygonFromText('MULTIPOLYGON(((-111.802173 54.083761, 79.215366 35.78695, -119.848598 38.973913, -111.802173 54.083761)), ((-59.274918 -57.730711, 50.645431 12.554275, -172.125765 79.731063, -40.305273 -49.726004, 16.554204 -39.514779, 54.801388 -6.358821, -59.274918 -57.730711)), ((-98.89348 57.331209, -133.776921 83.79208, 150.022255 53.485094, -98.89348 57.331209)))'));
      		 
      INSERT INTO t0 (c0, c1) VALUES (MultiLineStringFromText('MULTILINESTRING((97.028527 8.26473, -169.141254 57.381407, -169.755091 -4.373454, 97.634953 -35.719282, 133.326763 45.785104), (175.973401 -25.254845, 40.66765 -3.815325, 105.422305 36.29817), (166.190824 -2.746104, 30.61948 47.825556))'), MultiPolygonFromText('MULTIPOLYGON(((-116.848671 23.581016, -107.292672 -66.168852, -29.830491 6.076159, 48.274929 28.377464, 65.191654 10.75016, -98.936176 68.965503, -116.848671 23.581016)), ((177.392059 34.774265, 143.193653 71.15136, -113.741574 -17.538569, 118.514364 89.976984, -25.783762 -18.645259, 177.392059 34.774265)), ((75.709052 -74.716556, 50.329057 -79.208968, 26.024713 0.642226, -117.503918 88.73309, 75.709052 -74.716556)))'));
      		 
      INSERT INTO t0 (c0, c1) VALUES (MultiLineStringFromText('MULTILINESTRING((-135.105369 23.242331, 52.214621 -40.731325), (3.696821 -75.51963, 59.125857 72.221068, -84.154832 20.721035, 129.457296 37.548667))'), MultiPolygonFromText('MULTIPOLYGON(((62.343212 -7.829917, -33.395172 -83.387789, -156.036871 85.735534, 62.343212 -7.829917)), ((-121.686504 -86.282769, -162.478535 -74.592438, -37.426245 45.779073, 5.062436 -78.267994, 1.942628 -34.567448, -10.872522 -19.594717, -121.686504 -86.282769)))'));
      		 
      INSERT INTO t0 (c0, c1) VALUES (MultiLineStringFromText('MULTILINESTRING((-164.282417 7.872897, -13.7326 -27.738577), (98.724533 -68.676466, -114.167713 -66.121573))'), MultiPolygonFromText('MULTIPOLYGON(((-14.551387 -28.703153, 12.203796 67.699502, 45.62816 3.479576, 72.04227 47.705162, 3.221664 11.593701, 168.129048 17.680309, -14.551387 -28.703153)), ((78.566358 -32.022949, 175.883245 -64.850597, 98.273564 -10.667482, 24.239618 -41.91167, 78.566358 -32.022949)))'));

      2. Create Materialized Table t1 and Query

      MariaDB [test]> CREATE TABLE t1 AS (SELECT (ST_AsGeoJSON(c0)) AS c0 , c1 AS c1 FROM t0 );
      		 
      Query OK, 4 rows affected (0.023 sec)
      		 
      Records: 4  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> SELECT (c0) FROM t1 WHERE (((c0) <= (MultiPolygonFromText('MULTIPOLYGON(((156.565923 86.445211, 8.958872 54.561689, -111.40996 -89.445441, -27.512741 -78.43933, 52.052127 22.327915, 105.803184 -50.190584, 156.565923 86.445211)), ((131.441514 -16.367258, 95.100847 44.338775, 96.412062 71.253007, 93.228296 -88.6101, -111.122746 7.617783, 131.441514 -16.367258)), ((-176.705899 27.699519, -47.865633 87.651552, -107.352245 77.121262, -137.464803 32.407728, -16.447444 53.844025, -176.705899 27.699519)))'))) AND ('bz3dfoRs9F1AsTQtIgFz2EL8j9iWGDYM' > c1)) ORDER BY c1 DESC, (c0) DESC;
      		 
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | c0                                                                                                                                                                                                                                                                                                      |
      		 
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | {"type": "MultiLineString", "coordinates": [[[97.028527, 8.26473], [-169.141254, 57.381407], [-169.755091, -4.373454], [97.634953, -35.719282], [133.326763, 45.785104]], [[175.973401, -25.254845], [40.66765, -3.815325], [105.422305, 36.29817]], [[166.190824, -2.746104], [30.61948, 47.825556]]]} |
      		 
      | {"type": "MultiLineString", "coordinates": [[[143.478053, 43.905409], [133.412416, 73.864103]], [[-147.978948, -64.809239], [-92.746688, -21.873515], [44.498933, -74.266897], [140.226441, 33.07901], [-86.113262, -80.435631]]]}                                                                      |
      		 
      | {"type": "MultiLineString", "coordinates": [[[-164.282417, 7.872897], [-13.7326, -27.738577]], [[98.724533, -68.676466], [-114.167713, -66.121573]]]}                                                                                                                                                   |
      		 
      | {"type": "MultiLineString", "coordinates": [[[-135.105369, 23.242331], [52.214621, -40.731325]], [[3.696821, -75.51963], [59.125857, 72.221068], [-84.154832, 20.721035], [129.457296, 37.548667]]]}                                                                                                    |
      		 
      +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      4 rows in set (0.001 sec)

      3. Create t1 as View instead:

      MariaDB [test]> CREATE VIEW t1 AS (SELECT (ST_AsGeoJSON(c0)) AS c0 , c1 AS c1 FROM t0 );
      		 
      Query OK, 0 rows affected (0.012 sec)
      		 
       
      		 
      MariaDB [test]> SELECT (c0) FROM t1 WHERE (((c0) <= (MultiPolygonFromText('MULTIPOLYGON(((156.565923 86.445211, 8.958872 54.561689, -111.40996 -89.445441, -27.512741 -78.43933, 52.052127 22.327915, 105.803184 -50.190584, 156.565923 86.445211)), ((131.441514 -16.367258, 95.100847 44.338775, 96.412062 71.253007, 93.228296 -88.6101, -111.122746 7.617783, 131.441514 -16.367258)), ((-176.705899 27.699519, -47.865633 87.651552, -107.352245 77.121262, -137.464803 32.407728, -16.447444 53.844025, -176.705899 27.699519)))'))) AND ('bz3dfoRs9F1AsTQtIgFz2EL8j9iWGDYM' > c1)) ORDER BY c1 DESC, (c0) DESC;
      		 
      Empty set (0.011 sec)

      Expected Behavior:

      Both the materialized table and the view should return the same result set.

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35784 Inconsistent Behavior Between Materialized Table and View When Using BIN() Function on LINESTRING Data

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-35786 Inconsistent Query Results Between Materialized Table and View with MD5 Function on Spatial Data

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            dwenking chaos created issue -
            serg Sergei Golubchik made changes -
            Field Original Value New Value
            Fix Version/s 11.4 [ 29301 ]
            serg Sergei Golubchik made changes -
            Assignee Alexander Barkov [ bar ]
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -
            serg Sergei Golubchik made changes -

            People

              bar Alexander Barkov
              dwenking chaos
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.