[MDEV-35787] Inconsistent Query Results Between Materialized Table and View with ST_AsGeoJSON and Spatial Data - Jira

Details

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 11.6.2
Fix Version/s: 11.4
Component/s: None
Labels:
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

MDEV-35784 Inconsistent Behavior Between Materialized Table and View When Using BIN() Function on LINESTRING Data

Open

MDEV-35786 Inconsistent Query Results Between Materialized Table and View with MD5 Function on Spatial Data

Open

MariaDB Server

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

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration