Repository: googleapis/java-spanner-jdbc (java)

Issue:
WITH queries are (probably) not supported in the JDBC driver
Cloud Spanner now also supports `WITH` clauses to be added to queries, e.g.:

```sql
WITH subQ1 AS (SELECT SchoolID FROM Roster),
     subQ2 AS (SELECT OpponentID FROM PlayerStats)
SELECT * FROM subQ1
UNION ALL
SELECT * FROM subQ2;
```

These queries will (probably) not be recognized as a `SELECT` query by the JDBC driver.

https://cloud.google.com/spanner/docs/query-syntax#with-clause

Pull request:
fix: add support for WITH clauses
Cloud Spanner now also support `WITH` clauses in a `SELECT` statement. These clauses were not recognized by the JDBC driver.

Fixes #41 


Patch:
diff --git a/src/main/java/com/google/cloud/spanner/jdbc/StatementParser.java b/src/main/java/com/google/cloud/spanner/jdbc/StatementParser.java
index 9ed1ecd..e43a097 100644
--- a/src/main/java/com/google/cloud/spanner/jdbc/StatementParser.java
+++ b/src/main/java/com/google/cloud/spanner/jdbc/StatementParser.java
@@ -161,7 +161,7 @@ class StatementParser {
   }
 
   private final Set<String> ddlStatements = ImmutableSet.of("CREATE", "DROP", "ALTER");
-  private final Set<String> selectStatements = ImmutableSet.of("SELECT");
+  private final Set<String> selectStatements = ImmutableSet.of("SELECT", "WITH");
   private final Set<String> dmlStatements = ImmutableSet.of("INSERT", "UPDATE", "DELETE");
   private final Set<ClientSideStatementImpl> statements;
 


Test patch:
diff --git a/src/test/java/com/google/cloud/spanner/jdbc/StatementParserTest.java b/src/test/java/com/google/cloud/spanner/jdbc/StatementParserTest.java
index 6da65c1..1344a0f 100644
--- a/src/test/java/com/google/cloud/spanner/jdbc/StatementParserTest.java
+++ b/src/test/java/com/google/cloud/spanner/jdbc/StatementParserTest.java
@@ -266,6 +266,27 @@ public class StatementParserTest {
     assertTrue(parser.isQuery("select * from foo"));
     assertFalse(parser.isQuery("INSERT INTO FOO (ID, NAME) SELECT ID+1, NAME FROM FOO"));
 
+    assertTrue(
+        parser.isQuery(
+            "WITH subQ1 AS (SELECT SchoolID FROM Roster),\n"
+                + "     subQ2 AS (SELECT OpponentID FROM PlayerStats)\n"
+                + "SELECT * FROM subQ1\n"
+                + "UNION ALL\n"
+                + "SELECT * FROM subQ2"));
+    assertTrue(
+        parser.isQuery(
+            "with subQ1 AS (SELECT SchoolID FROM Roster),\n"
+                + "     subQ2 AS (SELECT OpponentID FROM PlayerStats)\n"
+                + "select * FROM subQ1\n"
+                + "UNION ALL\n"
+                + "SELECT * FROM subQ2"));
+    assertTrue(
+        parser
+            .parse(
+                Statement.of(
+                    "-- this is a comment\nwith foo as (select * from bar)\nselect * from foo"))
+            .isQuery());
+
     assertTrue(parser.parse(Statement.of("-- this is a comment\nselect * from foo")).isQuery());
     assertTrue(
         parser
@@ -310,6 +331,13 @@ public class StatementParserTest {
     assertTrue(
         parser.isQuery(
             "@{JOIN_METHOD=HASH_JOIN}\n /* Multi line comment\n with more comments\n */SELECT * FROM PersonsTable"));
+    assertTrue(
+        parser.isQuery(
+            "@{JOIN_METHOD=HASH_JOIN} WITH subQ1 AS (SELECT SchoolID FROM Roster),\n"
+                + "     subQ2 AS (SELECT OpponentID FROM PlayerStats)\n"
+                + "SELECT * FROM subQ1\n"
+                + "UNION ALL\n"
+                + "SELECT * FROM subQ2"));
 
     // Invalid query hints.
     assertFalse(parser.isQuery("@{JOIN_METHOD=HASH_JOIN SELECT * FROM PersonsTable"));
diff --git a/src/test/resources/com/google/cloud/spanner/jdbc/ITSqlMusicScriptTest.sql b/src/test/resources/com/google/cloud/spanner/jdbc/ITSqlMusicScriptTest.sql
index 93218da..243cdda 100644
--- a/src/test/resources/com/google/cloud/spanner/jdbc/ITSqlMusicScriptTest.sql
+++ b/src/test/resources/com/google/cloud/spanner/jdbc/ITSqlMusicScriptTest.sql
@@ -668,3 +668,8 @@ WHERE SingerId=2;
 SELECT COUNT(*) AS ACTUAL, 0 AS EXPECTED
 FROM Songs
 WHERE SingerId=2;
+
+@EXPECT RESULT_SET
+WITH Song2 AS (SELECT * FROM Songs WHERE SingerId=2)
+SELECT COUNT(*) AS ACTUAL, 0 AS EXPECTED
+FROM Song2;