split_part doesn't work when using a negative index on BigQuery

[b-per]

Describe the bug

In BigQuery, the macro split_part does not return results when the part_number is negative (e.g. when we want to get items from the end of the string and not the beginning)

Steps to reproduce

select {{ dbt_utils.split_part("'1/2/3/4.sql'", "'/'", -1) }}
which translates to select split('1/2/3/4.sql','/')[safe_offset(-1 -1)]

Expected results

4.sql

Actual results

Null

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

Are you interested in contributing the fix?

Happy to contribute or let another person do it.
The macro needs to be changed so we can add a check on whether part_number is negative, and if it is, the macro needs to return

split(
    {{ string_text }},
    {{ delimiter_text }}
)[safe_offset(
    array_length(
        split(
            {{ string_text }},
            {{ delimiter_text }}
        )
        {{ part_number}})
]

[b-per]

I just realized that Postgres doesn't support negative indexes either.
And reading Redshift docs it looks like the index needs to be positive as well.

[dbeatty10]

Thank you for finding and reporting this bug, @b-per !

If you're will to do the following, that would be great:

1. add an integration test that includes a negative part_number
2. solve for BigQuery, Postgres, and Redshift

Just let me know if you'd like help for any of the above!