MySQL Bugs: #1673: DatabaseMetaData getColumns() ordinalPosition error with columnNamePattern

Bug #1673	DatabaseMetaData getColumns() ordinalPosition error with columnNamePattern
Submitted:	27 Oct 2003 6:27	Modified:	19 Nov 2003 11:29
Reporter:	Tony Prichard	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.0 alpha and previous	OS:	Windows (MS Windows)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
In the getColumns method within the DatabaseMetaData class there is a variable ordPos that is used to track the Ordinal Position of the column within the table.

As the data for each column is processed ordPos is simply incremented and assigned to rowVal[16]. This works fine when all columns for a table are retrieved.

When a column name pattern specifying a particular column is supplied only one column is returned, as expected. The ordinal position that is assigned to the column in this case is always one. 

This may cause the same column to have a different ordinal position depending on whether the column data was retrieved with or without a column name pattern.

How to repeat:
Create a table TEST with more than one column (e.g. FIRST_INT, SECOND_INT).

Call getColumns(databasename, null, "TEST", null) to retrieve a ResultSet.
Process ResultSet to obtain data for column SECOND_INT, this will be in the second row of data in the ResultSet

Call getColumns(databasename, null, "TEST", "SECOND_INT") to retrieve a ResultSet.
Process ResultSet to obtain data for SECOND_INT, this will be in first row of data in the ResultSet.

The ordinal position for SECOND_INT from the first ResultSet will be 2, whereas
the ordinal position for SECOND_INT from the second ResultSet will be 1.

MySQL's internal metadata does not yet provide the information that Connector/J would need to provide the correct ordinal position when a column filter expression is used.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

I found a way to fix this. It requires two queries (once to get the _full_ column list, and then re-map the ordinals). This should become more efficient once MySQL has full system tables (looks like 5.0 or 5.1).

The fix is in the 3.1 tree. You can check out a nightly snapshot from http://downloads.mysql.com/snapshots.php after 00:00 GMT on November 20th.

Thanks for your bug report!