aws / aws/amazon-redshift-jdbc-driver
Column type name metadata incorrect for no schema binding views
- Dominant language
- Java
- Stars
- 71
- Forks
- 42
- PR merge metrics
- No merged PRs in 30d
Description
## Driver version
2.1.0.14
## Redshift version
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.49087
## Client Operating System
macOS 13.3.1
## JAVA/JVM version
OpenJDK Runtime Environment Temurin-11.0.17+8 (build 11.0.17+8)
## Table schema
```
create table test.product_table
(
product_name VARCHAR(100)
, net_price NUMERIC(10,2)
);
create view test.product_view as
select product_name
, net_price
from test.product_table;
create view test.product_view_nsb as
select product_name
, net_price
from test.product_table
with no schema binding;
```
## Problem description
Using `DatabaseMetaData#getColumns` method over the VIEW `product_view_nsb` reports incorrect results for the `TYPE_NAME`. It reports `character varying(100)` instead of `varchar` for the column `product_name`, and `numeric(10,2)` instead of `numeric` for the column `net_price`
Using the `DatabaseMetaData#getColumns` method over both TABLE `product_table` or the view `product_view` returns the correct values, `varchar` for column `product_name` and `numeric` for the column `net_price`
1. Expected behaviour:
The column `TYPE_NAME` for the described `product_view_nsb` view is `varchar` for column `product_name` and `numeric` for column `net_price`
2. Actual behaviour:
The column `TYPE_NAME` for the described `product_view_nsb` view is `character varying(100)` for column `product_name` and `numeric(10,2)` for column `net_price`
3. Any other details that can be helpful:
Looks like the error is in the last part of the query to get the metadata from `pg_get_late_binding_view_cols`, when it uses `columntype` as `TYPE_NAME` instead of `columntype_rep`
## JDBC trace logs
[log_nsb_view.log](https://github.com/aws/amazon-redshift-jdbc-driver/files/11342579/log_nsb_view.log)
## Reproduction code
```
try (
Connection connection = DriverManager.getConnection("jdbc:redshift://host:5439/dev", "user", "pass");
ResultSet resultSet = connection.getMetaData().getColumns("dev", "test", "product_view_nsb", null);
) {
while (resultSet.next()) {
System.out.printf("%s | %s%n", resultSet.getString("column_name"), resultSet.getString("type_name"));
}
}
```
Contributor guide
Research direction
Start with the DatabaseMetaData#getColumns implementation and the metadata query that calls pg_get_late_binding_view_cols. Compare the columntype and columntype_rep fields for no-schema-binding views, then use the supplied SQL and Java reproduction to verify that TYPE_NAME is varchar and numeric without length or precision suffixes.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- java
- Domain
- database
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 45/100