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
Triage: Triaged: D3 (Medium)

[15 Mar 2007 14:48] Martin Friebe
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
[15 Mar 2007 14:49] Martin Friebe
test case

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

[15 Mar 2007 14:50] Martin Friebe
additional test for patch

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

[15 Mar 2007 14:50] Martin Friebe
patch

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

[15 Mar 2007 14:51] Martin Friebe
patch for existing test case (test with broken sql)

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

[15 Mar 2007 15:05] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.0.38-BK on Linux.
[24 Mar 2007 17:01] Martin Friebe
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.

[12 Jul 2007 14:51] Bugs System
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.
[21 Dec 2007 16:09] Trudy Pelzer
Workaround: don't use invalid names.