MySQL Bugs: #1699: Create Index has Incorrect parsing of ASC / DESC with Multi Column Indexes

Bug #1699	Create Index has Incorrect parsing of ASC / DESC with Multi Column Indexes
Submitted:	29 Oct 2003 5:09	Modified:	29 Oct 2003 12:50
Reporter:	Stephen Dakin	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.16	OS:	Windows (Win2000)
Assigned to:		CPU Architecture:	Any

Description:
From your reference manual:

'An index_col_name specification may end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently they are parsed but ignored; index values are always stored in ascending order. '

THE BUG
The parsing is incorrect however for multi column indexes.

The syntax 
'create UNIQUE index anind on (col1 ASC, col2 ASC)' 
creates a unique index on col1. 

If the syntax is as follows:
'create UNIQUE index anind on (col1, col2 )'
the index is a composite index.

I have not tested with non unique indexes, but suspect the query parser to be the problem.

How to repeat:
On any test table with two or more columns (mine were a decimal 19,4 and a smallint (6)) try to create a unique composite index.

FYI my exact syntax was as follows:
CREATE  UNIQUE  INDEX ContainerCWB ON Containers ( CWB, CWBSourceID )

tabledef as follows
# Host: localhost
# Database: traffic
# Table: 'containers'
# 
CREATE TABLE `containers` (
  `Tag` int(11) default NULL,
  `Class_Id` smallint(6) default NULL,
  `Edit_Id` smallint(6) default NULL,
  `Origin` char(6) default NULL,
  `Destination` char(6) default NULL,
  `CreatedDT` datetime default NULL,
  `Status` smallint(6) default NULL,
  `Weight` int(11) default NULL,
  `CWB` decimal(19,4) default NULL,
  `CWBSOURCEID` smallint(6) default NULL,
  `ContainerTag` int(11) default NULL,
  `PossessedBy` char(6) default NULL,
  UNIQUE KEY `ContainerTagi` (`Tag`)
) TYPE=MyISAM; 

Suggested fix:
Fix the parser

I cannot duplicate this issue using the official MySQL AB binary release of 4.0.16 for Windows.  The command is parsed correctly and the index is created successfully.

If it does continue to occur, please comment with the exact CREATE INDEX statement you are using and then the output of SHOW CREATE TABLE containers; after you have created the index.

Thank you

Please accept my apologies. 

I upgraded my engine from 4.0.14 to 4.0.16 yesterday, and have to confess that I must have run that against the 4.0.14 engine (although not sure how I managed that). 
I can confirm that I am now unable to recreate the bug on 4.0.16.
Rgds, Steve