ResultSetMetaData returns wrong information depending on column order in query

[camsaul]

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

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:

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

I get

[{: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.

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:

;; 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

(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))))))))