Skip to content

PostgreSQL: accept CREATE SEQUENCE options in any clause order #2367

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
amaksimo wants to merge 1 commit into
base: main
Choose a base branch
from
+136 −35
Open

PostgreSQL: accept CREATE SEQUENCE options in any clause order #2367

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
4 changes: 4 additions & 0 deletions src/ast/mod.rs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -4695,6 +4695,10 @@ pub enum Statement {
/// CREATE [ { TEMPORARY | TEMP } ] SEQUENCE [ IF NOT EXISTS ] <sequence_name>
/// ```
/// Define a new sequence:
///
/// Note: PostgreSQL allows the option clauses (`INCREMENT`, `MINVALUE`,
/// `START`, etc.) to appear in any order.
/// See <https://www.postgresql.org/docs/current/sql-createsequence.html>
CreateSequence {
/// Whether the sequence is temporary.
temporary: bool,
Expand Down
77 changes: 42 additions & 35 deletions src/parser/mod.rs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -19823,45 +19823,52 @@ impl<'a> Parser<'a> {

fn parse_create_sequence_options(&mut self) -> Result<Vec<SequenceOptions>, ParserError> {
let mut sequence_options = vec![];
//[ INCREMENT [ BY ] increment ]
if self.parse_keywords(&[Keyword::INCREMENT]) {
if self.parse_keywords(&[Keyword::BY]) {
sequence_options.push(SequenceOptions::IncrementBy(self.parse_number()?, true));
} else {
sequence_options.push(SequenceOptions::IncrementBy(self.parse_number()?, false));
// PostgreSQL accepts these clauses in any order (e.g. pg_dump emits
// `START` before `INCREMENT`).
// https://www.postgresql.org/docs/current/sql-createsequence.html
loop {
//[ INCREMENT [ BY ] increment ]
if self.parse_keywords(&[Keyword::INCREMENT]) {
if self.parse_keywords(&[Keyword::BY]) {
sequence_options.push(SequenceOptions::IncrementBy(self.parse_number()?, true));
} else {
sequence_options
.push(SequenceOptions::IncrementBy(self.parse_number()?, false));
}
}
//[ MINVALUE minvalue | NO MINVALUE ]
else if self.parse_keyword(Keyword::MINVALUE) {
sequence_options.push(SequenceOptions::MinValue(Some(self.parse_number()?)));
} else if self.parse_keywords(&[Keyword::NO, Keyword::MINVALUE]) {
sequence_options.push(SequenceOptions::MinValue(None));
}
//[ MAXVALUE maxvalue | NO MAXVALUE ]
else if self.parse_keywords(&[Keyword::MAXVALUE]) {
sequence_options.push(SequenceOptions::MaxValue(Some(self.parse_number()?)));
} else if self.parse_keywords(&[Keyword::NO, Keyword::MAXVALUE]) {
sequence_options.push(SequenceOptions::MaxValue(None));
}
//[ START [ WITH ] start ]
else if self.parse_keywords(&[Keyword::START]) {
if self.parse_keywords(&[Keyword::WITH]) {
sequence_options.push(SequenceOptions::StartWith(self.parse_number()?, true));
} else {
sequence_options.push(SequenceOptions::StartWith(self.parse_number()?, false));
}
}
}
//[ MINVALUE minvalue | NO MINVALUE ]
if self.parse_keyword(Keyword::MINVALUE) {
sequence_options.push(SequenceOptions::MinValue(Some(self.parse_number()?)));
} else if self.parse_keywords(&[Keyword::NO, Keyword::MINVALUE]) {
sequence_options.push(SequenceOptions::MinValue(None));
}
//[ MAXVALUE maxvalue | NO MAXVALUE ]
if self.parse_keywords(&[Keyword::MAXVALUE]) {
sequence_options.push(SequenceOptions::MaxValue(Some(self.parse_number()?)));
} else if self.parse_keywords(&[Keyword::NO, Keyword::MAXVALUE]) {
sequence_options.push(SequenceOptions::MaxValue(None));
}

//[ START [ WITH ] start ]
if self.parse_keywords(&[Keyword::START]) {
if self.parse_keywords(&[Keyword::WITH]) {
sequence_options.push(SequenceOptions::StartWith(self.parse_number()?, true));
//[ CACHE cache ]
else if self.parse_keywords(&[Keyword::CACHE]) {
sequence_options.push(SequenceOptions::Cache(self.parse_number()?));
}
// [ [ NO ] CYCLE ]
else if self.parse_keywords(&[Keyword::NO, Keyword::CYCLE]) {
sequence_options.push(SequenceOptions::Cycle(true));
} else if self.parse_keywords(&[Keyword::CYCLE]) {
sequence_options.push(SequenceOptions::Cycle(false));
} else {
sequence_options.push(SequenceOptions::StartWith(self.parse_number()?, false));
break;
}
}
//[ CACHE cache ]
if self.parse_keywords(&[Keyword::CACHE]) {
sequence_options.push(SequenceOptions::Cache(self.parse_number()?));
}
// [ [ NO ] CYCLE ]
if self.parse_keywords(&[Keyword::NO, Keyword::CYCLE]) {
sequence_options.push(SequenceOptions::Cycle(true));
} else if self.parse_keywords(&[Keyword::CYCLE]) {
sequence_options.push(SequenceOptions::Cycle(false));
}

Ok(sequence_options)
}
Expand Down
90 changes: 90 additions & 0 deletions tests/sqlparser_common.rs
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -3861,6 +3861,96 @@ fn parse_negative_value() {
);
}

#[test]
fn parse_create_sequence_clause_order_independent() {
// pg_dump emits options in an order a positional parser could not handle
// (START before INCREMENT); it must parse cleanly.
let pg_dump_sql = "CREATE SEQUENCE public.t_id_seq AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1";
let canonical = "CREATE SEQUENCE public.t_id_seq AS INTEGER START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1";
match one_statement_parses_to(pg_dump_sql, canonical) {
Statement::CreateSequence {
temporary,
if_not_exists,
name,
data_type,
sequence_options,
owned_by,
} => {
assert!(!temporary);
assert!(!if_not_exists);
assert_eq!(name.to_string(), "public.t_id_seq");
assert_eq!(data_type, Some(DataType::Integer(None)));
assert_eq!(owned_by, None);
let expected = vec![
SequenceOptions::StartWith(Expr::value(number("1")), true),
SequenceOptions::IncrementBy(Expr::value(number("1")), true),
SequenceOptions::MinValue(None),
SequenceOptions::MaxValue(None),
SequenceOptions::Cache(Expr::value(number("1"))),
];
assert_eq!(sequence_options, expected);
}
other => panic!("expected CreateSequence, got {other:?}"),
}

// The AST vector reflects source order, so two reorderings of the same
// logical sequence parse into different vectors, each matching its input.
fn options_of(sql: &str) -> Vec<SequenceOptions> {
match verified_stmt(sql) {
Statement::CreateSequence {
sequence_options, ..
} => sequence_options,
other => panic!("expected CreateSequence, got {other:?}"),
}
}

let a = options_of("CREATE SEQUENCE s INCREMENT BY 2 START WITH 5 CACHE 1");
assert_eq!(
a,
vec![
SequenceOptions::IncrementBy(Expr::value(number("2")), true),
SequenceOptions::StartWith(Expr::value(number("5")), true),
SequenceOptions::Cache(Expr::value(number("1"))),
]
);

let b = options_of("CREATE SEQUENCE s CACHE 1 START WITH 5 INCREMENT BY 2");
assert_eq!(
b,
vec![
SequenceOptions::Cache(Expr::value(number("1"))),
SequenceOptions::StartWith(Expr::value(number("5")), true),
SequenceOptions::IncrementBy(Expr::value(number("2")), true),
]
);

// Regression: the original INCREMENT-first ordering must still round-trip.
verified_stmt(
"CREATE SEQUENCE IF NOT EXISTS name2 AS BIGINT INCREMENT 1 MINVALUE 1 MAXVALUE 20 START WITH 10 CACHE 2 NO CYCLE",
);
}

#[test]
fn parse_create_sequence_repeated_clause_kept() {
// Characterization: the loop accepts and preserves repeated clauses.
// Documents current permissive behavior, not a requirement.
match verified_stmt("CREATE SEQUENCE s INCREMENT 1 INCREMENT 2 CACHE 1") {
Statement::CreateSequence {
sequence_options, ..
} => {
assert_eq!(
sequence_options,
vec![
SequenceOptions::IncrementBy(Expr::value(number("1")), false),
SequenceOptions::IncrementBy(Expr::value(number("2")), false),
SequenceOptions::Cache(Expr::value(number("1"))),
]
);
}
other => panic!("expected CreateSequence, got {other:?}"),
}
}

#[test]
fn parse_create_table() {
let sql = "CREATE TABLE uk_cities (\
Expand Down
Loading