aws / aws/amazon-redshift-jdbc-driver

Column type name metadata incorrect for no schema binding views

Open
#89 5 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.