microsoft
ScriptDom.TSql160Parser fails to parse simple T-SQL expression
- Microsoft.SqlServer.DacFx Version 161.6374.0
- Reproduced on both .NET 6.0 and .NET 7.0
- Windows 11
The Microsoft.SqlServer.TransactSql.ScriptDom.TSql160Parser fails to parse the following expression.
We noticed the issue on a much more complicated and realistic expression but we simplified the repro to the following minimal expression.
Repro:
using Microsoft.SqlServer.TransactSql.ScriptDom;
var parser = new TSql160Parser(true); //sql server 2022 parser
IList<ParseError> errors;
var expr = @"
(
SELECT 1
WHERE (IIF(1 > 0 AND 2 > 1, 1, 0)) = 1
)";
var res = parser.ParseExpression(new StringReader(expr), out errors);
if (errors?.Count > 0)
Console.WriteLine(errors.First().Message); //ERROR!!!! Incorrect syntax near ).
To prove that the given expression is definitely a valid one, you can simply execute the following statement in SSMS and see that SQL SERVER executes the query succesfully.
SELECT
(
SELECT 1
WHERE (IIF(1 > 0 AND 2 > 1, 1, 0)) = 1
)
@llali Could this be addressed with the release of the sql server 2022 parser?
Hi @clement911, this expression worked var expr = @" ( SELECT 1 WHERE IIF(1 > 0 AND 2 > 1, 1, 0) = 1 )";
That's right. The parser gets confused by extra wrapping parentheses so hopefully the code fix is not too hard?
Of course we wouldn't write such a simple expression manually but our actual expression is much more complicated. Also, sql is sometimes autogenerated or provided by a user, in which case we have no control over it.
@clement911 Apologies for the delay, would update you whether it would be included in the next release. Thank you
@SeenaAugusty we really look forward to it.
By the way, if you would open source the parser, we'd be happy to spend some time working on such issues
@SeenaAugusty this is great news that you will open source this project 😄
However, I was also told the same thing.... 2.5 years ago.
Could you share the (rough) time line of when you will be open sourcing this project?
@SeenaAugusty any news on the open sourcing? Bugs remain open for months or years so the only hope I see of them getting fixed is if you open source the code.
While the time it takes to bring ScriptDOM to open source may be frustrating, we'll keep those conversations primarily over at https://github.com/microsoft/DacFx/issues/101
@dzsquared could you give a quick update there? Are you still working towards open sourcing the ScriptDom?
@SeenaAugusty are you looking into this currently? If not, I can take a crack at this 😄
Just checking back on the status for this one. It strikes me as a very core issue with the parser failing to parse a simple expression. It would be great if this could be looked into.
Adding to this as I am having the exact same issue.
Microsoft.SqlServer.DacFx 162.2.111
Microsoft.SqlServer.TransactSql.ScriptDom 161.9109.0
Microsoft.SqlServer.TransactSql.ScriptDom.TSql160Parser Parse() is erroring on multiple parse attempts for valid SQL.
I am seeing this on a dacpac built with SQL Server 2022 target platform.
The following exceptions I'm seeing are:
antlr.MismatchedTokenException
antlr.NoViableAltForCharException
Interestingly, SQLPackage does not throw these errors.
My intention is to generate deployment script, where SQLPackage.exe /Action:Script will work and DacServices.GenerateDeployScript() does not.
Is there something fundamentally hard about fixing this bug? The parser fails to parse a very basic expression. It's been over two years. I see that the parser is being enhanced in various ways but this doesn't seem to get picked up. I'm just wondering if there is hard to fix or whether it just slipped through the cracks.