Column names not properly escaped for SQL Server data source

[ooples]

Trying out the model builder and I have a column called Open in my database table which after the initial error I tried to set to ignore and still get the error below:

Incorrect syntax near the keyword 'Open'.
 at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at Microsoft.ML.Data.DatabaseLoader.Cursor.get_DataReader()
   at Microsoft.ML.Data.DatabaseLoader.Cursor.MoveNextCore()
   at Microsoft.ML.Data.RootCursorBase.MoveNext()
   at Microsoft.ML.ModelBuilder.AutoMLService.Proposer.Controller.CountRows(IDataView data, Int64 maxRows) in /_/src/Microsoft.ML.ModelBuilder.AutoMLService/Proposer/Controller.cs:line 165
   at Microsoft.ML.ModelBuilder.AutoMLService.Proposer.Controller.Initialize() in /_/src/Microsoft.ML.ModelBuilder.AutoMLService/Proposer/Controller.cs:line 104
   at Microsoft.ML.ModelBuilder.AutoMLService.Experiments.LocalAutoMLExperiment.<ExecuteAsync>d__15.MoveNext() in /_/src/Microsoft.ML.ModelBuilder.AutoMLService/Experiments/LocalAutoMLExperiment.cs:line 112
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.ML.ModelBuilder.AutoMLEngine.<StartTrainingAsync>d__21.MoveNext() in /_/src/Microsoft.ML.ModelBuilder.AutoMLService/AutoMLEngineService/AutoMLEngine.cs:line 163   at StreamJsonRpc.JsonRpc.<InvokeCoreAsync>d__139`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Microsoft.ML.ModelBuilder.ViewModels.TrainViewModel.<<StartTrainingAsync>b__109_0>d.MoveNext()

[beccamc]

@vzhuqin Can you test this scenario for a repro?

[vzhuqin]

Hi @beccamc, I can repro this issue on latest model builder (main): 16.9.1.2152702 if have one column called open or close.

Update:
Repro on SQL Server data source, not occur if the data source type is File.

[beccamc]

Thank you @vzhuqin!

@ooples Thank you for reporting, we will take a look. To unblock yourself, is renaming those columns an option?

[ooples]

No I'm afraid renaming them isn't an option but I was able to work around the issue by ignoring the model builder and just doing the same stuff manually using code instead. Also these columns are considered valid by SQL as long as you surround them by square brackets in SQL queries. [Open] for example

…

On Wed, Oct 27, 2021, 11:15 AM Becca McHenry ***@***.***> wrote: Thank you @vzhuqin <https://github.com/vzhuqin>! @ooples <https://github.com/ooples> Thank you for reporting, we will take a look. To unblock yourself, is renaming those columns an option? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1871 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAXO4ZNUAKB6LR5YA42JP4LUJAJSZANCNFSM5GVV5CXQ> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[beccamc]

That is likely the problem. We autogenerate the SQL query and aren't surrounding them in "[]". Glad you found a solution with code. You might also be able to edit the SQL information in the MBConfig file to update the command string

[beccamc]

Investigate to see if we can escape columns.