GoogleCloudPlatform / GoogleCloudPlatform/cloud-spanner-emulator
Emulator doesn't recreate unsupported query shape that can return a null-valued array of struct
- 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
Assessment
This issue has not been assessed yet.