Parser incorrectly generates a column on the wrong table when a column is not fully qualified in a SQL query

[chlewi]

Example SQL text:

SELECT CAST(qsp.plan_id AS NVARCHAR(16)) AS PlanId, '0x' + CONVERT(NVARCHAR(MAX), deqs.plan_handle, 2) AS PlanHandle FROM sys.query_store_plan qsp WITH (NOLOCK) JOIN sys.dm_exec_query_stats deqs WITH (NOLOCK) ON (qsp.query_plan_hash = deqs.query_plan_hash) WHERE qsp.query_id = @QUERY_ID AND qsp.last_execution_time > DATEADD(MINUTE, -15, GETUTCDATE()) AND
plan_forcing_type_desc != 'AUTO'

Actual Result: When parsing this object, the parser is thinking plan_forcing_type_desc is on BOTH sys.query_store_plan and sys.dm_exec_query_stats. The column only exists on sys.query_store_plan, but the resulting parsed file generates invalid syntax where plan_forcing_type_desc exists on both sys.query_store_plan and sys.dm_exec_query_stats.

Expected result: the parser should generate a resulting xml that only has plan_forcing_type_desc on sys.query_store_plan.

[clement911]

Can you share your actual code that you use to parse, and which node is invalid?

The parser would have no way to know which table each column belongs to, unless it is fully qualified (eg. sys.query_store_plan.plan_forcing_type_desc).

[chlewi]

Hey @Clement,

Please work with @Leila @.>, @Neha @.> and @Naman @.***> on this issue - they are looking to get a repro with more details.

Thanks,

Chris

From: Clement Gutel @.> Sent: Tuesday, November 21, 2023 12:54 PM To: microsoft/SqlScriptDOM @.> Cc: Chris Lewis (DATAVERSE) @.>; Author @.> Subject: Re: [microsoft/SqlScriptDOM] Parser incorrectly generates a column on the wrong table when a column is not fully qualified in a SQL query (Issue #62)

Can you share your actual code that you use to parse, and which node is invalid?

The parser would have no way to know which table each column belongs to, unless it is fully qualified (eg. sys.query_store_plan.plan_forcing_type_desc).

- Reply to this email directly, view it on GitHubhttps://github.com/microsoft/SqlScriptDOM/issues/62#issuecomment-1821662763, or unsubscribehttps://github.com/notifications/unsubscribe-auth/BEFFAURYT3B4JUD3A3IEPNDYFUIF3AVCNFSM6AAAAAA7VATKVWVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMYTQMRRGY3DENZWGM. You are receiving this because you authored the thread.Message ID: @.**@.>>

[llali]

this is not a bug in parser. I originally thought it might be, but I was wrong. when there's reference to a column in function, parser doesn't do anything with which object it's coming from. it would parser the column as is and doesn't apply objects if they are not specified.