PostgreSQL: Reject duplicate clauses across query parentheses
diff --git a/src/dialect/mod.rs b/src/dialect/mod.rs index 69bbe35..b3384b0 100644 --- a/src/dialect/mod.rs +++ b/src/dialect/mod.rs
@@ -756,6 +756,11 @@ false } + /// Returns true if a parenthesized query may set a clause that the enclosing query sets again, as in `(SELECT 1 LIMIT 1) LIMIT 2`. + fn supports_duplicate_clauses_on_parenthesized_query(&self) -> bool { + true + } + /// Return true if the dialect supports "FROM-first" inserts. /// /// Example:
diff --git a/src/dialect/postgresql.rs b/src/dialect/postgresql.rs index e49417c..a33f10f 100644 --- a/src/dialect/postgresql.rs +++ b/src/dialect/postgresql.rs
@@ -334,6 +334,11 @@ false } + /// See `insertSelectOptions` in <https://github.com/postgres/postgres/blob/REL_18_STABLE/src/backend/parser/gram.y> + fn supports_duplicate_clauses_on_parenthesized_query(&self) -> bool { + false + } + /// Postgres supports `NOTNULL` as an alias for `IS NOT NULL` /// See: <https://www.postgresql.org/docs/17/functions-comparison.html> fn supports_notnull_operator(&self) -> bool {
diff --git a/src/parser/mod.rs b/src/parser/mod.rs index db54296..5f6848d 100644 --- a/src/parser/mod.rs +++ b/src/parser/mod.rs
@@ -14902,6 +14902,7 @@ .into()) } else { let body = self.parse_query_body(self.dialect.prec_unknown())?; + let clauses_loc = self.peek_token_ref().span.start; let order_by = self.parse_optional_order_by()?; @@ -14953,7 +14954,7 @@ Vec::new() }; - Ok(Query { + let query = Query { with, body, order_by, @@ -14964,11 +14965,59 @@ settings, format_clause, pipe_operators, + }; + if !self + .dialect + .supports_duplicate_clauses_on_parenthesized_query() + { + Self::check_parenthesized_query_clauses(&query, clauses_loc)?; } - .into()) + Ok(query.into()) } } + /// Rejects a clause that `query` sets again on the parenthesized query forming its body. + fn check_parenthesized_query_clauses(query: &Query, loc: Location) -> Result<(), ParserError> { + fn has_offset(query: &Query) -> bool { + matches!( + query.limit_clause, + Some(LimitClause::LimitOffset { + offset: Some(_), + .. + }) | Some(LimitClause::OffsetCommaLimit { .. }) + ) + } + fn has_limit(query: &Query) -> bool { + query.fetch.is_some() + || matches!( + query.limit_clause, + Some(LimitClause::LimitOffset { limit: Some(_), .. }) + | Some(LimitClause::OffsetCommaLimit { .. }) + ) + } + + let mut body = &query.body; + while let SetExpr::Query(inner) = body.as_ref() { + if query.order_by.is_some() && inner.order_by.is_some() { + return parser_err!("multiple ORDER BY clauses not allowed", loc); + } + if has_offset(query) && has_offset(inner) { + return parser_err!("multiple OFFSET clauses not allowed", loc); + } + if has_limit(query) && has_limit(inner) { + return parser_err!("multiple LIMIT clauses not allowed", loc); + } + if let (Some(with), Some(_)) = (&query.with, &inner.with) { + return parser_err!( + "multiple WITH clauses not allowed", + with.with_token.0.span.start + ); + } + body = &inner.body; + } + Ok(()) + } + fn parse_pipe_operators(&mut self) -> Result<Vec<PipeOperator>, ParserError> { let mut pipe_operators = Vec::new();
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs index 4d51989..bf478a5 100644 --- a/tests/sqlparser_common.rs +++ b/tests/sqlparser_common.rs
@@ -20755,3 +20755,73 @@ dialects.verified_stmt("SELECT 1 AS 'x'"); dialects.verified_stmt("SELECT a FROM t AS 'y'"); } + +#[test] +fn parse_duplicate_clauses_on_parenthesized_query() { + let duplicates = [ + ("(SELECT n FROM t LIMIT 2) LIMIT 1", Some(("LIMIT", 27))), + ( + "(SELECT n FROM t ORDER BY n) ORDER BY n DESC", + Some(("ORDER BY", 30)), + ), + ("(SELECT n FROM t OFFSET 1) OFFSET 1", Some(("OFFSET", 28))), + ( + "(SELECT n FROM t LIMIT 2) FETCH FIRST 1 ROWS ONLY", + Some(("LIMIT", 27)), + ), + ( + "(SELECT n FROM t FETCH FIRST 2 ROWS ONLY) LIMIT 1", + Some(("LIMIT", 43)), + ), + ( + "(SELECT n FROM t LIMIT 2 OFFSET 1) OFFSET 1", + Some(("OFFSET", 36)), + ), + ("((SELECT n FROM t LIMIT 2)) LIMIT 1", Some(("LIMIT", 29))), + ( + "WITH x AS (SELECT 1) (WITH y AS (SELECT 2) SELECT 1)", + Some(("WITH", 1)), + ), + ( + "WITH c AS ((SELECT 1 ORDER BY 1) ORDER BY 1) SELECT * FROM c", + Some(("ORDER BY", 34)), + ), + ( + "SELECT * FROM ((SELECT n FROM t LIMIT 2) LIMIT 1) AS s", + None, + ), + ]; + + let rejecting = all_dialects_where(|d| !d.supports_duplicate_clauses_on_parenthesized_query()); + for (sql, expected) in duplicates { + for dialect in &rejecting.dialects { + let err = Parser::parse_sql(dialect.as_ref(), sql).unwrap_err(); + if let Some((clause, column)) = expected { + assert_eq!( + err, + ParserError::ParserError(format!( + "multiple {clause} clauses not allowed at Line: 1, Column: {column}" + )), + "{sql}" + ); + } + } + } + + let accepting = all_dialects_where(|d| d.supports_duplicate_clauses_on_parenthesized_query()); + for (sql, _) in duplicates { + accepting.verified_stmt(sql); + } + + for sql in [ + "(SELECT n FROM t ORDER BY n LIMIT 2) OFFSET 1", + "SELECT n FROM (SELECT n FROM t LIMIT 2) AS s LIMIT 1", + "(SELECT 1 LIMIT 1) UNION (SELECT 2 LIMIT 1) LIMIT 1", + "(SELECT 1 ORDER BY 1) UNION SELECT 2 ORDER BY 1", + "WITH x AS (SELECT 1) (SELECT 1 ORDER BY 1 LIMIT 1)", + "(WITH y AS (SELECT 2) SELECT 1) ORDER BY 1 LIMIT 1", + "(SELECT n FROM t ORDER BY n FOR UPDATE) FOR SHARE", + ] { + rejecting.verified_stmt(sql); + } +}