Wrong column names in ResultSetMetaData

With the new V3 driver the column names in ResultSetMetaData
don't reflect the aliases used in the SQL. I.e. if I do:

SELECT name as name_alias FROM ...

the metadata says I have a result column called "name" instead
of "name_alias".

Client: PostgreSQL 7.5devel JDBC2 with SSL (build 304)
Server: PostgreSQL 7.4.2 on i386-redhat-linux-gnu, compiled by GCC 2.96

The code below works with the 7.4 driver but fails with 7.5.
Is this more likely to be in the driver or in the server-side
V3 code?

Mike

AliasTest.java
-------------------

import java.sql.*;
import java.util.*;

public class AliasTest
{
public static void main(String args[]) throws Exception
{
if (args.length != 3)
throw new IllegalArgumentException("usage: java AliasTest
<database_spec> <username> <password>");

Class.forName("org.postgresql.Driver");

Connection conn = DriverManager.getConnection("jdbc:postgresql:" +
args[0], args[1], args[2]);
Statement stmt = conn.createStatement();

try { stmt.execute("DROP TABLE alias_test"); } catch (SQLException
e) {}
stmt.execute("CREATE TABLE alias_test ( name varchar(64) not null
primary key )");
stmt.executeUpdate("INSERT INTO alias_test ( name ) VALUES (
'abc' )");
stmt.close();

PreparedStatement ps = conn.prepareStatement("SELECT name as
name_alias FROM alias_test");
ResultSet rs = ps.executeQuery();

if (!rs.next()) throw new Exception("No row?");

List expectedColumnNames = Arrays.asList(new String[] {
"name_alias" });
List actualColumnNames = new ArrayList();
ResultSetMetaData rsmd = rs.getMetaData();

int colCount = rsmd.getColumnCount();

for (int colNum = 1; colNum <= colCount; ++colNum)
actualColumnNames.add(rsmd.getColumnName(colNum));

if (!expectedColumnNames.equals(actualColumnNames))
throw new Exception("Wrong column names in result set metadata,
expected " +
expectedColumnNames + ", got " + actualColumnNames);

rs.close();
ps.close();
conn.close();
}
}

I wrote:
> With the new V3 driver the column names in ResultSetMetaData
> don't reflect the aliases used in the SQL. I.e. if I do:
>
> SELECT name as name_alias FROM ...
>
> the metadata says I have a result column called "name" instead
> of "name_alias".

I think I see the problem. v2/QueryExecutorImpl.java:400 says:

fields[i] = new Field(columnLabel, columnLabel, typeOid, typeLength,
typeModifier, 0, 0);

whereas v3/QueryExecutorImpl.java:1097 says:

fields[i] = new Field(columnLabel,
null, /* name not yet determined */
typeOid, typeLength, typeModifier, tableOid,
positionInTable);

and a separate query is later done in getColumnName() to try
to return the unaliased *source* column name used in the query.

I'm almost certain this is wrong from a JDBC standpoint.

rsmd.getColumnName() is supposed to return the given name of
the *result* column, which SQL has rules to define.
rsmd.getColumnLabel() is "for use in printouts and displays"
and will often equate to the column name unless the DBMS has
some "prettier" column title for display purposes.

For programmatic purposes the column name concept is pretty
well defined by the docs on ResultSet. They're supposed to
behave such that:

String colname = rsmd.getColumnName(col);
return rs.getXXX(colname);

is equivalent to:

return rs.getXXX(col);

for every column that has a (non-duplicate) name.

I have to think this is going to break more code than just
ours.

Mike

On Wed, 28 Jul 2004, Mike Martin wrote:

> I wrote:
> > [new driver returns a column alias as the label, not name]
>
> I'm almost certain this is wrong from a JDBC standpoint.
>
> rsmd.getColumnName() is supposed to return the given name of
> the *result* column, which SQL has rules to define.
> rsmd.getColumnLabel() is "for use in printouts and displays"
> and will often equate to the column name unless the DBMS has
> some "prettier" column title for display purposes.
>
> For programmatic purposes the column name concept is pretty
> well defined by the docs on ResultSet. They're supposed to
> behave such that:
>
> String colname = rsmd.getColumnName(col);
> return rs.getXXX(colname);
>
> is equivalent to:
>
> return rs.getXXX(col);
>

I am not sure I see where it states that. Your logic makes some sense,
but I don't see anywhere it says the above explicity. The problem is how
to return both pieces of information (the alias and the underlying column
name) within the JDBC API. Certainly the alias makes more sense as the
label when you have to pick between the two of them.

Would you expect the results of getTableName() to return the underlying
table or the table's alias in the query? To be consistent with your
argument you'd have to claim the alias name which is useless here.

Further code doesn't make a whole lot of sense doing:

rs.getString(rsmd.getColumnName(1));

Why wouldn't it just do:

rs.getString(1);

Kris Jurka

Mike Martin wrote:

> For programmatic purposes the column name concept is pretty
> well defined by the docs on ResultSet.

Can you point me to these docs? I can't see this requirement from a
glance through the 1.4 javadoc, but the JDBC javadoc is pretty useless..

> I have to think this is going to break more code than just
> ours.

I don't remember intentionally changing this behaviour in the V3
overhaul so it may actually predate those changes. However, Field was
rearranged when doing the V3 changes so it's entirely possible I
accidentally broke this behaviour.

-O

On Thu, 29 Jul 2004, Oliver Jowett wrote:

> I don't remember intentionally changing this behaviour in the V3
> overhaul so it may actually predate those changes. However, Field was
> rearranged when doing the V3 changes so it's entirely possible I
> accidentally broke this behaviour.
>

This was done intentionally. Back in January I applied this patch:

date: 2004/01/13 03:07:09; author: jurka; state: Exp; lines: +95 -31
This patch is the culmination of Jan-Andre le Roux's work on enhancing
the ResultSetMetaData methods with new information available in the V3
protocol. He wandered off of the verge of completing it and I've just
done some minor reworking and editing.

This implements getSchemaName, getTableName, getColumnName,
getColumnLabel, and isNullable.

Kris Jurka

Kris Jurka wrote:

> This was done intentionally. Back in January I applied this patch:
>
> date: 2004/01/13 03:07:09; author: jurka; state: Exp; lines: +95 -31
> This patch is the culmination of Jan-Andre le Roux's work on enhancing
> the ResultSetMetaData methods with new information available in the V3
> protocol. He wandered off of the verge of completing it and I've just
> done some minor reworking and editing.
>
> This implements getSchemaName, getTableName, getColumnName,
> getColumnLabel, and isNullable.

I notice that the implementation of updateable resultsets use
Field.getColumnLabel() when generating the UPDATE/SELECT/etc SQL.
Presumably this should be using Field.getColumnName() given the current
semantics?

-O

"Kris Jurka" wrote:
> > For programmatic purposes the column name concept is pretty
> > well defined by the docs on ResultSet.
>
> I am not sure I see where it states that. Your logic makes some sense,
> but I don't see anywhere it says the above explicity. The problem is how
> to return both pieces of information (the alias and the underlying column
> name) within the JDBC API. Certainly the alias makes more sense as the
> label when you have to pick between the two of them.

It depends on how explicit you need it to be. :)

Unfortunately the JDBC spec itself lacks much of anything that
explicit. The Javadoc for ResultSet says:

"The column name option is designed to be used when column
names are used in the SQL query that generated the result set.
For columns that are NOT explicitly named in the query, it
is best to use column numbers."

I read "named" to mean named according to the usual SQL rules.
Combine that with the other references to the semantics of column
name (findColumn(), getXXX(colName)) and it seems like the
reasonable reading.

My other support is circumstantial:

1. An SQL query produces a result set which is itself a table,
that table has columns, those columns have names, and those
names are defined according to SQL rules. The column being
named is that of the result table, not that of the underlying
source(s) that contributed to it. I can't imagine
getColumnName() referring to any other concept.

2. Every other JDBC driver I'm familiar with behaves that way
(with the exception, I hear, of MimerSQL, which confuses
getColumnLabel() with SQL alias the way our new code does).

I'm not clear why a JDBC client would want or need to know the
"underlying" column name, if there even is one. I can see why
the driver might need to know that internally for purposes of
updateability but that's something different.

> Would you expect the results of getTableName() to return the underlying
> table or the table's alias in the query? To be consistent with your
> argument you'd have to claim the alias name which is useless here.

The alias. By SQL naming rules every column may also have a
table name component that can be used to disambiguate it from
other columns of the same name. If a query represents a single
table expression, and that expression has been given an alias
name (aka "range variable"), then every column takes that alias
as its table name.

Likewise with getCatalogName(int) except I believe a column loses
any catalog affiliation as soon as it's aliased in any way.

> Further code doesn't make a whole lot of sense doing:
>
> rs.getString(rsmd.getColumnName(1));
>
> Why wouldn't it just do:
>
> rs.getString(1);

In real life you would. I was just illustrating the expected
semantics.

Mike

On Thu, 29 Jul 2004, Mike Martin wrote:

> 2. Every other JDBC driver I'm familiar with behaves that way
> (with the exception, I hear, of MimerSQL, which confuses
> getColumnLabel() with SQL alias the way our new code does).

What do they return for getColumnLabel()? Previously we returned the same
value for both methods simply because we couldn't implement getColumnName
as we do now. If they all return the alias in getColumnLabel that would
be telling.

> I'm not clear why a JDBC client would want or need to know the
> "underlying" column name, if there even is one. I can see why
> the driver might need to know that internally for purposes of
> updateability but that's something different.
>

To allow client software to do the updates or allow the user to navigate
from a query result column to the base table or to/through foreign keys on
it. There could be a number of uses.

Kris Jurka

Kris Jurka wrote:
> > 2. Every other JDBC driver I'm familiar with behaves that way
> > (with the exception, I hear, of MimerSQL, which confuses
> > getColumnLabel() with SQL alias the way our new code does).
>
> What do they return for getColumnLabel()? Previously we returned the same
> value for both methods simply because we couldn't implement getColumnName
> as we do now. If they all return the alias in getColumnLabel that would
> be telling.

Most return the same thing as getColumnName(); not surprising
since that's a good default display title. I've heard that
some drivers return a string containing extra metadata fluff
about the column type.

Consider:

SELECT c1 + c2 FROM t1

The name/label distinction allows the DBMS/driver, if it wants,
to give the column a label for display purposes of, say,
"Expr c1+c2" or "Result, type=decimal(9,2)". But the column
has no SQL name. Nor could it have a name like "Expr c1+c2"
since that's not a valid SQL identifier.

> > I'm not clear why a JDBC client would want or need to know the
> > "underlying" column name, if there even is one. I can see why
> > the driver might need to know that internally for purposes of
> > updateability but that's something different.
>
> To allow client software to do the updates or allow the user to navigate
> from a query result column to the base table or to/through foreign keys on
> it. There could be a number of uses.

What about:

SELECT col1 AS price FROM t1
UNION
SELECT col2 AS price FROM t2

What is the "true" column name? SQL says that query produces a
table with one column named "PRICE".

Client code simply can't reverse-engineer query result columns to
find their origins. If a user is trying to do so for updating,
that's what updatable result sets are for, and that's why only a
small subset of queries are updatable.

>From http://java.sun.com/j2se/1.3/docs/guide/jdbc/spec2/jdbc2.1.frame5.html:

Due to differences in database implementations, the JDBC API
does not specify an exact set of SQL queries which must yield
an updatable result set for JDBC drivers that support
updatability. Developers can, however, generally expect
queries which meet the following criteria to produce an
updatable result set:

1. The query references only a single table in the database.
2. The query does not contain any join operations.
3. The query selects the primary key of the table it references.

In addition, an SQL query should also satisfy the conditions
listed below if inserts are to be performed.

4. The query selects all of the non-nullable columns in the
underlying table.
5. The query selects all columns that don't have a default
value.

Mike

Oliver Jowett wrote:
> Mike Martin wrote:
> > For programmatic purposes the column name concept is pretty
> > well defined by the docs on ResultSet.
>
> Can you point me to these docs? I can't see this requirement from a
> glance through the 1.4 javadoc, but the JDBC javadoc is pretty useless..

From the ResultSet Javadoc:

"The column name option is designed to be used when column
names are used in the SQL query that generated the result set.
For columns that are NOT explicitly named in the query, it
is best to use column numbers."

The only way to "name" a SQL query column is with AS. There's
corroboration in DatabaseMetaData:

public boolean supportsColumnAliasing() throws SQLException

Retrieves whether this database supports column aliasing.
If so, the SQL AS clause can be used to provide names for
computed columns or to provide alias names for columns as
required.

In both cases, "names" and not "labels".

I too wish the docs were more definitive on this. Googling on
the topic reveals that there has been confusion on it for some
time. Anyone know someone at Sun who could confirm the true
intent?

Mike

On Mon, 2 Aug 2004, Mike Martin wrote:

>
> [I want getColumnName to return the alias name used in a query.]
>

All the documentation you've pointed to is circumstancial at best. The
phrase "column name" is not a well defined term and seems to be used
differently in different places.

A little time with Google shows people desiring the current driver
behavior which shows there is some need for this. While it doesn't
show your side of the argument because not many drivers have implemented
it this way, you haven't given any other well defined behavior for
implementing getColumnLabel.

The bottom line is that I am comfortable with the driver's current
behavior. Perhaps not as comfortable as I originally was when the
frontend/backend protocol was adjusted specifically for this purpose, or
when the patch utilizing this new data was committed, but I believe it's a
logical and defensible position. You will need to find clear
documentation that this is incorrect or rally some other users to your
position.

Kris Jurka

On Thu, 29 Jul 2004, Oliver Jowett wrote:

> I notice that the implementation of updateable resultsets use
> Field.getColumnLabel() when generating the UPDATE/SELECT/etc SQL.
> Presumably this should be using Field.getColumnName() given the current
> semantics?
>

Fixed.

Kris Jurka

Kris Jurka wrote:
> All the documentation you've pointed to is circumstancial at best.

Come now, I think it's a little better than that. I guess I
don't see as much ambiguity in "... the SQL AS clause can be
used to provide names for computed columns" as you do.

> You will need to find clear
> documentation that this is incorrect or rally some other users to your
> position.

Then I call on all users using any of the following DBMS's
to weigh in on this issue:

SELECT PRICE AS ORIG_PRICE, PRICE + 1.00 AS PRICE FROM T

getColumnName(1) getColumnName(2)
DB/driver getColumnLabel(1) getColumnLabel(2)

Cloudscape ORIG_PRICE ORIG_PRICE PRICE PRICE
Firebird ORIG_PRICE ORIG_PRICE PRICE PRICE
SQL Server (JTDS) ORIG_PRICE ORIG_PRICE PRICE PRICE
SQL Server (MS) ORIG_PRICE ORIG_PRICE PRICE PRICE
MySQL Connector/J ORIG_PRICE ORIG_PRICE PRICE PRICE
Oracle thin ORIG_PRICE ORIG_PRICE PRICE PRICE
PostgreSQL 7.4 orig_price orig_price price price
PostgreSQL 7.5 price orig_price price ""

Those are the drivers I have handy at the moment. I have a couple
more I can try tomorrow. I invite others to post their results.

Of course, this is probably the wrong NG for a cross-DB call to
arms. :) But I should think that sample is large enough to cause
worry about how much code is going to be broken.

