RETURNS NULL ON NULL INPUT for CREATE FUNCTION in Snowflake Dialect is not supported

[krzysztof-kwitt]

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.

pip install 'sqlglot@git+ssh://git@github.com/tobymao/sqlglot.git'

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

[krzysztof-kwitt]

STRICT keyword (alias for RETURNS NULL ON NULL INPUT) is also not supported correctly;

>>> create_text = """
... CREATE OR REPLACE FUNCTION ibis_udfs.public.object_values("obj" OBJECT)
... RETURNS ARRAY
... LANGUAGE JAVASCRIPT
... STRICT
... AS
... $$ return Object.values(obj) $$
... """
>>> expr = sqlglot.parse_one(create_text, dialect='snowflake')
>>> expr
Create(
  this=UserDefinedFunction(
    this=Dot(
      this=Dot(
        this=Identifier(this=ibis_udfs, quoted=False),
        expression=Identifier(this=public, quoted=False)),
      expression=Identifier(this=object_values, quoted=False)),
    expressions=[
      ColumnDef(
        this=Identifier(this=obj, quoted=True),
        kind=DataType(this=Type.OBJECT, nested=True))],
    wrapped=True),
  kind=FUNCTION,
  replace=True,
  expression=Alias(
    this=Column(
      this=Identifier(this=STRICT, quoted=False)),
    alias=Identifier(this=return Object.values(obj) , quoted=False)),
  properties=Properties(
    expressions=[
      ReturnsProperty(
        this=DataType(this=Type.ARRAY, nested=True)),
      LanguageProperty(
        this=Var(this=JAVASCRIPT))]))
>>> print(expr)
CREATE OR REPLACE FUNCTION ibis_udfs.public.object_values("obj" OBJECT) RETURNS ARRAY LANGUAGE JAVASCRIPT AS STRICT AS  return Object.values(obj)

CREATE OR REPLACE FUNCTION ibis_udfs.public.object_values("obj" OBJECT) RETURNS ARRAY LANGUAGE JAVASCRIPT AS STRICT AS  return Object.values(obj)

raises the error:

Syntax error: unexpected 'STRICT'. (line 1)

[tobymao]

this isn’t high priority for us, feel free to submit a pr for this.

we welcome well documented, tested, and conforming prs, but won’t have time to implement this one for you

[krzysztof-kwitt]

@georgesittas Thank you!