False positives on KustoCode.ParseAndAnalyze (ifexists is ignored).

[jkindwall]

Our team stores a fairly large library of .kql files in source control that are used to ensure the schema of our main database is properly in sync with the rest of our code. We frequently run into deployment failures due to errors in these kql files that were not caught during code review and we don't find out about them until we are attempting to deploy the changes to our database.

I would like to use this library and specifically the KustoCode.ParseAndAnalyze method in a validation step as part of our PR builds to try and catch these types of errors before they are merged into our main branch. However, I'm currently running into far too many false positives in kql code that actually executes without issue against the actual database.

I am using this library: https://github.com/mattwar/Kusto.Toolkit to automatically generate the GlobalState object based on the current state of our test environment database, then running each command through the ParseAndAnalyze() method to detect errors.

One such error is that the "ifexists" parameter seems to be ignored on .drop and .rename commands.

.drop column MyTable.Status ifexists

KS142 - The name 'Status' does not refer to any known column, table, variable or function. The analyzer seems to be ignoring the "ifexists" parameter which should make this code execute without errors even when the Status field doesn't exist.

.rename tables MyTable_Old=MyTable ifexists

KS204 - The name 'MyTable' does not refer to any known table, tabular variable or function. Again, its ignoring "ifexists"

[sloutsky]

It's better to open issue per each bug you spot, and we will check this.

Get Outlook for Androidhttps://aka.ms/AAb9ysg

---

From: Jesse Kindwall @.> Sent: Monday, June 12, 2023 11:47:40 PM To: microsoft/Kusto-Query-Language @.> Cc: Subscribed @.***> Subject: [microsoft/Kusto-Query-Language] False positives on KustoCode.ParseAndAnalyze (Issue #119)

Our team stores a fairly large library of .kql files in source control that are used to ensure the schema of our main database is properly in sync with the rest of our code. We frequently run into deployment failures due to errors in these kql files that were not caught during code review and we don't find out about them until we are attempting to deploy the changes to our database.

I would like to use this library and specifically the KustoCode.ParseAndAnalyze method in a validation step as part of our PR builds to try and catch these types of errors before they are merged into our main branch. However, I'm currently running into far too many false positives in kql code that actually executes without issue against the actual database.

I am using this library: https://github.com/mattwar/Kusto.Toolkit to automatically generate the GlobalState object based on the current state of our test environment database, then running each command through the ParseAndAnalyze() method to detect errors. I have not inspected every error found so far (there are several hundred) and of course many of them are different instances of the same issue, but here are some examples of the false positives I've encountered:

.drop column MyTable.Status ifexists KS142 - The name 'Status' does not refer to any known column, table, variable or function. The analyzer seems to be ignoring the "ifexists" parameter which should make this code execute without errors even when the Status field doesn't exist.

.rename tables MyTable_Old=MyTable ifexists KS204 - The name 'MyTable' does not refer to any known table, tabular variable or function. Again, its ignoring "ifexists"

.alter table ["MyTable"] policy update with (AllowUnreferencedSourceTable=true) @'[' ' {' ' "IsEnabled": true,' ' "IsTransactional": false,' ' "PropagateIngestionProperties": false,' ' "Query": "MyFunction",' ' "Source": "MyOtherTable"' ' }' ']' KS006 - Missing string It seems the analyzer if failing on this part: "with (AllowUnreferencedSourceTable=true)". This seems to be an undocumented feature of the .alter table policy update command, but the actual database supports it.

| join kind=inner hint.strategy=shuffle hint.num_partitions=6 (vwMyView | where Enabled) on $left.Id == $right.Id KS141 - The expression must have the type bool. The vwMyView function returns tabular data where "Enabled" is a bool field, however the analyzer doesn't seem to recognize it as such.

let tag = tolower(strcat_array(CustomParseFunction(stringParameter).Parts, ":")); KS107 - A value of type dynamic expected. The "CustomParseFunction" returns a dynamic object with the field "Parts" as a dynamic array, however the analyzer does not recognize it as such.

| summarize FooCount = countif(IsFoo) by EntityCHUri KS107 - A value of type bool expected. The data fed into this summarize expression includes a bool "IsFoo" field but it is not recognized.

let info = CustomParseFunction(stringParameter); let data = getDataFunction(info.Parts[0]); KS147 - The element access operator [] is not allowed in this context. Again, the Parts field of the object returned by CustomParseFunction is not recognized as a dynamic array.

— Reply to this email directly, view it on GitHubhttps://github.com/microsoft/Kusto-Query-Language/issues/119, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AEH2NB4HGQNHZCOG7NJHPRLXK556ZANCNFSM6AAAAAAZD455NQ. You are receiving this because you are subscribed to this thread.Message ID: @.***>

[jkindwall]

Broke it up into separate issues.

[mattwar]

There is not much support for control commands beyond syntax parsing and recognizing a few locations where things like names appear, but no general recognition of other terms during analysis. I will look into finding a solution for this case, but it may not be good idea to rely on semantic analysis for control commands. You probably only want to rely on checking syntax errors.

[jkindwall]

That's a pretty significant limitation. I'm trying to use this library to validate the files that define our database schema and functions. They are pretty much all control commands. Mostly the following:

.create-or-alter function .create-merge table .alter table policy .delete table policy