[#CONJ-41] connection.getMetaData().getPrimaryKeys() returns wrong ordinal_colum when primary key is not in the first colum.

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;

[CONJ-41] connection.getMetaData().getPrimaryKeys() returns wrong ordinal_colum when primary key is not in the first colum. Created: 2013-05-23 Updated: 2013-09-20 Resolved: 2013-06-11
Status:	Closed
Project:	MariaDB Connector/J
Component/s:	None
Affects Version/s:	1.1.1
Fix Version/s:	1.1.3

[CONJ-41] connection.getMetaData().getPrimaryKeys() returns wrong ordinal_colum when primary key is not in the first colum. Created: 2013-05-23 Updated: 2013-09-20 Resolved: 2013-06-11