aws / aws/amazon-redshift-jdbc-driver
ResultSetMetaData returns wrong information depending on column order in query
- 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
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