Mike

On Mon, 2 Aug 2004, Mike Martin wrote:

> SELECT PRICE AS ORIG_PRICE, PRICE + 1.00 AS PRICE FROM T
>
> getColumnName(1) getColumnName(2)
> DB/driver getColumnLabel(1) getColumnLabel(2)
>
> Cloudscape ORIG_PRICE ORIG_PRICE PRICE PRICE
> Firebird ORIG_PRICE ORIG_PRICE PRICE PRICE
> SQL Server (JTDS) ORIG_PRICE ORIG_PRICE PRICE PRICE
> SQL Server (MS) ORIG_PRICE ORIG_PRICE PRICE PRICE
> MySQL Connector/J ORIG_PRICE ORIG_PRICE PRICE PRICE
> Oracle thin ORIG_PRICE ORIG_PRICE PRICE PRICE
> PostgreSQL 7.4 orig_price orig_price price price
> PostgreSQL 7.5 price orig_price price ""

I think you have the name and column for 2 backwards. It should return ""
for the column name because it is a derived column, so there is no
underlying table column to return.

This shows the results are different, but it doesn't indicate why. Is it
simply because the other dbs can't implement getColumnName as we have?
I certainly wouldn't expect this to be a common feature.

Further it doesn't show that users can't write portable code using the
ResultSetMetaData calls. It shows all drivers returning the same value
for getColumnLabel that you would expect. Earlier you had speculated that
other drivers would return something different for this, but you haven't
shown that. Yes it may break existing code, but it doesn't show that they
weren't simply using the wrong call.

