Bug #35578 Parser allows useless/illegal CREATE TABLE syntax
Submitted: 26 Mar 2008 16:59 Modified: 6 Mar 2010 18:56
Reporter: Paul Dubois Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:4.1 and up OS:Any
Assigned to: Marc Alff CPU Architecture:Any

[26 Mar 2008 16:59] Paul Dubois
Description:
For CREATE TABLE syntax, the parser allows:

CONSTRAINT [symbol] [CHECK(expr)]

This syntax, with a missing CHECK, is not in the SQL-2003 spec.

See

http://savage.net.au/SQL/sql-2003-2.bnf.html#column%20definition

The parser also allows:

[CONSTRAINT [symbol]] CHECK(expr)

This one is in the SQL-2003 spec.
However, the parser does not implement the syntax properly,
since the rule 'check_constraint:' does not contain the parenthesis.

How to repeat:
Examine sql_yacc.yy.
[26 Mar 2008 21:09] Valeriy Kravchuk
Thank you for a bug report. Verified just as described:

mysql> create table tc (c1 int, constraint chk);
Query OK, 0 rows affected (0.16 sec)

mysql> select version();
+--------------------------+
| version()                |
+--------------------------+
| 5.0.56-enterprise-gpl-nt |
+--------------------------+
1 row in set (0.03 sec)
[12 May 2008 22:30] 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/46638

ChangeSet@1.2644, 2008-05-12 16:29:50-06:00, malff@lambda.hsd1.co.comcast.net. +3 -0
  Bug#35578 (Parser allows useless/illegal CREATE TABLE syntax)
  
  Fix the (still non supported) CHECK syntax to:
  - enforce that parenthesis are present in the "CHECK ( <expr> )" syntax,
  per the SQL specification
  - enforce that "CONSTRAINT <name>" alone, without any constraint, is illegal,
  per the SQL specification
  
  Fixing this also resolves 3 shift/reduce conflicts in the grammar.
[12 Jun 2008 15:22] 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/47785
[12 Jun 2008 15:28] 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/47787
[13 Aug 2008 20:46] 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/51578

2762 Marc Alff	2008-08-13
      Bug#35578 (Parser allows useless/illegal CREATE TABLE syntax)
      
      Fix the (still non supported) CHECK syntax to:
      - enforce that parenthesis are present in the "CHECK ( <expr> )" syntax,
      per the SQL specification
      - enforce that "CONSTRAINT <name>" alone, without any constraint, is illegal,
      per the SQL specification
      
      Fixing this also resolves 3 shift/reduce conflicts in the grammar.
[13 Aug 2008 21:53] 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/51584

2764 Marc Alff	2008-08-13 [merge]
      Merge 6.0-bugteam -> local bugfix branch
[26 Aug 2008 19:20] Paul Dubois
Noted in 6.0.7 changelog.
[26 Aug 2008 20:17] Marc Alff
See related Bug#11714, which was fixed by this change.
[26 Aug 2008 21:05] Marc Alff
See related Bug#38696, which was fixed by this change
[28 Aug 2008 20:15] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)
[13 Sep 2008 21:11] Bugs System
Pushed into 6.0.7-alpha  (revid:marc.alff@sun.com-20080813204529-kqlg9vpz5blphfwx) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (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:37] 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:14] 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:02] 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.