[TSQL] Failed to Parse File containing various statements

esauser · January 21, 2019, 9:20pm

I originally started posting these individually, but there is a limit on the number you can post in a single day. Thus, consolidating them into a single post.

We just stood up a POC for Developer edition and ran Sonar scanner against 5 of our repositories. In total, 5,646 files were scanned and 399 of them failed to parse, due to the errors below.

Versions

SonarQube Scanner: 3.2.0.1227
Java: 1.8.0_151
Linux: 4.15.0-1023-gcp
Server Version: 7.5.0.20543
SonarTSQL: 1.3.1.3067

Error

Unable to parse file … Parse error at …

OPEN SYMMETRIC KEY

The parse error occurs at the K in KEY
Related statements that we also need to make sure parse are DECRYPTION BY CERTIFICATE and DECRYPTBYKEY()

MERGE

The parse error occurs at the M in MERGE

SET TRAN ISOLATION LEVEL

The parse error occurs at the T in TRAN
There are various levels than can follow this statement, for example READ UNCOMMITTED

ROWS UNBOUNDED PRECEDING

The parse error occurs at the R in ROWS.
This is part of a PARTITION BY statement, and can have many values following ROWS

GRANT EXECUTE ON TYPE::

The parse error occurs at the first :.

CREATE LOGIN [##MS_SQLEnableSystemAssemblyLoadingUser##] FROM ASYMMETRIC KEY [MS_SQLEnableSystemAssemblyLoadingKey]

The parse error occurs at the [ following KEY

ALTER SERVER ROLE

The parse error occurs at the S in SERVER

WHERE CONTAINS()

The parse error occurs at the C in CONTAINS.
This is part of the FULLTEXT feature

IF (UPDATE ()

The parse error occurs at the U in UPDATE.
This is part of an AFTER UPDATE trigger.

CREATE FULLTEXT INDEX ON

The parse error occurs at the F in FULLTEXT

CREATE FULLTEXT CATALOG

The parse error occurs at the F in FULLTEXT

LEFT JOIN FREETEXTTABLE

The parse error occurs at the F in FREETEXTTABLE
This is part of the FULLTEXT feature

esauser · January 29, 2019, 2:58am

We have encountered some more…

SELECT DISTINCT

when the fields begin on the next line.
The parse error occurs at the SPACE between the two terms.

GROUP BY GROUPING SETS((field1), (field2), ...)

The parse error occurs at the first ( following SETS

RAISERROR(0,15,1) WITH LOG, SETERROR

The parse error occurs at the S in SETERROR

convert(varchar(20),convert(double precision,FieldName))

The parse error occurs at the d in double

inserted.field1 - ISNULL(deleted.field2,0.0)

as part of a MERGE.
The parse error occurs at the -

Join table1 on field1 = field2

as part of a USING in a MERGE.
The parse error occurs at the J in JOIN

OUTPUT Inserted.field1 AS 'alias1'

as part of an UPDATE.
The parse error occurs at the first '.

EXECUTE ( @sql ) AT LinkedServerName

The parse error occurs at the L in LinkedServerName

CREATE AGGREGATE

The parse error occurs at the A in AGGREGATE

LEFT JOIN  OPENQUERY(LinkedServerName, Query)

The parse error occurs at the O in OPENQUERY

PERCENTILE_CONT(.50) WITHIN GROUP (ORDER BY subtotal.Ratio) OVER (partition BY ...)

The parse error occurs the W in WITHIN

Alexandre_Gigleux · January 29, 2019, 7:26am

Hello Eric,

I’m not surprised by your feedback and actually it is expected. When we originally created SonarTSQL, we decided to focus only on the most used syntaxes in order to already provide value to our customers and then adjust. We are in this “adjust” phase and release after release, we cover more T-SQL syntaxes: https://jira.sonarsource.com/jira/secure/ReleaseNote.jspa?projectId=11531&version=14740

I won’t be able to get back to you today with a list a dev tickets and a date when we will be able to fix these parse errors as we need to digest all this feedback.

Can I count on you to play the beta-tester once we progress and scan once again your 5,646 files with a newer version of SonarTSQL?

Regards

esauser · January 29, 2019, 1:42pm

Absolutely, if I’m able to do so. We are on a trial/POC of the product, waiting for these issues to be addressed. If I can count on our trial/POC being extended while we work through the issues, you can count on me verifying for you. Our current license goes through 2/11/19. When do you reasonably expect to start having some of these ready for testing?

esauser · January 29, 2019, 1:56pm

Is there a way to generate a report or something that will show the line the parse error occurred on? I wanted to prioritize them, but my only options seem to be viewing them one at a time from the Track parse errors rule violation issues.

Also, do you need additional details? Most of them appear to be failing for every occurrence, but I did notice at least some MERGE statements, for example, that were successfully parsed.

Please let me know anything I can do to help expedite resolving these. We’ve now scanned a total of 21,085 files and are up to 874 parse errors.

Alexandre_Gigleux · January 30, 2019, 4:09pm

Hello Eric,

Reviewing your parse errors was not planned on our side and you can imagine we are already on something else, so I really can’t tell you when we will fix them.

Still, that doesn’t prevent you to move forward with your PoC as you can already scan 96% of your 21085 files and get valuable feedbacks on them.

Regards

czurn · February 1, 2019, 3:43pm

Hello @esauser ,

First, thanks again for taking the time to get back to us and report all these parsing errors. It is great feedback for us!
I’ve confirmed that most of the errors you mentioned are genuine and come from our parser.
All tickets created following this have been gathered here: MMF-1593

For some of the errors you mentioned, I was either not able to reproduce, or there seems to be a bit of context missing.

So:

Most MERGE statements should already be parsed correctly. In this example, I believe the parsing error comes from the previous statement: expecting the next token to be something other than MERGE. Could you share the previous statement in order to confirm this?

Here, the parser does not recognize the TRAN keyword: it is expecting the full TRANSACTION keyword instead. Looking at the official documentation, I don’t see mentioned that TRAN can be used instead of its full counterpart. Although, it is indeed the case for BEGIN TRANSACTION.
Testing directly on a SQL server, I saw that it actually accepts such syntax. Is this syntax commonly used for the SET statement?

I could not reproduce this error. I was able to parse the following snippet without any issue:

SELECT DISTINCT 
pp.col1,
 pp.col2
 , col3
 FROM myTable

Could you maybe share the surrounding lines (with previous statement) so as to understand what can be the problem?

I believe this is a genuine parsing issue: our parser does not allow character literals as aliases. The following statements however are accepted and parsed correctly:

UPDATE myTable SET col1 = 'value1' OUTPUT col1 AS [alias1]
UPDATE myTable SET col1 = 'value1' OUTPUT col1 AS "alias1"

I am not sure I understand from which syntax this part is from. Could you share the whole statement for this one?

Thank you.

Regards
-Chris

esauser · February 1, 2019, 8:11pm

They all seem to have a CTE immediately preceding them.

WITH [CTE_NAME] AS (LONG_COMPLICATED_QUERY)
MERGE

It is for us. We have hundreds of parse errors for this.

Does this mean you intend to fix it? Will you be fixing this one?

czurn:

I could not reproduce this error. I was able to parse the following snippet without any issue:
SELECT DISTINCT 
pp.col1,
 pp.col2
 , col3
 FROM myTable
Could you maybe share the surrounding lines (with previous statement) so as to understand what can be the problem?

IF (CONDITION)
BEGIN
   SELECT DISINCT
                 value = [COLUMN]
               , display = [COLUMN]

We have parse errors as both part of an UPDATE and a MERGE

UPDATE [TABLE]
SET [COLUMN] = [VALUE]
OUTPUT inserted.[COLUMN] - deleted.[COLUMN]

MERGE INTO [TABLE]
USING (COMPLICATED_SOURCE_QUERY)
ON [MATCHING_CONDITION]
WHEN MATCHED THEN 
     [MATCHED_LOGIC]
WHEN NOT MATCHED THEN
     [NOT_MATCHED_LOGIC]
OUTPUT inserted.[COLUMN] - deleted.[COLUMN]

esauser · February 1, 2019, 8:34pm

While reviewing all 874 parse errors for the reply above I noticed a few more that hadn’t been reported yet.

EXEC [LINKED_SERVER].[DATABASE].[SCHEMA].[STORED_PROCEDURE]

The parse error occurs at the space between EXEC and [

ALTER PROCEDURE [NAME]
AS

A_WHOLE_LOT_OF_COMMENTED_CODE

GO

The parse error occurs at the G in GO

DROP QUEUE [QUEUE_NAME]

The parse error occurs at the Q in the QUEUE outside the []

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[SPACE][SPACE]

The parse error occurs at the first [SPACE]. Every single stored procedure has this block at the top. One fails to parse. I’m wondering if maybe it’s due to the fact that the line has spaces instead of just being empty.

czurn · February 7, 2019, 2:28pm

Hello Eric,

Thanks a lot for your precisions and the additional information!

Indeed, there is an issue with MERGE statements in CTE => ticket for fix created

Yes, here’s the related ticket.

I still cannot reproduce issue for this one. Our parser does not have any problem with the following:

IF (CONDITION)
BEGIN
   SELECT DISTINCT
                 value = [COLUMN]
               , display = [COLUMN]
               FROM table1
END

However, I noticed that there is a typo in how DISTINCT is written in the snippet you provided: it is written as DISINCT. Is this typo inside your code or just in your posted message?

Thanks, this comes from the output clause that is incorrectly matched: → ticket created

EXEC statements seem to be parsed correctly. I think in this case the problem comes from the previous statement again. Can you please share it with us?

For this one, I don’t believe this is an error on our side:
if the whole body of the procedure is commented out and is therefore empty, then it’s normal to have a parsing error. Documentation shows there should be at least 1 statement in the body of the altered procedure.

→ ticket created

I could not reproduce this. I did not have any problem parsing the following:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

  CREATE PROCEDURE proc1
  AS
  BEGIN
  PRINT 'abc'
  END

Are you sure the characters on the line are space characters and not another special unicode character?

Regards,
-Chris

esauser · February 9, 2019, 4:05am

This was a typo on my part when I redacted. Here is the complete file, redacted.

/****** Object:  StoredProcedure [dbo].[STORED_PROCEDURE_NAME]    Script Date: CONSTANTDATE ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Project: PROJECT_NAME
-- =============================================
-- Author:		AUTHOR_NAME
-- Create date: 2016-07-25
-- Description: 
-- =============================================
--EXEC STORED_PROCEDURE_NAME 1
--EXEC STORED_PROCEDURE_NAME 2
CREATE PROCEDURE [dbo].[STORED_PROCEDURE_NAME]
	@source INT -- 1=VALUE1, 2=VALUE2
AS
    SET NOCOUNT ON	
IF @source = 1
BEGIN
	SELECT DISTINCT
			value = COLUMN1
			,display = COLUMN2
	FROM dbo.TABLE1 m
		INNER JOIN dbo.TABLE2 s ON s.COLUMN3 = m.COLUMN4
		INNER JOIN dbo.TABLE2 lm ON lm.COLUMN5 = s.COLUMN6
	WHERE lm.COLUMN7 = 1
		AND COLUMN8 = 1
		AND COLUMN9 = 2
        AND COLUMN10 IS NULL
    ORDER BY COLUMN11
END
ELSE
BEGIN
	SELECT DISTINCT
			value = COLUMN1
			,display = COLUMN2
	FROM dbo.TABLE1 m
		INNER JOIN dbo.TABLE2 s ON s.COLUMN3 = m.COLUMN4
		INNER JOIN dbo.TABLE2 lm ON lm.COLUMN5 = s.COLUMN6
	WHERE lm.COLUMN7 = 1
		AND COLUMN8 = 1
		AND COLUMN9 = 2
    ORDER BY COLUMN11
END	
GO

That’s reasonable. But note, that SQL does allow it, despite what the Documentation might say.

I think you’re on to something here. When I hexdump xxd I see there are two c2 characters here. I can’t find a reference to them anywhere, so I’m guessing they are garbage. They definitely aren’t the expected UTF-8.

It’s part of an INSERT EXEC, meaning the following immediately precedes it

DECLARE @TABLE_NAME TABLE(COLUMN INT)
INSERT INTO @TABLE_NAME ( COLUMN )
EXEC [LINKED_SERVER].[DATABASE].[SCHEMA].[STORED_PROCEDURE]

czurn · February 11, 2019, 2:45pm

Thank’s for your quick update on the matter.

Thank’s for sharing the whole file. In the end, I could parse the file without any issue on our side. I wonder if it’s the same issue as for the other space chartacters, that is, there are actually non-UTF-8 character in the file that appears as spaces. Could you check this?

It’s the same for this one too, I can parse the syntax on my side, so maybe it’s also a case of a strange character inside the file…

Indeed, testing locally I can confirm that sql server accepts such syntax. Thus, I created a ticket to take care of this case.

Regards,
-Chris

Alexandre_Gigleux · May 10, 2019, 10:35am

Hello @esauser,

SonarTSQL 1.4 is available for download and it covers all the feedback you provided.
Thanks again for having shared your parse errors!

Regards

esauser · May 10, 2019, 3:31pm

@Alexandre_Gigleux I loaded the new plugin. We had 14 TSQL projects scanned before, but only 7 of them are easy to rescan right now. Of those 7, they had 351 parse failures. With the new version, there are 61 parse failures, so we took care of 290. Here are the remaining:

SET TRAN ISOLATION LEVEL READ UNCOMMITTED;

It still doesn’t like the TRAN. This accounts for 58 of the 61 plus 6 of the 8 below.

(Contains(TABLE_ALIAS.FIELD_NAME,@PARAMETER_NAME)

as part of a WHERE clause. It doesn’t like the . between TABLE_ALIAS and FIELD_NAME. This is a new one. This accounts for 2 of the 61.

CREATE SPATIAL INDEX [INDEX_NAME] ON [SCHEMA_NAME].[TABLE_NAME]

This is a new one. It doesn’t like SPATIAL. This accounts for 1 of the 61.

esauser · May 10, 2019, 5:24pm

@Alexandre_Gigleux I was able to scan 4 more (the other 3 aren’t going to happen right now). These 4 has 152 parse failures and now only have 8.

LEFT JOIN  OPENQUERY([LINKED_SERVER_NAME],'LINKED SERVER QUERY') a ON a.FIELD1 = FIELD2

This is a new one. There are two spaces between JOIN and OPENQUERY. It fails to parse on the second one. This is just 1 of the 8.

EXEC [LINKED_SERVER_NAME].LINKED_SERVER_DATABASE..sp_executesql N'LINKED_SERVER_QUERY';

This is a new one. It fails to parse the space between EXEC and [. This is just 1 of the 8.

The other 6 are for the TRAN mentioned in my previous comment.

Alexandre_Gigleux · May 10, 2019, 6:57pm

I’m not sure if “SET TRAN” is really correct. If you look at the T-SQL documentation it says:

SET TRANSACTION ISOLATION LEVEL

I’m guessing that by chance it’s working on your SQL Server because the SQL Server parser is less strict than we are or it used to work on an old version of SQL Server but if I were you I would change “SET TRAN” by “SET TRANSACTION”.

esauser · May 10, 2019, 7:14pm

TRAN is the same as TRANSACTION in every other instance that I’m aware of, I would imagine that is the case here as well. Are you implying that this is not something you will be fixing and that we will have to update our code if we want to scan these files? Seems a bit odd to fail to parse perfectly valid SQL.

czurn · May 15, 2019, 9:37am

Hello @esauser,

Thank you for your feedback! I’m glad we could already reduce quite a number of parsing errors on your side
We see that we can still do some more improvements to remove the remaining ones!

Regarding the parsing error you reported:

Indeed, SQL server does accept the TRANS keyword as a synonym to TRANSACTION, I guess the fact it is not mentioned in the official documentation for the SET TRANSACTION LEVEL statement is a simple omission.
I created a SONARTSQL-226 to support it.

Indeed, we don’t support spatial index yet. Ticket for fix is here: SONARTSQL-227.

esauser:

(Contains(TABLE_ALIAS.FIELD_NAME,@PARAMETER_NAME)
as part of a WHERE clause. It doesn’t like the . between TABLE_ALIAS and FIELD_NAME . This is a new one. This accounts for 2 of the 61

=> Corresponding ticket SONARTSQL-228.

For theses 2 cases:

esauser:

LEFT JOIN  OPENQUERY([LINKED_SERVER_NAME],'LINKED SERVER QUERY') a ON a.FIELD1 = FIELD2
This is a new one. There are two spaces between JOIN and OPENQUERY . It fails to parse on the second one. This is just 1 of the 8.
EXEC [LINKED_SERVER_NAME].LINKED_SERVER_DATABASE..sp_executesql N'LINKED_SERVER_QUERY';
This is a new one. It fails to parse the space between EXEC and [ . This is just 1 of the 8.

I did not have any problem to parse such statements on our side.
I am wondering if it’s the same problem you had in some of your other files, that is, that there is a non-UTF8 character somewhere in the statement. Could you please check whether it is the case?
If not, could you please provide the previous statements where those error happens, as it might come from them.

Best,
-Chris

Alexandre_Gigleux · May 16, 2019, 6:15am

Hello @esauser,

In addition to what has been requested by @czurn and related to TRAN vs TRANSACTION, I would like to suggest one thing. Try to manually replace TRAN by TRANSACTION on all the files that we fail to parse. It’s just a one-off. This way, SonarTSQL will be able to parse the “TRANS” lines and continue … and probably identify some other syntaxes we don’t like.
With this, I want to be sure that once we implement SONARTSQL-226, no other surprise will come.

Thanks

Topic		Replies	Views
Parse error with T-SQL plugin in sonar scanner for SQL files SonarQube Server / Community Build tsql , sonarqube , parse-error	8	2698	July 8, 2019
TSQL: More parsing errors SonarQube Server / Community Build tsql , parse-error	3	1347	August 5, 2019
Sonar Scanner T-SQL unable to parse file SonarQube Server / Community Build	2	1240	October 4, 2018
Various TSQL Statements not parsing by SonarWay quality profile SonarQube Cloud tsql , sonarqube-cloud	1	800	November 19, 2019
[TSQL] Failed to Parse File containing MERGE SonarQube Server / Community Build	1	879	January 21, 2019

[TSQL] Failed to Parse File containing various statements

Related topics