I opened case 2604090010000730.
Below is a more through comparison of the geometric function in SQL Database, SQL Analytics Endpoint, and Fabric Warehouse (all US East Region). The script below contains comments and results for statements run in the three different tools...
/******** All Queries Run in US East Region ********/
--Warehouse: Query below runs and returns expected restult
--SQL Database: Query below runs and returns expected restult
--SQL Analytics Endpoint: Query Error - Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
select geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0)
--Fabric Warehouse: Query runs and returns expected restult (US East)
--SQL Database: Query runs and returns expected restult (US East)
--SQL Analytits Enpoint: Query runs and retuns expected result (US East)
with cte as (
select 1 as [LineNo], geometry::STGeomFromText('LINESTRING (100 0, 200 0)', 0) as [Line1_Geom]
union all select 1 as [LineNo], geometry::STGeomFromText('LINESTRING (150 0, 250 0)', 0) as [Line1_Geom]
union all select 2 as [LineNo], geometry::STGeomFromText('LINESTRING (300 0, 450 0)', 0) as [Line1_Geom]
union all select 2 as [LineNo], geometry::STGeomFromText('LINESTRING (3000 0, 6500 0)', 0) as [Line1_Geom]
union all select 2 as [LineNo], geometry::STGeomFromText('LINESTRING (2000 0, 5000 0)', 0) as [Line1_Geom]
)
select [LineNo]
,geometry::UnionAggregate([Line1_Geom]).STLength()
,count(*)
from cte
group by [LineNo]
--Save sample data in WKT format to SQL Datbase and Warehouse
--SQL Database: Save WKT to table
--Warehouse: Save WKT to table
with cte as (
select 1 as [LineNo], 'LINESTRING (100 0, 200 0)' as [Line1_WKT]
union all select 1 as [LineNo], 'LINESTRING (150 0, 250 0)' as [Line1_WKT]
union all select 2 as [LineNo], 'LINESTRING (300 0, 450 0)' as [Line1_WKT]
union all select 2 as [LineNo], 'LINESTRING (3000 0, 6500 0)' as [Line1_WKT]
union all select 2 as [LineNo], 'LINESTRING (2000 0, 5000 0)' as [Line1_WKT]
)
select [LineNo], [Line1_WKT]
into dbo.testline1
from cte
--Confirm data in table
select * from dbo.testline1
--Warehouse: Error 8676, Level 16, State 21, Line 1 - Invalid Plan (US East)
--SQL Database: Query runs and returns expected restult (US East)
--SQL Analytics Endpoint: Error 8676, Level 16, State 21, Line 1 - Invalid Plan (US East)
with cte as (
select t.[LineNo]
,geometry::STGeomFromText(t.[Line1_WKT], 0) as [Line1_Geom]
from dbo.testline1 t
)
select cte.[LineNo]
,geometry::UnionAggregate(cte.[Line1_Geom]).STLength()
,count(*)
from cte
group by [LineNo]
