Azure / Azure/data-api-builder
[Bug]: Table with a geometry column fails schema discovery ("GetFieldType returned null") even when fields.include excludes it
- Dominant language
- C#
- Stars
- 1.5k
- Forks
- 370
- Avg merge
- 3d 17h
- Merged PRs (30d)
- 8
Description
### What happened?
# Database: SQL Server 2017 on-premises
An entity backed by a table that has a `geometry` column cannot be started, even when the
permissions of that entity list every readable field explicitly and the spatial column is not
among them.
Expected: since no role can read the spatial column, the engine ignores it and exposes the
remaining columns.
Actual: startup fails while reading the metadata for the entity, and the entity is unusable.
Removing the spatial column from the table (or pointing the entity at a view that does not
select it) is the only way to get the entity to load.
### Repro
Table:
```sql
create table dbo.Geom
(
Id int identity(1,1) not null primary key,
Name nvarchar(100) not null,
Location geometry null
);
```
Entity, with every readable field listed and `Location` deliberately left out:
```json
"Geom": {
"source": { "object": "dbo.Geom", "type": "table" },
"permissions": [
{
"role": "anonymous",
"actions": [
{
"action": "read",
"fields": { "include": [ "Id", "Name" ] }
}
]
}
]
}
```
Start the engine against that config.
### Error
```
Cannot obtain Schema for entity Point with underlying database object source: dbo.Points
due to: DataReader.GetFieldType(2) returned null.
```
### Root cause
`SqlMetadataProvider.FillSchemaForTableAsync` reads the shape of the object with
`SELECT * FROM .` and hands it to `DbDataAdapter.FillSchema`. Building the
schema mapping requires a CLR type for every column in the projection, and for the spatial
column the reader returns none, so the adapter throws.
Because this happens during metadata discovery, it runs before any authorization is
considered, so `fields.include` and `fields.exclude` have no effect on it.
Even if a CLR type were resolved, the column would not get much further:
`TypeHelper.GetDbTypeFromSystemType`, `TypeHelper.GetEdmPrimitiveTypeFromSystemType` and the
GraphQL schema converter have no mapping for spatial types. The engine already treats these
types as unsupported elsewhere — autoentity discovery in `MsSqlQueryBuilder` skips objects
whose columns include `geography`, `geometry`, `hierarchyid`, `sql_variant`, `xml`,
`rowversion` or `vector`.
The practical consequence is that a single spatial column makes the whole table
unreachable through DAB, with no configuration-side workaround.
### Version
Microsoft.DataApiBuilder 2.0.12+0b38aa7cbf4118034ad8dee1f2712b1c4bac4c32
### What database are you using?
Azure SQL
### What hosting model are you using?
Local (including CLI)
### Which API approach are you accessing DAB through?
MCP, GraphQL, REST
### Relevant log output
```Text
fail: Azure.DataApiBuilder.Service.Startup[0]
Unable to complete runtime initialization. Refer to exception for error details.
Azure.DataApiBuilder.Service.Exceptions.DataApiBuilderException: Cannot obtain Schema for entity Geom with underlying database object source: dbo.Geom due to: DataReader.GetFieldType(2) returned null.
---> System.InvalidOperationException: DataReader.GetFieldType(2) returned null.
at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.FillSchemaFromReader(DataSet dataset, DataTable datatable, SchemaType schemaType, String srcTable, IDataReader dataReader)
at System.Data.Common.DataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, String srcTable, IDataReader dataReader)
at System.Data.Common.DbDataAdapter.FillSchemaInternal(DataSet dataset, DataTable datatable, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, IDbCommand command, String srcTable, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillSchema(DataSet dataSet, SchemaType schemaType, String srcTable)
at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.FillSchemaForTableAsync(String schemaName, String tableName) in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 1740
at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.GetTableWithSchemaFromDataSetAsync(String entityName, String schemaName, String tableName) in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 1619
--- End of inner exception stack trace ---
at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.HandleOrRecordException(Exception e) in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 107
at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntity(String entityName, Entity entity) in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 1265
at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.PopulateObjectDefinitionForEntities() in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 1144
at Azure.DataApiBuilder.Core.Services.SqlMetadataProvider`3.InitializeAsync() in /_/src/Core/Services/MetadataProviders/SqlMetadataProvider.cs:line 349
at Azure.DataApiBuilder.Core.Services.MetadataProviders.MetadataProviderFactory.InitializeAsync() in /_/src/Core/Services/MetadataProviders/MetadataProviderFactory.cs:line 95
at Azure.DataApiBuilder.Service.Startup.PerformOnConfigChangeAsync(IApplicationBuilder app) in /_/src/Service/Startup.cs:line 1301
```
### Code of Conduct
- [x] I agree to follow this project's Code of Conduct
Contributor guide
Assessment
This issue has not been assessed yet.