Re: The column name <col> was not found in this ResultSet

On 11/14/2012 06:42 PM, Zsolt Kúti wrote:
> Hello,
>
> In a query like this:
> select * form T1 a, T2 b where a.id='xx' and a.id=b.id
>
> calling the result set:
> rs.getString("o.id")
>
> I get:
> org.postgresql.util.PSQLException: The column name o.id was not found
> in this ResultSet. at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.findColumn(AbstractJdbc2ResultSet.java:2562)
> at
> org.postgresql.jdbc2.AbstractJdbc2ResultSet.getString(AbstractJdbc2ResultSet.java:2405)
> at
> hu.prolan.emgmt.fre.service.store.impl.OrderStoreImpl.loadOrder(OrderStoreImpl.java:236)
>
> Getting data by index is OK, just as simply using getString('id'). This
> nonqualified access however cannot get to the next column with the
> same name...
>
> How can the columns accessed by qualified name?
> Is it a bug?

I don't think so, but it isn't simple.

Consider the following:

regress=> SELECT a.n, 'somevalue' AS "a.n" FROM (VALUES ('othervalue'))
a(n);
n | a.n
------------+-----------
othervalue | somevalue
(1 row)

Which of these columns is "a.n" to PgJDBC? The one actually called "a.n"
(note the identifier quotes)? Or the one called "n" with table-alias "a"?

The JDBC API does not offer us a "ResultSet.getString(String columnname,
String schemaname)" that would allow us to disambiguate these cases. So
we either have to:

(a) Intepret all identifiers passed to get...() functions as if they
were PostgreSQL identifier literals, unescaping them and splitting them
into schema-part and literal-part; or

(b) Just get the first column with the given name and require users to
alias column names to disambiguate them.

In my opinion (a) would be horrible to use, and it appears to be
contrary to the JDBC Resultset spec as well. You'd have to "double
quote" every identifier if you wanted to use mixed case or any kind of
non-alphanumeric strings, so:

rs.getString("B.F. Name")

would have to become:

rs.getString("\"B.F. Name\"")

or users would get syntax errors, identifier parsing errors, or bizarre
errors about columns not being found.

So (b) is the best compromise, really. Just alias your columns to
disambiguate them in the result set by specifying `AS` aliases.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services