[TSQL] Failed to Parse File containing various statements

(Eric Sauser) #1

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

[TSQL] Failed to Parse File containing SET TRAN ISOLATION LEVEL READ UNCOMMITTED;
[TSQL] Failed to Parse File containing MERGE
(Eric Sauser) #3

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) #4

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

(Eric Sauser) #5

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?

(Eric Sauser) #6

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) #7

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

(Christophe Zurn) #8

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

(Eric Sauser) #9

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?

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]
(Eric Sauser) #10

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.

(Christophe Zurn) #12

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

(Eric Sauser) #13

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]
(Christophe Zurn) #14

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