Bug #48228 UNIQUE_KEY_FLAG not respected for multiple columns in unique key

Submitted: 22 Oct 2009 13:17
Reporter: Tonci Grgin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S2 (Serious)
Version:5.0.88, 5.1.31, 5.1.41 OS:Any
Assigned to: CPU Architecture:Any

[22 Oct 2009 13:17] Tonci Grgin
Description:
Looking into mysql_com.h we see:
#define UNIQUE_KEY_FLAG 4		/* Field is part of a unique key */

but this is not what I see in cl client:
Field   2:  `col2`
Catalog:    `def`
Database:   `test`
Table:      `keys_test`
Org_table:  `keys_test`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     5
Max_length: 0
Decimals:   0
Flags:      MULTIPLE_KEY PART_KEY

Field 2 is missing UNIQUE_KEY_FLAG
---
Field   3:  `col3`
Catalog:    `def`
Database:   `test`
Table:      `keys_test`
Org_table:  `keys_test`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     5
Max_length: 0
Decimals:   0
Flags:      PART_KEY

Field 3 is missing UNIQUE_KEY_FLAG and MULTIPLE_KEY_FLAG

It does not matter if fields are nullable or not.

How to repeat:
drop table if exists keys_test;
create table keys_test(
Id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, col2 VARCHAR(5), 
col3 VARCHAR(5), col4 VARCHAR(5), col5 BLOB, col6 int unique,
UNIQUE Key_Uni(col2, col3)); 

mysql> select * from keys_test;
Field   1:  `Id`
Catalog:    `def`
Database:   `test`
Table:      `keys_test`
Org_table:  `keys_test`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NOT_NULL PRI_KEY AUTO_INCREMENT NUM PART_KEY

Field   2:  `col2`
Catalog:    `def`
Database:   `test`
Table:      `keys_test`
Org_table:  `keys_test`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     5
Max_length: 0
Decimals:   0
Flags:      MULTIPLE_KEY PART_KEY

Field   3:  `col3`
Catalog:    `def`
Database:   `test`
Table:      `keys_test`
Org_table:  `keys_test`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     5
Max_length: 0
Decimals:   0
Flags:      PART_KEY

Field   4:  `col4`
Catalog:    `def`
Database:   `test`
Table:      `keys_test`
Org_table:  `keys_test`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     5
Max_length: 0
Decimals:   0
Flags:

Field   5:  `col5`
Catalog:    `def`
Database:   `test`
Table:      `keys_test`
Org_table:  `keys_test`
Type:       BLOB
Collation:  binary (63)
Length:     65535
Max_length: 0
Decimals:   0
Flags:      BLOB BINARY

Field   6:  `col6`
Catalog:    `def`
Database:   `test`
Table:      `keys_test`
Org_table:  `keys_test`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      UNIQUE_KEY NUM PART_KEY
[22 Oct 2009 13:23] Valeriy Kravchuk
Verified with recent 5.0.88 and 5.1.41 from bzr on Mac OS X also.
[22 Oct 2009 13:42] Tonci Grgin
Actually, Field 3 is *not* missing MULTIPLE_KEY_FLAG but it is wrongly put into Field 2 according to http://dev.mysql.com/doc/refman/5.1/en/c-api-datatypes.html:
MULTIPLE_KEY_FLAG 	Field is part of a nonunique key

Maybe docs are wrong?
[22 Oct 2009 14:47] Paul DuBois
MULTIPLE_KEY is set if an index can contain multiple identical values. This can occur even for a UNIQUE index, if the columns in the index allow NULL values (as is the case for the keys_test table), because such a UNIQUE index allows multiple NULL entries.

It does appear that MULTIPLE_KEY is set only for the *first* column of the index.

mysql> desc keys_test;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| Id    | int(11)    | NO   | PRI | NULL    | auto_increment |
| col2  | varchar(5) | YES  | MUL | NULL    |                |
| col3  | varchar(5) | YES  |     | NULL    |                |
| col4  | varchar(5) | YES  |     | NULL    |                |
| col5  | blob       | YES  |     | NULL    |                |
| col6  | int(11)    | YES  | UNI | NULL    |                |
+-------+------------+------+-----+---------+----------------+
[22 Oct 2009 15:01] Tonci Grgin
Somewhere around middle of first comment:
Field 3 is missing UNIQUE_KEY_FLAG and MULTIPLE_KEY_FLAG

It does not matter if fields are nullable or not. <<<
[22 Oct 2009 15:01] Paul DuBois
Well, I tried again after declaring col2 and col3 as NOT NULL, and the MUL value is still the same in the DESC output. I don't know how to account for that.
[16 Apr 2013 15:15] Igor Solodovnikov
This is not a CAPI bug. It is about how table column metadata is handled in
the server.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.057 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.