Description:
When using the ST_Collect function in a WHERE clause, different behavior is observed depending on whether the function is used directly in a SELECT query or within a table created via CREATE TABLE AS SELECT. The query on the original table (t0) returns NULL, while the query on the newly created table (t1) returns an empty set, despite both queries being expected to return similar results.
How to repeat:
1.Create table t0 and insert data
CREATE TABLE t0 (c0 POLYGON);
INSERT INTO t0 (c0) VALUES (ST_GeomFromText('POLYGON((61.85181 74.801155, 78.086641 26.700628, 96.778202 77.696627, 142.040745 61.66329, 61.85181 74.801155))'));
INSERT INTO t0 (c0) VALUES (ST_GeomFromText('POLYGON((-126.790675 87.176657, -93.78417 23.609077, -129.682427 30.973845, 48.221367 -14.493615, -75.428887 -36.458731, -126.790675 87.176657))'));
2. Query with ST_Collect in the SELECT statement and WHERE clause
mysql> SELECT (ST_Collect(c0)) FROM t0 WHERE (ST_GeomFromText('LINESTRING(-21.006397 -45.372426, 154.901935 -24.331927, -44.132809 -49.690502)')) IN (SELECT (ST_Collect(c0)) FROM t0);
+------------------------------------+
| (ST_Collect(c0)) |
+------------------------------------+
| NULL |
+------------------------------------+
1 row in set (0.00 sec)
3. Create table t1 using CREATE TABLE AS SELECT with ST_Collect and then query t1 using the semantically same SELECT WHERE condition
mysql> CREATE TABLE t1 AS (SELECT (ST_Collect(c0)) AS c0 FROM t0);
Query OK, 1 row affected (0.05 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT (c0) FROM t1 WHERE (ST_GeomFromText('LINESTRING(-21.006397 -45.372426, 154.901935 -24.331927, -44.132809 -49.690502)')) IN (SELECT (c0) FROM t1);
Empty set (0.00 sec)
Expected Result:
The SELECT results from t0 and t1 should be identical, as they are expected to have the same types and values. However, the result is the opposite: the query on t0 returns NULL, while the query on t1 returns no rows at all.
Description: When using the ST_Collect function in a WHERE clause, different behavior is observed depending on whether the function is used directly in a SELECT query or within a table created via CREATE TABLE AS SELECT. The query on the original table (t0) returns NULL, while the query on the newly created table (t1) returns an empty set, despite both queries being expected to return similar results. How to repeat: 1.Create table t0 and insert data CREATE TABLE t0 (c0 POLYGON); INSERT INTO t0 (c0) VALUES (ST_GeomFromText('POLYGON((61.85181 74.801155, 78.086641 26.700628, 96.778202 77.696627, 142.040745 61.66329, 61.85181 74.801155))')); INSERT INTO t0 (c0) VALUES (ST_GeomFromText('POLYGON((-126.790675 87.176657, -93.78417 23.609077, -129.682427 30.973845, 48.221367 -14.493615, -75.428887 -36.458731, -126.790675 87.176657))')); 2. Query with ST_Collect in the SELECT statement and WHERE clause mysql> SELECT (ST_Collect(c0)) FROM t0 WHERE (ST_GeomFromText('LINESTRING(-21.006397 -45.372426, 154.901935 -24.331927, -44.132809 -49.690502)')) IN (SELECT (ST_Collect(c0)) FROM t0); +------------------------------------+ | (ST_Collect(c0)) | +------------------------------------+ | NULL | +------------------------------------+ 1 row in set (0.00 sec) 3. Create table t1 using CREATE TABLE AS SELECT with ST_Collect and then query t1 using the semantically same SELECT WHERE condition mysql> CREATE TABLE t1 AS (SELECT (ST_Collect(c0)) AS c0 FROM t0); Query OK, 1 row affected (0.05 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> SELECT (c0) FROM t1 WHERE (ST_GeomFromText('LINESTRING(-21.006397 -45.372426, 154.901935 -24.331927, -44.132809 -49.690502)')) IN (SELECT (c0) FROM t1); Empty set (0.00 sec) Expected Result: The SELECT results from t0 and t1 should be identical, as they are expected to have the same types and values. However, the result is the opposite: the query on t0 returns NULL, while the query on t1 returns no rows at all.