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

• Submitted: 10 Aug 2008 10:42
• Modified: 27 Aug 2008 3:09
• Reporter: Jeremy Gardiner
• Status: Closed
• Category: 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
• Triage: D4 (Minor)

[10 Aug 2008 10: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 19: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 19: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 22: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 3:09] Paul DuBois

Noted in 6.0.7 changelog.

[14 Sep 2008 5:50] Bugs System

Pushed into 6.0.7-alpha  (revid:[e-mail]-20080826205615-qg5lbt6yxtpqnc87)
(version source revid:[e-mail]-20080820070709-nx09bk6qx81osd5s) (pib:3)