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

Bug #1673	DatabaseMetaData getColumns() ordinalPosition error with columnNamePattern
Submitted:	27 Oct 2003 7:27	Modified:	19 Nov 2003 12:29
Reporter:	Tony Prichard
Status:	Closed
Category:	Connector/J	Severity:	S3 (Non-critical)
Version:	3.1.0 alpha and previous	OS:	Microsoft Windows (MS Windows)
Assigned to:	Mark Matthews	Target Version:

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!