minor bug in 3.23

[Michael Widenius]

>>>>> "Tani" == Tani Hosokawa <unknown@stripped> writes:

Tani> I'm not really sure where the fault lies, but...
mysql> explain programs;
Tani>
> +---------+-----------------------------------+------+-----+---------+-------+---------------------------------+
Tani> | Field   | Type                              | Null | Key | Default |
Tani> Extra | Privileges                      |
Tani>
> +---------+-----------------------------------+------+-----+---------+-------+---------------------------------+
Tani> | program | enum('signup','unique','sliding') | YES  | PRI | NULL    |
Tani> | select,insert,update,references |
Tani> | type    | enum('basic','sliding','signup')  | YES  |     | NULL    |
Tani> | select,insert,update,references |
Tani> | sites   | set('mt')                         | YES  |     | NULL    |
Tani> | select,insert,update,references |
Tani>
> +---------+-----------------------------------+------+-----+---------+-------+---------------------------------+

Tani> $ mysqldump -d -pxxxxxxx xxxxxx programs   
Tani> # MySQL dump 6.3
Tani> #
Tani> # Host: localhost    Database: rscash
Tani> #--------------------------------------------------------
Tani> # Server version        3.23.4-alpha

Tani> #
Tani> # Table structure for table 'programs'
Tani> #
Tani> CREATE TABLE programs (
Tani> program enum('signup','unique','sliding'),
Tani> type enum('basic','sliding','signup'),
Tani> sites set('mt'),
Tani> PRIMARY KEY (program)
Tani> );

Tani> however, if I try to execute that:

Tani> ERROR 1171: All parts of a PRIMARY KEY must be NOT NULL;  If you need NULL
Tani> in a key, use UNIQUE instead

Tani> This is apparently because I created the table with program as null, but I
Tani> was later able to use an "ALTER TABLE MODIFY PROGRAM
Tani> ENUM(signup','unique','sliding')" to get a table with a primary key that
Tani> allows nulls.

Tani> Thing is, I recall hearing (before 3.23 was released) that 3.23 was going
Tani> to allow indexing on null columns.  Is it the mysql shell that's
Tani> mistakenly rejecting my create table, or MyISAM for allowing that alter
Tani> table call go to thru in the first place?

Hi!

MySQL 3.23 supports NOT NULL for UNIQUE keys, but not for PRIMARY KEYS 
(this is to conform to ANSI SQL).

Here is a patch to fix the above problem:

*** /my/monty/master/mysql-3.23.6-alpha/sql/sql_table.cc	Wed Nov 10 14:38:14 1999
--- ./sql_table.cc	Sat Nov 13 00:41:28 1999
***************
*** 939,945 ****
      }
      if (key_parts.elements)
        key_list.push_back(new Key(key_info->flags & HA_NOSAME ?
! 				 Key::UNIQUE :
  				 Key::MULTIPLE,
  				 key_name,key_parts));
    }
--- 939,946 ----
      }
      if (key_parts.elements)
        key_list.push_back(new Key(key_info->flags & HA_NOSAME ?
! 				 (!my_strcasecmp(key_name, "PRIMARY") ?
! 				  Key::PRIMARY  : Key::UNIQUE) :
  				 Key::MULTIPLE,
  				 key_name,key_parts));
    }

Regards,
Monty