Bug #5308 Cluster: can't create UNIQUE INDEX unless all columns are NOT NULL

• Submitted: 30 Aug 2004 23:57
• Modified: 31 Aug 2004 15:04
• Reporter: Trudy Pelzer
• Status: Closed
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S2 (Serious)
• Version: 4.1.4-gamma (ndb)
• OS: Linux (SuSE 9.1)
• Assigned to: Magnus Blåudd
• CPU Architecture: Any

[30 Aug 2004 23:57] Trudy Pelzer

Description:
If one creates a table as follows: 
 
mysql> show create table sp_n; 
+-------+-----------------------+ 
| Table | Create Table         |                                                                                                                                                   
+-------+------------------------+ 
| sp_n  | CREATE TABLE `sp_n` ( 
  `sno` char(3) default NULL, 
  `pno` char(3) NOT NULL default '', 
  `qty` decimal(5,0) default NULL 
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 | 
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
1 row in set (0.00 sec) 
 
and then attempts to create a unique index, the server  
returns the following error: 
ERROR 1121 (42000): Column 'sno' is used with UNIQUE or INDEX but is not defined as NOT NULL 
 
But a unique index's keys are not required to be NOT NULL, so 
the index should be created (it is with MyISAM and InnoDB). 
 
 
 

How to repeat:
create table sp (sno char(3), pno char(3) NOT NULL, qty decimal(5,0)); 
create unique index sp_ind on sp (sno,pno); 
show index from sp; 
create table sp_n (sno char(3), pno char(3) NOT NULL, qty decimal(5,0)) ENGINE=ndbcluster; 
create unique index sp_indn on sp_n (sno,pno);

[31 Aug 2004 5:17] MySQL Verification Team

Thank you for the bug report I was able to repeat:

mysql> create table sp_n (sno char(3), pno char(3) NOT NULL, qty decimal(5,0))
    -> ENGINE=ndbcluster;
Query OK, 0 rows affected (0.40 sec)

mysql> create unique index sp_indn on sp_n (sno,pno);
ERROR 1121 (42000): Column 'sno' is used with UNIQUE or INDEX but is not defined as NOT NULL

[31 Aug 2004 7:48] Jonas Oreland

The feature was originaly designed this way. (To mimic oracle's index null handling)
But, it's a wl-entry that is being worked on.

[31 Aug 2004 15:04] Martin Skold

WL#1783 Support for nullable columns in unique index
is now completed and pushed to mysql-4.1