GoogleCloudPlatform / GoogleCloudPlatform/training-data-analyst

OFFSET SQL function yields `400 Array index 1 is out of bounds` while extracting info from `hacker_news` dataset

Open
#2,434 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Jupyter Notebook
Stars
8.6k
Forks
6.1k
Avg merge
4h 44m
Merged PRs (30d)
2

Description

Many SQL sections in various notebooks where the instructions explore the information in the dataset uses `OFFSET(1)` while trying to extract the domain name stem as the source. Three labs are mentioned in the #2432 issue (with their name and Cloud Skills Boost URL) along with their notebook, but there are many more notebooks. Example query cell:

```
%%bigquery --project $PROJECT

SELECT
ARRAY_REVERSE(SPLIT(REGEXP_EXTRACT(url, '.*://(.[^/]+)/'), '.'))[OFFSET(1)] AS source,
COUNT(title) AS num_articles
FROM
`bigquery-public-data.hacker_news.full`
WHERE
REGEXP_CONTAINS(REGEXP_EXTRACT(url, '.*://(.[^/]+)/'), '.com$')
AND LENGTH(title) > 10
GROUP BY
source
ORDER BY num_articles DESC
LIMIT 100
```

Resulting error:
```
ERROR:
400 Array index 1 is out of bounds (overflow)

Location: US
Job ID: 389a7292-2c3b-4f14-8129-af10d4270423
```

A workaround is to use `SAFE_OFFSET` instead of `OFFSET`. A few other notebooks use that, and all notebooks use that in the https://github.com/GoogleCloudPlatform/asl-ml-immersion/ repo. I'll amend the PR#2433 with this.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.