GoogleCloudPlatform / GoogleCloudPlatform/cloud-spanner-emulator
Enable `FEATURE_WITH_ON_SUBQUERY` feature switch in zetasql package
- Dominant language
- C++
- Stars
- 334
- Forks
- 77
- Avg merge
- 8m
- Merged PRs (30d)
- 2
Description
Hi,
I'm trying to use a WITH CTE expression in a Spanner emulator query locally.
If I try to execute the query using the dotnet SDKs, (which, in turn I believe hit a analyze endpoint first) I receive the error:
`WITH is not supported on subqueries in this language version`
However, if I execute directly against the database using the same SQL query, it executes correctly.
I'm using the docker image version of the emulator, specifically this tag:
`[cloud-spanner-emulator/emulator:1.5.41]`
This _leads_ me to believe the functionality required for CTEs is present and just needs the: `FEATURE_WITH_ON_SUBQUERY` feature toggle enabled.
For reference, my SQL CTE is:
```
WITH user_acl_matches AS (
SELECT DISTINCT
iacl.Id,
acl.Grant
FROM ACLEntity as iacl
INNER JOIN ACL as acl ON acl.AclId = iacl.AclId
INNER JOIN UNNEST(@userClaims) as uc ON uc = CONCAT(acl.ClaimType, '|', acl.ClaimValue)
WHERE acl.Permission = @permission
),
entity_permissions AS (
SELECT
Id,
SUM(CASE WHEN Grant = 'Allow' THEN 1 ELSE 0 END) as allow_count,
SUM(CASE WHEN Grant = 'Deny' THEN 1 ELSE 0 END) as deny_count
FROM user_acl_matches
GROUP BY Id
HAVING allow_count > 0 AND deny_count = 0
)
```
It might be something specific in my query, but as I say, when executing the same query with `spanner-cli` against the same cloud spanner emulator instance, it executes successfully.
Contributor guide
Assessment
This issue has not been assessed yet.