SQL Lint Tests fail if used SYSTEM$GET_PREDECESSOR_RETURN_VALUE

Question

SQL Lint Pipeline fails when I'm trying to publish tasks containing SYSTEM$GET_PREDECESSOR_RETURN_VALUE lines, altough they pass the SQL fluff tests locally on VS Code. Any idea how can i fix these without adding --noqa for that line ?

My code :

...
WHEN SYSTEM$GET_PREDECESSOR_RETURN_VALUE('previous_task_name') = 'TRUE'
AS
    CALL ...

The error :

Found unparsable section: Line X position Y...

If I remove the "When" line above, it passes.

Tried --noqa and it works, but I need my code to pass the SQL lint tests.

Answer 1

You need to give us more context to fix this particular error, but a way to reproduce this error is creating a file with the following contents:

-- test.sql
SELECT SYSTEM$TASK_DEPENDENTS_ENABLE ('mydb.myschema.mytask');

Then this would fail with the same error:

sqlfluff lint test.sql --dialect mysql

Which returns:

Line 2, Position 14: Found unparsable section:
                       | "$TASK_DEPENDENTS_ENABLE ('mydb.myschema...."

Which is easily fixed by changing the dialect to Snowflake:

sqlfluff lint test.sql --dialect snowflake

Which returns:

All Finished  !

As we don't know what version of sqlfluff you are using, or how you are calling it, or a complete sample of the code is tripping on - we can't go further for the purposes of this question. Please post a new one with more details if this doesn't fix the larger problem.

Answer 2

The function in question can only be used in context of a task

select SYSTEM$GET_PREDECESSOR_RETURN_VALUE('a');
-- Function SYSTEM$GET_PREDECESSOR_RETURN_VALUE must be called from within a task.

CREATE TASK:

WHEN boolean_expr

• SYSTEM$GET_PREDECESSOR_RETURN_VALUE is supported for evaluation in the SQL expression.

---

Sample test.sql:

CREATE TASK mytask1
--WHEN SYSTEM$GET_PREDECESSOR_RETURN_VALUE('previous_task_name') = 'TRUE'
AS
    CALL MY_SP();

Sqlfluff:

sqlfluff lint test.sql --dialect snowflake
All Finished  !

---

Uncomment WHEN:

CREATE TASK mytask1
WHEN SYSTEM$GET_PREDECESSOR_RETURN_VALUE('previous_task_name') = 'TRUE'
AS
    CALL MY_SP();

Call:

== [test.sql] FAIL
L:   1 | P:   1 |  PRS | Line 1, Position 1: Found unparsable section: 'CREATE
                       | TASK mytask1\nWHEN SYSTEM$GET_PRED...'
WARNING: Parsing errors found and dialect is set to 'snowflake'. 

---

It seems that in context of CREATE TASK WHEN the function is not properly recognized:

sqlfluff parse test.sql --dialect snowflake
[L:  1, P:  1]      |file:
[L:  1, P:  1]      |    unparsable:                                               !! Expected: "<Delimited: [<Ref: 'StatementSegment'>]>"
[L:  1, P:  1]      |        word:                                                 'CREATE'
[L:  1, P:  7]      |        whitespace:                                           ' '
[L:  1, P:  8]      |        word:                                                 'TASK'
[L:  1, P: 12]      |        whitespace:                                           ' '
[L:  1, P: 13]      |        word:                                                 'mytask1'
[L:  1, P: 20]      |        newline:                                              '\n'
[L:  2, P:  1]      |        word:                                                 'WHEN'
[L:  2, P:  5]      |        whitespace:                                           ' '
[L:  2, P:  6]      |        inline_dollar_sign:                                   'SYSTEM$GET_PREDECESSOR_RETURN_VALUE'
[L:  2, P: 41]      |        whitespace:                                           ' '
[L:  2, P: 42]      |        start_bracket:                                        '('
[L:  2, P: 43]      |        single_quote:                                         "'previous_task_name'"
[L:  2, P: 63]      |        end_bracket:                                          ')'
[L:  2, P: 64]      |        whitespace:                                           ' '
[L:  2, P: 65]      |        equals:                                               '='
[L:  2, P: 66]      |        whitespace:                                           ' '
[L:  2, P: 67]      |        single_quote:                                         "'TRUE'"
[L:  2, P: 73]      |        newline:                                              '\n'
[L:  3, P:  1]      |        word:                                                 'AS'
[L:  3, P:  3]      |        newline:                                              '\n'
[L:  4, P:  1]      |        whitespace:                                           '    '
[L:  4, P:  5]      |        word:                                                 'CALL'
[L:  4, P:  9]      |        whitespace:                                           ' '
[L:  4, P: 10]      |        word:                                                 'my_sp'
[L:  4, P: 15]      |        start_bracket:                                        '('
[L:  4, P: 16]      |        end_bracket:                                          ')'
[L:  4, P: 17]      |        semicolon:                                            ';'
[L:  4, P: 18]      |    newline:                                                  '\n'
[L:  5, P:  1]      |    [META] end_of_file:

