This is an automated email from the ASF dual-hosted git repository.
iffyio pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion-sqlparser-rs.git
The following commit(s) were added to refs/heads/main by this push:
new a73577c2 Add support for `NOT NULL` and `NOTNULL` expressions (#1927)
a73577c2 is described below
commit a73577c29fc86b365f14545a0734e619b439dbf4
Author: Ryan Schneider <[email protected]>
AuthorDate: Mon Jul 21 03:58:20 2025 -0700
Add support for `NOT NULL` and `NOTNULL` expressions (#1927)
Co-authored-by: Ifeanyi Ubah <[email protected]>
---
src/dialect/duckdb.rs | 6 +++
src/dialect/mod.rs | 14 ++++++
src/dialect/postgresql.rs | 6 +++
src/dialect/sqlite.rs | 6 +++
src/keywords.rs | 1 +
src/parser/mod.rs | 104 ++++++++++++++++++++++++++++++++++++++----
tests/sqlparser_clickhouse.rs | 24 ++++++++++
tests/sqlparser_common.rs | 47 +++++++++++++++++++
8 files changed, 200 insertions(+), 8 deletions(-)
diff --git a/src/dialect/duckdb.rs b/src/dialect/duckdb.rs
index fa18463a..aee7ee93 100644
--- a/src/dialect/duckdb.rs
+++ b/src/dialect/duckdb.rs
@@ -98,4 +98,10 @@ impl Dialect for DuckDbDialect {
fn supports_select_wildcard_exclude(&self) -> bool {
true
}
+
+ /// DuckDB supports `NOTNULL` as an alias for `IS NOT NULL`,
+ /// see DuckDB Comparisons
<https://duckdb.org/docs/stable/sql/expressions/comparison_operators#between-and-is-not-null>
+ fn supports_notnull_operator(&self) -> bool {
+ true
+ }
}
diff --git a/src/dialect/mod.rs b/src/dialect/mod.rs
index c79b279d..c78b0003 100644
--- a/src/dialect/mod.rs
+++ b/src/dialect/mod.rs
@@ -677,8 +677,16 @@ pub trait Dialect: Debug + Any {
Token::Word(w) if w.keyword == Keyword::MATCH => Ok(p!(Like)),
Token::Word(w) if w.keyword == Keyword::SIMILAR =>
Ok(p!(Like)),
Token::Word(w) if w.keyword == Keyword::MEMBER => Ok(p!(Like)),
+ Token::Word(w)
+ if w.keyword == Keyword::NULL &&
!parser.in_column_definition_state() =>
+ {
+ Ok(p!(Is))
+ }
_ => Ok(self.prec_unknown()),
},
+ Token::Word(w) if w.keyword == Keyword::NOTNULL &&
self.supports_notnull_operator() => {
+ Ok(p!(Is))
+ }
Token::Word(w) if w.keyword == Keyword::IS => Ok(p!(Is)),
Token::Word(w) if w.keyword == Keyword::IN => Ok(p!(Between)),
Token::Word(w) if w.keyword == Keyword::BETWEEN => Ok(p!(Between)),
@@ -1122,6 +1130,12 @@ pub trait Dialect: Debug + Any {
) -> bool {
false
}
+
+ /// Returns true if the dialect supports the `x NOTNULL`
+ /// operator expression.
+ fn supports_notnull_operator(&self) -> bool {
+ false
+ }
}
/// This represents the operators for which precedence must be defined
diff --git a/src/dialect/postgresql.rs b/src/dialect/postgresql.rs
index 9cea252c..b12abaaf 100644
--- a/src/dialect/postgresql.rs
+++ b/src/dialect/postgresql.rs
@@ -263,4 +263,10 @@ impl Dialect for PostgreSqlDialect {
fn supports_alter_column_type_using(&self) -> bool {
true
}
+
+ /// 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 {
+ true
+ }
}
diff --git a/src/dialect/sqlite.rs b/src/dialect/sqlite.rs
index 847e0d13..64a8d532 100644
--- a/src/dialect/sqlite.rs
+++ b/src/dialect/sqlite.rs
@@ -110,4 +110,10 @@ impl Dialect for SQLiteDialect {
fn supports_dollar_placeholder(&self) -> bool {
true
}
+
+ /// SQLite supports `NOTNULL` as aliases for `IS NOT NULL`
+ /// See: <https://sqlite.org/syntax/expr.html>
+ fn supports_notnull_operator(&self) -> bool {
+ true
+ }
}
diff --git a/src/keywords.rs b/src/keywords.rs
index 9e689a6d..7781939b 100644
--- a/src/keywords.rs
+++ b/src/keywords.rs
@@ -608,6 +608,7 @@ define_keywords!(
NOT,
NOTHING,
NOTIFY,
+ NOTNULL,
NOWAIT,
NO_WRITE_TO_BINLOG,
NTH_VALUE,
diff --git a/src/parser/mod.rs b/src/parser/mod.rs
index 3bb91311..19acd4b5 100644
--- a/src/parser/mod.rs
+++ b/src/parser/mod.rs
@@ -38,6 +38,7 @@ use crate::ast::*;
use crate::dialect::*;
use crate::keywords::{Keyword, ALL_KEYWORDS};
use crate::tokenizer::*;
+use sqlparser::parser::ParserState::ColumnDefinition;
mod alter;
@@ -275,6 +276,12 @@ enum ParserState {
/// PRIOR expressions while still allowing prior as an identifier name
/// in other contexts.
ConnectBy,
+ /// The state when parsing column definitions. This state prohibits
+ /// NOT NULL as an alias for IS NOT NULL. For example:
+ /// ```sql
+ /// CREATE TABLE foo (abc BIGINT NOT NULL);
+ /// ```
+ ColumnDefinition,
}
/// A SQL Parser
@@ -3578,6 +3585,11 @@ impl<'a> Parser<'a> {
let negated = self.parse_keyword(Keyword::NOT);
let regexp = self.parse_keyword(Keyword::REGEXP);
let rlike = self.parse_keyword(Keyword::RLIKE);
+ let null = if !self.in_column_definition_state() {
+ self.parse_keyword(Keyword::NULL)
+ } else {
+ false
+ };
if regexp || rlike {
Ok(Expr::RLike {
negated,
@@ -3587,6 +3599,8 @@ impl<'a> Parser<'a> {
),
regexp,
})
+ } else if negated && null {
+ Ok(Expr::IsNotNull(Box::new(expr)))
} else if self.parse_keyword(Keyword::IN) {
self.parse_in(expr, negated)
} else if self.parse_keyword(Keyword::BETWEEN) {
@@ -3624,6 +3638,9 @@ impl<'a> Parser<'a> {
self.expected("IN or BETWEEN after NOT",
self.peek_token())
}
}
+ Keyword::NOTNULL if dialect.supports_notnull_operator() => {
+ Ok(Expr::IsNotNull(Box::new(expr)))
+ }
Keyword::MEMBER => {
if self.parse_keyword(Keyword::OF) {
self.expect_token(&Token::LParen)?;
@@ -7742,6 +7759,15 @@ impl<'a> Parser<'a> {
return option;
}
+ self.with_state(
+ ColumnDefinition,
+ |parser| -> Result<Option<ColumnOption>, ParserError> {
+ parser.parse_optional_column_option_inner()
+ },
+ )
+ }
+
+ fn parse_optional_column_option_inner(&mut self) ->
Result<Option<ColumnOption>, ParserError> {
if self.parse_keywords(&[Keyword::CHARACTER, Keyword::SET]) {
Ok(Some(ColumnOption::CharacterSet(
self.parse_object_name(false)?,
@@ -7757,15 +7783,19 @@ impl<'a> Parser<'a> {
} else if self.parse_keyword(Keyword::NULL) {
Ok(Some(ColumnOption::Null))
} else if self.parse_keyword(Keyword::DEFAULT) {
- Ok(Some(ColumnOption::Default(self.parse_expr()?)))
+ Ok(Some(ColumnOption::Default(
+ self.parse_column_option_expr()?,
+ )))
} else if dialect_of!(self is ClickHouseDialect| GenericDialect)
&& self.parse_keyword(Keyword::MATERIALIZED)
{
- Ok(Some(ColumnOption::Materialized(self.parse_expr()?)))
+ Ok(Some(ColumnOption::Materialized(
+ self.parse_column_option_expr()?,
+ )))
} else if dialect_of!(self is ClickHouseDialect| GenericDialect)
&& self.parse_keyword(Keyword::ALIAS)
{
- Ok(Some(ColumnOption::Alias(self.parse_expr()?)))
+ Ok(Some(ColumnOption::Alias(self.parse_column_option_expr()?)))
} else if dialect_of!(self is ClickHouseDialect| GenericDialect)
&& self.parse_keyword(Keyword::EPHEMERAL)
{
@@ -7774,7 +7804,9 @@ impl<'a> Parser<'a> {
if matches!(self.peek_token().token, Token::Comma | Token::RParen)
{
Ok(Some(ColumnOption::Ephemeral(None)))
} else {
- Ok(Some(ColumnOption::Ephemeral(Some(self.parse_expr()?))))
+ Ok(Some(ColumnOption::Ephemeral(Some(
+ self.parse_column_option_expr()?,
+ ))))
}
} else if self.parse_keywords(&[Keyword::PRIMARY, Keyword::KEY]) {
let characteristics = self.parse_constraint_characteristics()?;
@@ -7817,7 +7849,8 @@ impl<'a> Parser<'a> {
}))
} else if self.parse_keyword(Keyword::CHECK) {
self.expect_token(&Token::LParen)?;
- let expr = self.parse_expr()?;
+ // since `CHECK` requires parentheses, we can parse the inner
expression in ParserState::Normal
+ let expr: Expr = self.with_state(ParserState::Normal, |p|
p.parse_expr())?;
self.expect_token(&Token::RParen)?;
Ok(Some(ColumnOption::Check(expr)))
} else if self.parse_keyword(Keyword::AUTO_INCREMENT)
@@ -7851,7 +7884,7 @@ impl<'a> Parser<'a> {
} else if self.parse_keywords(&[Keyword::ON, Keyword::UPDATE])
&& dialect_of!(self is MySqlDialect | GenericDialect)
{
- let expr = self.parse_expr()?;
+ let expr = self.parse_column_option_expr()?;
Ok(Some(ColumnOption::OnUpdate(expr)))
} else if self.parse_keyword(Keyword::GENERATED) {
self.parse_optional_column_option_generated()
@@ -7869,7 +7902,9 @@ impl<'a> Parser<'a> {
} else if self.parse_keyword(Keyword::SRID)
&& dialect_of!(self is MySqlDialect | GenericDialect)
{
- Ok(Some(ColumnOption::Srid(Box::new(self.parse_expr()?))))
+ Ok(Some(ColumnOption::Srid(Box::new(
+ self.parse_column_option_expr()?,
+ ))))
} else if self.parse_keyword(Keyword::IDENTITY)
&& dialect_of!(self is MsSqlDialect | GenericDialect)
{
@@ -7909,6 +7944,31 @@ impl<'a> Parser<'a> {
}
}
+ /// When parsing some column option expressions we need to revert to
[ParserState::Normal] since
+ /// `NOT NULL` is allowed as an alias for `IS NOT NULL`.
+ /// In those cases we use this helper instead of calling
[Parser::parse_expr] directly.
+ ///
+ /// For example, consider these `CREATE TABLE` statements:
+ /// ```sql
+ /// CREATE TABLE foo (abc BOOL DEFAULT (42 NOT NULL) NOT NULL);
+ /// ```
+ /// vs
+ /// ```sql
+ /// CREATE TABLE foo (abc BOOL NOT NULL);
+ /// ```
+ ///
+ /// In the first we should parse the inner portion of `(42 NOT NULL)` as
[Expr::IsNotNull],
+ /// whereas is both statements that trailing `NOT NULL` should only be
parsed as a
+ /// [ColumnOption::NotNull].
+ fn parse_column_option_expr(&mut self) -> Result<Expr, ParserError> {
+ if self.peek_token_ref().token == Token::LParen {
+ let expr: Expr = self.with_state(ParserState::Normal, |p|
p.parse_prefix())?;
+ Ok(expr)
+ } else {
+ Ok(self.parse_expr()?)
+ }
+ }
+
pub(crate) fn parse_tag(&mut self) -> Result<Tag, ParserError> {
let name = self.parse_object_name(false)?;
self.expect_token(&Token::Eq)?;
@@ -7953,7 +8013,7 @@ impl<'a> Parser<'a> {
}))
} else if self.parse_keywords(&[Keyword::ALWAYS, Keyword::AS]) {
if self.expect_token(&Token::LParen).is_ok() {
- let expr = self.parse_expr()?;
+ let expr: Expr = self.with_state(ParserState::Normal, |p|
p.parse_expr())?;
self.expect_token(&Token::RParen)?;
let (gen_as, expr_mode) = if
self.parse_keywords(&[Keyword::STORED]) {
Ok((
@@ -16532,6 +16592,10 @@ impl<'a> Parser<'a> {
Ok(None)
}
}
+
+ pub(crate) fn in_column_definition_state(&self) -> bool {
+ matches!(self.state, ColumnDefinition)
+ }
}
fn maybe_prefixed_expr(expr: Expr, prefix: Option<Ident>) -> Expr {
@@ -17267,4 +17331,28 @@ mod tests {
assert!(Parser::parse_sql(&MySqlDialect {}, sql).is_err());
}
+
+ #[test]
+ fn test_parse_not_null_in_column_options() {
+ let canonical = concat!(
+ "CREATE TABLE foo (",
+ "abc INT DEFAULT (42 IS NOT NULL) NOT NULL,",
+ " def INT,",
+ " def_null BOOL GENERATED ALWAYS AS (def IS NOT NULL) STORED,",
+ " CHECK (abc IS NOT NULL)",
+ ")"
+ );
+ all_dialects().verified_stmt(canonical);
+ all_dialects().one_statement_parses_to(
+ concat!(
+ "CREATE TABLE foo (",
+ "abc INT DEFAULT (42 NOT NULL) NOT NULL,",
+ " def INT,",
+ " def_null BOOL GENERATED ALWAYS AS (def NOT NULL) STORED,",
+ " CHECK (abc NOT NULL)",
+ ")"
+ ),
+ canonical,
+ );
+ }
}
diff --git a/tests/sqlparser_clickhouse.rs b/tests/sqlparser_clickhouse.rs
index 9e5b6ce8..bc1431f9 100644
--- a/tests/sqlparser_clickhouse.rs
+++ b/tests/sqlparser_clickhouse.rs
@@ -1705,6 +1705,30 @@ fn parse_table_sample() {
clickhouse().verified_stmt("SELECT * FROM tbl SAMPLE 1 / 10 OFFSET 1 / 2");
}
+#[test]
+fn test_parse_not_null_in_column_options() {
+ // In addition to DEFAULT and CHECK ClickHouse also supports MATERIALIZED,
all of which
+ // can contain `IS NOT NULL` and thus `NOT NULL` as an alias.
+ let canonical = concat!(
+ "CREATE TABLE foo (",
+ "abc INT DEFAULT (42 IS NOT NULL) NOT NULL,",
+ " not_null BOOL MATERIALIZED (abc IS NOT NULL),",
+ " CHECK (abc IS NOT NULL)",
+ ")",
+ );
+ clickhouse().verified_stmt(canonical);
+ clickhouse().one_statement_parses_to(
+ concat!(
+ "CREATE TABLE foo (",
+ "abc INT DEFAULT (42 NOT NULL) NOT NULL,",
+ " not_null BOOL MATERIALIZED (abc NOT NULL),",
+ " CHECK (abc NOT NULL)",
+ ")",
+ ),
+ canonical,
+ );
+}
+
fn clickhouse() -> TestedDialects {
TestedDialects::new(vec![Box::new(ClickHouseDialect {})])
}
diff --git a/tests/sqlparser_common.rs b/tests/sqlparser_common.rs
index 4183c553..4995b941 100644
--- a/tests/sqlparser_common.rs
+++ b/tests/sqlparser_common.rs
@@ -16040,6 +16040,27 @@ fn parse_create_procedure_with_parameter_modes() {
}
}
+#[test]
+fn parse_not_null() {
+ let _ = all_dialects().expr_parses_to("x NOT NULL", "x IS NOT NULL");
+ let _ = all_dialects().expr_parses_to("NULL NOT NULL", "NULL IS NOT NULL");
+
+ assert_matches!(
+ all_dialects().expr_parses_to("NOT NULL NOT NULL", "NOT NULL IS NOT
NULL"),
+ Expr::UnaryOp {
+ op: UnaryOperator::Not,
+ ..
+ }
+ );
+ assert_matches!(
+ all_dialects().expr_parses_to("NOT x NOT NULL", "NOT x IS NOT NULL"),
+ Expr::UnaryOp {
+ op: UnaryOperator::Not,
+ ..
+ }
+ );
+}
+
#[test]
fn test_select_exclude() {
let dialects = all_dialects_where(|d|
d.supports_select_wildcard_exclude());
@@ -16183,3 +16204,29 @@ fn test_identifier_unicode_start() {
]);
let _ = dialects.verified_stmt(sql);
}
+
+#[test]
+fn parse_notnull() {
+ // Some dialects support `x NOTNULL` as an expression while others consider
+ // `x NOTNULL` like `x AS NOTNULL` and thus consider `NOTNULL` an alias
for x.
+ let notnull_unsupported_dialects = all_dialects_except(|d|
d.supports_notnull_operator());
+ let _ = notnull_unsupported_dialects
+ .verified_only_select_with_canonical("SELECT NULL NOTNULL", "SELECT
NULL AS NOTNULL");
+
+ // Supported dialects consider `x NOTNULL` as an alias for `x IS NOT NULL`
+ let notnull_supported_dialects = all_dialects_where(|d|
d.supports_notnull_operator());
+ let _ = notnull_supported_dialects.expr_parses_to("x NOTNULL", "x IS NOT
NULL");
+
+ // For dialects which support it, `NOT NULL NOTNULL` should
+ // parse as `(NOT (NULL IS NOT NULL))`
+ assert_matches!(
+ notnull_supported_dialects.expr_parses_to("NOT NULL NOTNULL", "NOT
NULL IS NOT NULL"),
+ Expr::UnaryOp {
+ op: UnaryOperator::Not,
+ ..
+ }
+ );
+
+ // for unsupported dialects, parsing should stop at `NOT NULL`
+ notnull_unsupported_dialects.expr_parses_to("NOT NULL NOTNULL", "NOT
NULL");
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
