Support EXISTS subqueries

benesch · benesch · commit 975106b20772 · 2019-06-03T11:14:24.000-04:00
Fix #5.
diff --git a/src/sqlast/mod.rs b/src/sqlast/mod.rs
@@ -125,6 +125,9 @@ pub enum ASTNode {
         results: Vec<ASTNode>,
         else_result: Option<Box<ASTNode>>,
     },
+    /// An exists expression `EXISTS(SELECT ...)`, used in expressions like
+    /// `WHERE EXISTS (SELECT ...)`.
+    SQLExists(Box<SQLQuery>),
     /// A parenthesized subquery `(SELECT ...)`, used in expression like
     /// `SELECT (subquery) AS x` or `WHERE (subquery) = x`
     SQLSubquery(Box<SQLQuery>),
@@ -230,6 +233,7 @@ impl ToString for ASTNode {
                 }
                 s + " END"
             }
+            ASTNode::SQLExists(s) => format!("EXISTS ({})", s.to_string()),
             ASTNode::SQLSubquery(s) => format!("({})", s.to_string()),
         }
     }
diff --git a/src/sqlparser.rs b/src/sqlparser.rs
@@ -191,6 +191,7 @@ impl Parser {
                 }
                 "CASE" => self.parse_case_expression(),
                 "CAST" => self.parse_cast_expression(),
+                "EXISTS" => self.parse_exists_expression(),
                 "NOT" => Ok(ASTNode::SQLUnary {
                     operator: SQLOperator::Not,
                     expr: Box::new(self.parse_subexpr(Self::UNARY_NOT_PREC)?),
@@ -404,6 +405,14 @@ impl Parser {
         })
     }
 
+    /// Parse a SQL EXISTS expression e.g. `WHERE EXISTS(SELECT ...)`.
+    pub fn parse_exists_expression(&mut self) -> Result<ASTNode, ParserError> {
+        self.expect_token(&Token::LParen)?;
+        let exists_node = ASTNode::SQLExists(Box::new(self.parse_query()?));
+        self.expect_token(&Token::RParen)?;
+        Ok(exists_node)
+    }
+
     /// Parse an operator following an expression
     pub fn parse_infix(&mut self, expr: ASTNode, precedence: u8) -> Result<ASTNode, ParserError> {
         debug!("parsing infix");
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
@@ -1385,6 +1385,46 @@ fn parse_scalar_subqueries() {
     });
 }
 
+#[test]
+fn parse_exists_subquery() {
+    let expected_inner = verified_query("SELECT 1");
+    let sql = "SELECT * FROM t WHERE EXISTS (SELECT 1)";
+    let select = verified_only_select(sql);
+    assert_eq!(
+        ASTNode::SQLExists(Box::new(expected_inner.clone())),
+        select.selection.unwrap(),
+    );
+
+    let sql = "SELECT * FROM t WHERE NOT EXISTS (SELECT 1)";
+    let select = verified_only_select(sql);
+    assert_eq!(
+        ASTNode::SQLUnary {
+            operator: SQLOperator::Not,
+            expr: Box::new(ASTNode::SQLExists(Box::new(expected_inner))),
+        },
+        select.selection.unwrap(),
+    );
+
+    verified_stmt("SELECT * FROM t WHERE EXISTS (WITH u AS (SELECT 1) SELECT * FROM u)");
+    verified_stmt("SELECT EXISTS (SELECT 1)");
+
+    let res = parse_sql_statements("SELECT EXISTS (");
+    assert_eq!(
+        ParserError::ParserError(
+            "Expected SELECT or a subquery in the query body, found: EOF".to_string()
+        ),
+        res.unwrap_err(),
+    );
+
+    let res = parse_sql_statements("SELECT EXISTS (NULL)");
+    assert_eq!(
+        ParserError::ParserError(
+            "Expected SELECT or a subquery in the query body, found: NULL".to_string()
+        ),
+        res.unwrap_err(),
+    );
+}
+
 #[test]
 fn parse_create_view() {
     let sql = "CREATE VIEW myschema.myview AS SELECT foo FROM bar";

Original file line number	Diff line number	Diff line change
`@@ -125,6 +125,9 @@ pub enum ASTNode {`
`125`	`125`	`results: Vec<ASTNode>,`
`126`	`126`	`else_result: Option<Box<ASTNode>>,`
`127`	`127`	`},`
	`128`	+ /// An exists expression `EXISTS(SELECT ...)`, used in expressions like
	`129`	+ /// `WHERE EXISTS (SELECT ...)`.
	`130`	`+ SQLExists(Box<SQLQuery>),`
`128`	`131`	/// A parenthesized subquery `(SELECT ...)`, used in expression like
`129`	`132`	/// `SELECT (subquery) AS x` or `WHERE (subquery) = x`
`130`	`133`	`SQLSubquery(Box<SQLQuery>),`
`@@ -230,6 +233,7 @@ impl ToString for ASTNode {`
`230`	`233`	`}`
`231`	`234`	`s + " END"`
`232`	`235`	`}`
	`236`	`+ ASTNode::SQLExists(s) => format!("EXISTS ({})", s.to_string()),`
`233`	`237`	`ASTNode::SQLSubquery(s) => format!("({})", s.to_string()),`
`234`	`238`	`}`
`235`	`239`	`}`