Why does a known type produce a runtime error but an inferred type produces a null value?

Question

Let's take the following example from BigQuery, but it's the same across all other databases I've seen:

with tbl as (
    select [1,2,3] as arr,
    '{"IDS": [1,2,3], "Author": "Shakespeare", "Title": "Romeo & Juliet"}' as json_str,
    STRUCT('Hamlet' AS title, 'Shakespeare' AS author) as struct_val
) select
    arr[OFFSET(0)],
    struct_val.title,
    JSON_QUERY(json_str, "$.IDS[0][0].invalid")
from tbl

This returns:

1 - Hamlet - null

In other words, if a json access hits an invalid path, it returns null. However, when doing something like struct_val.invalid_access or arr[OFFSET(100)], it will give an actual Runtime error, such as:

Error1: Array index 100 is out of bounds (overflow)
Error2: Field name invalid_access does not exist in STRUCT<title STRING, author STRING>

My question is why does one form give a null value but the other one returns a runtime error? My thinking was it would be more consistent for them all to return null, but obviously people with far more knowledge than I have constructed these systems, so curious why this is so...

Answer 1

The information you're trying to find might be within the design documents but the actual answer is more likely to be what Serg has mentioned.

When you're doing arr[OFFSET(100)] you're telling the engine that you want to access the element that is in that position, if there's no such index, then the error appears, in other words, the engine cannot execute what is asked. In the JSON_QUERY, you're asking the engine to look for a value in a JSON-like string, the engine can execute what is requested, compare a json path with the string, and if nothing is found, it returns null.

This is something that gets discussed a lot outside the DB context, for example: https://softwareengineering.stackexchange.com/questions/228287/returning-null-or-a-empty-value-throw-exception