spannertest: limitation of "UNNEST" clause usage

[sryoya]

Is your feature request related to a problem? Please describe.
It seems AS clause cannot be used to the output from UNNEST.

I tried to run the below SQL in spannertest/intergration_test.go when I was developing #3286 , but received below error.

SELECT AVG(x) as avg FROM UNNEST([0, 2, 4, 4, 5]) as x

spanner: code = "InvalidArgument", desc = "bad query: unexpected trailing query contents \"([0, 2, 4, 4, 5]) AS x\

I have not pursed which code causes this issue, but I guess the parsing logic in spansql may have a point to improve.

[dsymonds]

It is because the array literal is unsupported (this is in the README.md). FWIW, it should work with ... UNNEST(@p) for some param.

[sryoya]

Thank you for your explanation. My doubt on AS clause seems to be off the mark.

But, is "UNNEST soon after FROM" also unsupported?

I used UNNEST(@p) as you said, and tried the below SQL.

		{
			`SELECT * FROM UNNEST(@p)`,
			map[string]interface{}{"p": []int64{3, 1}},
			[][]interface{}{
				{int64(3)},
				{int64(1)},
			},
		},

But, it got the following error.

spanner: code = "InvalidArgument", desc = "bad query: unexpected trailing query contents \"(@p)\""

And, if I use UNNEST in WHERE clause like this, it runs successfully.

		{
			`SELECT * FROM Staff WHERE ID IN UNNEST(@p)`,
			map[string]interface{}{"p": []int64{3, 1}},
			[][]interface{}{
				{int64(3)},
				{int64(1)},
			},
		},

Based on the above, "UNNEST" itself works by avoiding hard-coded the array literal as you say, but, it may not work soon after "FROM" .

Let me know if you have anything else.

[dsymonds]

Ah, right. UNNEST works in expressions, but does not work as a SELECT target.

[sryoya]

I see. Thanks. That seems the very reason my SQL doesn't work.