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

Question

This is asked in Stack overflow platform.

Link: https://stackoverflow.com/questions/75358643/sp-refreshsqlmodule-is-failing-with-union-intersect-or-except-operator-must-hav

I have written one stored procedure where I have added UNION like this:

SELECT *,SysStartTime, SysEndTime FROM dbo.FirstTable WHERE Id = @Id

UNION

SELECT * FROM history.FirstTable WHERE Id = @Id

where dbo.FirstTable is temporal table and history.FirstTable is it's history table.

If I write a query like:

exec sp_refreshsqlmodule N'USP_MySPName'

It fails with below error:

Msg 205, Level 16, State 1, Procedure sys.sp_refreshsqlmodule_internal, Line 85 [Batch Start Line 0]

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

However if I alter it, it doesn't show any error. And even while execution it doesn't show any error. Even if I execute the above query separately it works fine.

I tried searching for the cause of this error but I m not able to find any reference.

Can someone help me with the reason for this error.

This error can be fixed with manually specifying all the column names from both tables instead if using *

NOTE: I asked the same in feedback forum. Sharing the link. https://feedback.azure.com/d365community/idea/a5ccec71-e0b4-ed11-a81b-6045bd79fc6e

Answer 1

Hi,

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 hsitory 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