peter-toth commented on PR #34693: URL: https://github.com/apache/spark/pull/34693#issuecomment-1111007589
I think the requirement is to be able to use CTE queries with JDBC sources on MSSQL. Currently it is doesn't work as Spark wraps the original query into a SELECT statement (e.g. when it queries the schema it wraps it into `SELECT * FROM (<query>) WHERE 1=0`), which construct is not supported by MSSQL. - `sessionInitStatement` is a separate statement so you can't put the WITH clause there and keep the SELECT clause in `query`. - We could put the whole CTE query into `sessionInitStatement` as `CREATE TEMPORARY VIEW v AS <query>` and use `SELECT v FROM` in `query` but temporary views are also not supported in MSSQL. - We could improve Spark to identify CTE queries and assemble the schema query in a way that is compatible with MSSQL, but splitting an arbitrary query into WITH and SELECT clauses programatically is not that simple. - This PR offers a new `withClause` option where the user can split the query manually. (I should probably call it a `queryPrefix` as it also works with MSSQL's temp table syntax.) -- 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]
