0

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.

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

Shardul
  • 309
  • 1
  • 3
  • 17
  • `exec sp_refreshsqlmodule N'USP_MySPName'` Are you executing this query in the same database where stored procedure is stored? – Aswin Feb 06 '23 at 08:21
  • yes..I m executing it in same database. Otherwise it will throw stored proc not found error. – Shardul Feb 06 '23 at 10:11
  • I presume the `SysStartTime` and `SysEndTime` columns are marked as hidden in the table? Perhaps it doesn't deal with that correctly. But the solution is surely to not use `*` as that is bad practice anyway. – Martin Smith Feb 06 '23 at 10:38
  • Do you really need to `UNION` these tables yourself anyway? vs using `FOR SYSTEM_TIME ALL`? – Martin Smith Feb 06 '23 at 10:47
  • @MartinSmith I don't know actually. ````FOR SYSTEM_TIME ALL```` is returning fewer rows than present in history table for a parameter value. – Shardul Feb 06 '23 at 13:55
  • yeah from what I remember `FOR SYSTEM_TIME ALL` won't return rows where the validity timespan was zero (as same row written to multiple times in the same tran) - do you really need those? – Martin Smith Feb 06 '23 at 14:12
  • @MartinSmith No I don't need those but that's not my actual issue here. – Shardul Feb 06 '23 at 16:05
  • Well if `FOR SYSTEM_TIME ALL` does what you need and doesn't reproduce the issue then you are done! – Martin Smith Feb 06 '23 at 16:42
  • 1
    And looks like that does solve the issue. https://dbfiddle.uk/69hIkZvaz `FOR SYSTEM_TIME ALL` does a `UNION ALL` on the history table already. It has an additional predicate on `WHERE FooHistory.ValidFrom<> FooHistory.ValidTo` but if you don't need these zero validity rows anyway why is this a problem? – Martin Smith Feb 07 '23 at 00:25
  • @MartinSmith I agree resolves the issue. But my actual question is different. I want to know why the other syntax doesn't work. It does not throw any compile time or run time error, but it throws error in sprefreshmodule. – Shardul Feb 07 '23 at 17:02
  • Because its a bug in the product. There isn't going to be any good reason for it. Just an edge case overlooked when they introduced `HIDDEN` columns. So you need to work around it and report it to Microsoft (not Stackoverflow) if you want it fixed – Martin Smith Feb 07 '23 at 17:11
  • And please don’t report it as a documentation issue. You must open a proper support case. – Randolph West Feb 23 '23 at 17:18

1 Answers1

0

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

Ronen Ariely
  • 2,336
  • 12
  • 21