MySQL Bugs: #48228: UNIQUE_KEY_FLAG not respected for multiple columns in unique key

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

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

Verified with recent 5.0.88 and 5.1.41 from bzr on Mac OS X also.

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?

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    |                |
+-------+------------+------+-----+---------+----------------+

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. <<<

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.

This is not a CAPI bug. It is about how table column metadata is handled in
the server.