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

Bug #27175	invalid colum attributes in create/alter table
Submitted:	15 Mar 2007 15:48	Modified:	26 Mar 14:10
Reporter:	Martin Friebe (Gold Quality Contributor)
Status:	Verified
Category:	Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0.38 4.1.23	OS:	Linux (Linux)
Assigned to:	Chad MILLER	Target Version:	6.0
Tags:	CREATE TABLE, column, parser, qc, Contribution, PatchRefused
Triage:	Triaged: D3 (Medium)

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.