[ADAP-384] [Bug] Negative part_number for dbt.split_part() is not working

[dave-connors-3]

Is this your first time submitting a feature request?

• I have read the expectations for open source contributors
• I have searched the existing issues, and I could not find an existing issue for this feature
• I am requesting a straightforward extension of existing dbt-bigquery functionality, rather than a Big Idea better suited to a discussion

Describe the feature

per the docs, the split part macro supports negative part_number arguments to select elements from the end of the resulting array.

the current implementation in dbt-bigquery checks for negative values for this argument, and if the arg is negative, just disregards it wholesale!

{% macro bigquery__split_part(string_text, delimiter_text, part_number) %}

  {% if part_number >= 0 %}
    split(
        {{ string_text }},
        {{ delimiter_text }}
        )[safe_offset({{ part_number - 1 }})]
  {% else %}
    split(
        {{ string_text }},
        {{ delimiter_text }}
        )[safe_offset(
          length({{ string_text }})
          - length(
              replace({{ string_text }},  {{ delimiter_text }}, '')
          ) + 1
        )]
  {% endif %}

{% endmacro %}

We should update the logic to accept negative args and handle them appropriately.

Describe alternatives you've considered

keep on keepin on

Who will this benefit?

No response

Are you interested in contributing this feature?

yes!

Anything else?

No response

[dbeatty10]

Good call @dave-connors-3

Looking at the postgres implementation, it is explicitly supporting the case of a negative part_number, so it makes sense for dbt-bigquery to match it:
postgres/macros/utils/split_part.sql

The first action to implement this will be adding a negative test case (e.g., -1 or -2) to this file in dbt-core:
tests/adapter/dbt/tests/adapter/utils/fixture_split_part.py

After opening a PR for dbt-core, then each adapter should be tested against that branch. We'd expect dbt-bigquery to fail (until you implement it). If other adapters fail, we'll want to open PRs for those as well.

Overall, one workflow you could use looks something like this:

1. Open PR for dbt-core
2. Open PRs for each of the adapters (or merely local branches)
3. Temporarily update the dev-requirements.txt file within each adapter to point to your PR in Step 1, like this.
4. Once all PRs are passing CI, reviewed, and approved, then merge the dbt-core PR
5. Undo the temporary update to dev-requirements.txt in each of the adapter PRs (so that they point at the main branch of dbt-core again) like this
6. Once CI passes, then all the adapter PRs can be merged as well.