Parse Error in sql File for Temporal Table

SonarQube Server / Community Build
, , ,
1

Hi!

I think there is a Bug in the Parser for SQL Server Temporal Tables.

WARN: Unable to parse file '/R:/CICD/_W/File.sql' : Parse error at line 10 column 122:

 1: CREATE TABLE [ODS].[FA_CounterpartyMapping_Exclusions_HISTORY]
 2: (
 3:  [Kundennummer] BIGINT NOT NULL PRIMARY KEY,
 4:  [PartyShortname] VARCHAR(50) NULL,
 5:  [Description] VARCHAR(250) NULL,
 6:  [SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '0001-01-01 00:00:00.0000000'),
 7:  [SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
 8:  PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
 9: )
10: WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[ODS].[FA_CounterpartyMapping_Exclusions_HISTORY], DATA_CONSISTENCY_CHECK=ON, HISTORY_RETENTION_PERIOD = 10 YEARS));

image
image940×109 6.13 KB

SQL Server Dokumentation for Temporal Tables and HISTORY_RETENTION_PERIOD

AzureDevOps Server Pipeline with Sonar Extension 6.2.0
Sonar Enterprise Edition Version 10.4.1 (build 88267)

THX

2

Hey there.

Can you please share this file in full?

3

Hi
Thats the hole File. Its only the table.

CREATE TABLE [ODS].[FA_CounterpartyMapping_Exclusions]
(
	[Kundennummer] BIGINT NOT NULL PRIMARY KEY,
	[PartyShortname] VARCHAR(50) NULL,
	[Description] VARCHAR(250) NULL,
	[SysStart] DATETIME2 (7) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '0001-01-01 00:00:00.0000000'),
	[SysEnd] DATETIME2 (7) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
	PERIOD FOR SYSTEM_TIME ([SysStart], [SysEnd])
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE=[ODS].[FA_CounterpartyMapping_Exclusions_HISTORY], DATA_CONSISTENCY_CHECK=ON, HISTORY_RETENTION_PERIOD = 10 YEARS));
6

Hello @ArminPrieschl,

Thank you for reporting this issue!

I was able to reproduce it and I have created a ticket to fix it:

https://sonarsource.atlassian.net/browse/SONARTSQL-337

Have a nice day.

10

We have the same issue appearing in our logs for Sonar Cloud and v2.3.2 of the AzureDevOps Sonar extension.

I cannot see the JIRA ticket. Has this been resolved? Does a new ticket need raising for SonarCloud?

11

Hey @OliB

I’m not sure why that ticket isn’t accessible unless you’re logged in (which only SonarSourcers can be). It was surely a public project before. I’ll flag this for attention.

In any case, the ticket is still open.

14

I’m sorry to inform you that the TSQL and PL/SQL projects have been changed to private. This was intentional and is not planned to change.
The ticket in question has not been resolved yet.

16

The newest version of the TSQL analyzer fixes the parsing of HISTORY_RETENTION_PERIOD. It will soon be available on SonarQube.