Kris Jurka

Mike Martin wrote:

> The only way to "name" a SQL query column is with AS. There's
> corroboration in DatabaseMetaData:
>
> public boolean supportsColumnAliasing() throws SQLException
>
> Retrieves whether this database supports column aliasing.
> If so, the SQL AS clause can be used to provide names for
> computed columns or to provide alias names for columns as
> required.
>
> In both cases, "names" and not "labels".

Is an "alias name" the same as a "column name"?

-O

Kris wrote:
> > PostgreSQL 7.5 price orig_price price ""
>
> I think you have the name and column for 2 backwards. It should return ""
> for the column name because it is a derived column, so there is no
> underlying table column to return.

Sorry, you're right. I inadvertently reversed them in the process
of table-ization.

> This shows the results are different, but it doesn't indicate why. Is it
> simply because the other dbs can't implement getColumnName as we have?
> I certainly wouldn't expect this to be a common feature.
>
> Further it doesn't show that users can't write portable code using the
> ResultSetMetaData calls. It shows all drivers returning the same value
> for getColumnLabel that you would expect. Earlier you had speculated that
> other drivers would return something different for this, but you haven't
> shown that. Yes it may break existing code, but it doesn't show that they
> weren't simply using the wrong call.

No, I speculated that a driver *could*, if it had a special pretty
print label available from the DBMS, return it from getColumnLabel().
Most DBMS's don't. Oracle does in SQL*Plus; for example:

