microsoft / SqlScriptDOM

ScriptDOM/SqlDOM is a .NET library for parsing T-SQL statements and interacting with its abstract syntax tree
MIT License
128 stars 13 forks source link

Sql150ScriptGenerator.GenerateScript ignores WHERE statement #79

Open krylatij opened 3 months ago

krylatij commented 3 months ago

I try to format my sql script using GenerateScript method but WHERE clause is missing from its output. Nuget version Microsoft.SqlServer.TransactSql.ScriptDom 161.9109.0. Under debug I see parsed WHERE statement.

input and output:

ORIGINAL:
SELECT
        x2_0.KeyPowerPlantUnit
   ,x3_0.KeyEnergyPlantTypeParent
   ,x2_0.KeyEnergyPlantType
   ,x2_0.MostRecentSequence
FROM SNLEdit_new.dbo.PowerPlantUnitFuel x2_0
LEFT JOIN Lookup.dbo.EnergyPlantType x3_0
        ON x2_0.KeyEnergyPlantType = x3_0.KeyEnergyPlantType
                AND x3_0.updoperation < 2
WHERE x2_0.MostRecentSequence IN (1, 2, 3, 4, 5, 6);

FORMATTED:
SELECT x2_0.KeyPowerPlantUnit,
       x3_0.KeyEnergyPlantTypeParent,
       x2_0.KeyEnergyPlantType,
       x2_0.MostRecentSequence
FROM SNLEdit_new.dbo.PowerPlantUnitFuel AS x2_0
     LEFT OUTER JOIN
     Lookup.dbo.EnergyPlantType AS x3_0
     ON x2_0.KeyEnergyPlantType = x3_0.KeyEnergyPlantType
        AND x3_0.updoperation < 2

Code to reproduce:

using Microsoft.SqlServer.TransactSql.ScriptDom;
using System.Diagnostics;

var sql = @"
SELECT
    x2_0.KeyPowerPlantUnit
   ,x3_0.KeyEnergyPlantTypeParent
   ,x2_0.KeyEnergyPlantType
   ,x2_0.MostRecentSequence
FROM SNLEdit_new.dbo.PowerPlantUnitFuel x2_0
LEFT JOIN Lookup.dbo.EnergyPlantType x3_0
    ON x2_0.KeyEnergyPlantType = x3_0.KeyEnergyPlantType
        AND x3_0.updoperation < 2
WHERE x2_0.MostRecentSequence IN (1, 2, 3, 4, 5, 6);
";

Console.WriteLine("ORIGINAL:" + sql);

var reader = new StringReader(sql);
var parser = new TSql150Parser(true);

var fragment = (TSqlScript)parser.Parse(reader, out var errors);

if (errors.Count > 0)
{
    Console.WriteLine("Failed to parse SQL script:");
    foreach (var error in errors)
    {
        Console.WriteLine(error.Message);
    }

    Debugger.Break();
}

var options = new SqlScriptGeneratorOptions
{
    IncludeSemicolons = true,
    IndentationSize = 4
};
var generator = new Sql150ScriptGenerator(options);

var statement = (SelectStatement)fragment.Batches[0].Statements[0];

var expression = (QuerySpecification)statement.QueryExpression;

generator.GenerateScript(expression, out var formattedSql);

Console.WriteLine();
Console.WriteLine("FORMATTED:" + Environment.NewLine + formattedSql);
Console.ReadLine();