Bug #38696 CREATE TABLE ... CHECK ... allows illegal syntax

• Submitted: 10 Aug 2008 8:42
• Modified: 6 Mar 2010 18:56
• Reporter: Jeremy Gardiner
• Status: Closed
• Category: MySQL Server: Parser
• Severity: S3 (Non-critical)
• Version: 5.0.22, 5.0.66a, 5.1.26, 6.0.5
• OS: Windows
• Assigned to: Marc ALFF
• CPU Architecture: Any

[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:[e-mail]-20080826205615-qg5lbt6yxtpqnc87) (version source revid:[e-mail]-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:[e-mail]-20100213083436-9pesg4h55w1mekxc) (version source revid:[e-mail]-20100211135109-t63avry9fqpgyh78) (merge vers: 6.0.14-alpha) (pib:16)

[13 Feb 2010 8:38] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100213083327-cee4ao3jpg33eggv) (version source revid:[e-mail]-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:[e-mail]-20100306103849-hha31z2enhh7jwt3) (version source revid:[e-mail]-20100213160132-nx1vlocxuta76txh) (merge vers: 5.5.99-m3) (pib:16)

[6 Mar 2010 18:56] Paul DuBois

Noted in 5.5.3 changelog.