MySQL Bugs: #27175: invalid colum attributes in create/alter table

Bug #27175	invalid colum attributes in create/alter table
Submitted:	15 Mar 2007 14:48	Modified:	26 Mar 2008 13:10
Reporter:	Martin Friebe (Gold Quality Contributor) (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.38 4.1.23	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	column, Contribution, CREATE TABLE, parser, PatchRefused, qc

Description:
The parser for column type/attribute is often not strict enough.

This results in the ability to declare nonsense and/or invalid columns.

nonsense declaration with reocurring or even clashing attributes. Those can happen in create, alter and declare statements
 a int(11) unsigned unsigned
 a int(11) unsigned signed
 a int(11) null  not null

invalid statements are those where a mandotary attribute is later undone.
auto_increment columns must be non-null (and specifing autoincrement makes a column non-null).
BUT
 drop table if exists t1;
 create table t1 (a int unique auto_increment null, b char(11) );
 insert into t1 (b) values ('a');
 insert into t1 (b) values ('b');
 select * from t1;
 drop table if exists t1;

The above creates a auto_increment column wich can be null. (And by the way it will not auto-increment)

How to repeat:
see sql above and test case attached.

The test case, does not include the sql (insert / select) from above, but only the create statement.
I believe the table from the sql should fail at creation time (due to invalid column specs)

Various other create table statements in the test case, that should fail

Suggested fix:
I believe the parser should be modified to detect any of the following:

1) repeated occurance of the same atribute, like
 a int(11) unsigned signed
 a int(11) null not  not null

2) attributes that attempt to undo explicit requirements, such as
 a int(11) null not     null
 a int(11) primary key  null

I have created a patch, that modifes the parser to include the above checks.

There also is a 2nd (large) test case, containing a huge amount of correct create and alter statements. The resuld for it has been recorded with an unmodified mysql server. This should assure, that the patch does not break any correct sql

--
one more thing:

The multi_update.test contains the following invalid sql:

create table `t2` (`c2_id` int(10) unsigned NULL auto_increment, `c2_p_id` int(10) unsigned NOT NULL default '0', `c2_note` text NOT NULL, `c2_active` tinyint(1) unsigned NOT NULL default '1', PRIMARY KEY (`c2_id`), KEY `c2_p_id` (`c2_p_id`) );

Note that the column is first declared "Null", then "auto_inc". It behaves well, because the auto_inc overwrites the earlier "null".
(Yet it seems an incorrect declaration, and it will fail with the above patch)

I believe this to be a typo, and have added a patch for it

test case

Attachment: column_creation.tar.gz (application/x-gzip, text), 716 bytes.

additional test for patch

Attachment: column_creation2.tar.gz (application/x-gzip, text), 21.57 KiB.

patch

Attachment: column_spec.patch (text/x-patch), 10.06 KiB.

patch for existing test case (test with broken sql)

Attachment: column_mu_test.patch (text/x-patch), 3.04 KiB.

Thank you for a bug report. Verified just as described with latest 5.0.38-BK on Linux.

replacement for intial patch, no more tabs, changed some names and comments (test and other patches can be kept as they are)

Attachment: column_spec.patch (text/x-patch), 13.67 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/30788

ChangeSet@1.2508, 2007-07-12 10:49:37-04:00, cmiller@zippy.cornsilk.net +7 -0
  Bug#27175: invalid colum attributes in create/alter table
  
  One could define columns with contradictory or duplicate attri-
  butes.  E.g. "NULL NOT NULL DEFAULT 'foo' DEFAULT 'bar' NULL NULL"
  
  Now, check that explicitly-set or -unset flags are never reset
  in the same statement.

Workaround: don't use invalid names.