DacFX: Ignore non Columns in a ColumnReferenceExpression visitor

Question

When using a visitor scanning for ColumnReferenceExpression's in a TSqlFragment there are some things that get picked up as columns that truly are not. For example in this sql:

select Age, FirstName, LastName, DATEADD(year, Age, getdate())   
from table1 
inner join table2 on 1 = 1

The year parameter gets picked up as a column reference along with Age, FirstName, and LastName. There does not appear any way to differentiate year from the other columns.

The reason I am asking is that we have written a SqlCodeAnalysisRule to check for two part names when there is more than one table in a select and it is picking up these non-columns as well. Here is that Analyze for the rule in case anyone has any ideas on how to exclude them:

public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
    var problems = new List<SqlRuleProblem>();
    var sqlObj = ruleExecutionContext.ModelElement;
    if (sqlObj == null) { return problems; }

    var fragment = ruleExecutionContext.ScriptFragment;
    var selectStatementVisitor = new SelectStatementVisitor();
    fragment.Accept(selectStatementVisitor);

    if (selectStatementVisitor.Statements.Count == 0) { return problems; }

    foreach (var select in selectStatementVisitor.Statements)
    {
        var fromClause = (select.QueryExpression as QuerySpecification)?.FromClause;
        if (fromClause == null) { continue; }
        //check to ensure we have more than one table
        var namedTableVisitor = new NamedTableReferenceVisitor();
        fromClause.Accept(namedTableVisitor);
        if(namedTableVisitor.Statements.Count <= 1) { continue; }

        var columnReferences = new ColumnReferenceExpressionVisitor();
        select.Accept(columnReferences);

        //TODO: This will erroneously pickup things which appear to be column references as well
        //      such as the 'dd' in DATEADD(dd, rev.ReviewReferenceDaysStart, @StartDate)
        var offenders = columnReferences.Statements
            .Where(c => (c as ColumnReferenceExpression).MultiPartIdentifier?.Identifiers.Count == 1)
            .Select(n => (n as ColumnReferenceExpression).MultiPartIdentifier.Identifiers[0]);

        problems.AddRange(offenders.Select(cr => new SqlRuleProblem(string.Format(Message, cr.Value), sqlObj, cr)));
    }
    return problems;
}

All of our visitors follow pretty much the same pattern. Here is the column reference visitor as an example of the others mentioned:

internal class ColumnReferenceExpressionVisitor : TSqlFragmentVisitor, IVisitor<ColumnReferenceExpression>
{
    public IList<ColumnReferenceExpression> Statements { get; } = new List<ColumnReferenceExpression>();

    public override void ExplicitVisit(ColumnReferenceExpression node)
    {
        Statements.Add(node);
    }
}   

public interface IVisitor<T> where T : TSqlFragment
{
    IList<T> Statements { get; }
}

I have compared all of the properties for each of the columns found to the non-columns and there is nothing different that can be used to exclude them that I see.

score 1 · Accepted Answer · answered May 19 '17 at 14:38

The dd is a column reference as you are getting all the ColumnReferences in the tree - the script dom doesn't know what dateadd is, it just knows that it is a function and so dd could very well be a column.

If it was me I would get the select statement, get the SelectElements and iterate through them (if you have correlated subqueries for example, by using the Select visitor you should still get all of those) and in this case ignore anything that isn't a ColumnReference.

Doing it manually (rather than linq) so it is a bit clearer it would be something like:

foreach (var select in selectStatementVisitor.Statements)
        {

            var columnReferences = new ColumnReferenceExpressionVisitor();
            select.Accept(columnReferences);

            foreach (var item in (select.QueryExpression as QuerySpecification).SelectElements)
            {

//other code here if you want ...

                if (item is SelectScalarExpression)
                {
                    var expression = item as SelectScalarExpression;
                    if (expression.Expression is ColumnReferenceExpression)
                    {
                        var column = expression.Expression as ColumnReferenceExpression;
                        Console.WriteLine(column); // <-- this is only ColumnReferenceExpression's
                    }
                }
            }

        }

DacFX: Ignore non Columns in a ColumnReferenceExpression visitor

1 Answers1