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 Email Updates:
Status: Closed Impact on me:
None 
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] Miguel Solorzano
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