Search code examples
google-cloud-spanner

Array index is out of bounds in Cloud Spanner

I have the following query:

SELECT * FROM Concerts
WHERE (ARRAY_LENGTH(TicketPrices) > 0 AND TicketPrices[OFFSET(0)] IS NOT NULL);

and I got:

ERROR: OUT_OF_RANGE: Array index 0 is out of bounds.

Why is the 2nd part being evaluated even when the first part is not true? How do I check if the array has one element with certain value?

Solution

  • In general, SQL doesn't make any guarantees about evaluation order (unlike languages like C++ that offer short-circuit evaluation). The engine is free to evaluate the right-side of the AND before evaluating the left-side. You can use the IF(cond,then-clause,else-clause) to force a particular evaluation order.

    Rewritten this way, your query would be:

    SELECT * FROM Concerts
WHERE IF(ARRAY_LENGTH(TicketPrices) > 0, TicketPrices[OFFSET(0)] IS NOT NULL, FALSE);