aws / aws/amazon-redshift-jdbc-driver

ResultSetMetaData returns wrong information depending on column order in query

Open
#118 0 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.28
```

## 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.66954
```

## Client Operating System

WSL 2/Windows 11

## JAVA/JVM version

```
openjdk 19.0.2 2023-01-17
```

## Table schema

```sql
CREATE TABLE 2024_05_31_e15f5997_60ab_4c7d_8fde_164a001da04c_schema.test_data_venues (
id integer NOT NULL identity(1,1) ENCODE az64,
name character varying(1024) ENCODE lzo,
category_id integer ENCODE az64,
latitude double precision ENCODE raw,
longitude double precision ENCODE raw,
price integer ENCODE az64,
PRIMARY KEY (id),
FOREIGN KEY (category_id) REFERENCES "2024_05_31_e15f5997_60ab_4c7d_8fde_164a001da04c_schema".test_data_categories(id)
)
DISTSTYLE AUTO;
```

## Problem description

`ResultSetMetaData` returns the wrong information depending on the order of the columns in the `SELECT`. With this query:

```sql
SELECT name, price
FROM "2024_05_31_e15f5997_60ab_4c7d_8fde_164a001da04c_schema"."test_data_venues"
LIMIT 1
```

I get

```clj
[{:name "name", :db-type "varchar", :auto-increment? true}
{:name "price", :db-type "int4", :auto-increment? false}]
```

which is only a little wrong (how can a `varchar` be auto-increment?

but if I swap the order of the columns, e.g.

```sql
SELECT price, name
FROM "2024_05_31_e15f5997_60ab_4c7d_8fde_164a001da04c_schema"."test_data_venues"
LIMIT 1
```

then not only is the autoincrement version wrong, the type information for `price` is now wrong too:

```clj
;; price is an integer, not a serial
[{:name "price", :db-type "serial", :auto-increment? true}
{:name "name", :db-type "varchar", :auto-increment? false}]
```

I'm guessing this has something to do with the fact that the first column in the table `id`, is an auto-incrementing serial column. So maybe it's still looking at that column even tho I'm not fetching it in the queries above.

## Reproduction code

I used this Clojure code to reproduce the problem

```clj
(metabase.driver.sql-jdbc.execute/do-with-connection-with-options
:redshift (metabase.driver/with-driver :redshift (metabase.test/db)) nil
(fn [^java.sql.Connection conn]
(with-open [stmt (.prepareStatement conn (str "SELECT name, price"
" FROM \"2024_05_31_e15f5997_60ab_4c7d_8fde_164a001da04c_schema\".\"test_data_venues\""
" LIMIT 1"))]
(let [meta (.getMetaData stmt)]
(mapv (fn [^Long i]
{:name (.getColumnLabel meta i)
:db-type (.getColumnTypeName meta i)
:auto-increment? (.isAutoIncrement meta i)})
(range 1 (inc (.getColumnCount meta))))))))
```

Contributor guide

Open the contributing guide

Research direction

Start with the JDBC ResultSetMetaData entry point and run the supplied Clojure reproduction against Redshift, checking both SELECT column orders. Trace how each selected column's type and auto-increment metadata is associated with the result position; done means both queries report the correct column type and auto-increment status independently of order.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 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.