inline_dollar_sign: 'SYSTEM$GET_PREDECESSOR_RETURN_VALUE'

If we try to quote the name:

CREATE TASK mytask1
WHEN "SYSTEM$GET_PREDECESSOR_RETURN_VALUE"('previous_task_name') = 'TRUE'
AS
    CALL my_sp();

It will correctly recognize function:

function_name: [L: 2, P: 6] | quoted_identifier: '"SYSTEM$GET_PREDECESSOR_RETURN_VALUE"'

sqlfluff parse test.sql --dialect snowflake
[L:  1, P:  1]      |file:
[L:  1, P:  1]      |    statement:
[L:  1, P:  1]      |        create_task_statement:
[L:  1, P:  1]      |            keyword:                                          'CREATE'
[L:  1, P:  7]      |            whitespace:                                       ' '
[L:  1, P:  8]      |            keyword:                                          'TASK'
[L:  1, P: 12]      |            whitespace:                                       ' '
[L:  1, P: 13]      |            object_reference:
[L:  1, P: 13]      |                naked_identifier:                             'mytask1'
[L:  1, P: 20]      |            newline:                                          '\n'
[L:  2, P:  1]      |            [META] indent:
[L:  2, P:  1]      |            [META] dedent:
[L:  2, P:  1]      |            keyword:                                          'WHEN'
[L:  2, P:  5]      |            [META] indent:
[L:  2, P:  5]      |            whitespace:                                       ' '
[L:  2, P:  6]      |            snowflake_task_expression_segment:
[L:  2, P:  6]      |                expression:
[L:  2, P:  6]      |                    function:
[L:  2, P:  6]      |                        function_name:
[L:  2, P:  6]      |                            quoted_identifier:                '"SYSTEM$GET_PREDECESSOR_RETURN_VALUE"'
[L:  2, P: 43]      |                        bracketed:
[L:  2, P: 43]      |                            start_bracket:                    '('
[L:  2, P: 44]      |                            [META] indent:
[L:  2, P: 44]      |                            expression:
[L:  2, P: 44]      |                                quoted_literal:               "'previous_task_name'"
[L:  2, P: 64]      |                            [META] dedent:
[L:  2, P: 64]      |                            end_bracket:                      ')'
[L:  2, P: 65]      |                    whitespace:                               ' '
[L:  2, P: 66]      |                    comparison_operator:
[L:  2, P: 66]      |                        raw_comparison_operator:              '='
[L:  2, P: 67]      |                    whitespace:                               ' '
[L:  2, P: 68]      |                    quoted_literal:                           "'TRUE'"
[L:  2, P: 74]      |            [META] dedent:
[L:  2, P: 74]      |            newline:                                          '\n'
[L:  3, P:  1]      |            keyword:                                          'AS'
[L:  3, P:  3]      |            [META] indent:
[L:  3, P:  3]      |            newline:                                          '\n'
[L:  4, P:  1]      |            whitespace:                                       '    '
[L:  4, P:  5]      |            statement:
[L:  4, P:  5]      |                call_segment:
[L:  4, P:  5]      |                    keyword:                                  'CALL'
[L:  4, P:  9]      |                    whitespace:                               ' '
[L:  4, P: 10]      |                    function:
[L:  4, P: 10]      |                        function_name:
[L:  4, P: 10]      |                            function_name_identifier:         'my_sp'
[L:  4, P: 15]      |                        bracketed:
[L:  4, P: 15]      |                            start_bracket:                    '('
[L:  4, P: 16]      |                            [META] indent:
[L:  4, P: 16]      |                            [META] dedent:
[L:  4, P: 16]      |                            end_bracket:                      ')'
[L:  4, P: 17]      |            [META] dedent:
[L:  4, P: 17]      |    statement_terminator:                                     ';'
[L:  4, P: 18]      |    newline:                                                  '\n'
[L:  5, P:  1]      |    [META] end_of_file:

But will fail with:

sqlfluff lint test.sql --dialect snowflake
== [test.sql] FAIL
L:   2 | P:   6 | RF05 | Do not use special characters in identifiers.
                       | [references.special_chars]

---

If we try any other function(only for a test):

CREATE TASK mytask1
WHEN GET_PREDECESSOR_RETURN_VALUE('previous_task_name') = 'TRUE'
AS
    CALL MY_SP();

Run:

sqlfluff lint test.sql --dialect snowflake
All Finished  !

Conclusion: Based on observations it seems that sqlfluff(2.3.5) for dialect snowflake does not properly handle SYSTEM$ functions in CREATE TASK WHEN context.