GoogleCloudPlatform / GoogleCloudPlatform/cloud-spanner-emulator

Emulator doesn't recreate unsupported query shape that can return a null-valued array of struct

Open
#292 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
C++
Stars
334
Forks
77
Avg merge
8m
Merged PRs (30d)
2

Description

Consider the following query that works on spanner emulator, but doesn't work on prod spanner:

```sql
with
grps as (select * from unnest([10, 20, 30]) as group_id),
nums as (select * from
unnest(array>[
(1, 10, 'foo'), (2, 20, 'bar'), (11, 10, 'baz')
])
)

select group_id, (select array_agg(struct(id, name)) from nums where nums.group_id=grps.group_id) from grps;
```

Prod spanner reports:

`ERROR: spanner: code="Unimplemented", desc: Unsupported query shape: This query can return a null-valued array of struct, which is not supported by Spanner. ARRAY> is the type that might contain null values. Use IFNULL/COALESCE to convert potentially null values to non-null values.`

While in the emulator, this query succeeds.


Note that to fix on prod, we follow the error message's advise to use `ifnull/coalesce`:

```sql
with
grps as (select * from unnest([10, 20, 30]) as group_id),
nums as (select * from
unnest(array>[
(1, 10, 'foo'), (2, 20, 'bar'), (11, 10, 'baz')
])
)

select group_id, (select coalesce(array_agg(struct(id, name)), []) from nums where nums.group_id=grps.group_id) from grps;
```

However, on the emulator, this fails due to the related issue #165.

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.