Spark - Wrong query generated

Hi,

We're using Spark Connector to send queries to our custom Spark Server.

However, we receive some queries that are not Spark protocol (e.g. SELECT TOP). This is not valid Hive SQL.

They are slightly different than the most of the queries that we receive in the following ways (just a few big differences that I first noticed):

- usage of TOP instead of LIMIT

- not all uppercase

- queries are formatted and multi-line 

Example of working query:

SELECT C_4331 AS C_1, COUNT(C_43) AS C_4332 FROM (SELECT C_4f54424c.`Name` AS C_43, C_4954424c.`Age` AS C_4331 FROM `file_azbs1_static`.`titanic` C_4f54424c LEFT OUTER JOIN `es_local_spark`.`demotitanic___titanic` C_4954424c ON (C_4f54424c.`PassengerId` = C_4954424c.`PassengerId`) ) ITBL_0 GROUP BY C_4331 LIMIT 1000001

Example of a query wrongly generated (notice the TOP instead of LIMIT and not only)

select top 1000001
    `C4`,
    { fn convert(count(distinct(`Age`)), SQL_DOUBLE) } + { fn convert(max(`C1`), SQL_DOUBLE) } as `C1`
from 
(
    select `OTBL`.`Age`,
        `ITBL`.`Name` as `C4`,
        case
            when `Age` is null
            then 1
            else 0
        end as `C1`
    from `es_local_spark`.`demotitanic___titanic` as `OTBL`
    left outer join `file_azbs1_static`.`titanic` as `ITBL` on (`OTBL`.`PassengerId` = `ITBL`.`PassengerId`)
) as `ITBL`
group by `C4`' 
select top 1000001
    `C4`,
    { fn convert(count(distinct(`Age`)), SQL_DOUBLE) } + { fn convert(max(`C1`), SQL_DOUBLE) } as `C1`
from 
(
    select `OTBL`.`Age`,
        `ITBL`.`Name` as `C4`,
        case
            when `Age` is null
            then 1
            else 0
        end as `C1`
    from `es_local_spark`.`demotitanic___titanic` as `OTBL`
    left outer join `file_azbs1_static`.`titanic` as `ITBL` on (`OTBL`.`PassengerId` = `ITBL`.`PassengerId`)
) as `ITBL`
group by `C4`

The query is generated by:

We have two titanic tables, joining them on a common PassengerId column generating a One-To-One relationship, then doing a Stacked Column Chart with "Name" column from one table at "Legend" and Count Distinct of "Age" from othe other table at "Value".

If you need more specific info let me know

Does anyone know anything about that ?

Here some logs from PBI

DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8921589Z","Action":"Engine/IO/Odbc/Connection/GetInfoString","HostProcessId":"4640","InfoType":"SQL_DRIVER_NAME","Value":"Microsoft Spark ODBC Driver","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0001077"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8922767Z","Action":"Engine/IO/Odbc/Connection/GetInfoString","HostProcessId":"4640","InfoType":"SQL_DRIVER_VER","Value":"1.2.5.1006","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000089"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8923317Z","Action":"Engine/IO/Odbc/Connection/GetInfoString","HostProcessId":"4640","InfoType":"SQL_DBMS_NAME","Value":"Spark SQL","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000401"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8923818Z","Action":"Engine/IO/Odbc/Connection/GetInfoString","HostProcessId":"4640","InfoType":"SQL_DBMS_VER","Value":"2.4.3","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000147"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8921270Z","Action":"Engine/IO/Odbc/Connection/Info","HostProcessId":"4640","DriverName":"Microsoft Spark ODBC Driver","DriverVersion":"1.2.5.1006","DBMSName":"Spark SQL","DBMSVersion":"2.4.3","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0003105"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.8924416Z","Action":"Engine/IO/Odbc/Command/ExecuteDirect","HostProcessId":"4640","CommandText":"select top 1000001\r\n `C4`,\r\n { fn convert(count(distinct(`Age`)), SQL_DOUBLE) } + { fn convert(max(`C1`), SQL_DOUBLE) } as `C1`\r\nfrom \r\n(\r\n select `OTBL`.`Age`,\r\n `ITBL`.`Name` as `C4`,\r\n case\r\n when `Age` is null\r\n then 1\r\n else 0\r\n end as `C1`\r\n from `SPARK`.`es_local_spark`.`demotitanic___titanic` as `OTBL`\r\n left outer join `SPARK`.`file_azbs1_static`.`titanic` as `ITBL` on (`OTBL`.`PassengerId` = `ITBL`.`PassengerId`)\r\n) as `ITBL`\r\ngroup by `C4`","ParameterCount":"0","Skip":"0","Take":"Infinite","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.5789570"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:57.4714876Z","Action":"BackgroundThread/RollingTraceWriter/Flush","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":4,"Duration":"00:00:00.0000967"} DataMashup.Trace Error: 24579 : {"Start":"2019-09-14T17:28:56.8002621Z","Action":"Engine/IO/Db/ODBC/RunWithRetryAttempt","HostProcessId":"4640","Attempt":"0","MaxAttempt":"3","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '0' error message: 'x.x.exceptions.UnsupportedQuery: This type of query is not supported '.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcUtils.HandleErrorCheckNoData(OdbcHandle hrHandle, RetCode retcode)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteStatement(String commandText, IList`1 parameters, OdbcStatementHandle statement, OdbcBuffer& parameterBuffer)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDataSource.<>c__DisplayClass15.<ExecuteCore>b__10()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.RunWithRetryGuard[T](Int32 maxRetryAttempts, Func`1 action)\r\n\r\n\r\n","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.6785934"} DataMashup.Trace Error: 24579 : {"Start":"2019-09-14T17:28:56.8002554Z","Action":"Engine/IO/Db/ODBC/RunWithRetry","HostProcessId":"4640","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '0' error message: 'x.x.exceptions.UnsupportedQuery: This type of query is not supported '.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Odbc.Interop.OdbcUtils.HandleErrorCheckNoData(OdbcHandle hrHandle, RetCode retcode)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteStatement(String commandText, IList`1 parameters, OdbcStatementHandle statement, OdbcBuffer& parameterBuffer)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcService.OdbcEngineConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcTracingService.OdbcTracingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcCachingService.OdbcCachingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDelegatingConnection.ExecuteDirect(String commandText, IList`1 parameters, RowRange rowRange)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDataSource.<>c__DisplayClass15.<ExecuteCore>b__10()\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.RunWithRetryGuard[T](Int32 maxRetryAttempts, Func`1 action)\r\n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.Invoke[T](Int32 maxRetry, Func`1 action)\r\n\r\n\r\n","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.6804273"} DataMashup.Trace Warning: 24579 : {"Start":"2019-09-14T17:28:56.7990142Z","Action":"RemotePageReader/RunStub","HostProcessId":"4640","Exception":"Exception:\r\nExceptionType: Microsoft.Mashup.Engine1.Runtime.ValueException, Microsoft.MashupEngine, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35\r\nMessage: [DataSource.Error] ODBC: ERROR [HY000] [Microsoft][Hardy] (35) Error from server: error code: '0' error message: 'x.x.exceptions.UnsupportedQuery: This type of query is not supported '.\r\nStackTrace:\n at Microsoft.Mashup.Engine1.Library.Common.DbExceptionHandler.Invoke[T](Int32 maxRetry, Func`1 action)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcDataSource.ExecuteCore(String commandText, IList`1 parameters, RowRange rowRange, String[] columnNames, ColumnConversion[] columnConversions)\r\n at Microsoft.Mashup.Engine1.Library.Odbc.OdbcQuery.TryGetReader(IPageReader& reader)\r\n at Microsoft.Mashup.Engine1.Language.Query.QueryTableValue.GetReader()\r\n at Microsoft.Mashup.Engine.Interface.Tracing.TracingDataReaderSource.get_PageReader()\r\n at Microsoft.Mashup.Evaluator.RemoteDocumentEvaluator.Service.<>c__DisplayClass11.<>c__DisplayClass13.<OnBeginGetDataReaderSource>b__10()\r\n at Microsoft.Mashup.Evaluator.RemotePageReader.<>c__DisplayClass7.<RunStub>b__0()\r\n at Microsoft.Mashup.Evaluator.EvaluationHost.ReportExceptions(IHostTrace trace, IEngineHost engineHost, IMessageChannel channel, Action action)\r\n\r\n\r\n","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.6844163"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:56.7052607Z","Action":"RemoteDocumentEvaluator/Service/OnBeginGetResult","HostProcessId":"4640","evaluationID":"16","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.7785012"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:57.4848383Z","Action":"DependencyCompiler/Dispose","HostProcessId":"4640","Spark":"True","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000184"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:57.4849237Z","Action":"MemoryCache/CacheStats/Size","HostProcessId":"4640","entryCount":"15","totalSize":"22643","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000117"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:57.4849856Z","Action":"MemoryCache/CacheStats/Access","HostProcessId":"4640","hits":"78","requests":"108","hitrate":"0,722222222222222","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"75ba1d3c-09ae-438c-8bb0-a90f3cbe6190","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":1,"Duration":"00:00:00.0000174"} DataMashup.Trace Information: 24579 : {"Start":"2019-09-14T17:28:57.4863907Z","Action":"BackgroundThread/RollingTraceWriter/Flush","ProductVersion":"2.60.5169.4101 (18.07)","ActivityId":"00000000-0000-0000-0000-000000000000","Process":"Microsoft.Mashup.Container.NetFX40","Pid":5520,"Tid":4,"Duration":"00:00:00.0000641"}