Bug #68098 DatabaseMetaData.getIndexInfo sorts results incorrectly

• Submitted: 16 Jan 2013 18:16
• Modified: 26 Jun 2013 22:14
• Reporter: Andrew Gaul (OCA)
• Status: Closed
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 5.1.22
• OS: Any
• Assigned to: Filipe Silva
• CPU Architecture: Any

[16 Jan 2013 18:16] Andrew Gaul

Description:
DatabaseMetaData.getIndexInfo specifies a sort order for its results:

They are ordered by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION.

http://docs.oracle.com/javase/7/docs/api/java/sql/DatabaseMetaData.html#getIndexInfo(java...., java.lang.String, java.lang.String, boolean, boolean)

However, my test shows that this is not the case:

java.lang.AssertionError: expected:<[PRIMARY, index2, index3]> but was:<[PRIMARY, index3, index2]>

How to repeat:
    @Test
    public void testGetIndexInfo() throws Exception {
        Connection conn = dataSource.getConnection();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.prepareStatement(
                    "CREATE TABLE table_name (" +
                    " column1 INT NOT NULL," +
                    " column2 INT NOT NULL," +
                    " column3 INT NOT NULL," +
                    " PRIMARY KEY (column1))");
            stmt.execute();
            stmt.close();
      
            stmt = conn.prepareStatement(
                    "CREATE INDEX index3 ON table_name (column3)");
            stmt.execute();
            stmt.close();
      
            stmt = conn.prepareStatement(
                    "CREATE INDEX index2 ON table_name (column2)");
            stmt.execute();
            stmt.close();
      
            List<String> indexNames = Lists.newArrayList();
            DatabaseMetaData dbmeta = conn.getMetaData();
            rs = dbmeta.getIndexInfo(/*catalog=*/ null, /*schema=*/ null,
                    "table_name", /*unique=*/ false, /*approximate=*/ false);
            while (rs.next()) {
                indexNames.add(rs.getString("INDEX_NAME"));
            }
            rs.close();
    
            List<String> sortedIndexNames = Lists.newArrayList(indexNames);
            Collections.sort(sortedIndexNames);
            assertEquals(sortedIndexNames, indexNames);
        } finally {
            DatabaseUtils.closeQuietly(rs);
            DatabaseUtils.closeQuietly(stmt);
            DatabaseUtils.closeQuietly(conn);
        }           
    }

[25 Jan 2013 17:37] Sveta Smirnova

Thank you for the report.

Verified as described.

[25 Jan 2013 18:41] Sveta Smirnova

test case for c/J testsuite

Attachment: bug68098.java (text/x-java), 2.67 KiB.

[26 Jun 2013 22:14] Daniel So

Added changelog entry for Connector/J version 5.1.26: 

"The results returned by the method DatabaseMetaData.getIndexInfo() were not sorted in the order described in the JDBC specification (NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION)."