Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
12.2.2
-
None
-
12.2.2-MariaDB-ubu2404
-
Not for Release Notes
Description
I found a wrong-result issue involving a POLYGON column, GROUP BY, ST_IsValid(), and CREATE TEMPORARY TABLE AS SELECT.
The same query returns different results depending on whether the grouped geometry result is evaluated through a VIEW, a CTE, or a TEMPORARY TABLE.
The VIEW and CTE versions return 1 valid polygon row, while the equivalent TEMPORARY TABLE version returns an empty result set.
This looks incorrect because the base table contains one valid POLYGON value and one NULL value. After GROUP BY, the valid polygon row should still satisfy ST_IsValid(c) = 1.
Environment:
MariaDB version:
12.2.2-MariaDB-ubu2404
How to repeat:
DROP VIEW IF EXISTS v0; |
DROP TEMPORARY TABLE IF EXISTS temp_t; |
DROP TABLE IF EXISTS a; |
|
|
CREATE TABLE a ( |
b POLYGON
|
);
|
|
|
INSERT INTO a VALUES ( |
ST_GeomFromText('POLYGON((-45.4806 82.2754, 44.9806 82.2754, 44.9806 82.7754, -45.4806 82.7754, -45.4806 82.2754))') |
);
|
|
|
INSERT INTO a VALUES (NULL); |
|
|
CREATE VIEW v0 AS |
SELECT b AS c |
FROM a |
GROUP BY c; |
|
|
SELECT c |
FROM v0 |
WHERE ST_IsValid(c) = 1; |
|
|
WITH CTE AS ( |
SELECT b AS c |
FROM a |
GROUP BY c |
)
|
SELECT c |
FROM CTE |
WHERE ST_IsValid(c) = 1; |
|
|
CREATE TEMPORARY TABLE temp_t AS |
SELECT b AS c |
FROM a |
GROUP BY c; |
|
|
SELECT c |
FROM temp_t |
WHERE ST_IsValid(c) = 1; |
Actual result:
The VIEW query returns 1 row:
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| c |
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 0x0000000001030000000100000005000000933A014D84BD46C061545227A0915440933A014D847D464061545227A0915440933A014D847D464061545227A0B15440933A014D84BD46C061545227A0B15440933A014D84BD46C061545227A0915440 |
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
The CTE query returns 1 row:
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| c |
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 0x0000000001030000000100000005000000933A014D84BD46C061545227A0915440933A014D847D464061545227A0915440933A014D847D464061545227A0B15440933A014D84BD46C061545227A0B15440933A014D84BD46C061545227A0915440 |
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
The TEMPORARY TABLE query returns:
Empty set
|
Expected result:
The TEMPORARY TABLE query should return the same valid polygon row as the VIEW and CTE queries:
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| c |
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| 0x0000000001030000000100000005000000933A014D84BD46C061545227A0915440933A014D847D464061545227A0915440933A014D847D464061545227A0B15440933A014D84BD46C061545227A0B15440933A014D84BD46C061545227A0915440 |
|
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Reason:
The base table contains two rows:
valid POLYGON value
|
NULL
|
The query:
SELECT b AS c |
FROM a |
GROUP BY c |
should preserve the valid POLYGON value as one grouped row.
For that row:
ST_IsValid(c)
|
should evaluate to 1, so this predicate should match it:
WHERE ST_IsValid(c) = 1 |
The VIEW and CTE versions confirm this behavior and return the valid polygon row. However, after materializing the same grouped result into a TEMPORARY TABLE, the same predicate returns an empty set.
Observed inconsistency:
The following three forms should be semantically consistent:
1. VIEW:
SELECT c FROM v0 WHERE ST_IsValid(c) = 1
2. CTE:
WITH CTE AS (...) SELECT c FROM CTE WHERE ST_IsValid(c) = 1
3. TEMPORARY TABLE:
CREATE TEMPORARY TABLE temp_t AS SELECT b AS c FROM a GROUP BY c;
SELECT c FROM temp_t WHERE ST_IsValid(c) = 1
However, only the TEMPORARY TABLE version returns an empty result set.
This suggests a wrong-result bug involving CREATE TEMPORARY TABLE AS SELECT, GROUP BY on a geometry column, and ST_IsValid() evaluation on the materialized geometry value.
Attachments
Issue Links
- duplicates
-
-
- Closed
-