search

seancorfield / next-jdbc

A modern low-level Clojure wrapper for JDBC-based access to databases.
https://cljdoc.org/d/com.github.seancorfield/next.jdbc/
Eclipse Public License 1.0
767 stars 90 forks source link

`jdbc/execute!` unexpectedly qualifies column names that were explicitly unqualified using `SELECT ... AS ...` #260

Closed devurandom closed 12 months ago

devurandom commented 12 months ago

Describe the bug

jdbc/execute! unexpectedly qualifies column names that were explicitly unqualified using SELECT ... AS ....

To Reproduce

This HoneySQL:

(sql/format {:select [[[:encode :tab/one_field "hex"] :one]
                      [:tab/other_field :other]]
             :from   [:tab]})

Generates this SQL:

SELECT ENCODE(tab.one_field, "hex") AS one, tab.other_field AS other FROM tab

When I jdbc/execute! that, I get the following shape back:

[{:one "..." :tab/other "..."}]

Expected behavior

I explicitly renamed tab.other_field to other using AS, i.e. to something that was not qualified with the table name, so I would expect the map returned by next-jdbc to respect my choice.

I would expect tab.other_field to end up in the same way that tab.one_field does, which was also renamed using AS.

Environment (please complete the following information):

seancorfield commented 12 months ago

The qualifiers are based on the table name that the JDBC driver provides for the columns. When you alias a column to a new name, several (most?) JDBC drivers retain the table name. When you alias a computed expression to a name, the drivers provide a blank table name, i.e., no qualifier. next.jdbc is just acting as a thin wrapper around JDBC here -- it only knows about what comes back from the driver, not what went into the database.