aws / aws/amazon-redshift-jdbc-driver

BUG: Table existence check for tables with long names does NOT WORK anymore

Open
#148 7 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
71
Forks
42
PR merge metrics
No merged PRs in 30d

Description

Use the following reproduction scenario:
```
-- note that the created table has 127 characters
create table testdb.test_schema.test_create_gg254bgvzkzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz (a int);

SHOW TABLES FROM SCHEMA testdb.test_schema LIKE 'test\\_create\\_gg254bgvzkzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz';
-- does not return any result

SHOW TABLES FROM SCHEMA testdb.test_schema;
-- does return among other table names also the table with the long table name
```

It seems that recently there has been introduced a regression in AWS Redshift with regards to checking table existence and the following statement does not work anymore as it was intended:

```
SHOW TABLES FROM SCHEMA {1}.{2} LIKE {3}
```

See for details `com.amazon.redshift.jdbc.MetadataServerAPIHelper.callShowTablesWithLike`

This is likely an issue on AWS Redshift server side and not on the driver.

Issue seems to be reproducible when using `2.2.0` version of the driver, but not with `2.1.0.30`

In `2.1.0.30` we're seeing the following SQL statement created in `com.amazon.redshift.jdbc.RedshiftDatabaseMetaData#getTables`

```
SELECT CAST(current_database() AS VARCHAR(124)) AS TABLE_CAT, n.nspname AS TABLE_SCHEM, c.relname AS TABLE_NAME, CASE n.nspname ~ '^pg_' OR n.nspname = 'information_schema' WHEN true THEN CASE WHEN n.nspname = 'pg_catalog' OR n.nspname = 'information_schema' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TABLE' WHEN 'v' THEN 'SYSTEM VIEW' WHEN 'i' THEN 'SYSTEM INDEX' ELSE NULL END WHEN n.nspname = 'pg_toast' THEN CASE c.relkind WHEN 'r' THEN 'SYSTEM TOAST TABLE' WHEN 'i' THEN 'SYSTEM TOAST INDEX' ELSE NULL END ELSE CASE c.relkind WHEN 'r' THEN 'TEMPORARY TABLE' WHEN 'p' THEN 'TEMPORARY TABLE' WHEN 'i' THEN 'TEMPORARY INDEX' WHEN 'S' THEN 'TEMPORARY SEQUENCE' WHEN 'v' THEN 'TEMPORARY VIEW' ELSE NULL END END WHEN false THEN CASE c.relkind WHEN 'r' THEN 'TABLE' WHEN 'p' THEN 'PARTITIONED TABLE' WHEN 'i' THEN 'INDEX' WHEN 'S' THEN 'SEQUENCE' WHEN 'v' THEN 'VIEW' WHEN 'c' THEN 'TYPE' WHEN 'f' THEN 'FOREIGN TABLE' WHEN 'm' THEN 'MATERIALIZED VIEW' ELSE NULL END ELSE NULL END AS TABLE_TYPE, d.description AS REMARKS, '' as TYPE_CAT, '' as TYPE_SCHEM, '' as TYPE_NAME, '' AS SELF_REFERENCING_COL_NAME, '' AS REF_GENERATION FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid AND d.objsubid = 0) LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND dn.nspname='pg_catalog') WHERE c.relnamespace = n.oid AND current_database() = 'testdb' AND TABLE_SCHEM LIKE 'test\\_schema' AND TABLE_NAME LIKE 'test\\_create\\_fpwfxwuw9izzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz' AND (false OR ( c.relkind = 'r' AND n.nspname !~ '^pg_' AND n.nspname <> 'information_schema' ) OR ( c.relkind = 'v' AND n.nspname <> 'pg_catalog' AND n.nspname <> 'information_schema' ) ) ORDER BY TABLE_TYPE,TABLE_SCHEM,TABLE_NAME
```

Contributor guide

Open the contributing guide

Research direction

Start with the reproduction using the AWS Redshift SHOW TABLES query, then inspect com.amazon.redshift.jdbc.MetadataServerAPIHelper.callShowTablesWithLike and com.amazon.redshift.jdbc.RedshiftDatabaseMetaData#getTables. Compare the SQL generated by driver versions 2.1.0.30 and 2.2.0 for 127-character table names; done requires identifying whether the regression is in the driver or on the Redshift server.

Written by the indexing model from the issue text.

Assessment

Tech stack
aws, java
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Quiet
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.