Is it correct that "ResultSet.getMetaData.getTableName(col)" of postgresql's jdbc driver is always returning an empty string?

Question

When I use postgresql, I found following code:

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from t");

String tableName = rs.getMetaData().getTableName(1);
System.out.println(tableName);

It prints an empty string.

So I checked the source code, and found the method org.postgresql.jdbc2.AbstractJdbc2ResultSetMetaData#getTableName always returns an empty string.

The source code is:

public abstract class AbstractJdbc2ResultSetMetaData implements PGResultSetMetaData {

    /*
     * @param column the first column is 1, the second is 2...
     * @return column name, or "" if not applicable
     * @exception SQLException if a database access error occurs
     */
    public String getTableName(int column) throws SQLException
    {
        return "";
    }
}

You can see it just return a "".

I found a discussion about this, please see: http://archives.postgresql.org/pgsql-jdbc/2009-12/msg00100.php

They think "rs.getMetaData.getTableName(col)" should return the alias name in query not the underlying table name. But which is hard to implement, so it's better to leave it empty.

Also they gave a method to get the table name, use:

PGResultSetMetaData.getBaseTableName() 

Sample:

ResultSet rs = stmt.executeQuery("select * from x");
// convert it to PGResultSetMetaData 
PGResultSetMetaData meta = (PGResultSetMetaData)rs.getMetaData(); 
String tableName = meta.getBaseTableName(1);

Now it can print the correct table name.

I don't know the implementation of postgresql is correct, but returning the underlying table name is much more useful than an empty string, and, most of other databases provides underlying table name instead of an empty string.

I have a problem using play2's anorm framework with postgesql: Play2's anorm can't work on postgresql, but that works well on other databases.

What do you think the correct implementation of postgresql's jdbc driver? Return an empty string, underlying table name, or something else?

Answer 1

I would say that returning an empty string is obviously an incorrect implementation of the interface, since the table name could never be considered to be an empty string.

The problem that I believe they are struggling with is that while their current implementation is wrong, once they choose an implementation they will be stuck with it until they decide that breaking dependencies on the behaviour is acceptable. Therefore, they choose to add a method whose name is unambiguous and provide the data that most users were expecting to come from getTableName, and leave an obviously broken implementation of the getTableName method until some consensus is reached on what it should return or until a patch is submitted that implements the consensus.

My gut reaction is that the method getTableName should return the alias being used for that table. A table could be joined with itself, and using the alias would allow you to identify which was being referenced. A table might have been generated in the query (such as unnesting an array), and therefore not even have a table name in the database. If you make the decision “absolutely always, getTableName returns the alias”, then at least users know what to expect; otherwise, you end up with it not being obvious what the method should return.

However, even if I assume that my gut reaction is “the correct implementation”, it raises the issue of compatibility. It is desirable that it be possible to switch from another DBMS to PostgreSQL with as little investment as possible, if one of PostgreSQL’s goals is to grow in popularity. Therefore, things like “how do other JDBCs implement the java.sql interfaces?” become relevant. As you say, a framework exists that has expectations of how the ResultSetMetaData should be implemented, and it is likely not the only one with certain expectations of how java.sql interfaces will be implemented.

Whichever implementation they end up choosing is going to be a tradeoff, so I can see why “kick the can down the road” is their choice. Once they choose the tradeoff they want to make, they are locked in.

EDIT: I would suggest that throwing an exception regarding not implemented would be better than just silently failing. I expect that frameworks that rely on a specific implementation of getTableName will not have much use for empty string anyway, and either error or themselves fail silently.