This is an automated email from the ASF dual-hosted git repository.
dongjoon pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new 9b68d3c92b4c [SPARK-54244][GEO][SQL] Introduce type coercion support
for GEOMETRY data types
9b68d3c92b4c is described below
commit 9b68d3c92b4c7bff7abcb8bb5fe540f2bfefd705
Author: Uros Bojanic <[email protected]>
AuthorDate: Sat Nov 8 07:30:25 2025 -0800
[SPARK-54244][GEO][SQL] Introduce type coercion support for GEOMETRY data
types
### What changes were proposed in this pull request?
Implement least common type (LCT) logic for `GEOMETRY` types, as follows:
- LCT for GeometryType(`srid_1`) and GeometryType(`srid_1`) is:
GeometryType(`srid_1`)
- LCT for GeometryType(`srid_1`) and GeometryType(`srid_2`) is:
GeometryType(`ANY`)
- LCT for GeometryType(`srid_1`) and GeometryType(`ANY`) is:
GeometryType(`ANY`)
- LCT for GeometryType(`ANY`) and GeometryType(`ANY`) is:
GeometryType(`ANY`)
In other words, the mixed SRID `GEOMETRY` type is the *common type* for all
GEOMETRY types.
### Why are the changes needed?
Introducing LCT and type coercion logic in the geospatial data type system.
### Does this PR introduce _any_ user-facing change?
Yes, type coercion is now supported for `GeometryType`.
### How was this patch tested?
Added tests for geometry type coercion:
- `AnsiTypeCoercionSuite`
- `TypeCoercionSuite`
Added appropriate Scala suite unit tests:
- `STExpressionsSuite`
Added appropriate end-to-end SQL tests:
- `st-functions`
### Was this patch authored or co-authored using generative AI tooling?
No.
Closes #52945 from uros-db/geo-coercion-geom.
Authored-by: Uros Bojanic <[email protected]>
Signed-off-by: Dongjoon Hyun <[email protected]>
---
.../sql/catalyst/analysis/AnsiTypeCoercion.scala | 3 +
.../spark/sql/catalyst/analysis/TypeCoercion.scala | 3 +
.../catalyst/analysis/AnsiTypeCoercionSuite.scala | 12 ++
.../sql/catalyst/analysis/TypeCoercionSuite.scala | 12 ++
.../analyzer-results/nonansi/st-functions.sql.out | 72 ++++++++++
.../analyzer-results/st-functions.sql.out | 72 ++++++++++
.../resources/sql-tests/inputs/st-functions.sql | 9 ++
.../sql-tests/results/nonansi/st-functions.sql.out | 81 ++++++++++++
.../sql-tests/results/st-functions.sql.out | 81 ++++++++++++
.../org/apache/spark/sql/STExpressionsSuite.scala | 146 +++++++++++++++++++++
10 files changed, 491 insertions(+)
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercion.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercion.scala
index ea4d04ff6e77..e23e7561f0e3 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercion.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercion.scala
@@ -133,6 +133,9 @@ object AnsiTypeCoercion extends TypeCoercionBase {
// We allow coercion from GEOGRAPHY(<srid>) types (i.e. fixed SRID types)
to the
// GEOGRAPHY(ANY) type (i.e. mixed SRID type). This coercion is always
safe to do.
case (t1: GeographyType, t2: GeographyType) if t1 != t2 =>
Some(GeographyType("ANY"))
+ // We allow coercion from GEOMETRY(<srid>) types (i.e. fixed SRID types)
to the
+ // GEOMETRY(ANY) type (i.e. mixed SRID type). This coercion is always safe
to do.
+ case (t1: GeometryType, t2: GeometryType) if t1 != t2 =>
Some(GeometryType("ANY"))
case (t1, t2) => findTypeForComplex(t1, t2, findTightestCommonType)
}
diff --git
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala
index 9030de9473de..ce387ef397ac 100644
---
a/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala
+++
b/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala
@@ -101,6 +101,9 @@ object TypeCoercion extends TypeCoercionBase {
// We allow coercion from GEOGRAPHY(<srid>) types (i.e. fixed SRID
types) to the
// GEOGRAPHY(ANY) type (i.e. mixed SRID type). This coercion is always
safe to do.
case (t1: GeographyType, t2: GeographyType) if t1 != t2 =>
Some(GeographyType("ANY"))
+ // We allow coercion from GEOMETRY(<srid>) types (i.e. fixed SRID types)
to the
+ // GEOMETRY(ANY) type (i.e. mixed SRID type). This coercion is always
safe to do.
+ case (t1: GeometryType, t2: GeometryType) if t1 != t2 =>
Some(GeometryType("ANY"))
case (t1, t2) => findTypeForComplex(t1, t2, findTightestCommonType)
}
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
index 0e5ebcfa313c..fa5027ce259d 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/AnsiTypeCoercionSuite.scala
@@ -180,6 +180,18 @@ class AnsiTypeCoercionSuite extends TypeCoercionSuiteBase {
widenTest(GeographyType("ANY"), GeographyType("ANY"),
Some(GeographyType("ANY")))
widenTest(GeographyType("ANY"), GeographyType(4326),
Some(GeographyType("ANY")))
widenTest(GeographyType(4326), GeographyType("ANY"),
Some(GeographyType("ANY")))
+ // Geometry with same fixed SRIDs.
+ widenTest(GeometryType(0), GeometryType(0), Some(GeometryType(0)))
+ widenTest(GeometryType(3857), GeometryType(3857), Some(GeometryType(3857)))
+ widenTest(GeometryType(4326), GeometryType(4326), Some(GeometryType(4326)))
+ // Geometry with different fixed SRIDs.
+ widenTest(GeometryType(0), GeometryType(3857), Some(GeometryType("ANY")))
+ widenTest(GeometryType(3857), GeometryType(4326),
Some(GeometryType("ANY")))
+ widenTest(GeometryType(4326), GeometryType(0), Some(GeometryType("ANY")))
+ // Geometry with mixed SRIDs.
+ widenTest(GeometryType("ANY"), GeometryType("ANY"),
Some(GeometryType("ANY")))
+ widenTest(GeometryType("ANY"), GeometryType(4326),
Some(GeometryType("ANY")))
+ widenTest(GeometryType(4326), GeometryType("ANY"),
Some(GeometryType("ANY")))
// Integral mixed with floating point.
widenTest(IntegerType, FloatType, Some(DoubleType))
diff --git
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
index 0169034c3475..e6a9690ad757 100644
---
a/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
+++
b/sql/catalyst/src/test/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercionSuite.scala
@@ -603,6 +603,18 @@ class TypeCoercionSuite extends TypeCoercionSuiteBase {
widenTest(GeographyType("ANY"), GeographyType("ANY"),
Some(GeographyType("ANY")))
widenTest(GeographyType("ANY"), GeographyType(4326),
Some(GeographyType("ANY")))
widenTest(GeographyType(4326), GeographyType("ANY"),
Some(GeographyType("ANY")))
+ // Geometry with same fixed SRIDs.
+ widenTest(GeometryType(0), GeometryType(0), Some(GeometryType(0)))
+ widenTest(GeometryType(3857), GeometryType(3857), Some(GeometryType(3857)))
+ widenTest(GeometryType(4326), GeometryType(4326), Some(GeometryType(4326)))
+ // Geometry with different fixed SRIDs.
+ widenTest(GeometryType(0), GeometryType(3857), Some(GeometryType("ANY")))
+ widenTest(GeometryType(3857), GeometryType(4326),
Some(GeometryType("ANY")))
+ widenTest(GeometryType(4326), GeometryType(0), Some(GeometryType("ANY")))
+ // Geometry with mixed SRIDs.
+ widenTest(GeometryType("ANY"), GeometryType("ANY"),
Some(GeometryType("ANY")))
+ widenTest(GeometryType("ANY"), GeometryType(4326),
Some(GeometryType("ANY")))
+ widenTest(GeometryType(4326), GeometryType("ANY"),
Some(GeometryType("ANY")))
// Integral mixed with floating point.
widenTest(IntegerType, FloatType, Some(FloatType))
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/st-functions.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/st-functions.sql.out
index 71d5a808e686..c86d2454d759 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/st-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/nonansi/st-functions.sql.out
@@ -161,6 +161,14 @@ Project [typeof(array(cast(st_geogfromwkb(wkb#x) as
geography(any)), cast(st_geo
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(array(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))
FROM geodata
+-- !query analysis
+Project [typeof(array(cast(st_geomfromwkb(wkb#x) as geometry(any)),
cast(st_geomfromwkb(wkb#x) as geometry(any)))) AS
typeof(array(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(map('a', ST_GeogFromWKB(wkb), 'b',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY))) FROM geodata
-- !query analysis
@@ -169,6 +177,14 @@ Project [typeof(map(a, cast(st_geogfromwkb(wkb#x) as
geography(any)), b, cast(st
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(map('a', ST_GeomFromWKB(wkb), 'b',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY))) FROM geodata
+-- !query analysis
+Project [typeof(map(a, cast(st_geomfromwkb(wkb#x) as geometry(any)), b,
cast(st_geomfromwkb(wkb#x) as geometry(any)))) AS typeof(map(a,
st_geomfromwkb(wkb), b, CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(array(named_struct('g1', ST_GeogFromWKB(wkb), 'g2',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)), named_struct('g1',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY), 'g2', ST_GeogFromWKB(wkb)))) FROM geodata
-- !query analysis
@@ -177,6 +193,14 @@ Project [typeof(array(cast(named_struct(g1,
st_geogfromwkb(wkb#x), g2, cast(st_g
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(array(named_struct('g1', ST_GeomFromWKB(wkb), 'g2',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)), named_struct('g1',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY), 'g2', ST_GeomFromWKB(wkb)))) FROM geodata
+-- !query analysis
+Project [typeof(array(cast(named_struct(g1, st_geomfromwkb(wkb#x), g2,
cast(st_geomfromwkb(wkb#x) as geometry(any))) as
struct<g1:geometry(any),g2:geometry(any)>), cast(named_struct(g1,
cast(st_geomfromwkb(wkb#x) as geometry(any)), g2, st_geomfromwkb(wkb#x)) as
struct<g1:geometry(any),g2:geometry(any)>))) AS typeof(array(named_struct(g1,
st_geomfromwkb(wkb), g2, CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))),
named_struct(g1, CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY)), g2,
st_geomfromwkb(wk [...]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(named_struct('a', array(ST_GeogFromWKB(wkb),
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)), 'b', map('g', ST_GeogFromWKB(wkb), 'h',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))) FROM geodata
-- !query analysis
@@ -185,6 +209,14 @@ Project [typeof(named_struct(a,
array(cast(st_geogfromwkb(wkb#x) as geography(an
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(named_struct('a', array(ST_GeomFromWKB(wkb),
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)), 'b', map('g', ST_GeomFromWKB(wkb), 'h',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))) FROM geodata
+-- !query analysis
+Project [typeof(named_struct(a, array(cast(st_geomfromwkb(wkb#x) as
geometry(any)), cast(st_geomfromwkb(wkb#x) as geometry(any))), b, map(g,
cast(st_geomfromwkb(wkb#x) as geometry(any)), h, cast(st_geomfromwkb(wkb#x) as
geometry(any))))) AS typeof(named_struct(a, array(st_geomfromwkb(wkb),
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))), b, map(g, st_geomfromwkb(wkb), h,
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY)))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(nvl(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))
FROM geodata
-- !query analysis
@@ -193,6 +225,14 @@ Project [typeof(nvl(st_geogfromwkb(wkb#x),
cast(st_geogfromwkb(wkb#x) as geograp
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(nvl(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))
FROM geodata
+-- !query analysis
+Project [typeof(nvl(st_geomfromwkb(wkb#x), cast(st_geomfromwkb(wkb#x) as
geometry(any)))) AS typeof(nvl(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(nvl2(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY),
ST_GeogFromWKB(wkb))) FROM geodata
-- !query analysis
@@ -201,6 +241,14 @@ Project [typeof(nvl2(st_geogfromwkb(wkb#x),
cast(st_geogfromwkb(wkb#x) as geogra
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(nvl2(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY),
ST_GeomFromWKB(wkb))) FROM geodata
+-- !query analysis
+Project [typeof(nvl2(st_geomfromwkb(wkb#x), cast(st_geomfromwkb(wkb#x) as
geometry(any)), st_geomfromwkb(wkb#x))) AS typeof(nvl2(st_geomfromwkb(wkb),
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY)), st_geomfromwkb(wkb)))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(CASE WHEN wkb IS NOT NULL THEN
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY) ELSE ST_GeogFromWKB(wkb) END) FROM geodata
-- !query analysis
@@ -209,6 +257,14 @@ Project [typeof(CASE WHEN isnotnull(wkb#x) THEN
cast(st_geogfromwkb(wkb#x) as ge
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(CASE WHEN wkb IS NOT NULL THEN
ST_GeomFromWKB(wkb)::GEOMETRY(ANY) ELSE ST_GeomFromWKB(wkb) END) FROM geodata
+-- !query analysis
+Project [typeof(CASE WHEN isnotnull(wkb#x) THEN cast(st_geomfromwkb(wkb#x) as
geometry(any)) ELSE cast(st_geomfromwkb(wkb#x) as geometry(any)) END) AS
typeof(CASE WHEN (wkb IS NOT NULL) THEN CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)) ELSE st_geomfromwkb(wkb) END)#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(coalesce(ST_GeogFromWKB(wkb),
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY))) FROM geodata
-- !query analysis
@@ -217,6 +273,14 @@ Project [typeof(coalesce(cast(st_geogfromwkb(wkb#x) as
geography(any)), cast(st_
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(coalesce(ST_GeomFromWKB(wkb),
ST_GeomFromWKB(wkb)::GEOMETRY(ANY))) FROM geodata
+-- !query analysis
+Project [typeof(coalesce(cast(st_geomfromwkb(wkb#x) as geometry(any)),
cast(st_geomfromwkb(wkb#x) as geometry(any)))) AS
typeof(coalesce(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(IF(wkb IS NOT NULL, ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY),
ST_GeogFromWKB(wkb))) FROM geodata
-- !query analysis
@@ -225,6 +289,14 @@ Project [typeof(if (isnotnull(wkb#x))
cast(st_geogfromwkb(wkb#x) as geography(an
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(IF(wkb IS NOT NULL, ST_GeomFromWKB(wkb)::GEOMETRY(ANY),
ST_GeomFromWKB(wkb))) FROM geodata
+-- !query analysis
+Project [typeof(if (isnotnull(wkb#x)) cast(st_geomfromwkb(wkb#x) as
geometry(any)) else cast(st_geomfromwkb(wkb#x) as geometry(any))) AS
typeof((IF((wkb IS NOT NULL), CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY)),
st_geomfromwkb(wkb))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT
hex(ST_AsBinary(ST_GeogFromWKB(X'0101000000000000000000f03f0000000000000040')))
AS result
-- !query analysis
diff --git
a/sql/core/src/test/resources/sql-tests/analyzer-results/st-functions.sql.out
b/sql/core/src/test/resources/sql-tests/analyzer-results/st-functions.sql.out
index 71d5a808e686..c86d2454d759 100644
---
a/sql/core/src/test/resources/sql-tests/analyzer-results/st-functions.sql.out
+++
b/sql/core/src/test/resources/sql-tests/analyzer-results/st-functions.sql.out
@@ -161,6 +161,14 @@ Project [typeof(array(cast(st_geogfromwkb(wkb#x) as
geography(any)), cast(st_geo
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(array(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))
FROM geodata
+-- !query analysis
+Project [typeof(array(cast(st_geomfromwkb(wkb#x) as geometry(any)),
cast(st_geomfromwkb(wkb#x) as geometry(any)))) AS
typeof(array(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(map('a', ST_GeogFromWKB(wkb), 'b',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY))) FROM geodata
-- !query analysis
@@ -169,6 +177,14 @@ Project [typeof(map(a, cast(st_geogfromwkb(wkb#x) as
geography(any)), b, cast(st
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(map('a', ST_GeomFromWKB(wkb), 'b',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY))) FROM geodata
+-- !query analysis
+Project [typeof(map(a, cast(st_geomfromwkb(wkb#x) as geometry(any)), b,
cast(st_geomfromwkb(wkb#x) as geometry(any)))) AS typeof(map(a,
st_geomfromwkb(wkb), b, CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(array(named_struct('g1', ST_GeogFromWKB(wkb), 'g2',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)), named_struct('g1',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY), 'g2', ST_GeogFromWKB(wkb)))) FROM geodata
-- !query analysis
@@ -177,6 +193,14 @@ Project [typeof(array(cast(named_struct(g1,
st_geogfromwkb(wkb#x), g2, cast(st_g
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(array(named_struct('g1', ST_GeomFromWKB(wkb), 'g2',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)), named_struct('g1',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY), 'g2', ST_GeomFromWKB(wkb)))) FROM geodata
+-- !query analysis
+Project [typeof(array(cast(named_struct(g1, st_geomfromwkb(wkb#x), g2,
cast(st_geomfromwkb(wkb#x) as geometry(any))) as
struct<g1:geometry(any),g2:geometry(any)>), cast(named_struct(g1,
cast(st_geomfromwkb(wkb#x) as geometry(any)), g2, st_geomfromwkb(wkb#x)) as
struct<g1:geometry(any),g2:geometry(any)>))) AS typeof(array(named_struct(g1,
st_geomfromwkb(wkb), g2, CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))),
named_struct(g1, CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY)), g2,
st_geomfromwkb(wk [...]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(named_struct('a', array(ST_GeogFromWKB(wkb),
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)), 'b', map('g', ST_GeogFromWKB(wkb), 'h',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))) FROM geodata
-- !query analysis
@@ -185,6 +209,14 @@ Project [typeof(named_struct(a,
array(cast(st_geogfromwkb(wkb#x) as geography(an
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(named_struct('a', array(ST_GeomFromWKB(wkb),
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)), 'b', map('g', ST_GeomFromWKB(wkb), 'h',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))) FROM geodata
+-- !query analysis
+Project [typeof(named_struct(a, array(cast(st_geomfromwkb(wkb#x) as
geometry(any)), cast(st_geomfromwkb(wkb#x) as geometry(any))), b, map(g,
cast(st_geomfromwkb(wkb#x) as geometry(any)), h, cast(st_geomfromwkb(wkb#x) as
geometry(any))))) AS typeof(named_struct(a, array(st_geomfromwkb(wkb),
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))), b, map(g, st_geomfromwkb(wkb), h,
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY)))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(nvl(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))
FROM geodata
-- !query analysis
@@ -193,6 +225,14 @@ Project [typeof(nvl(st_geogfromwkb(wkb#x),
cast(st_geogfromwkb(wkb#x) as geograp
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(nvl(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))
FROM geodata
+-- !query analysis
+Project [typeof(nvl(st_geomfromwkb(wkb#x), cast(st_geomfromwkb(wkb#x) as
geometry(any)))) AS typeof(nvl(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(nvl2(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY),
ST_GeogFromWKB(wkb))) FROM geodata
-- !query analysis
@@ -201,6 +241,14 @@ Project [typeof(nvl2(st_geogfromwkb(wkb#x),
cast(st_geogfromwkb(wkb#x) as geogra
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(nvl2(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY),
ST_GeomFromWKB(wkb))) FROM geodata
+-- !query analysis
+Project [typeof(nvl2(st_geomfromwkb(wkb#x), cast(st_geomfromwkb(wkb#x) as
geometry(any)), st_geomfromwkb(wkb#x))) AS typeof(nvl2(st_geomfromwkb(wkb),
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY)), st_geomfromwkb(wkb)))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(CASE WHEN wkb IS NOT NULL THEN
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY) ELSE ST_GeogFromWKB(wkb) END) FROM geodata
-- !query analysis
@@ -209,6 +257,14 @@ Project [typeof(CASE WHEN isnotnull(wkb#x) THEN
cast(st_geogfromwkb(wkb#x) as ge
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(CASE WHEN wkb IS NOT NULL THEN
ST_GeomFromWKB(wkb)::GEOMETRY(ANY) ELSE ST_GeomFromWKB(wkb) END) FROM geodata
+-- !query analysis
+Project [typeof(CASE WHEN isnotnull(wkb#x) THEN cast(st_geomfromwkb(wkb#x) as
geometry(any)) ELSE cast(st_geomfromwkb(wkb#x) as geometry(any)) END) AS
typeof(CASE WHEN (wkb IS NOT NULL) THEN CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)) ELSE st_geomfromwkb(wkb) END)#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(coalesce(ST_GeogFromWKB(wkb),
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY))) FROM geodata
-- !query analysis
@@ -217,6 +273,14 @@ Project [typeof(coalesce(cast(st_geogfromwkb(wkb#x) as
geography(any)), cast(st_
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(coalesce(ST_GeomFromWKB(wkb),
ST_GeomFromWKB(wkb)::GEOMETRY(ANY))) FROM geodata
+-- !query analysis
+Project [typeof(coalesce(cast(st_geomfromwkb(wkb#x) as geometry(any)),
cast(st_geomfromwkb(wkb#x) as geometry(any)))) AS
typeof(coalesce(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT typeof(IF(wkb IS NOT NULL, ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY),
ST_GeogFromWKB(wkb))) FROM geodata
-- !query analysis
@@ -225,6 +289,14 @@ Project [typeof(if (isnotnull(wkb#x))
cast(st_geogfromwkb(wkb#x) as geography(an
+- Relation spark_catalog.default.geodata[wkb#x] parquet
+-- !query
+SELECT typeof(IF(wkb IS NOT NULL, ST_GeomFromWKB(wkb)::GEOMETRY(ANY),
ST_GeomFromWKB(wkb))) FROM geodata
+-- !query analysis
+Project [typeof(if (isnotnull(wkb#x)) cast(st_geomfromwkb(wkb#x) as
geometry(any)) else cast(st_geomfromwkb(wkb#x) as geometry(any))) AS
typeof((IF((wkb IS NOT NULL), CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY)),
st_geomfromwkb(wkb))))#x]
++- SubqueryAlias spark_catalog.default.geodata
+ +- Relation spark_catalog.default.geodata[wkb#x] parquet
+
+
-- !query
SELECT
hex(ST_AsBinary(ST_GeogFromWKB(X'0101000000000000000000f03f0000000000000040')))
AS result
-- !query analysis
diff --git a/sql/core/src/test/resources/sql-tests/inputs/st-functions.sql
b/sql/core/src/test/resources/sql-tests/inputs/st-functions.sql
index e1c11bb089b5..70fbdef53330 100644
--- a/sql/core/src/test/resources/sql-tests/inputs/st-functions.sql
+++ b/sql/core/src/test/resources/sql-tests/inputs/st-functions.sql
@@ -32,23 +32,32 @@ SELECT
CAST(ST_GeomFromWKB(X'0101000000000000000000f03f0000000000000040')::GEOME
-- Array
SELECT typeof(array(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))
FROM geodata;
+SELECT typeof(array(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))
FROM geodata;
-- Map
SELECT typeof(map('a', ST_GeogFromWKB(wkb), 'b',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY))) FROM geodata;
+SELECT typeof(map('a', ST_GeomFromWKB(wkb), 'b',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY))) FROM geodata;
-- Struct
SELECT typeof(array(named_struct('g1', ST_GeogFromWKB(wkb), 'g2',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)), named_struct('g1',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY), 'g2', ST_GeogFromWKB(wkb)))) FROM geodata;
+SELECT typeof(array(named_struct('g1', ST_GeomFromWKB(wkb), 'g2',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)), named_struct('g1',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY), 'g2', ST_GeomFromWKB(wkb)))) FROM geodata;
-- Nested
SELECT typeof(named_struct('a', array(ST_GeogFromWKB(wkb),
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)), 'b', map('g', ST_GeogFromWKB(wkb), 'h',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))) FROM geodata;
+SELECT typeof(named_struct('a', array(ST_GeomFromWKB(wkb),
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)), 'b', map('g', ST_GeomFromWKB(wkb), 'h',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))) FROM geodata;
-- NVL
SELECT typeof(nvl(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))
FROM geodata;
+SELECT typeof(nvl(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))
FROM geodata;
-- NVL2
SELECT typeof(nvl2(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY),
ST_GeogFromWKB(wkb))) FROM geodata;
+SELECT typeof(nvl2(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY),
ST_GeomFromWKB(wkb))) FROM geodata;
-- CASE WHEN
SELECT typeof(CASE WHEN wkb IS NOT NULL THEN
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY) ELSE ST_GeogFromWKB(wkb) END) FROM geodata;
+SELECT typeof(CASE WHEN wkb IS NOT NULL THEN
ST_GeomFromWKB(wkb)::GEOMETRY(ANY) ELSE ST_GeomFromWKB(wkb) END) FROM geodata;
-- COALESCE
SELECT typeof(coalesce(ST_GeogFromWKB(wkb),
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY))) FROM geodata;
+SELECT typeof(coalesce(ST_GeomFromWKB(wkb),
ST_GeomFromWKB(wkb)::GEOMETRY(ANY))) FROM geodata;
-- IF
SELECT typeof(IF(wkb IS NOT NULL, ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY),
ST_GeogFromWKB(wkb))) FROM geodata;
+SELECT typeof(IF(wkb IS NOT NULL, ST_GeomFromWKB(wkb)::GEOMETRY(ANY),
ST_GeomFromWKB(wkb))) FROM geodata;
---- ST reader/writer expressions
diff --git
a/sql/core/src/test/resources/sql-tests/results/nonansi/st-functions.sql.out
b/sql/core/src/test/resources/sql-tests/results/nonansi/st-functions.sql.out
index 71a7d6eaa5dc..95e7412bfea4 100644
--- a/sql/core/src/test/resources/sql-tests/results/nonansi/st-functions.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/nonansi/st-functions.sql.out
@@ -178,6 +178,15 @@ array<geography(any)>
array<geography(any)>
+-- !query
+SELECT typeof(array(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))
FROM geodata
+-- !query schema
+struct<typeof(array(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)))):string>
+-- !query output
+array<geometry(any)>
+array<geometry(any)>
+
+
-- !query
SELECT typeof(map('a', ST_GeogFromWKB(wkb), 'b',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY))) FROM geodata
-- !query schema
@@ -187,6 +196,15 @@ map<string,geography(any)>
map<string,geography(any)>
+-- !query
+SELECT typeof(map('a', ST_GeomFromWKB(wkb), 'b',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY))) FROM geodata
+-- !query schema
+struct<typeof(map(a, st_geomfromwkb(wkb), b, CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)))):string>
+-- !query output
+map<string,geometry(any)>
+map<string,geometry(any)>
+
+
-- !query
SELECT typeof(array(named_struct('g1', ST_GeogFromWKB(wkb), 'g2',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)), named_struct('g1',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY), 'g2', ST_GeogFromWKB(wkb)))) FROM geodata
-- !query schema
@@ -196,6 +214,15 @@ array<struct<g1:geography(any),g2:geography(any)>>
array<struct<g1:geography(any),g2:geography(any)>>
+-- !query
+SELECT typeof(array(named_struct('g1', ST_GeomFromWKB(wkb), 'g2',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)), named_struct('g1',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY), 'g2', ST_GeomFromWKB(wkb)))) FROM geodata
+-- !query schema
+struct<typeof(array(named_struct(g1, st_geomfromwkb(wkb), g2,
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))), named_struct(g1,
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY)), g2, st_geomfromwkb(wkb)))):string>
+-- !query output
+array<struct<g1:geometry(any),g2:geometry(any)>>
+array<struct<g1:geometry(any),g2:geometry(any)>>
+
+
-- !query
SELECT typeof(named_struct('a', array(ST_GeogFromWKB(wkb),
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)), 'b', map('g', ST_GeogFromWKB(wkb), 'h',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))) FROM geodata
-- !query schema
@@ -205,6 +232,15 @@
struct<a:array<geography(any)>,b:map<string,geography(any)>>
struct<a:array<geography(any)>,b:map<string,geography(any)>>
+-- !query
+SELECT typeof(named_struct('a', array(ST_GeomFromWKB(wkb),
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)), 'b', map('g', ST_GeomFromWKB(wkb), 'h',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))) FROM geodata
+-- !query schema
+struct<typeof(named_struct(a, array(st_geomfromwkb(wkb),
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))), b, map(g, st_geomfromwkb(wkb), h,
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))))):string>
+-- !query output
+struct<a:array<geometry(any)>,b:map<string,geometry(any)>>
+struct<a:array<geometry(any)>,b:map<string,geometry(any)>>
+
+
-- !query
SELECT typeof(nvl(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))
FROM geodata
-- !query schema
@@ -214,6 +250,15 @@ geography(any)
geography(any)
+-- !query
+SELECT typeof(nvl(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))
FROM geodata
+-- !query schema
+struct<typeof(nvl(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)))):string>
+-- !query output
+geometry(any)
+geometry(any)
+
+
-- !query
SELECT typeof(nvl2(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY),
ST_GeogFromWKB(wkb))) FROM geodata
-- !query schema
@@ -223,6 +268,15 @@ geography(any)
geography(any)
+-- !query
+SELECT typeof(nvl2(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY),
ST_GeomFromWKB(wkb))) FROM geodata
+-- !query schema
+struct<typeof(nvl2(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)), st_geomfromwkb(wkb))):string>
+-- !query output
+geometry(any)
+geometry(any)
+
+
-- !query
SELECT typeof(CASE WHEN wkb IS NOT NULL THEN
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY) ELSE ST_GeogFromWKB(wkb) END) FROM geodata
-- !query schema
@@ -232,6 +286,15 @@ geography(any)
geography(any)
+-- !query
+SELECT typeof(CASE WHEN wkb IS NOT NULL THEN
ST_GeomFromWKB(wkb)::GEOMETRY(ANY) ELSE ST_GeomFromWKB(wkb) END) FROM geodata
+-- !query schema
+struct<typeof(CASE WHEN (wkb IS NOT NULL) THEN CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)) ELSE st_geomfromwkb(wkb) END):string>
+-- !query output
+geometry(any)
+geometry(any)
+
+
-- !query
SELECT typeof(coalesce(ST_GeogFromWKB(wkb),
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY))) FROM geodata
-- !query schema
@@ -241,6 +304,15 @@ geography(any)
geography(any)
+-- !query
+SELECT typeof(coalesce(ST_GeomFromWKB(wkb),
ST_GeomFromWKB(wkb)::GEOMETRY(ANY))) FROM geodata
+-- !query schema
+struct<typeof(coalesce(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)))):string>
+-- !query output
+geometry(any)
+geometry(any)
+
+
-- !query
SELECT typeof(IF(wkb IS NOT NULL, ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY),
ST_GeogFromWKB(wkb))) FROM geodata
-- !query schema
@@ -250,6 +322,15 @@ geography(any)
geography(any)
+-- !query
+SELECT typeof(IF(wkb IS NOT NULL, ST_GeomFromWKB(wkb)::GEOMETRY(ANY),
ST_GeomFromWKB(wkb))) FROM geodata
+-- !query schema
+struct<typeof((IF((wkb IS NOT NULL), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)), st_geomfromwkb(wkb)))):string>
+-- !query output
+geometry(any)
+geometry(any)
+
+
-- !query
SELECT
hex(ST_AsBinary(ST_GeogFromWKB(X'0101000000000000000000f03f0000000000000040')))
AS result
-- !query schema
diff --git a/sql/core/src/test/resources/sql-tests/results/st-functions.sql.out
b/sql/core/src/test/resources/sql-tests/results/st-functions.sql.out
index 71a7d6eaa5dc..95e7412bfea4 100644
--- a/sql/core/src/test/resources/sql-tests/results/st-functions.sql.out
+++ b/sql/core/src/test/resources/sql-tests/results/st-functions.sql.out
@@ -178,6 +178,15 @@ array<geography(any)>
array<geography(any)>
+-- !query
+SELECT typeof(array(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))
FROM geodata
+-- !query schema
+struct<typeof(array(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)))):string>
+-- !query output
+array<geometry(any)>
+array<geometry(any)>
+
+
-- !query
SELECT typeof(map('a', ST_GeogFromWKB(wkb), 'b',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY))) FROM geodata
-- !query schema
@@ -187,6 +196,15 @@ map<string,geography(any)>
map<string,geography(any)>
+-- !query
+SELECT typeof(map('a', ST_GeomFromWKB(wkb), 'b',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY))) FROM geodata
+-- !query schema
+struct<typeof(map(a, st_geomfromwkb(wkb), b, CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)))):string>
+-- !query output
+map<string,geometry(any)>
+map<string,geometry(any)>
+
+
-- !query
SELECT typeof(array(named_struct('g1', ST_GeogFromWKB(wkb), 'g2',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)), named_struct('g1',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY), 'g2', ST_GeogFromWKB(wkb)))) FROM geodata
-- !query schema
@@ -196,6 +214,15 @@ array<struct<g1:geography(any),g2:geography(any)>>
array<struct<g1:geography(any),g2:geography(any)>>
+-- !query
+SELECT typeof(array(named_struct('g1', ST_GeomFromWKB(wkb), 'g2',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)), named_struct('g1',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY), 'g2', ST_GeomFromWKB(wkb)))) FROM geodata
+-- !query schema
+struct<typeof(array(named_struct(g1, st_geomfromwkb(wkb), g2,
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))), named_struct(g1,
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY)), g2, st_geomfromwkb(wkb)))):string>
+-- !query output
+array<struct<g1:geometry(any),g2:geometry(any)>>
+array<struct<g1:geometry(any),g2:geometry(any)>>
+
+
-- !query
SELECT typeof(named_struct('a', array(ST_GeogFromWKB(wkb),
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)), 'b', map('g', ST_GeogFromWKB(wkb), 'h',
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))) FROM geodata
-- !query schema
@@ -205,6 +232,15 @@
struct<a:array<geography(any)>,b:map<string,geography(any)>>
struct<a:array<geography(any)>,b:map<string,geography(any)>>
+-- !query
+SELECT typeof(named_struct('a', array(ST_GeomFromWKB(wkb),
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)), 'b', map('g', ST_GeomFromWKB(wkb), 'h',
ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))) FROM geodata
+-- !query schema
+struct<typeof(named_struct(a, array(st_geomfromwkb(wkb),
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))), b, map(g, st_geomfromwkb(wkb), h,
CAST(st_geomfromwkb(wkb) AS GEOMETRY(ANY))))):string>
+-- !query output
+struct<a:array<geometry(any)>,b:map<string,geometry(any)>>
+struct<a:array<geometry(any)>,b:map<string,geometry(any)>>
+
+
-- !query
SELECT typeof(nvl(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY)))
FROM geodata
-- !query schema
@@ -214,6 +250,15 @@ geography(any)
geography(any)
+-- !query
+SELECT typeof(nvl(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY)))
FROM geodata
+-- !query schema
+struct<typeof(nvl(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)))):string>
+-- !query output
+geometry(any)
+geometry(any)
+
+
-- !query
SELECT typeof(nvl2(ST_GeogFromWKB(wkb), ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY),
ST_GeogFromWKB(wkb))) FROM geodata
-- !query schema
@@ -223,6 +268,15 @@ geography(any)
geography(any)
+-- !query
+SELECT typeof(nvl2(ST_GeomFromWKB(wkb), ST_GeomFromWKB(wkb)::GEOMETRY(ANY),
ST_GeomFromWKB(wkb))) FROM geodata
+-- !query schema
+struct<typeof(nvl2(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)), st_geomfromwkb(wkb))):string>
+-- !query output
+geometry(any)
+geometry(any)
+
+
-- !query
SELECT typeof(CASE WHEN wkb IS NOT NULL THEN
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY) ELSE ST_GeogFromWKB(wkb) END) FROM geodata
-- !query schema
@@ -232,6 +286,15 @@ geography(any)
geography(any)
+-- !query
+SELECT typeof(CASE WHEN wkb IS NOT NULL THEN
ST_GeomFromWKB(wkb)::GEOMETRY(ANY) ELSE ST_GeomFromWKB(wkb) END) FROM geodata
+-- !query schema
+struct<typeof(CASE WHEN (wkb IS NOT NULL) THEN CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)) ELSE st_geomfromwkb(wkb) END):string>
+-- !query output
+geometry(any)
+geometry(any)
+
+
-- !query
SELECT typeof(coalesce(ST_GeogFromWKB(wkb),
ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY))) FROM geodata
-- !query schema
@@ -241,6 +304,15 @@ geography(any)
geography(any)
+-- !query
+SELECT typeof(coalesce(ST_GeomFromWKB(wkb),
ST_GeomFromWKB(wkb)::GEOMETRY(ANY))) FROM geodata
+-- !query schema
+struct<typeof(coalesce(st_geomfromwkb(wkb), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)))):string>
+-- !query output
+geometry(any)
+geometry(any)
+
+
-- !query
SELECT typeof(IF(wkb IS NOT NULL, ST_GeogFromWKB(wkb)::GEOGRAPHY(ANY),
ST_GeogFromWKB(wkb))) FROM geodata
-- !query schema
@@ -250,6 +322,15 @@ geography(any)
geography(any)
+-- !query
+SELECT typeof(IF(wkb IS NOT NULL, ST_GeomFromWKB(wkb)::GEOMETRY(ANY),
ST_GeomFromWKB(wkb))) FROM geodata
+-- !query schema
+struct<typeof((IF((wkb IS NOT NULL), CAST(st_geomfromwkb(wkb) AS
GEOMETRY(ANY)), st_geomfromwkb(wkb)))):string>
+-- !query output
+geometry(any)
+geometry(any)
+
+
-- !query
SELECT
hex(ST_AsBinary(ST_GeogFromWKB(X'0101000000000000000000f03f0000000000000040')))
AS result
-- !query schema
diff --git
a/sql/core/src/test/scala/org/apache/spark/sql/STExpressionsSuite.scala
b/sql/core/src/test/scala/org/apache/spark/sql/STExpressionsSuite.scala
index 6dd8f4347105..4f0d567fccc4 100644
--- a/sql/core/src/test/scala/org/apache/spark/sql/STExpressionsSuite.scala
+++ b/sql/core/src/test/scala/org/apache/spark/sql/STExpressionsSuite.scala
@@ -271,6 +271,152 @@ class STExpressionsSuite
}
}
+ test("CreateArray with GEOMETRY literals") {
+ // Test data: WKB representation of POINT(1 2).
+ val wkbString = "0101000000000000000000F03F0000000000000040"
+ // Test with literals, using geometries with different SRID values.
+ val geom1 = s"ST_GeomFromWKB(X'$wkbString')" // Literal with fixed SRID
(0).
+ val geometryType1 = GeometryType(0)
+ val geom2 = s"$geom1::GEOMETRY(ANY)" // Literal with mixed SRID (ANY).
+ val geometryType2 = GeometryType("ANY")
+ val geo = "hex(ST_AsBinary(g)), ST_Srid(g)"
+ val row = Row(wkbString, 0)
+
+ val testCases = Seq(
+ (s"array($geom1)", geometryType1, Seq(row)),
+ (s"array($geom2)", geometryType2, Seq(row)),
+ (s"array($geom1, $geom1)", geometryType1, Seq(row, row)),
+ (s"array($geom2, $geom2)", geometryType2, Seq(row, row)),
+ (s"array($geom1, $geom2)", mixedSridGeometryType, Seq(row, row)),
+ (s"array($geom2, $geom1)", mixedSridGeometryType, Seq(row, row))
+ )
+
+ for ((expr, expectedType, expectedRows) <- testCases) {
+ assertType(
+ s"SELECT $expr",
+ ArrayType(expectedType)
+ )
+ checkAnswer(
+ sql(s"WITH t AS (SELECT explode($expr) AS g) SELECT $geo FROM t"),
+ expectedRows
+ )
+ }
+ }
+
+ test("CreateArray with GEOMETRY columns") {
+ // Test data: WKB representation of POINT(1 2).
+ val wkbString = "0101000000000000000000F03F0000000000000040"
+ // Test with columns, using geometries with different SRID values.
+ val geom1 = "ST_GeomFromWKB(wkb)" // Column with fixed SRID (0).
+ val geometryType1 = GeometryType(0)
+ val geom2 = s"$geom1::GEOMETRY(ANY)" // Column with mixed SRID (ANY).
+ val geometryType2 = GeometryType("ANY")
+ val geo = "hex(ST_AsBinary(g)), ST_Srid(g)"
+ val row = Row(wkbString, 0)
+
+ val testCases = Seq(
+ (s"array($geom1)", geometryType1, Seq(row)),
+ (s"array($geom2)", geometryType2, Seq(row)),
+ (s"array($geom1, $geom1)", geometryType1, Seq(row, row)),
+ (s"array($geom2, $geom2)", geometryType2, Seq(row, row)),
+ (s"array($geom1, $geom2)", mixedSridGeometryType, Seq(row, row)),
+ (s"array($geom2, $geom1)", mixedSridGeometryType, Seq(row, row))
+ )
+
+ // Test with literal and column, using geometries with different SRID
values.
+ withTable("tbl") {
+ // Construct and populate the test table.
+ sql("CREATE TABLE tbl (wkb BINARY)")
+ sql(s"INSERT INTO tbl VALUES (X'$wkbString')")
+
+ for ((query, expectedType, expectedRows) <- testCases) {
+ assertType(
+ s"SELECT $query FROM tbl",
+ ArrayType(expectedType)
+ )
+ checkAnswer(
+ sql(s"WITH t AS (SELECT explode($query) AS g FROM tbl) SELECT $geo
FROM t"),
+ expectedRows
+ )
+ }
+ }
+ }
+
+ test("NVL with GEOMETRY literals") {
+ // Test data: WKB representation of POINT(1 2).
+ val wkbString = "0101000000000000000000F03F0000000000000040"
+ // Test with literals, using geometries with different SRID values.
+ val geom1 = s"ST_GeomFromWKB(X'$wkbString')" // Literal with fixed SRID
(0).
+ val geometryType1 = GeometryType(0)
+ val geom2 = s"$geom1::GEOMETRY(ANY)" // Literal with mixed SRID (ANY).
+ val geometryType2 = GeometryType("ANY")
+ val geo = "hex(ST_AsBinary(g)), ST_Srid(g)"
+ val row = Row(wkbString, 0)
+
+ val testCases = Seq(
+ (s"nvl(null, $geom1)", geometryType1, Seq(row)),
+ (s"nvl($geom1, null)", geometryType1, Seq(row)),
+ (s"nvl(null, $geom2)", geometryType2, Seq(row)),
+ (s"nvl($geom2, null)", geometryType2, Seq(row)),
+ (s"nvl($geom1, $geom1)", geometryType1, Seq(row)),
+ (s"nvl($geom2, $geom2)", geometryType2, Seq(row)),
+ (s"nvl($geom1, $geom2)", mixedSridGeometryType, Seq(row)),
+ (s"nvl($geom2, $geom1)", mixedSridGeometryType, Seq(row))
+ )
+
+ for ((expr, expectedType, expectedRows) <- testCases) {
+ assertType(
+ s"SELECT $expr",
+ expectedType
+ )
+ checkAnswer(
+ sql(s"WITH t AS (SELECT $expr AS g) SELECT $geo FROM t"),
+ expectedRows
+ )
+ }
+ }
+
+ test("NVL with GEOMETRY columns") {
+ // Test data: WKB representation of POINT(1 2).
+ val wkbString = "0101000000000000000000F03F0000000000000040"
+ // Test with columns, using geometries with different SRID values.
+ val geom1 = "ST_GeomFromWKB(wkb)" // Column with fixed SRID (0).
+ val geometryType1 = GeometryType(0)
+ val geom2 = s"$geom1::GEOMETRY(ANY)" // Column with mixed SRID (ANY).
+ val geometryType2 = GeometryType("ANY")
+ val geo = "hex(ST_AsBinary(g)), ST_Srid(g)"
+ val row = Row(wkbString, 0)
+
+ val testCases = Seq(
+ (s"nvl(null, $geom1)", geometryType1, Seq(row)),
+ (s"nvl($geom1, null)", geometryType1, Seq(row)),
+ (s"nvl(null, $geom2)", geometryType2, Seq(row)),
+ (s"nvl($geom2, null)", geometryType2, Seq(row)),
+ (s"nvl($geom1, $geom1)", geometryType1, Seq(row)),
+ (s"nvl($geom2, $geom2)", geometryType2, Seq(row)),
+ (s"nvl($geom1, $geom2)", mixedSridGeometryType, Seq(row)),
+ (s"nvl($geom2, $geom1)", mixedSridGeometryType, Seq(row))
+ )
+
+ // Test with literal and column, using geometries with different SRID
values.
+ withTable("tbl") {
+ // Construct and populate the test table.
+ sql("CREATE TABLE tbl (wkb BINARY)")
+ sql(s"INSERT INTO tbl VALUES (X'$wkbString')")
+
+ for ((query, expectedType, expectedRows) <- testCases) {
+ assertType(
+ s"SELECT $query FROM tbl",
+ expectedType
+ )
+ checkAnswer(
+ sql(s"WITH t AS (SELECT $query AS g FROM tbl) SELECT $geo FROM t"),
+ expectedRows
+ )
+ }
+ }
+ }
+
/** ST reader/writer expressions. */
test("ST_AsBinary") {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
