Doesn't support creating an empty array of structs

[dmateusp]

We're getting the following error:

"Unsupported query shape: Spanner does not support array constructor syntax for an empty array where array elements are Structs."

The same query runs fine outside of the emulator.

Simple reproduction:

select array<struct<id bytes, foo int64>>[]

Our query needs to initialize an empty array as part of a coalesce for it to type check, otherwise we get the following error (outside of the emulator):

Statement failed: Unsupported query shape: This query can return a null-valued array of struct, which is not supported by Spanner. A likely cause for this error is a sub-SELECT in a FULL OUTER JOIN, or on the nullable side of a LEFT/RIGHT OUTER JOIN. ARRAY<STRUCT<...>> is the type that might contain null values. Use IFNULL/COALESCE to convert potentially null values to non-null values.

[ajorgensen]

I too ran into this. The same query works against gcloud Spanner but fails with Unsupported query shape: Spanner does not support array constructor syntax for an empty array where array elements are Structs. when run against the emulator.

[ajorgensen]

I was able to work around my specific problem by doing the following, YMMV

IFNULL(crc.things,
  ARRAY(
    SELECT AS STRUCT
      "" AS foo,
      "" AS bar
    FROM UNNEST([])
)) foobar

This seemed to be the only way I could get both production spanner and the emulator to not complain and produce the same results.

Other things I had tried were

IFNULL(crc.things, null) which appears to only work in the emulator but throws an error against production spanner
IFNULL(crc.things, ARRAY[]) which worked against production spanner but throws and error against the emulator.

[MITSUBOSHI]

I wrote the patch to fix this issue.
I hope this PR will be merged in main branch... 🙏

#198