column c1 heading "# OF ROWS" format 99,999,999;
column c2 heading "#_rows*row_len" format 9,999,999,999;
column c3 heading "PCT USED" format 999;

select num_rows c1,
num_rows*avg_row_len c2,
((num_rows*avg_row_len)/(blocks*&blksz))*100 c3
from perfstat.stats$tab_stats a
...

Does their driver support that? I don't know. But it is an
example of exactly what the docs for getColumnLabel() describe:
the "suggested title for use in printouts and displays". In the
absence of such support, doesn't it make sense that driver
writers would have simply defaulted label to column name?

I don't see anything ambiguous in "suggested title for use in
printouts and displays", nor anything that would even remotely
tie it to SQL alias. But I do see, and have given you, text that
explicitly ties column names to SQL aliases. Plus evidence that
many other well-known drivers work on that basis.

And you can't just ask everyone to switch their getColumnName() to
getColumnLabel(). It doesn't even work because of findColumn()
and rs.getXXX(String). Those methods are *required* to resolve a
duplicate name to the first matching column (ResultSet Javadoc).
With your code, my rs.getFloat("PRICE") *must* resolve to column
1, and that's simply the wrong answer.

Mike

Oliver Jowett wrote:
> > The only way to "name" a SQL query column is with AS. There's
> > corroboration in DatabaseMetaData:
> >
> > public boolean supportsColumnAliasing() throws SQLException
> >
> > Retrieves whether this database supports column aliasing.
> > If so, the SQL AS clause can be used to provide names for
> > computed columns or to provide alias names for columns as
> > required.
> >
> > In both cases, "names" and not "labels".
>
> Is an "alias name" the same as a "column name"?

