Uploaded image for project: 'MariaDB Connector/J'
  1. MariaDB Connector/J
  2. CONJ-41

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

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.1
    • Fix Version/s: 1.1.3
    • Component/s: None
    • Labels:
      None

      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;
      

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

              Hide
              framas Frank S added a comment -

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

              Show
              framas Frank S added a comment - Sorry for the bad formatting, I'm not able to edit the task after I have created it.
              Hide
              framas 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
              framas 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:
                  wlad Vladislav Vaintroub
                  Reporter:
                  framas 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