| Bug #11714 | Non-sensical ALTER TABLE ADD CONSTRAINT allowed | ||
|---|---|---|---|
| Submitted: | 3 Jul 2005 23:35 | Modified: | 6 Mar 2010 18:55 |
| Reporter: | Roland Bouman | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Parser | Severity: | S3 (Non-critical) |
| Version: | 5.0.7 | OS: | Any |
| Assigned to: | Marc ALFF | CPU Architecture: | Any |
[4 Jul 2005 0:53]
MySQL Verification Team
mysql> alter table t add constraint cons1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t engine=innodb;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t add constraint cons1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`c1` int(11) default NULL,
`c2` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SET sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)
mysql> alter table t add constraint cons1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
[26 Aug 2008 20:56]
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/52608 2804 Marc Alff 2008-08-26 Bug#11714 (Non-sensical ALTER TABLE ADD CONSTRAINT allowed) Bug#38696 (CREATE TABLE ... CHECK ... allows illegal syntax) These two bugs have been fixed indirectly by the fix for 35578, adding the test cases to the test suite for coverage.
[27 Aug 2008 1:09]
Paul DuBois
Noted in 6.0.7 changelog.
[12 Sep 2008 1:44]
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/53909 2682 He Zhenxing 2008-09-12 [merge] Auto merge Update plugin.h.pp for WL#4398
[14 Sep 2008 3:50]
Bugs System
Pushed into 6.0.7-alpha (revid:marc.alff@sun.com-20080826205615-qg5lbt6yxtpqnc87) (version source revid:v.narayanan@sun.com-20080820070709-nx09bk6qx81osd5s) (pib:3)
[23 Jan 2010 2:03]
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/97959 2972 Marc Alff 2010-01-22 Bug#11714 Non-sensical ALTER TABLE ADD CONSTRAINT allowed Bug#35578 Parser allows useless/illegal CREATE TABLE syntax Bug#38696 CREATE TABLE ... CHECK ... allows illegal syntax Backport from 6.0 to mysql-next-mr.
[23 Jan 2010 2:05]
Marc ALFF
Backport for 5.5 queued in: - mysql-next-mr-marc
[13 Feb 2010 8:38]
Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100213083436-9pesg4h55w1mekxc) (version source revid:luis.soares@sun.com-20100211135109-t63avry9fqpgyh78) (merge vers: 6.0.14-alpha) (pib:16)
[13 Feb 2010 8:39]
Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100213083327-cee4ao3jpg33eggv) (version source revid:luis.soares@sun.com-20100211135018-1f9dbghg0itszigo) (pib:16)
[13 Feb 2010 13:11]
Marc ALFF
Doc team: to document in mysql-next-mr / Celosia
[13 Feb 2010 18:02]
Paul DuBois
Already fixed in earlier 6.0.x. Setting report to Need Merge pending push of Celosia to release tree.
[6 Mar 2010 11:09]
Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20100213160132-nx1vlocxuta76txh) (merge vers: 5.5.99-m3) (pib:16)
[6 Mar 2010 18:55]
Paul DuBois
Noted in 5.5.3 changelog.
Description: What I did: issue a nonsensical ALTER TABLE ADD CONSTRAINT statement without specifying wheter it was a foreign/unique/primary key and associated details. It was expected that mysql would disallow such a statement, or issue a warning However, Mysql accepts the statement without error or warning. After issuing the statement, nothing seems to have happened (which is good!). No corresponding constraint is seen in information_schema.TABLE_CONSTRAINTS. Also, show create table does not show the constraint. How to repeat: mysql> create database db -> ; Query OK, 1 row affected (0.00 sec) mysql> use db; Database changed mysql> create table t( -> c1 int -> ,c2 int -> ); Query OK, 0 rows affected (0.14 sec) mysql> alter table t add constraint cons1; Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * -> from information_schema.table_constraints -> where table_schema = 'db'; Empty set (0.00 sec) Suggested fix: Issue a warning or an error when such a statement is issued.