GoogleCloudPlatform / GoogleCloudPlatform/cloud-spanner-emulator

Enable `FEATURE_WITH_ON_SUBQUERY` feature switch in zetasql package

Open
#276 3 comments 0 reactions 1 assignee Claimed by @larkee View on GitHub
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

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.