Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.5.2
-
None
-
None
Description
The query behavior between a materialized table and a view created from the same source table, which contains LINESTRING and POINT types, is inconsistent when using the BIN() function.
Steps to Reproduce:
1. Create Table t0 and Insert Data
CREATE TABLE t0 (c0 LINESTRING, c1 POINT); |
INSERT INTO t0 (c0, c1) VALUES |
(LineFromText('LINESTRING(110.625891 23.647987, 131.761062 -22.036987)'), PointFromText('POINT(-31.973521 -32.253348)')), |
(LineFromText('LINESTRING(-64.099221 85.347816, 83.476771 49.964253, 67.395816 -68.661706, 89.535845 -60.182826)'), PointFromText('POINT(72.132833 50.949942)')), |
(LineFromText('LINESTRING(30.138314 30.867908, 36.898225 63.201669, 167.383497 51.478527, 114.171311 55.291141, -88.146277 -78.797587)'), PointFromText('POINT(-90.396609 66.746729)')); |
2. Create Materialized Table t1 and Query
MariaDB [test]> CREATE TABLE t1 AS (SELECT (BIN(c0)) AS c0 , c1 AS c1 FROM t0 ); |
Query OK, 3 rows affected (0.018 sec) |
Records: 3 Duplicates: 0 Warnings: 0
|
|
|
MariaDB [test]> SELECT c1 FROM t1 WHERE (CASE WHEN ((c0) > (PointFromText('POINT(159.026499 76.330577)'))) THEN (('WRHVP0tOYN')) ELSE (('8kUtsNTg5m')) END); |
+---------------------------+ |
| c1 |
|
+---------------------------+ |
| 8?@m @ |
|
| R@eyI@ |
|
| '
|
bVc~nhʯP@ |
|
+---------------------------+ |
3 rows in set, 3 warnings (0.001 sec) |
3. Create t1 as VIEW instead:
MariaDB [test]> CREATE VIEW t1 AS (SELECT (BIN(c0)) AS c0 , c1 AS c1 FROM t0 ); |
Query OK, 0 rows affected (0.007 sec) |
|
|
MariaDB [test]> SELECT c1 FROM t1 WHERE (CASE WHEN ((c0) > (PointFromText('POINT(159.026499 76.330577)'))) THEN (('WRHVP0tOYN')) ELSE (('8kUtsNTg5m')) END); |
Empty set, 3 warnings (0.005 sec) |
Expected Behavior:
Both the materialized table (t1) and the view (t1) should return the same result for the SELECT query.
Observed Behavior:
Materialized Table (t1) Query: Returns 3 rows.
View (t1) Query: Returns an empty set, even though the data in the underlying table is identical.
Attachments
Issue Links
- relates to
-
-
- Open
-