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

          Activity

            No workflow transitions have been executed yet.

            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.