Repository: tobymao/sqlglot

Issue:
RETURNS NULL ON NULL INPUT for CREATE FUNCTION in Snowflake Dialect is not supported
**Before you file an issue**
- Make sure you specify the "read" dialect eg. `parse_one(sql, read="spark")`
- Make sure you specify the "write" dialect eg. `ast.sql(dialect="duckdb")`
- Check if the issue still exists on main

**Fully reproducible code snippet**
Please include a fully reproducible code snippet or the input sql, dialect, and expected output.
```shell
pip install 'sqlglot@git+ssh://git@github.com/tobymao/sqlglot.git'
```
```python
create_text = """
CREATE OR REPLACE FUNCTION ibis_udfs.public.object_values("obj" OBJECT)
RETURNS ARRAY
LANGUAGE JAVASCRIPT
RETURNS NULL ON NULL INPUT
AS
$$ return Object.values(obj) $$
"""
import sqlglot
sqlglot.parse(create_text, dialect='snowflake')
```

```
sqlglot.errors.ParseError: Required keyword: 'this' missing for <class 'sqlglot.expressions.InputModelProperty'>. Line 6, Col: 2.
```
**Official Documentation**
Please include links to official SQL documentation related to your issue.
https://docs.snowflake.com/en/sql-reference/sql/create-function#optional-parameters


Discussion:

Patch:
diff --git a/sqlglot/dialects/athena.py b/sqlglot/dialects/athena.py
index f2deec88..5fc7e524 100644
--- a/sqlglot/dialects/athena.py
+++ b/sqlglot/dialects/athena.py
@@ -13,6 +13,8 @@ class Athena(Trino):
         }
 
     class Generator(Trino.Generator):
