Cannot publish update to Graph Edges

[JerryNixon]

• SqlPackage or DacFx Version: Running 64-bit SqlPackage Version 16.0.5400.1 on .NET Framework

Steps to Reproduce:

1. In Visual Studio project: Create a SQL Graph Edge table with zero columns
2. Publish
3. Insert some data into the table
4. In Visual Studio project: Add an index to the Edge table (still zero columns)
5. Publish

The script attempts to move the contents of the table to a temp table. When it does this, there is a script error because the SELECT clause has zero columns (also does not have the $from_id, $to_id) and publish can NEVER complete.

Please note: This is not only blocking, this is a devastating error crippling our whole pipeline.

[arvindshmicrosoft]

Here's a more detailed reproduction,

Environment

• SQL Server 2019 and Microsoft Visual Studio Enterprise 2022
• VS 2019 17.1.4 and SQL Server Data Tools 17.0.62203.25080

Steps

1. Create a SQL Database Project with target type SQL 2019 / SQL MI
2. Add exactly one graph edge table to it with the T-SQL definition below:

create table isPartOf AS EDGE;
GO

CREATE UNIQUE CLUSTERED INDEX [GRAPH_FromTo_INDEX_isPartOf] on isPartOf ($from_id, $to_id) WITH (DATA_COMPRESSION = PAGE);
GO

3. Deploy the DACPAC once. This succeeds without any error, and the SQL Graph edge table isPartOf is created exactly as needed.

4. Now, change the definition of the table in the SQL Database project to the below:

   CREATE TABLE [dbo].[isPartOf] (
   INDEX [GRAPH_UNIQUE_INDEX_isPartOf] UNIQUE NONCLUSTERED ($edge_id) WITH (DATA_COMPRESSION = PAGE),
   INDEX [GRAPH_FromTo_INDEX_isPartOf] CLUSTERED ($from_id, $to_id) WITH (DATA_COMPRESSION = PAGE),
   INDEX [GRAPH_ToFrom_INDEX_isPartOf] NONCLUSTERED ($to_id, $from_id) WITH (DATA_COMPRESSION = PAGE)
   ) AS EDGE;

5. Try to publish the above changes to the same database as in step 3 above. It fails repeatedly for me with the error:

The write operation failed. You must first acquire write access from DataSchemaModelController.

6. As an attempt to workaround, do a Schema Compare from the SQL project to the same target DB. In the Schema Compare window, click on Generate Script. The generated script has the below form, with a clearly illegal INSERT ... SELECT statement:

CREATE TABLE [dbo].[tmp_ms_xx_isPartOf] (
    INDEX [GRAPH_UNIQUE_INDEX_isPartOf] UNIQUE NONCLUSTERED ($edge_id),
    INDEX [GRAPH_FromTo_INDEX_isPartOf] CLUSTERED ($from_id, $to_id),
    INDEX [GRAPH_ToFrom_INDEX_isPartOf] NONCLUSTERED ($to_id, $from_id)
) AS EDGE;

IF EXISTS (SELECT TOP 1 1 
           FROM   [dbo].[isPartOf])
    BEGIN
        INSERT INTO [dbo].[tmp_ms_xx_isPartOf]
        SELECT -- <<<<< note the lack of a column list
        FROM   [dbo].[isPartOf];
    END

DROP TABLE [dbo].[isPartOf];

EXECUTE sp_rename N'[dbo].[tmp_ms_xx_isPartOf]', N'isPartOf';

7. Ideally, the generated plan should have simple CREATE INDEX statements to add the 2 new indexes:
   • INDEX [GRAPH_UNIQUE_INDEX_isPartOf] UNIQUE NONCLUSTERED ($edge_id)
   • INDEX [GRAPH_ToFrom_INDEX_isPartOf] NONCLUSTERED ($to_id, $from_id)

It should not require dropping and recreate the original table. And at a minimum, it should be able to handle the fact that edge tables in SQL graph can be "empty" i.e. not have any user-defined columns.