Querying a system-versioned temporal table with FOR system_time ALL appears to omit rows where the start and end times are equal.
For example:
/* Create a simple system-versioned table */
CREATE TABLE [dbo].[svt_test](
[string] [VARCHAR](30) NOT NULL,
[validFrom] [DATETIME2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[validTo] [DATETIME2](7) GENERATED ALWAYS AS ROW END NOT NULL,
CONSTRAINT [PK_svt_test] PRIMARY KEY CLUSTERED
(
[string] ASC
),
PERIOD FOR SYSTEM_TIME ([validFrom], [validTo])
)
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[svt_test_history] )
);
/*
Insert and immediately delete a row.
May have to try multiple times to get a row with the same start and end
timestamp.
*/
INSERT INTO dbo.svt_test (string) VALUES ('string');
DELETE dbo.svt_test WHERE string = 'string';
/* 0 rows */
SELECT * FROM dbo.svt_test;
/* 1 row */
SELECT * FROM dbo.svt_test_history;
/*
0 rows.
Should be the "union of rows that belong to the current and the history table"
per https://msdn.microsoft.com/en-us/library/dn935015.aspx
*/
SELECT * FROM dbo.svt_test FOR system_time ALL;
/*
CONTAINED IN does not find the row either, even though MSDN says the
interval is defined as SysStartTime >= start_date_time AND SysEndTime <=
end_date_time
*/
DECLARE @Start DATETIME2(7);
SELECT @Start = validFrom FROM [dbo].[svt_test_history] WHERE string = 'string';
DECLARE @End DATETIME2(7);
SELECT @End = validTo FROM [dbo].[svt_test_history] WHERE string = 'string';
SELECT * FROM dbo.svt_test FOR system_time CONTAINED IN (@Start, @End);
/* Clean up */
ALTER TABLE dbo.svt_test SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.svt_test;
DROP TABLE dbo.svt_test_history;
I've verified this on 2016 SP1 CU1 (12.0.4416) and 2016 CU3 (13.0.2186). I got the same result with datetime2(0) instead of datetime2(7).
Would anyone happen to know whether this is likely a bug or by design? Alternatively, perhaps I've misunderstood something?