+        WITH_PROPERTIES_PREFIX = "TBLPROPERTIES"
+
         PROPERTIES_LOCATION = {
             **Trino.Generator.PROPERTIES_LOCATION,
             exp.LocationProperty: exp.Properties.Location.POST_SCHEMA,
@@ -32,6 +34,3 @@ class Athena(Trino):
             return (
                 f"{self.property_name(expression, string_key=True)}={self.sql(expression, 'value')}"
             )
-
-        def with_properties(self, properties: exp.Properties) -> str:
-            return self.properties(properties, prefix=self.seg("TBLPROPERTIES"))
diff --git a/sqlglot/dialects/bigquery.py b/sqlglot/dialects/bigquery.py
index 900b1e71..47fb0ce6 100644
--- a/sqlglot/dialects/bigquery.py
+++ b/sqlglot/dialects/bigquery.py
@@ -570,6 +570,7 @@ class BigQuery(Dialect):
         SUPPORTS_TO_NUMBER = False
         NAMED_PLACEHOLDER_TOKEN = "@"
         HEX_FUNC = "TO_HEX"
+        WITH_PROPERTIES_PREFIX = "OPTIONS"
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -907,9 +908,6 @@ class BigQuery(Dialect):
                 self.unsupported("INTERSECT without DISTINCT is not supported in BigQuery")
             return f"INTERSECT{' DISTINCT' if expression.args.get('distinct') else ' ALL'}"
 
-        def with_properties(self, properties: exp.Properties) -> str:
-            return self.properties(properties, prefix=self.seg("OPTIONS"))
-
         def version_sql(self, expression: exp.Version) -> str:
             if expression.name == "TIMESTAMP":
                 expression.set("this", "SYSTEM_TIME")
diff --git a/sqlglot/dialects/hive.py b/sqlglot/dialects/hive.py
index 2f746c09..b12e2002 100644
--- a/sqlglot/dialects/hive.py
+++ b/sqlglot/dialects/hive.py
@@ -254,7 +254,7 @@ class Hive(Dialect):
             "REFRESH": TokenType.REFRESH,
             "TIMESTAMP AS OF": TokenType.TIMESTAMP_SNAPSHOT,
             "VERSION AS OF": TokenType.VERSION_SNAPSHOT,
-            "WITH SERDEPROPERTIES": TokenType.SERDE_PROPERTIES,
+            "SERDEPROPERTIES": TokenType.SERDE_PROPERTIES,
         }
 
         NUMERIC_LITERALS = {
@@ -332,7 +332,7 @@ class Hive(Dialect):
 
         PROPERTY_PARSERS = {
             **parser.Parser.PROPERTY_PARSERS,
-            "WITH SERDEPROPERTIES": lambda self: exp.SerdeProperties(
+            "SERDEPROPERTIES": lambda self: exp.SerdeProperties(
                 expressions=self._parse_wrapped_csv(self._parse_property)
             ),
         }
@@ -443,6 +443,7 @@ class Hive(Dialect):
         LAST_DAY_SUPPORTS_DATE_PART = False
         JSON_PATH_SINGLE_QUOTE_ESCAPE = True
         SUPPORTS_TO_NUMBER = False
+        WITH_PROPERTIES_PREFIX = "TBLPROPERTIES"
 
         EXPRESSIONS_WITHOUT_NESTED_CTES = {
             exp.Insert,
@@ -562,7 +563,6 @@ class Hive(Dialect):
             exp.UnixToTime: _unix_to_time_sql,
             exp.UnixToTimeStr: rename_func("FROM_UNIXTIME"),
             exp.PartitionedByProperty: lambda self, e: f"PARTITIONED BY {self.sql(e, 'this')}",
-            exp.SerdeProperties: lambda self, e: self.properties(e, prefix="WITH SERDEPROPERTIES"),
             exp.NumberToStr: rename_func("FORMAT_NUMBER"),
             exp.National: lambda self, e: self.national_sql(e, prefix=""),
             exp.ClusteredColumnConstraint: lambda self,
@@ -628,9 +628,6 @@ class Hive(Dialect):
                 expression.this.this if isinstance(expression.this, exp.Order) else expression.this,
             )
 
-        def with_properties(self, properties: exp.Properties) -> str:
-            return self.properties(properties, prefix=self.seg("TBLPROPERTIES"))
-
         def datatype_sql(self, expression: exp.DataType) -> str:
             if expression.this in self.PARAMETERIZABLE_TEXT_TYPES and (
                 not expression.expressions or expression.expressions[0].name == "MAX"
@@ -665,3 +662,23 @@ class Hive(Dialect):
                     values.append(e)
 
             return self.func("STRUCT", *values)
+
+        def alterset_sql(self, expression: exp.AlterSet) -> str:
+            exprs = self.expressions(expression, flat=True)
+            exprs = f" {exprs}" if exprs else ""
+            location = self.sql(expression, "location")
+            location = f" LOCATION {location}" if location else ""
+            file_format = self.expressions(expression, key="file_format", flat=True, sep=" ")
+            file_format = f" FILEFORMAT {file_format}" if file_format else ""
+            serde = self.sql(expression, "serde")
+            serde = f" SERDE {serde}" if serde else ""
+            tags = self.expressions(expression, key="tag", flat=True, sep="")
+            tags = f" TAGS {tags}" if tags else ""
+
+            return f"SET{serde}{exprs}{location}{file_format}{tags}"
+
+        def serdeproperties_sql(self, expression: exp.SerdeProperties) -> str:
+            prefix = "WITH " if expression.args.get("with") else ""
+            exprs = self.expressions(expression, flat=True)
+
+            return f"{prefix}SERDEPROPERTIES ({exprs})"
diff --git a/sqlglot/dialects/postgres.py b/sqlglot/dialects/postgres.py
index 953f45d4..03a493e7 100644
--- a/sqlglot/dialects/postgres.py
+++ b/sqlglot/dialects/postgres.py
@@ -609,3 +609,15 @@ class Postgres(Dialect):
             expressions = [f"{self.sql(e)} @@ {this}" for e in expression.expressions]
             sql = " OR ".join(expressions)
             return f"({sql})" if len(expressions) > 1 else sql
+
+        def alterset_sql(self, expression: exp.AlterSet) -> str:
+            exprs = self.expressions(expression, flat=True)
+            exprs = f"({exprs})" if exprs else ""
+
+            access_method = self.sql(expression, "access_method")
+            access_method = f"ACCESS METHOD {access_method}" if access_method else ""
+            tablespace = self.sql(expression, "tablespace")
+            tablespace = f"TABLESPACE {tablespace}" if tablespace else ""
+            option = self.sql(expression, "option")
+
+            return f"SET {exprs}{access_method}{tablespace}{option}"
diff --git a/sqlglot/dialects/redshift.py b/sqlglot/dialects/redshift.py
index 656c5ba6..d3388e7a 100644
--- a/sqlglot/dialects/redshift.py
+++ b/sqlglot/dialects/redshift.py
@@ -142,6 +142,8 @@ class Redshift(Postgres):
         MULTI_ARG_DISTINCT = True
         COPY_PARAMS_ARE_WRAPPED = False
         HEX_FUNC = "TO_HEX"
+        # Redshift doesn't have `WITH` as part of their with_properties so we remove it
+        WITH_PROPERTIES_PREFIX = " "
 
         TYPE_MAPPING = {
             **Postgres.Generator.TYPE_MAPPING,
@@ -375,10 +377,6 @@ class Redshift(Postgres):
             alias = self.expressions(expression.args.get("alias"), key="columns", flat=True)
             return f"{arg} AS {alias}" if alias else arg
 
-        def with_properties(self, properties: exp.Properties) -> str:
-            """Redshift doesn't have `WITH` as part of their with_properties so we remove it"""
-            return self.properties(properties, prefix=" ", suffix="")
-
         def cast_sql(self, expression: exp.Cast, safe_prefix: t.Optional[str] = None) -> str:
             if expression.is_type(exp.DataType.Type.JSON):
                 # Redshift doesn't support a JSON type, so casting to it is treated as a noop
@@ -401,3 +399,13 @@ class Redshift(Postgres):
                     expression.append("expressions", exp.var("MAX"))
 
             return super().datatype_sql(expression)
+
+        def alterset_sql(self, expression: exp.AlterSet) -> str:
+            exprs = self.expressions(expression, flat=True)
+            exprs = f" TABLE PROPERTIES ({exprs})" if exprs else ""
+            location = self.sql(expression, "location")
+            location = f" LOCATION {location}" if location else ""
+            file_format = self.expressions(expression, key="file_format", flat=True, sep=" ")
+            file_format = f" FILE FORMAT {file_format}" if file_format else ""
+
+            return f"SET{exprs}{location}{file_format}"
diff --git a/sqlglot/dialects/snowflake.py b/sqlglot/dialects/snowflake.py
index 12339612..c35b2c3e 100644
--- a/sqlglot/dialects/snowflake.py
+++ b/sqlglot/dialects/snowflake.py
@@ -426,7 +426,6 @@ class Snowflake(Dialect):
 
         ALTER_PARSERS = {
             **parser.Parser.ALTER_PARSERS,
-            "SET": lambda self: self._parse_set(tag=self._match_text_seq("TAG")),
             "UNSET": lambda self: self.expression(
                 exp.Set,
                 tag=self._match_text_seq("TAG"),
@@ -773,6 +772,9 @@ class Snowflake(Dialect):
         COPY_PARAMS_ARE_WRAPPED = False
         COPY_PARAMS_EQ_REQUIRED = True
         STAR_EXCEPT = "EXCLUDE"
+        WITH_PROPERTIES_PREFIX = ""
+        WITH_PROPERTIES_SEP = " "
+        WITH_PROPERTIES_WRAPPED = False
 
         TRANSFORMS = {
             **generator.Generator.TRANSFORMS,
@@ -1042,9 +1044,6 @@ class Snowflake(Dialect):
             this = self.sql(expression, "this")
             return f"SWAP WITH {this}"
 
-        def with_properties(self, properties: exp.Properties) -> str:
-            return self.properties(properties, wrapped=False, prefix=self.seg(""), sep=" ")
-
         def cluster_sql(self, expression: exp.Cluster) -> str:
             return f"CLUSTER BY ({self.expressions(expression, flat=True)})"
 
@@ -1071,3 +1070,15 @@ class Snowflake(Dialect):
                 )
 
             return self.func("APPROX_PERCENTILE", expression.this, expression.args.get("quantile"))
+
+        def alterset_sql(self, expression: exp.AlterSet) -> str:
+            exprs = self.expressions(expression, flat=True)
+            exprs = f" {exprs}" if exprs else ""
+            file_format = self.expressions(expression, key="file_format", flat=True, sep=" ")
+            file_format = f" STAGE_FILE_FORMAT = ({file_format})" if file_format else ""
+            copy_options = self.expressions(expression, key="copy_options", flat=True, sep=" ")
+            copy_options = f" STAGE_COPY_OPTIONS = ({copy_options})" if copy_options else ""
+            tag = self.expressions(expression, key="tag", flat=True)
+            tag = f" TAG {tag}" if tag else ""
+
+            return f"SET{exprs}{file_format}{copy_options}{tag}"
diff --git a/sqlglot/expressions.py b/sqlglot/expressions.py
index eae1bb07..0b28ba54 100644
--- a/sqlglot/expressions.py
+++ b/sqlglot/expressions.py
@@ -1644,6 +1644,20 @@ class AlterSortKey(Expression):
     arg_types = {"this": False, "expressions": False, "compound": False}
 
 
+class AlterSet(Expression):
+    arg_types = {
+        "expressions": False,
+        "option": False,
+        "tablespace": False,
+        "access_method": False,
+        "file_format": False,
+        "copy_options": False,
+        "tag": False,
+        "location": False,
+        "serde": False,
+    }
+
+
 class RenameColumn(Expression):
     arg_types = {"this": True, "to": True, "exists": False}
 
@@ -2506,6 +2520,10 @@ class DataBlocksizeProperty(Property):
     }
 
 
+class DataDeletionProperty(Property):
+    arg_types = {"on": True, "filter_col": False, "retention_period": False}
+
+
 class DefinerProperty(Property):
     arg_types = {"this": True}
 
@@ -2682,7 +2700,11 @@ class RemoteWithConnectionModelProperty(Property):
 
 
 class ReturnsProperty(Property):
-    arg_types = {"this": True, "is_table": False, "table": False}
+    arg_types = {"this": False, "is_table": False, "table": False, "null": False}
+
+
+class StrictProperty(Property):
+    arg_types = {}
 
 
 class RowFormatProperty(Property):
@@ -2728,7 +2750,7 @@ class SchemaCommentProperty(Property):
 
 
 class SerdeProperties(Property):
-    arg_types = {"expressions": True}
+    arg_types = {"expressions": True, "with": False}
 
 
 class SetProperty(Property):
@@ -2797,8 +2819,13 @@ class WithJournalTableProperty(Property):
 
 
 class WithSystemVersioningProperty(Property):
-    # this -> history table name, expression -> data consistency check
-    arg_types = {"this": False, "expression": False}
+    arg_types = {
+        "on": False,
+        "this": False,
+        "data_consistency": False,
+        "retention_period": False,
+        "with": True,
+    }
 
 
 class Properties(Expression):
diff --git a/sqlglot/generator.py b/sqlglot/generator.py
index 4ec5935c..967840e7 100644
--- a/sqlglot/generator.py
+++ b/sqlglot/generator.py
@@ -125,7 +125,9 @@ class Generator(metaclass=_Generator):
         exp.PathColumnConstraint: lambda self, e: f"PATH {self.sql(e, 'this')}",
         exp.RemoteWithConnectionModelProperty: lambda self,
         e: f"REMOTE WITH CONNECTION {self.sql(e, 'this')}",
-        exp.ReturnsProperty: lambda self, e: self.naked_property(e),
+        exp.ReturnsProperty: lambda self, e: (
+            "RETURNS NULL ON NULL INPUT" if e.args.get("null") else self.naked_property(e)
+        ),
         exp.SampleProperty: lambda self, e: f"SAMPLE BY {self.sql(e, 'this')}",
         exp.SetConfigProperty: lambda self, e: self.sql(e, "this"),
         exp.SetProperty: lambda _, e: f"{'MULTI' if e.args.get('multi') else ''}SET",
@@ -135,6 +137,7 @@ class Generator(metaclass=_Generator):
         exp.SqlSecurityProperty: lambda _,
         e: f"SQL SECURITY {'DEFINER' if e.args.get('definer') else 'INVOKER'}",
         exp.StabilityProperty: lambda _, e: e.name,
+        exp.StrictProperty: lambda *_: "STRICT",
         exp.TemporaryProperty: lambda *_: "TEMPORARY",
         exp.TitleColumnConstraint: lambda self, e: f"TITLE {self.sql(e, 'this')}",
         exp.Timestamp: lambda self, e: self.func("TIMESTAMP", e.this, e.expression),
@@ -359,6 +362,11 @@ class Generator(metaclass=_Generator):
     # The HEX function name
     HEX_FUNC = "HEX"
 
+    # The keywords to use when prefixing & separating WITH based properties
+    WITH_PROPERTIES_PREFIX = "WITH"
+    WITH_PROPERTIES_SEP = ", "
+    WITH_PROPERTIES_WRAPPED = True
+
     TYPE_MAPPING = {
         exp.DataType.Type.NCHAR: "CHAR",
         exp.DataType.Type.NVARCHAR: "VARCHAR",
@@ -417,6 +425,7 @@ class Generator(metaclass=_Generator):
         exp.Cluster: exp.Properties.Location.POST_SCHEMA,
         exp.ClusteredByProperty: exp.Properties.Location.POST_SCHEMA,
         exp.DataBlocksizeProperty: exp.Properties.Location.POST_NAME,
+        exp.DataDeletionProperty: exp.Properties.Location.POST_SCHEMA,
         exp.DefinerProperty: exp.Properties.Location.POST_CREATE,
         exp.DictRange: exp.Properties.Location.POST_SCHEMA,
         exp.DictProperty: exp.Properties.Location.POST_SCHEMA,
@@ -470,6 +479,7 @@ class Generator(metaclass=_Generator):
         exp.SqlReadWriteProperty: exp.Properties.Location.POST_SCHEMA,
         exp.SqlSecurityProperty: exp.Properties.Location.POST_CREATE,
         exp.StabilityProperty: exp.Properties.Location.POST_SCHEMA,
+        exp.StrictProperty: exp.Properties.Location.POST_SCHEMA,
         exp.TemporaryProperty: exp.Properties.Location.POST_CREATE,
         exp.ToTableProperty: exp.Properties.Location.POST_SCHEMA,
         exp.TransientProperty: exp.Properties.Location.POST_CREATE,
@@ -958,6 +968,12 @@ class Generator(metaclass=_Generator):
                 )
             )
 
+            if properties_locs.get(exp.Properties.Location.POST_SCHEMA):
+                properties_sql = self.sep() + properties_sql
+            elif not self.pretty:
+                # Standalone POST_WITH properties need a leading whitespace in non-pretty mode
+                properties_sql = f" {properties_sql}"
+
         begin = " BEGIN" if expression.args.get("begin") else ""
         end = " END" if expression.args.get("end") else ""
 
@@ -1343,13 +1359,17 @@ class Generator(metaclass=_Generator):
             elif p_loc == exp.Properties.Location.POST_SCHEMA:
                 root_properties.append(p)
 
-        return self.root_properties(
-            exp.Properties(expressions=root_properties)
-        ) + self.with_properties(exp.Properties(expressions=with_properties))
+        root_props = self.root_properties(exp.Properties(expressions=root_properties))
+        with_props = self.with_properties(exp.Properties(expressions=with_properties))
+
+        if root_props and with_props and not self.pretty:
+            with_props = " " + with_props
+
+        return root_props + with_props
 
     def root_properties(self, properties: exp.Properties) -> str:
         if properties.expressions:
-            return self.sep() + self.expressions(properties, indent=False, sep=" ")
+            return self.expressions(properties, indent=False, sep=" ")
         return ""
 
     def properties(
@@ -1368,7 +1388,12 @@ class Generator(metaclass=_Generator):
         return ""
 
     def with_properties(self, properties: exp.Properties) -> str:
-        return self.properties(properties, prefix=self.seg("WITH"))
+        return self.properties(
+            properties,
+            wrapped=self.WITH_PROPERTIES_WRAPPED,
+            prefix=self.seg(self.WITH_PROPERTIES_PREFIX, sep=""),
+            sep=self.WITH_PROPERTIES_SEP,
+        )
 
     def locate_properties(self, properties: exp.Properties) -> t.DefaultDict:
         properties_locs = defaultdict(list)
@@ -1536,19 +1561,25 @@ class Generator(metaclass=_Generator):
         return f"{data_sql}{statistics_sql}"
 
     def withsystemversioningproperty_sql(self, expression: exp.WithSystemVersioningProperty) -> str:
-        sql = "WITH(SYSTEM_VERSIONING=ON"
-
-        if expression.this:
-            history_table = self.sql(expression, "this")
-            sql = f"{sql}(HISTORY_TABLE={history_table}"
+        this = self.sql(expression, "this")
+        this = f"HISTORY_TABLE={this}" if this else ""
+        data_consistency: t.Optional[str] = self.sql(expression, "data_consistency")
+        data_consistency = (
+            f"DATA_CONSISTENCY_CHECK={data_consistency}" if data_consistency else None
+        )
+        retention_period: t.Optional[str] = self.sql(expression, "retention_period")
+        retention_period = (
+            f"HISTORY_RETENTION_PERIOD={retention_period}" if retention_period else None
+        )
 
-            if expression.expression:
-                data_consistency_check = self.sql(expression, "expression")
-                sql = f"{sql}, DATA_CONSISTENCY_CHECK={data_consistency_check}"
+        if this:
+            on_sql = self.func("ON", this, data_consistency, retention_period)
+        else:
+            on_sql = "ON" if expression.args.get("on") else "OFF"
 
-            sql = f"{sql})"
+        sql = f"SYSTEM_VERSIONING={on_sql}"
 
-        return f"{sql})"
+        return f"WITH({sql})" if expression.args.get("with") else sql
 
     def insert_sql(self, expression: exp.Insert) -> str:
         hint = self.sql(expression, "hint")
@@ -3031,6 +3062,10 @@ class Generator(metaclass=_Generator):
         new_column = self.sql(expression, "to")
         return f"RENAME COLUMN{exists} {old_column} TO {new_column}"
 
+    def alterset_sql(self, expression: exp.AlterSet) -> str:
+        exprs = self.expressions(expression, flat=True)
+        return f"SET {exprs}"
+
     def altertable_sql(self, expression: exp.AlterTable) -> str:
         actions = expression.args["actions"]
 
@@ -3889,3 +3924,15 @@ class Generator(metaclass=_Generator):
 
     def semicolon_sql(self, expression: exp.Semicolon) -> str:
         return ""
+
+    def datadeletionproperty_sql(self, expression: exp.DataDeletionProperty) -> str:
+        on_sql = "ON" if expression.args.get("on") else "OFF"
+        filter_col: t.Optional[str] = self.sql(expression, "filter_column")
+        filter_col = f"FILTER_COLUMN={filter_col}" if filter_col else None
+        retention_period: t.Optional[str] = self.sql(expression, "retention_period")
+        retention_period = f"RETENTION_PERIOD={retention_period}" if retention_period else None
+
+        if filter_col or retention_period:
+            on_sql = self.func("ON", filter_col, retention_period)
+
+        return f"DATA_DELETION={on_sql}"
diff --git a/sqlglot/parser.py b/sqlglot/parser.py
index 56c8e1b6..67ffd8f5 100644
--- a/sqlglot/parser.py
+++ b/sqlglot/parser.py
@@ -790,6 +790,7 @@ class Parser(metaclass=_Parser):
         "CONTAINS": lambda self: self._parse_contains_property(),
         "COPY": lambda self: self._parse_copy_property(),
         "DATABLOCKSIZE": lambda self, **kwargs: self._parse_datablocksize(**kwargs),
+        "DATA_DELETION": lambda self: self._parse_data_deletion_property(),
         "DEFINER": lambda self: self._parse_definer(),
         "DETERMINISTIC": lambda self: self.expression(
             exp.StabilityProperty, this=exp.Literal.string("IMMUTABLE")
@@ -838,6 +839,7 @@ class Parser(metaclass=_Parser):
         "READS": lambda self: self._parse_reads_property(),
         "REMOTE": lambda self: self._parse_remote_with_connection(),
         "RETURNS": lambda self: self._parse_returns(),
+        "STRICT": lambda self: self.expression(exp.StrictProperty),
         "ROW": lambda self: self._parse_row(),
         "ROW_FORMAT": lambda self: self._parse_property_assignment(exp.RowFormatProperty),
         "SAMPLE": lambda self: self.expression(
@@ -942,6 +944,7 @@ class Parser(metaclass=_Parser):
         "DELETE": lambda self: self.expression(exp.Delete, where=self._parse_where()),
         "DROP": lambda self: self._parse_alter_table_drop(),
         "RENAME": lambda self: self._parse_alter_table_rename(),
+        "SET": lambda self: self._parse_alter_table_set(),
     }
 
     ALTER_ALTER_PARSERS = {
@@ -1883,23 +1886,65 @@ class Parser(metaclass=_Parser):
 
         return self.expression(exp.StabilityProperty, this=exp.Literal.string("VOLATILE"))
 
-    def _parse_system_versioning_property(self) -> exp.WithSystemVersioningProperty:
-        self._match_pair(TokenType.EQ, TokenType.ON)
+    def _parse_retention_period(self) -> exp.Var:
+        # Parse TSQL's HISTORY_RETENTION_PERIOD: {INFINITE | <number> DAY | DAYS | MONTH ...}
+        number = self._parse_number()
+        number_str = f"{number} " if number else ""
+        unit = self._parse_var(any_token=True)
+        return exp.var(f"{number_str}{unit}")
 
-        prop = self.expression(exp.WithSystemVersioningProperty)
+    def _parse_system_versioning_property(
+        self, with_: bool = False
+    ) -> exp.WithSystemVersioningProperty:
+        self._match(TokenType.EQ)
+        prop = self.expression(
+            exp.WithSystemVersioningProperty,
+            **{  # type: ignore
+                "on": True,
+                "with": with_,
+            },
+        )
+
+        if self._match_text_seq("OFF"):
+            prop.set("on", False)
+            return prop
+
+        self._match(TokenType.ON)
         if self._match(TokenType.L_PAREN):
-            self._match_text_seq("HISTORY_TABLE", "=")
-            prop.set("this", self._parse_table_parts())
+            while self._curr and not self._match(TokenType.R_PAREN):
+                if self._match_text_seq("HISTORY_TABLE", "="):
+                    prop.set("this", self._parse_table_parts())
+                elif self._match_text_seq("DATA_CONSISTENCY_CHECK", "="):
+                    prop.set("data_consistency", self._advance_any() and self._prev.text.upper())
+                elif self._match_text_seq("HISTORY_RETENTION_PERIOD", "="):
+                    prop.set("retention_period", self._parse_retention_period())
 
-            if self._match(TokenType.COMMA):
-                self._match_text_seq("DATA_CONSISTENCY_CHECK", "=")
-                prop.set("expression", self._advance_any() and self._prev.text.upper())
+                self._match(TokenType.COMMA)
 
-            self._match_r_paren()
+        return prop
+
+    def _parse_data_deletion_property(self) -> exp.DataDeletionProperty:
+        self._match(TokenType.EQ)
+        on = self._match_text_seq("ON") or not self._match_text_seq("OFF")
+        prop = self.expression(exp.DataDeletionProperty, on=on)
+
+        if self._match(TokenType.L_PAREN):
+            while self._curr and not self._match(TokenType.R_PAREN):
+                if self._match_text_seq("FILTER_COLUMN", "="):
+                    prop.set("filter_column", self._parse_column())
+                elif self._match_text_seq("RETENTION_PERIOD", "="):
+                    prop.set("retention_period", self._parse_retention_period())
+
+                self._match(TokenType.COMMA)
 
         return prop
 
     def _parse_with_property(self) -> t.Optional[exp.Expression] | t.List[exp.Expression]:
+        if self._match_text_seq("(", "SYSTEM_VERSIONING"):
+            prop = self._parse_system_versioning_property(with_=True)
+            self._match_r_paren()
+            return prop
+
         if self._match(TokenType.L_PAREN, advance=False):
             return self._parse_wrapped_properties()
 
@@ -1914,6 +1959,9 @@ class Parser(metaclass=_Parser):
         elif self._match_text_seq("NO", "DATA"):
             return self._parse_withdata(no=True)
 
+        if self._match(TokenType.SERDE_PROPERTIES, advance=False):
+            return self._parse_serde_properties(with_=True)
+
         if not self._next:
             return None
 
@@ -2262,6 +2310,7 @@ class Parser(metaclass=_Parser):
 
     def _parse_returns(self) -> exp.ReturnsProperty:
         value: t.Optional[exp.Expression]
+        null = None
         is_table = self._match(TokenType.TABLE)
 
         if is_table:
@@ -2275,10 +2324,13 @@ class Parser(metaclass=_Parser):
                     self.raise_error("Expecting >")
             else:
                 value = self._parse_schema(exp.var("TABLE"))
+        elif self._match_text_seq("NULL", "ON", "NULL", "INPUT"):
+            null = True
+            value = None
         else:
             value = self._parse_types()
 
-        return self.expression(exp.ReturnsProperty, this=value, is_table=is_table)
+        return self.expression(exp.ReturnsProperty, this=value, is_table=is_table, null=null)
 
     def _parse_describe(self) -> exp.Describe:
         kind = self._match_set(self.CREATABLES) and self._prev.text
@@ -2401,6 +2453,21 @@ class Parser(metaclass=_Parser):
             return None
         return self._parse_row_format()
 
+    def _parse_serde_properties(self, with_: bool = False) -> t.Optional[exp.SerdeProperties]:
+        index = self._index
+        with_ = with_ or self._match_text_seq("WITH")
+
+        if not self._match(TokenType.SERDE_PROPERTIES):
+            self._retreat(index)
+            return None
+        return self.expression(
+            exp.SerdeProperties,
+            **{  # type: ignore
+                "expressions": self._parse_wrapped_properties(),
+                "with": with_,
+            },
+        )
+
     def _parse_row_format(
         self, match_row: bool = False
     ) -> t.Optional[exp.RowFormatSerdeProperty | exp.RowFormatDelimitedProperty]:
@@ -2410,11 +2477,7 @@ class Parser(metaclass=_Parser):
         if self._match_text_seq("SERDE"):
             this = self._parse_string()
 
-            serde_properties = None
-            if self._match(TokenType.SERDE_PROPERTIES):
-                serde_properties = self.expression(
-                    exp.SerdeProperties, expressions=self._parse_wrapped_properties()
-                )
+            serde_properties = self._parse_serde_properties()
 
             return self.expression(
                 exp.RowFormatSerdeProperty, this=this, serde_properties=serde_properties
@@ -5958,6 +6021,41 @@ class Parser(metaclass=_Parser):
         self._match_text_seq("TO")
         return self.expression(exp.RenameTable, this=self._parse_table(schema=True))
 
+    def _parse_alter_table_set(self) -> exp.AlterSet:
+        alter_set = self.expression(exp.AlterSet)
+
+        if self._match(TokenType.L_PAREN, advance=False) or self._match_text_seq(
+            "TABLE", "PROPERTIES"
+        ):
+            alter_set.set("expressions", self._parse_wrapped_csv(self._parse_conjunction))
+        elif self._match_text_seq("FILESTREAM_ON", advance=False):
+            alter_set.set("expressions", [self._parse_conjunction()])
+        elif self._match_texts(("LOGGED", "UNLOGGED")):
+            alter_set.set("option", exp.var(self._prev.text.upper()))
+        elif self._match_text_seq("WITHOUT") and self._match_texts(("CLUSTER", "OIDS")):
+            alter_set.set("option", exp.var(f"WITHOUT {self._prev.text.upper()}"))
+        elif self._match_text_seq("LOCATION"):
+            alter_set.set("location", self._parse_field())
+        elif self._match_text_seq("ACCESS", "METHOD"):
+            alter_set.set("access_method", self._parse_field())
+        elif self._match_text_seq("TABLESPACE"):
+            alter_set.set("tablespace", self._parse_field())
+        elif self._match_text_seq("FILE", "FORMAT") or self._match_text_seq("FILEFORMAT"):
+            alter_set.set("file_format", [self._parse_field()])
+        elif self._match_text_seq("STAGE_FILE_FORMAT"):
+            alter_set.set("file_format", self._parse_wrapped_options())
+        elif self._match_text_seq("STAGE_COPY_OPTIONS"):
+            alter_set.set("copy_options", self._parse_wrapped_options())
+        elif self._match_text_seq("TAG") or self._match_text_seq("TAGS"):
+            alter_set.set("tag", self._parse_csv(self._parse_conjunction))
+        else:
+            if self._match_text_seq("SERDE"):
+                alter_set.set("serde", self._parse_field())
+
+            alter_set.set("expressions", [self._parse_properties()])
+
+        return alter_set
+
     def _parse_alter(self) -> exp.AlterTable | exp.Command:
         start = self._prev
 


Test patch:
diff --git a/tests/dialects/test_postgres.py b/tests/dialects/test_postgres.py
index 6b6117ea..a8a6c12e 100644
--- a/tests/dialects/test_postgres.py
+++ b/tests/dialects/test_postgres.py
@@ -733,6 +733,13 @@ class TestPostgres(Validator):
         self.validate_identity("TRUNCATE TABLE t1 RESTRICT")
         self.validate_identity("TRUNCATE TABLE t1 CONTINUE IDENTITY CASCADE")
         self.validate_identity("TRUNCATE TABLE t1 RESTART IDENTITY RESTRICT")
+        self.validate_identity("ALTER TABLE t1 SET LOGGED")
+        self.validate_identity("ALTER TABLE t1 SET UNLOGGED")
+        self.validate_identity("ALTER TABLE t1 SET WITHOUT CLUSTER")
+        self.validate_identity("ALTER TABLE t1 SET WITHOUT OIDS")
+        self.validate_identity("ALTER TABLE t1 SET ACCESS METHOD method")
+        self.validate_identity("ALTER TABLE t1 SET TABLESPACE tablespace")
+        self.validate_identity("ALTER TABLE t1 SET (fillfactor = 5, autovacuum_enabled = TRUE)")
         self.validate_identity(
             "CREATE TABLE t (vid INT NOT NULL, CONSTRAINT ht_vid_nid_fid_idx EXCLUDE (INT4RANGE(vid, nid) WITH &&, INT4RANGE(fid, fid, '[]') WITH &&))"
         )
@@ -798,23 +805,21 @@ class TestPostgres(Validator):
             "CREATE TABLE test (x TIMESTAMP[][])",
         )
         self.validate_identity(
-            "CREATE FUNCTION add(INT, INT) RETURNS INT SET search_path TO 'public' AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE",
-            check_command_warning=True,
+            "CREATE FUNCTION add(integer, integer) RETURNS INT LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT AS 'select $1 + $2;'",
         )
         self.validate_identity(
-            "CREATE FUNCTION x(INT) RETURNS INT SET foo FROM CURRENT",
-            check_command_warning=True,
+            "CREATE FUNCTION add(integer, integer) RETURNS INT LANGUAGE SQL IMMUTABLE STRICT AS 'select $1 + $2;'"
         )
         self.validate_identity(
-            "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT",
+            "CREATE FUNCTION add(INT, INT) RETURNS INT SET search_path TO 'public' AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE",
             check_command_warning=True,
         )
         self.validate_identity(
-            "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE CALLED ON NULL INPUT",
+            "CREATE FUNCTION x(INT) RETURNS INT SET foo FROM CURRENT",
             check_command_warning=True,
         )
         self.validate_identity(
-            "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE STRICT",
+            "CREATE FUNCTION add(integer, integer) RETURNS integer AS 'select $1 + $2;' LANGUAGE SQL IMMUTABLE CALLED ON NULL INPUT",
             check_command_warning=True,
         )
         self.validate_identity(
diff --git a/tests/dialects/test_redshift.py b/tests/dialects/test_redshift.py
index 2b29a94b..3925e325 100644
--- a/tests/dialects/test_redshift.py
+++ b/tests/dialects/test_redshift.py
@@ -509,6 +509,9 @@ FROM (
         self.validate_identity("ALTER TABLE t ALTER DISTSTYLE EVEN")
         self.validate_identity("ALTER TABLE t ALTER DISTSTYLE AUTO")
         self.validate_identity("ALTER TABLE t ALTER DISTSTYLE KEY DISTKEY c")
+        self.validate_identity("ALTER TABLE t SET TABLE PROPERTIES ('a' = '5', 'b' = 'c')")
+        self.validate_identity("ALTER TABLE t SET LOCATION 's3://bucket/folder/'")
+        self.validate_identity("ALTER TABLE t SET FILE FORMAT AVRO")
         self.validate_identity(
             "ALTER TABLE t ALTER DISTKEY c",
             "ALTER TABLE t ALTER DISTSTYLE KEY DISTKEY c",
diff --git a/tests/dialects/test_snowflake.py b/tests/dialects/test_snowflake.py
index e8682745..d3c47af4 100644
--- a/tests/dialects/test_snowflake.py
+++ b/tests/dialects/test_snowflake.py
@@ -45,7 +45,6 @@ WHERE
 
         self.validate_identity("ALTER TABLE table1 CLUSTER BY (name DESC)")
         self.validate_identity("SELECT rename, replace")
-        self.validate_identity("ALTER TABLE table1 SET TAG foo.bar = 'baz'")
         self.validate_identity("SELECT TIMEADD(HOUR, 2, CAST('09:05:03' AS TIME))")
         self.validate_identity("SELECT CAST(OBJECT_CONSTRUCT('a', 1) AS MAP(VARCHAR, INT))")
         self.validate_identity("SELECT CAST(OBJECT_CONSTRUCT('a', 1) AS OBJECT(a CHAR NOT NULL))")
@@ -89,11 +88,6 @@ WHERE
         self.validate_identity("a$b")  # valid snowflake identifier
         self.validate_identity("SELECT REGEXP_LIKE(a, b, c)")
         self.validate_identity("CREATE TABLE foo (bar FLOAT AUTOINCREMENT START 0 INCREMENT 1)")
-        self.validate_identity("ALTER TABLE IF EXISTS foo SET TAG a = 'a', b = 'b', c = 'c'")
-        self.validate_identity("ALTER TABLE foo UNSET TAG a, b, c")
-        self.validate_identity("ALTER TABLE foo SET COMMENT = 'bar'")
-        self.validate_identity("ALTER TABLE foo SET CHANGE_TRACKING = FALSE")
-        self.validate_identity("ALTER TABLE foo UNSET DATA_RETENTION_TIME_IN_DAYS, CHANGE_TRACKING")
         self.validate_identity("COMMENT IF EXISTS ON TABLE foo IS 'bar'")
         self.validate_identity("SELECT CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', col)")
         self.validate_identity("ALTER TABLE a SWAP WITH b")
@@ -1228,6 +1222,12 @@ WHERE
         self.validate_identity(
             "CREATE ICEBERG TABLE my_iceberg_table (amount ARRAY(INT)) CATALOG='SNOWFLAKE' EXTERNAL_VOLUME='my_external_volume' BASE_LOCATION='my/relative/path/from/extvol'"
         )
+        self.validate_identity(
+            """CREATE OR REPLACE FUNCTION ibis_udfs.public.object_values("obj" OBJECT) RETURNS ARRAY LANGUAGE JAVASCRIPT RETURNS NULL ON NULL INPUT AS ' return Object.values(obj) '"""
+        )
+        self.validate_identity(
+            """CREATE OR REPLACE FUNCTION ibis_udfs.public.object_values("obj" OBJECT) RETURNS ARRAY LANGUAGE JAVASCRIPT STRICT AS ' return Object.values(obj) '"""
+        )
         self.validate_identity(
             "CREATE OR REPLACE FUNCTION my_udf(location OBJECT(city VARCHAR, zipcode DECIMAL(38, 0), val ARRAY(BOOLEAN))) RETURNS VARCHAR AS $$ SELECT 'foo' $$",
             "CREATE OR REPLACE FUNCTION my_udf(location OBJECT(city VARCHAR, zipcode DECIMAL(38, 0), val ARRAY(BOOLEAN))) RETURNS VARCHAR AS ' SELECT \\'foo\\' '",
@@ -1940,3 +1940,20 @@ SINGLE = TRUE""",
         self.validate_identity("SELECT $1:a.b", "SELECT GET_PATH($1, 'a.b')")
         self.validate_identity("SELECT t.$23:a.b", "SELECT GET_PATH(t.$23, 'a.b')")
         self.validate_identity("SELECT t.$17:a[0].b[0].c", "SELECT GET_PATH(t.$17, 'a[0].b[0].c')")
+
+    def test_alter_set_unset(self):
+        self.validate_identity("ALTER TABLE tbl SET DATA_RETENTION_TIME_IN_DAYS=1")
+        self.validate_identity("ALTER TABLE tbl SET DEFAULT_DDL_COLLATION='test'")
+        self.validate_identity("ALTER TABLE foo SET COMMENT='bar'")
+        self.validate_identity("ALTER TABLE foo SET CHANGE_TRACKING=FALSE")
+        self.validate_identity("ALTER TABLE table1 SET TAG foo.bar = 'baz'")
+        self.validate_identity("ALTER TABLE IF EXISTS foo SET TAG a = 'a', b = 'b', c = 'c'")
+        self.validate_identity(
+            """ALTER TABLE tbl SET STAGE_FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = '|' NULL_IF = () FIELD_OPTIONALLY_ENCLOSED_BY = '"' TIMESTAMP_FORMAT = 'TZHTZM YYYY-MM-DD HH24:MI:SS.FF9' DATE_FORMAT = 'TZHTZM YYYY-MM-DD HH24:MI:SS.FF9' BINARY_FORMAT = BASE64)""",
+        )
+        self.validate_identity(
+            """ALTER TABLE tbl SET STAGE_COPY_OPTIONS = (ON_ERROR = SKIP_FILE SIZE_LIMIT = 5 PURGE = TRUE MATCH_BY_COLUMN_NAME = CASE_SENSITIVE)"""
+        )
+
+        self.validate_identity("ALTER TABLE foo UNSET TAG a, b, c")
+        self.validate_identity("ALTER TABLE foo UNSET DATA_RETENTION_TIME_IN_DAYS, CHANGE_TRACKING")
diff --git a/tests/dialects/test_tsql.py b/tests/dialects/test_tsql.py
index ac28cb9d..45a46579 100644
--- a/tests/dialects/test_tsql.py
+++ b/tests/dialects/test_tsql.py
@@ -192,16 +192,9 @@ class TestTSQL(Validator):
         )
 
         self.validate_all(
-            """
-            CREATE TABLE x(
-                [zip_cd] [varchar](5) NULL NOT FOR REPLICATION,
-                [zip_cd_mkey] [varchar](5) NOT NULL,
-                CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC)
-                WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF) ON [INDEX]
-            ) ON [SECONDARY]
-            """,
+            """CREATE TABLE x ([zip_cd] VARCHAR(5) NULL NOT FOR REPLICATION, [zip_cd_mkey] VARCHAR(5) NOT NULL, CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC) WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON [INDEX]) ON [SECONDARY]""",
             write={
-                "tsql": "CREATE TABLE x ([zip_cd] VARCHAR(5) NULL NOT FOR REPLICATION, [zip_cd_mkey] VARCHAR(5) NOT NULL, CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC)  WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON [INDEX]) ON [SECONDARY]",
+                "tsql": "CREATE TABLE x ([zip_cd] VARCHAR(5) NULL NOT FOR REPLICATION, [zip_cd_mkey] VARCHAR(5) NOT NULL, CONSTRAINT [pk_mytable] PRIMARY KEY CLUSTERED ([zip_cd_mkey] ASC) WITH (PAD_INDEX=ON, STATISTICS_NORECOMPUTE=OFF) ON [INDEX]) ON [SECONDARY]",
                 "spark2": "CREATE TABLE x (`zip_cd` VARCHAR(5), `zip_cd_mkey` VARCHAR(5) NOT NULL, CONSTRAINT `pk_mytable` PRIMARY KEY (`zip_cd_mkey`))",
             },
         )
@@ -770,18 +763,32 @@ class TestTSQL(Validator):
             expression.sql(dialect="tsql"), "ALTER TABLE dbo.DocExe DROP CONSTRAINT FK_Column_B"
         )
 
-        for clusterd_keyword in ("CLUSTERED", "NONCLUSTERED"):
+        for clustered_keyword in ("CLUSTERED", "NONCLUSTERED"):
             self.validate_identity(
                 'CREATE TABLE "dbo"."benchmark" ('
                 '"name" CHAR(7) NOT NULL, '
                 '"internal_id" VARCHAR(10) NOT NULL, '
-                f'UNIQUE {clusterd_keyword} ("internal_id" ASC))',
+                f'UNIQUE {clustered_keyword} ("internal_id" ASC))',
                 "CREATE TABLE [dbo].[benchmark] ("
                 "[name] CHAR(7) NOT NULL, "
                 "[internal_id] VARCHAR(10) NOT NULL, "
-                f"UNIQUE {clusterd_keyword} ([internal_id] ASC))",
+                f"UNIQUE {clustered_keyword} ([internal_id] ASC))",
             )
 
+        self.validate_identity(
+            "ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, DATA_CONSISTENCY_CHECK=OFF, HISTORY_RETENTION_PERIOD=5 DAYS)"
+        )
+        self.validate_identity(
+            "ALTER TABLE tbl SET SYSTEM_VERSIONING=ON(HISTORY_TABLE=db.tbl, HISTORY_RETENTION_PERIOD=INFINITE)"
+        )
+        self.validate_identity("ALTER TABLE tbl SET SYSTEM_VERSIONING=OFF")
+        self.validate_identity("ALTER TABLE tbl SET FILESTREAM_ON = 'test'")
+        self.validate_identity(
+            "ALTER TABLE tbl SET DATA_DELETION=ON(FILTER_COLUMN=col, RETENTION_PERIOD=5 MONTHS)"
+        )
+        self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=ON")
+        self.validate_identity("ALTER TABLE tbl SET DATA_DELETION=OFF")
+
         self.validate_identity(
             "CREATE PROCEDURE foo AS BEGIN DELETE FROM bla WHERE foo < CURRENT_TIMESTAMP - 7 END",
             "CREATE PROCEDURE foo AS BEGIN DELETE FROM bla WHERE foo < GETDATE() - 7 END",
diff --git a/tests/test_parser.py b/tests/test_parser.py
index 35d07a66..2cefc076 100644
--- a/tests/test_parser.py
+++ b/tests/test_parser.py
@@ -856,6 +856,26 @@ class TestParser(unittest.TestCase):
             with self.subTest(dialect):
                 self.assertEqual(parse_one(sql, dialect=dialect).sql(dialect=dialect), sql)
 
+    def test_alter_set(self):
+        sqls = [
+            "ALTER TABLE tbl SET TBLPROPERTIES ('x'='1', 'Z'='2')",
+            "ALTER TABLE tbl SET SERDE 'test' WITH SERDEPROPERTIES ('k'='v', 'kay'='vee')",
+            "ALTER TABLE tbl SET SERDEPROPERTIES ('k'='v', 'kay'='vee')",
+            "ALTER TABLE tbl SET LOCATION 'new_location'",
+            "ALTER TABLE tbl SET FILEFORMAT file_format",
+            "ALTER TABLE tbl SET TAGS ('tag1' = 't1', 'tag2' = 't2')",
+        ]
+
+        for dialect in (
+            "hive",
+            "spark2",
+            "spark",
+            "databricks",
+        ):
+            for sql in sqls:
+                with self.subTest(f"Testing query '{sql}' for dialect {dialect}"):
+                    self.assertEqual(parse_one(sql, dialect=dialect).sql(dialect=dialect), sql)
+
     def test_distinct_from(self):
         self.assertIsInstance(parse_one("a IS DISTINCT FROM b OR c IS DISTINCT FROM d"), exp.Or)