Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
1.1.1
-
None
-
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; |
Attachments
Issue Links
- relates to
-
-
- Closed
-