MySQL Bugs: #35578: Parser allows useless/illegal CREATE TABLE syntax

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

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.

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)

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.

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

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

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.

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

Noted in 6.0.7 changelog.

See related Bug#11714, which was fixed by this change.

See related Bug#38696, which was fixed by this change

Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)

Pushed into 6.0.7-alpha  (revid:marc.alff@sun.com-20080813204529-kqlg9vpz5blphfwx) (version source revid:hakan@mysql.com-20080725175322-8wgujj5xuzrjz3ke) (pib:3)

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.

Backport for 5.5 queued in:
- mysql-next-mr-marc

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)

Pushed into mysql-next-mr (revid:alik@sun.com-20100213083327-cee4ao3jpg33eggv) (version source revid:luis.soares@sun.com-20100211135018-1f9dbghg0itszigo) (pib:16)

Doc team: to document in mysql-next-mr / Celosia

Already fixed in earlier 6.0.x.

Setting report to Need Merge pending push of Celosia to release tree.

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)

Noted in 5.5.3 changelog.