New version of sqlpackage.exe (162.5.57.1) generate bad deployment plan on publish dacpac

Hi,

I have problem with newest sqlpackage module (ver: 162.5.57.1) with publish process of dacpac on existing database (which was created by previous version of dacpac).

Publish dacpac fails when the same dacpac with older version of sqlpackage is working properly. Older version is 15.0.4538.1

(C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\150)

Details about problematic part of database project:

1. CLR module: Assembly [Infrastructure]: with function fns_LatLngsParse
2. Scalar function fns_LatlngsToGeography with schemabinding returns geography type which has execution of CLR fns_LatLngsParse function + some aditional logic

forbidden_area table with columns:

• [latlngs] varchar(MAX)
• [BorderWidth] INT
• [latlngsGeography] AS [dbo].[fns_LatlngsToGeography] ( latlngs, BorderWidth ) PERSISTED,

• index: SPATIAL INDEX six_forbidden_area_latlngsGeography ON dbo.forbidden_area(latlngsGeography)

log from sqlpackage (15.0.4538.1) publish dacpac:

Dropping [dbo].[forbidden_area].[six_forbidden_area_latlngsGeography]...

Unbinding columns from changing objects on table [dbo].[forbidden_area]...

Removing schema binding from [dbo].[fns_LatlngsToGeography]...

Dropping [dbo].[fns_LatLngsParse]...

Dropping [Infrastructure]...

Creating [Infrastructure]...

Creating [dbo].[fns_LatLngsParse]...

Adding schema binding to [dbo].[fns_LatlngsToGeography]...

Starting rebuilding table [dbo].[forbidden_area]...

Everything is ok.

log from sqlpackage (162.5.57.1) publish dacpac:

Dropping Spatial Index [dbo].[forbidden_area].[six_forbidden_area_latlngsGeography]...

Unbinding columns from changing objects on table [dbo].[forbidden_area]...

Removing schema binding from [dbo].[fns_LatlngsToGeography]...

Dropping Function [dbo].[fns_LatLngsParse]...

Dropping Assembly [Infrastructure]...

Creating Assembly [Infrastructure]...

Starting rebuilding table [dbo].[forbidden_area]...

An error occurred while the batch was being executed.

Could not deploy package. Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 4936, Level 16, State 1, Line 7 Computed column 'latlngsGeography' in table 'tmp_ms_xx_forbidden_area' cannot be persisted because the column is non-deterministic.

We can see, that schemabinding was removed to alter all objects which use fns_LatlngsToGeography, but before rebuilding forbidden_area table the fns_LatlngsToGeography function is not altered to original definition (with schemabinding) when older sqlpackage handles this situation correctly. We can recognize also that fns_LatLngsParse has been not recreated.

When I try execute second time (after fail on first attempt) publishing of dacpac by sqlpackage (162.5.57.1) i can see:

Creating Function [dbo].[fns_LatLngsParse]...

Altering Function [dbo].[fns_LatlngsToGeography]...

Starting rebuilding table [dbo].[forbidden_area]...

Creating Spatial Index [dbo].[forbidden_area].[six_forbidden_area_latlngsGeography]...

Publish dacpac finished succesfully because fns_LatlngsToGeography was changed by previous execution (remove schemabinding) and now was recognized as different definition which should be altered before rebuild forbidden_area table. (fns_LatLngsParse has been created as well).

Best regards,

Tomasz