MariaDB Client Library for Java Applications
  1. MariaDB Client Library for Java Applications
  2. CONJ-41

connection.getMetaData().getPrimaryKeys() returns wrong ordinal_colum when primary key is not in the first colum.

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: jdbc-1.1.1
    • Fix Version/s: jdbc-1.1.3
    • Labels:
      None
    • Global Rank:
      3065

      Description

      Try the following

      create database SomeTest;
      
      use SomeTest;
      
      CREATE TABLE `ExampleTable` (
        `someId` smallint(6) NOT NULL DEFAULT '0',
        `somePK` decimal(19,0) NOT NULL DEFAULT '0',
        `someValue1` decimal(19,0) DEFAULT NULL,
        `someValue2` smallint(6) DEFAULT NULL,
        `someValue3` datetime DEFAULT NULL,
        `someValue4` datetime DEFAULT NULL,
        PRIMARY KEY (`somePK`),
        KEY `IN_SOME_ID` (`someId`)
      ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
      

      When you use the query like it is coded in MySQLDatabaseMetaData to query the primary keys with getPrimaryKeys()
      the wrong information_schema table is queried.

      Consider the follwing testcase:

      SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, NULL PK_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_KEY='pri' AND (ISNULL(database()) OR (TABLE_SCHEMA = database())) AND (TABLE_NAME LIKE 'ExampleTable') ORDER BY column_name;
      +-----------+-------------+--------------+-------------+---------+---------+
      | TABLE_CAT | TABLE_SCHEM | TABLE_NAME   | COLUMN_NAME | KEY_SEQ | PK_NAME |
      +-----------+-------------+--------------+-------------+---------+---------+
      | SomeTest  |        NULL | ExampleTable | somePK      |       2 |    NULL |
      +-----------+-------------+--------------+-------------+---------+---------+
      

      As KEY_SEQ which is the ordinal position of the primary key should be returned 1 and not the column of the primary key. The Sun jdbc implementation uses a own resultset which is build against "show keys from table"

      show keys from ExampleTable;
      +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | Table        | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
      +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      | ExampleTable |          0 | PRIMARY    |            1 | somePK      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
      | ExampleTable |          1 | IN_SOME_ID |            1 | someId      | A         |        NULL |     NULL | NULL   |      | BTREE      |         |               |
      +--------------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
      

      As Seq_in_index which is mapped as KEY_SEQ is returned 1, the right value.
      The issue can be solved by using a query against the table KEY_COLUMN_USAGE here as example:

      SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION KEY_SEQ, NULL PK_NAME  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  WHERE (ISNULL(database()) OR (TABLE_SCHEMA = database())) AND (TABLE_NAME LIKE 'ExampleTable') ORDER BY column_name;
      

        Issue Links

          Activity

          Hide
          Frank S added a comment -

          Sorry for the bad formatting, I'm not able to edit the task after I have created it.

          Show
          Frank S added a comment - Sorry for the bad formatting, I'm not able to edit the task after I have created it.
          Hide
          Frank S added a comment -

          FIX:

          public ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException

          { String sql = "SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, " + "ORDINAL_POSITION KEY_SEQ, NULL PK_NAME " + "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE " + "WHERE " + catalogCond("TABLE_SCHEMA", catalog) + "AND " + patternCond("TABLE_NAME", table) + " ORDER BY column_name;"; return executeQuery(sql); }
          Show
          Frank S added a comment - FIX: public ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException { String sql = "SELECT TABLE_SCHEMA TABLE_CAT, NULL TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, " + "ORDINAL_POSITION KEY_SEQ, NULL PK_NAME " + "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE " + "WHERE " + catalogCond("TABLE_SCHEMA", catalog) + "AND " + patternCond("TABLE_NAME", table) + " ORDER BY column_name;"; return executeQuery(sql); }

            People

            • Assignee:
              Vladislav Vaintroub
              Reporter:
              Frank S
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - 0 minutes
                0m
                Logged:
                Time Spent - 3 hours
                3h