Bug #38696 CREATE TABLE ... CHECK ... allows illegal syntax
Submitted: 10 Aug 2008 8:42 Modified: 6 Mar 2010 18:56
Reporter: Jeremy Gardiner Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.22, 5.0.66a, 5.1.26, 6.0.5 OS:Microsoft Windows
Assigned to: Marc Alff CPU Architecture:Any
Triage: D4 (Minor)

[10 Aug 2008 8:42] Jeremy Gardiner
Description:
mysql  Ver 14.12 Distrib 5.0.22, for Win32 (ia32)

CREATE TABLE ... CHECK allows an extra expression before (expression).

Example:

This is expected to succeed:

mysql> CREATE TABLE a1 (col_1 INT CHECK (whatever));
Query OK, 0 rows affected (0.15 sec)

This should be illegal, according to MySQL CREATE TABLE syntax in the Reference Manual:

mysql> CREATE TABLE a1 (col_1 INT CHECK something (whatever));
Query OK, 0 rows affected (0.13 sec)

Note: may be related to bug #35578 - Parser allows useless/illegal CREATE TABLE syntax.

How to repeat:
See example in description.

Suggested fix:
Update either source code or reference manual.
[10 Aug 2008 17:08] Valeriy Kravchuk
Thank you for a problem report. Verified on all recent 5.0.x, 5.1.x and 6.0.x versions.
[10 Aug 2008 17:35] Jeremy Gardiner
Note the following also unexpectedly succeeds:

mysql> CREATE TABLE a1 (col_1 INT CHECK something);
Query OK, 0 rows affected (0.17 sec)
[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.
[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:06] Marc Alff
Backport for 5.5 queued in:
- mysql-next-mr-marc
[13 Feb 2010 8:36] 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:38] 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:15] Marc Alff
Doc team: to document in mysql-next-mr / Celosia
[13 Feb 2010 18:01] Paul Dubois
Already fixed in earlier 6.0.x.

Setting report to Need Merge pending push of Celosia to release tree.
[6 Mar 2010 10:57] 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:56] Paul Dubois
Noted in 5.5.3 changelog.