In SQL, yes. A result column has a name, and the AS clause is
the way to reassign that name.

Try this on your favorite DBMS:

SELECT COL FROM (SELECT COL AS FOO FROM T) T1

It fails because the parenthesized query result has no column
named COL.

Mike

Kris Jurka wrote:

> This shows the results are different, but it doesn't indicate why. Is it
> simply because the other dbs can't implement getColumnName as we have?
> I certainly wouldn't expect this to be a common feature.
>
> Further it doesn't show that users can't write portable code using the
> ResultSetMetaData calls. It shows all drivers returning the same value
> for getColumnLabel that you would expect. Earlier you had speculated that
> other drivers would return something different for this, but you haven't
> shown that. Yes it may break existing code, but it doesn't show that they
> weren't simply using the wrong call.

I'm leaning towards Mike's argument here. It seems reasonable that the
column name returned is the same as what you'd use to look up a column
by name via ResultSet, and the only sensible thing to do in ResultSet
lookups is to use the column aliases.

The argument for exposing the real column name as a way to allow
portable updating of the underlying table seems weak given that other
drivers don't support the same behaviour. The more portable behaviour
seems to be to always use getColumnName() and forbid the use of AS in
queries that you want to be updatable.

getColumnLabel() isn't the right place for the underlying column name,
though. JDBC doesn't actually seem to have a mapping for this concept at
all. I'd suggest adding a PG-specific interface if we want to expose
this. How about:

