aws / aws/amazon-redshift-jdbc-driver
BUG: Table existence check for tables with long names does NOT WORK anymore
- 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
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