[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

Activity

No workflow transitions have been executed yet.

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