Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.6.2
-
None
-
None
Description
A query involving the MD5 function applied to spatial data (MULTIPOINT) produces inconsistent results between a materialized table and a view.
Steps to Reproduce:
1. Create Table t0 and Insert Data
CREATE TABLE t0 (c0 MULTIPOINT, c1 POLYGON, c2 POINT); |
INSERT INTO t0 (c0, c1, c2) VALUES |
(MultiPointFromText('MULTIPOINT(-13.243508 -89.72321, 20.984539 -9.791904)'), PolygonFromText('POLYGON((-133.63634 -70.901156, 139.151461 51.460862, 41.957586 -29.523348, 160.812111 -72.018653, 140.898969 -23.690752, -27.020237 -64.367375, -133.63634 -70.901156))'), PointFromText('POINT(-28.979681 -0.061117)')), |
(MultiPointFromText('MULTIPOINT(-165.448856 -62.542856, -99.178338 -61.046728, -130.798938 -72.509047, 151.462503 -35.061082)'), PolygonFromText('POLYGON((78.376913 -79.65474, 15.4778 42.529888, -85.467094 -50.868233, -100.070364 9.924562, 78.376913 -79.65474))'), PointFromText('POINT(-128.593046 -61.042668)')), |
(MultiPointFromText('MULTIPOINT(-130.656774 50.634299, -10.000391 81.329334)'), PolygonFromText('POLYGON((-23.326944 -79.702146, 69.436945 5.255152, 150.159648 32.016004, -67.343279 -39.003253, -139.206815 -48.013763, -170.27374 -15.678534, -23.326944 -79.702146))'), PointFromText('POINT(119.977739 -49.693341)')), |
(MultiPointFromText('MULTIPOINT(-174.402103 -74.577663, 27.708025 -65.662975, -70.868585 0.908852, 52.061741 81.275564)'), PolygonFromText('POLYGON((105.464146 42.606199, -37.608089 -21.452164, -81.015738 -56.798221, -112.076569 71.59244, 105.464146 42.606199))'), PointFromText('POINT(-43.976735 35.765613)')), |
(MultiPointFromText('MULTIPOINT(7.477497 -72.874114, 158.943549 -75.50094, 155.455865 43.905502, 121.062514 38.583963, -31.241544 -67.333167)'), PolygonFromText('POLYGON((-37.850314 64.046637, 177.40746 -86.566796, 10.384323 22.471495, -173.818114 -21.278777, 78.275293 61.993126, -37.850314 64.046637))'), PointFromText('POINT(14.039224 86.153274)')); |
2. Create Materialized Table t1 and Query
MariaDB [test]> CREATE TABLE t1 AS (SELECT (MD5(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 ); |
Query OK, 5 rows affected (0.019 sec) |
Records: 5 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> SELECT c2, (c0) FROM t1 WHERE ((c2 = 'SpAYBNczXg') OR ((c0) > PointFromText('POINT(64.611855 -39.518889)'))) ORDER BY (c0) ASC, c1 ASC; |
+---------------------------+----------------------------------+ |
| c2 | c0 |
|
+---------------------------+----------------------------------+ |
| 8_<Su8J | ecd5a4a0bf095d8df432df97e14ab5d5 |
|
+---------------------------+----------------------------------+ |
1 row in set (0.000 sec) |
3. Create t1 as View and Query
MariaDB [test]> CREATE VIEW t1 AS (SELECT (MD5(c0)) AS c0 , c1 AS c1, c2 AS c2 FROM t0 ); |
Query OK, 0 rows affected (0.010 sec) |
|
|
MariaDB [test]> SELECT c2, (c0) FROM t1 WHERE ((c2 = 'SpAYBNczXg') OR ((c0) > PointFromText('POINT(64.611855 -39.518889)'))) ORDER BY (c0) ASC, c1 ASC; |
+---------------------------+----------------------------------+ |
| c2 | c0 |
|
+---------------------------+----------------------------------+ |
| E<2VA@ | 355d3fcadc2a6c2871a90da4e9d37251 |
|
| ;`J %vN | 38bac7c2753af2666caa2ba12fe26999 |
|
| o
|
+,@T=ωU@ | 584d50c622163b5ec833abb8d2e7086c |
|
| AF]@eH | 942db91195e5deafe91175aaf348ec0d |
|
| 8_<Su8J | ecd5a4a0bf095d8df432df97e14ab5d5 |
|
+---------------------------+----------------------------------+ |
5 rows in set (0.005 sec) |
Expected Behavior:
Both the materialized table and the view should return the same result set.
Observed Behavior:
- Materialized Table: Returns 1 row.
- View: Returns 5 rows
Attachments
Issue Links
- relates to
-
-
- Open
-