redshift-alpha: existing integration test actually fails to deploy

Describe the bug

There is an integ.database.ts integ test, but this test cannot actually be deployed to AWS.

~/git/aws-cdk/packages/@aws-cdk/aws-redshift-alpha
❯ yarn integ test/integ.database.js --force           
yarn run v1.22.21
$ integ-runner --language javascript test/integ.database.js --force

Verifying integration test snapshots...

  UNCHANGED  integ.database 1.152s

Snapshot Results: 

Tests:    1 passed, 1 total

Running integration tests for failed tests...

Running in parallel across regions: us-east-1, us-east-2, us-west-2
Running test /Users/kazuhoshinozuka/git/aws-cdk/packages/@aws-cdk/aws-redshift-alpha/test/integ.database.js in us-east-1

✨  Synthesis time: 0.03s

...

aws-cdk-redshift-cluster-database: deploying... [1/1]
aws-cdk-redshift-cluster-database: creating CloudFormation changeset...

...

Failed resources:
aws-cdk-redshift-cluster-database | 12:36:28 AM | CREATE_FAILED        | Custom::RedshiftDatabaseQuery               | User/TablePrivileges/Resource/Resource/Default (UserTablePrivileges3829D614) Received response status [FAILED] from custom resource. Message returned: Statement status was FAILED: ERROR: syntax error at or near "-" in context "SELECT ON clustereb0386a7-", at line 1
  Position: 48

Logs: /aws/lambda/aws-cdk-redshift-cluster--QueryRedshiftDatabase3de-OKD1xpr2NRJ9

  Position: 48
    at waitForStatementComplete (/var/task/redshift-data.js:34:15)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async executeStatement (/var/task/redshift-data.js:18:5)
    at async Promise.all (index 0)
    at async grantPrivileges (/var/task/privileges.js:35:5)
    at async handler (/var/task/privileges.js:11:9) (RequestId: 0de4c80b-c1c3-4d12-8ea9-45dc9b986515)
❌  aws-cdk-redshift-cluster-database failed: The stack named aws-cdk-redshift-cluster-database failed creation, it may need to be manually deleted from the AWS console: ROLLBACK_COMPLETE: Received response status [FAILED] from custom resource. Message returned: Statement status was FAILED: ERROR: syntax error at or near "-" in context "SELECT ON clustereb0386a7-", at line 1
  Position: 48

Generated template file is here.

Regression Issue

[ ] Select this option if this issue appears to be a regression.

Last Known Working CDK Version

2.160.0

Expected Behavior

The integ test should be able to deploy.

Current Behavior

Failed to deploy.

Reproduction Steps

You can perform this integration test as follows.

cd packages/@aws-cdk/aws-redshift-alpha
yarn 
yarn build
yarn integ test/integ.database.js --force

Possible Solution

No response

Additional Information/Context

No response

CDK CLI Version

2.160.0

Framework Version

No response

Node.js Version

v22.9.0

OS

macOS

Language

TypeScript

Language Version

No response

Other information

No response

Looks like grantPrivileges() > executeStatement() is failing while executing GRANT ${actions.join(', ')} ON ${tableName} TO ${username}.

Syntax for GRANT allows to specify comma separated actions (SELECT, UPDATE,..., etc.) ON [ TABLE ] table_name where TABLE keyword is optional.

Table name can use:

Standard identifiers which can contain ASCII single-byte alphanumeric characters, underscores, or dollar signs, or UTF-8 multibyte characters two to four bytes long. It cannot contain hyphen -.
Delimited identifiers which begin and end with double quotation marks ("). The identifier can contain any standard UTF-8 printable characters other than the double quotation mark itself. Therefore, you can create column or table names that include otherwise illegal characters, such as spaces or the percent symbol.

In the issue, the error is syntax error at or near "-" in context "SELECT ON clustereb0386a7-". So looks like table name somehow contains -. Hence, the database-query-provider should either be updated to use delimited identifiers, OR, integration test should make sure not to use illegal characters in table names.

aws / aws-cdk