RSMD.getColumnName() returns the column alias
RSMD.getColumnLabel() returns the column alias, or maybe something
like the column's comment (can you COMMENT ON columns?) later.
PGResultSetMetadata.getSourceColumnName() returns the underlying
table column name. The driver's updateable resultset code can use this
when constructing update SQL.

On a related note, if I execute this query:

SELECT * FROM footable f

should RSMD.getTableName() return "footable" or "f"? By analogy to
RSMD.getColumnName() it should return "f", but it seems more useful to
return "footable".

-O

On Tue, 3 Aug 2004, Oliver Jowett wrote:

> RSMD.getColumnLabel() returns the column alias, or maybe something
> like the column's comment (can you COMMENT ON columns?) later.

This is my major beef with the argument, what do you do for
getColumnLabel? Returning the column's comment (which you can do) isn't
the right thing either. I've got comments running to paragraph length
describing some of the more obscure columns in my databases. No other
driver in Mike's list showed an implementation of this different from
getColumnName, so what's the point? The spec authors must have had some
existing implementation to inspire this feature. The argument that
getColumnLabel and getColumnName should do the exact same thing just seems
wrong to me.

Kris Jurka

Oliver wrote:
> RSMD.getColumnName() returns the column alias
> RSMD.getColumnLabel() returns the column alias, or maybe something
> like the column's comment (can you COMMENT ON columns?) later.
> PGResultSetMetadata.getSourceColumnName() returns the underlying
> table column name. The driver's updateable resultset code can use this
> when constructing update SQL.

I like that, not just because I think it jives with the spec
but because a new method can explicitly document that the
source column name is not available if the result column has
multiple sources.

> On a related note, if I execute this query:
>
> SELECT * FROM footable f
>
> should RSMD.getTableName() return "footable" or "f"? By analogy to
> RSMD.getColumnName() it should return "f", but it seems more useful to
> return "footable".

According to SQL "f" is the right answer. One way convince yourself
is to observe the difference between:

SELECT f.col FROM footable f
SELECT footable.col FROM footable f

The former works as you might expect. The latter fails on some
DBMS's and I'm pretty sure it's invalid SQL. Interestingly, on
PG the latter is evaluated as though it were:

SELECT f2.col FROM footable f1 CROSS JOIN footable f2

which for a footable of N rows returns N*N rows!

Mike

Kris Jurka wrote:
>
> On Tue, 3 Aug 2004, Oliver Jowett wrote:
>
>
>> RSMD.getColumnLabel() returns the column alias, or maybe something
>>like the column's comment (can you COMMENT ON columns?) later.
>
>
> The spec authors must have had some
> existing implementation to inspire this feature. The argument that
> getColumnLabel and getColumnName should do the exact same thing just seems
> wrong to me.

That wasn't the argument. The column name is just that a good default
label if you have nothing better to hand. If Postgres had a
(hypothetical) column label field associated with each column, for
example, we could return that.

As for existing implementations, a quick google turned this up:

http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/rbafymst69.htm

which seems to indicate that DB2 has a column display label (via LABEL
ON COLUMN) in addition to column name & column comment.

-O

Kris wrote:
> > RSMD.getColumnLabel() returns the column alias, or maybe something
> > like the column's comment (can you COMMENT ON columns?) later.
>
> This is my major beef with the argument, what do you do for
> getColumnLabel? Returning the column's comment (which you can do) isn't
> the right thing either. I've got comments running to paragraph length
> describing some of the more obscure columns in my databases. No other
> driver in Mike's list showed an implementation of this different from
> getColumnName, so what's the point? The spec authors must have had some
> existing implementation to inspire this feature. The argument that
> getColumnLabel and getColumnName should do the exact same thing just seems
> wrong to me.

If I'm not mistaken the spec authors were heavily influenced by
ODBC. The two specs have far too much alignment for it to have
been otherwise.

Have a look at the FieldIdentifiers table at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlcolattribute.asp

SQLColAttribute is the ODBC function that retrieves result set
metadata.

SQL_DESC_NAME
The column alias, if it applies. If the column alias does not
apply, the column name is returned. In either case,
SQL_DESC_UNNAMED is set to SQL_NAMED. If there is no column
name or a column alias, an empty string is returned and
SQL_DESC_UNNAMED is set to SQL_UNNAMED.

This information is returned from the SQL_DESC_NAME record field
of the IRD.

SQL_DESC_LABEL
The column label or title. For example, a column named EmpName
might be labeled Employee Name or might be labeled with an alias.

