[SPARK-34928] CTE Execution fails for Sql Server

• Type: Bug
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Affects Version/s: 3.0.1
• Component/s: Spark Core
• Assignee: Unassigned
• Reporter: Supun De Silva
• Created: 01/Apr/21 02:49
• Updated: 12/Dec/22 18:11

Description

Issue

We have a simple Sql statement that we intend to execute on SQL Server. This has a CTE component.

Execution of this yields to an error that looks like follows

java.sql.SQLException: Incorrect syntax near the keyword 'WITH'.

We are using the jdbc driver net.sourceforge.jtds.jdbc.Driver (version 1.3.1)

This is a particularly annoying issue and due to this we are having to write inner queries that are fair bit inefficient.

SQL statement

(not the actual one but a simplified version with renamed parameters)

 

WITH OldChanges as (
   SELECT distinct 
        SomeDate,
        Name
   FROM [dbo].[DateNameFoo] (nolock)
   WHERE SomeDate!= '2021-03-30'
       AND convert(date, UpdateDateTime) = '2021-03-31'

SELECT * from OldChanges 

Update on 2021-04-21

We tried com.microsoft.sqlserver.jdbc.SQLServerDriver driver as well. This also yields to the same issue.