Azure SQL Database has a bug with View on Node table

[avorobjovs]

Describe the bug

1. We use the Azure SQL Database.
2. We have created the Graph Database.
3. We have created Node tables in our Graph Database.
4. To insert/update data in our Node tables, we use Updatable Views with the INSTEAD OF INSERT triggers.

It means that when we insert data to this View, the INSTEAD OF INSERT trigger checks this data and inserts a new row to the Node table or updates an existing one. In our Updatable Views, we select only those columns from the source Node tables that we need and can update. Previously, it worked fine.

But unexpectedly (we noticed it on November 12), some new behavior of the Azure SQL Database happens.

If we create a new Updatable View or alter the existing one, to starts automatically expose additional, not selected, unwanted columns from the source Node tables: graphid and objid.

And this behavior completely breaks our solution. Now, when we try to insert data in our Updatable Views, we have the error: "Column name or number of supplied values does not match table definition".

It is because there are two more columns (graphid and objid) in our Updatable Views. And the View requires data for them. But we cannot provide such data because they are Node table's system columns. And we didn't modify our View's code, we didn't add these columns, we don't want to use them anyhow. These columns appear automatically and we don't know how to say the Azure SQL Database to not use them in Views.

To Reproduce

1. Create a new database in the Azure SQL Database.
2. Create a new Node table. For example,

   CREATE TABLE [graph].[user] (
   [Id] [int] IDENTITY(1,1) NOT NULL,
   [Login] [nvarchar](50) NOT NULL,
   [FirstName] [nvarchar](50) NULL,
   [LastName] [nvarchar](50) NULL,
   PRIMARY KEY CLUSTERED ([Id] ASC),
   INDEX [IX_S_USER] UNIQUE NONCLUSTERED ($node_id)
   ) AS NODE
   GO

3. Create a new Updatable View with the INSTEAD OF INSERT trigger for this table. For example,

   
   CREATE VIEW [graph].[in_user] AS
   SELECT [Login]
         ,[FirstName]
         ,[LastName]
   FROM [graph].[user]
   GO

CREATE TRIGGER [graph].[TG_IN_USER] ON [graph].[in_user] INSTEAD OF INSERT AS BEGIN

MERGE INTO [graph].[user] as target
USING inserted as source
ON (target.[Login] = source.[Login])
WHEN MATCHED THEN
    UPDATE SET [Login] = source.[Login]
              ,[FirstName] = source.[FirstName]
              ,[LastName] = source.[LastName]
WHEN NOT MATCHED THEN
    INSERT ([Login]
           ,[FirstName]
           ,[LastName]
    )
    VALUES (source.[Login]
           ,source.[FirstName]
           ,source.[LastName]
    );

END; GO

4. Try to insert data to the View. For example,

INSERT INTO [graph].[in_user] VALUES ('BOB', 'Robert', 'Green') GO

And, as a result, we have an error:
**"Column name or number of supplied values does not match table definition"**.

**Expected behavior**
All provided data should be successfully processed by the INSTEAD OF INSERT trigger and a new row should be added to the  [graph].[user] Node table.

**Screenshots**
1. Select from the table with some existing data
![user_table](https://user-images.githubusercontent.com/7294773/99155360-e48d3300-26bf-11eb-89d7-d4f59228092a.png)

2. Select from the view
![in_user_view](https://user-images.githubusercontent.com/7294773/99155362-e820ba00-26bf-11eb-8346-ebc9fce74afd.png)

3. An error that is shown on insert data to the View
![insert_error](https://user-images.githubusercontent.com/7294773/99155235-cffc6b00-26be-11eb-961e-58ca51b7072b.png)

4. Two hidden unexpectedly appeared columns in the View
![view_columns](https://user-images.githubusercontent.com/7294773/99155237-d2f75b80-26be-11eb-932f-7e9473cbb8c1.png)

**Additional context**
This issue happens only with a View on a Node table. It doesn't happen with a View on a normal relational table.

[welcome[bot]]

Thank you for creating the issue! One of our team members will get back to you shortly with additional information. If this is a product issue, please close this and contact the particular product's support instead (see https://support.microsoft.com/allproducts for the list of support websites).

[avorobjovs]

Currently, we have two workarounds.

1. The first workaround You need to explicitly specify columns that you insert to the View.

2. The second workaround As we realized, this issue happens only with a View on only one table. If the View is on several tables or data sources, this issue doesn't happen. So, the second workaround is to add a second fake table to the Updatable View. For example,

CREATE VIEW [graph].[in_user] AS
    SELECT [Login]
          ,[FirstName]
          ,[LastName]
    FROM [graph].[user], (SELECT 0 as fix_col) fix_tbl
GO

In this case, these two Node system columns (graphid and objid) don't appear in the View.

And it allows successfully inserting data to the View.