If a column does not have a label, the column name is returned.
If the column is unlabeled and unnamed, an empty string is
returned.

Note the close alignment of all the Fields to RSMD concepts. Note
also that they have explicit fields for SQL_DESC_BASE_COLUMN_NAME
and SQL_DESC_BASE_TABLE_NAME which JDBC evidently chose not to
inherit. I think that's what the new V3 info would have corresponded
to.

Bottom line is, I think PG does not have a concept that corresponds
directly to getColumnLabel(). By ODBC rules a label should default
to the column's alias or name.

Mike

On Tue, 3 Aug 2004, Oliver Jowett wrote:

> As for existing implementations, a quick google turned this up:
>
> http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/rbafymst69.htm
>
> which seems to indicate that DB2 has a column display label (via LABEL
> ON COLUMN) in addition to column name & column comment.
>

This is certainly interesting. OK, I'm pretty much convinced, but would
like to go over some issues with regard to getTableName and getSchemaName.

I believe we agree that for consistency's sake we should return the table
alias (if any) in getTableName. How could we do that, and what would the
purpose be? At the moment there is no way to even detect that a column's
source has been aliased and if you could what would a user do with that
alias? The inability to detect an alias means that we cannot report the
base table and schema names only in the non-aliased case. Is it worth
creating pg specific getBaseXXXName functions for table/schema while the
official ones go unused (return "" always) solely for consistency?

Kris Juraka

Kris Jurka:
> I believe we agree that for consistency's sake we should return the table
> alias (if any) in getTableName. How could we do that, and what would the
> purpose be?

CREATE TABLE person (
id int4 primary key,
parent_id int4 references person (id),
name varchar(50)
)

SELECT * FROM person inner join person parent on person.parent_id =
parent.id

rs.getTableName() distinguishes person.name from parent.name.

In my experience, drivers correctly implement getTableName()
*far* less frequently. I've never been able to rely on it.

Of the drivers I have handy, only DB2J and MySQL get it right.
Cloudscape, Firebird, hsqldb, mssqlserver, and pg75 all return
the underlying base table name. JTDS, Oracle, and pg74 all
return empty strings!

Mike

On Tue, 3 Aug 2004, Mike Martin wrote:

> SELECT * FROM person inner join person parent on person.parent_id =
> parent.id
>
> rs.getTableName() distinguishes person.name from parent.name.
>
> In my experience, drivers correctly implement getTableName()
> *far* less frequently. I've never been able to rely on it.
>
> Of the drivers I have handy, only DB2J and MySQL get it right.
> Cloudscape, Firebird, hsqldb, mssqlserver, and pg75 all return
> the underlying base table name. JTDS, Oracle, and pg74 all
> return empty strings!
>

You seem surprised that it would return an empty string, while that was
what I was planning on making it do. Why should it return the base table
name when it can't determine if it has been aliased? Isn't this returning
incorrect information?

Kris Jurka

Kris wrote:
> On Tue, 3 Aug 2004, Mike Martin wrote:
>
> > SELECT * FROM person inner join person parent on person.parent_id =
> > parent.id
> >
> > rs.getTableName() distinguishes person.name from parent.name.
> >
> > In my experience, drivers correctly implement getTableName()
> > *far* less frequently. I've never been able to rely on it.
> >
> > Of the drivers I have handy, only DB2J and MySQL get it right.
> > Cloudscape, Firebird, hsqldb, mssqlserver, and pg75 all return
> > the underlying base table name. JTDS, Oracle, and pg74 all
> > return empty strings!
>
> You seem surprised that it would return an empty string, while that was
> what I was planning on making it do. Why should it return the base table
> name when it can't determine if it has been aliased? Isn't this returning
> incorrect information?

You're right, that exclamation point is undeserved.

Based on the description of getTableName() (and getSchemaName()
and getCatalogName()) I would think a driver should either
return the correct information or "" if not available.

Mike

Fixed, in this commit message.

----------------------------
revision 1.1
date: 2004/08/05 03:50:19; author: jurka; state: Exp;
Revert ResultSetMetaData methods getColumnName, getTableName and
getSchemaName to previous implementation that returned the column's
alias if available and "" for the table and schema. Move the new
functionality to access the underlying sources of the query to a
new PGResultSetMetaData interface which offers getBaseXXXName.

Per complaints of Mike Martin.