sp_refreshsqlmodule is failing with UNION, INTERSECT or EXCEPT operator must have an equal number of expressions error

Next time please provide a full sample to reproduce the scenario. It is highly important in the feedback system as well. I added it there and will do the same here before responding as I see it.

The short explanation is that this issue related to the HIDDEN columns in the system-versioned temporal tables.

For the discussion and for monitoring, we can reproduce the issue:

CREATE TABLE dbo.RonenA
(
  C1 int NOT NULL PRIMARY KEY CLUSTERED
  , C2 nvarchar(100) NOT NULL
  , [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 (HISTORY_TABLE = dbo.RonenAHistory));
GO

INSERT RonenA (C1,C2) values (1,'Ronen')
GO
INSERT RonenA (C1,C2) values (2,'Ariely')
GO
UPDATE RonenA SET C1= 3 where c2 = 'Ronen'
GO

Since the columns [ValidFrom] and [ValidTo] are HIDDEN, using SELECT * will not returns these columns in the original table RonenA, but the same query from the history table returns all columns.

Therefore the following query will return error:

SELECT * FROM RonenA
UNION
SELECT * FROM RonenAHistory
GO

The solution is to explicitly add the hidden columns in the select

SELECT * , [ValidFrom],[ValidTo] FROM RonenA
UNION
SELECT * FROM RonenAHistory
GO -- OK

Now, we can create SP from this query:

CREATE PROCEDURE RonenA_SP as
SELECT * , [ValidFrom],[ValidTo] FROM RonenA
UNION
SELECT * FROM RonenAHistory
GO

EXEC RonenA_SP
GO

All the above bahaves normal, but the issue is when we want to set the sp_refreshsqlmodule on the SP

exec sp_refreshsqlmodule N'RonenA_SP'
GO
-- ERROR: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

It seems like sp_refreshsqlmodule checks all the columns when we use "SELECT *" including the HIDDEN columns.

Therefore by using the query "SELECT * , [ValidFrom],[ValidTo] FROM VerHIDDEN" it actually "think" that we have 6 columns in the result set (four from the star and two from the explicit column names.

This lead to the error since the second query from the history table includes only four columns.

Bypass the issue: Do not use HIDDEN columns or simply use explicit columns names.

Is this a bug? YES!

in my opinion it is a bug, since the behaviour is not consistency with the idea of using the sp_refreshsqlmodule for cases we use implicit list of columns (using start *) and as such it does not support IDDEN columns in the system-versioned temporal tables