KevinAppelBofa commented on PR #34693: URL: https://github.com/apache/spark/pull/34693#issuecomment-1111001247
@cloud-fan the requirement is to be able to use the Spark JDBC to access Microsoft SQL Server and use items that are unique to SQL Server, such as temp tables or common table expression (with statement). The Oracle and Mysql both also support CTE however their language allows them to begin with a select statement. The SQL server for CTE is only having it one way where it starts with, WITH, because of how Spark is wrapping the query this is what causes the issue. I had opened up a case with Microsoft and got to their Spark team but they were not able to provide any feedback or commits on how to fix Spark to handle this. This fix that Peter created is allowing both of these items to work, the CTE query and also the temp table query; in both of these it is very difficult to try to split out the sql into parts to place into the sessionInitStatement vs being able to run the query as is The sample queries look like query2 = """ WITH DummyCTE AS ( SELECT 1 as DummyCOL ) SELECT * FROM DummyCTE """ query3 = """ (SELECT * INTO #Temp1a FROM (SELECT @@VERSION as version) data ) (SELECT * FROM #Temp1a) """ -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
