SQL Subquery using EXCEPT between temporal and non-temporal table giving number of expressions error

Question

The Problem

I need to find and remove all of the equal rows from two tables who's schemas may often change. In this case, equal meaning all values in the rows are equal to each other, for example:

ID	Message	Date	=	ID	Message	Date
1	'Hello'	1/1/1999	==	1	'Hello'	1/1/1999
2	'Hello'	1/1/1999	!=	2	'Goodbye'	1/1/1999
3	'Goodbye'	null	==	3	'Goodbye'	null

I would like to do so utilizing the EXCEPT keyword, because it does not require me to specify the equality condition for each column and update the query when it has non-key column names changes. I am aware that using wildcards in selects is a bad code smell, however both of these tables will always have the same non-hidden columns, and writing the procedure this way will make the code much easier to maintain.

A possibly important note, one of the tables Table1 is a Temporal table, and Table2 is not. However, the history for Table1 is stored in a separate table, and the queries are not utilizing SYSTEM_TIME (ie, querying current data only).

What I've tried

Table1 is created with the following script:

CREATE TABLE Table1 (
  ID INT NOT NULL PRIMARY KEY
  , ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
  , ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON)

Table2 is created with the following script:

CREATE TABLE Table2 (
  ID INT NOT NULL PRIMARY KEY
)

What I have been trying is the following, which I think should be deleting all entries from Table1 for which an exact match across all columns exists in Table2:

DELETE FROM [Table1]
  WHERE [Table1].[ID] NOT IN (
    SELECT ID FROM (
      SELECT * FROM [Table1]
      EXCEPT
      SELECT * FROM [Table2]
    ) AS INNER_QUERY
  )

However, the SQL Server gives the following unexpected error:

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Yet, the following query works as expected with no issues

SELECT * FROM [Table1]
  WHERE [Table1].[ID] NOT IN (
    SELECT ID FROM (
      SELECT * FROM [Table1]
      EXCEPT
      SELECT * FROM [Table2]
    ) AS INNER_QUERY
  )

Answer 1

This is clearly a bug.

The "hidden columns" abstraction is sometimes a bit leaky (other example).

One workaround would be to just break it up into two steps

SELECT ID
INTO   #Ids
FROM   (SELECT *
        FROM   [Table1]
        EXCEPT
        SELECT *
        FROM   [Table2]) AS INNER_QUERY

DELETE FROM [Table1]
WHERE  [Table1].[ID] NOT IN (SELECT ID
                             FROM   #Ids) 

I'm unclear about the circumstances where the Bug manifests itself.

When the exception is thrown for the DELETE it is during algebrization and the call stack looks as follows

When doing the SELECT ... INTO and putting a break point on CRelOp_IntersectExcept::PexprBindSelf the call stack looks as follows (with no error thrown). Perhaps CRelOp_SelectQuery::BindTree has some extra logic for these hidden columns (?!).

Answer 2

As mentioned this appears to be a bug.

I understand you don't want to have to specify each column. But you can instead use dynamic SQL, which avoids the overhead of writing the IDs to a temp table each time.

DECLARE @cols nvarchar(max) = (
    SELECT STRING_AGG('t.' + QUOTENAME(c1.name), ', ')
    FROM sys.tables t1
    JOIN sys.columns c1 ON t1.object_id = c1.object_id
    JOIN sys.tables t2
      ON t2.name = 'Table2'
    JOIN sys.columns c2 ON t2.object_id = c2.object_id
      AND c2.name = c1.name
    WHERE t1.name = 'Table1'
      AND c1.is_hidden = 0
      AND c2.is_hidden = 0
);

DECLARE @sql nvarchar(max) = '
DELETE t
FROM Table1 t
WHERE NOT EXISTS (
    SELECT ' + @cols + '
    EXCEPT
    SELECT ' + @cols + '
    FROM Table2 t
);
';

PRINT @sql;  -- your friend

EXEC sp_executesql @sql;

db<>fiddle

Note that I've not used a FROM in the first half of the subquery, this avoids a second scan of the table.