Column type name metadata incorrect for no schema binding views

[YotillaAntoni]

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

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"));
    }
}

[bhvkshah]

Thanks @YotillaAntoni for submitting this issue! I'll take a look and get back to you when I have an update

[jiamingparker]

Any updates on this?

[camsaul]

Also seeing an issue where an integer column is coming back as serial even tho it definitely is not. Also seeing isAutoIncrement come back as true for every column even text columns. If it helps this is when running queries that only SELECT a single column

[paoliniluis]

@bhvkshah sorry for pinging you directy on this one. Is there any way you can fix this? our customers are having issues in Metabase due to this matter

[paoliniluis]

also tagging @bsharifi and @timm4205