Bug #9801 Views: imperfect error message
Submitted: 11 Apr 2005 1:00 Modified: 6 Mar 2010 18:53
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.0.5-beta-debug OS:Linux (SUSE 9.2)
Assigned to: Marc Alff CPU Architecture:Any

[11 Apr 2005 1:00] Peter Gulutzan
Description:
When I create a non-updatable view using a WITH CHECK OPTION clause, I expect the error message "CHECK option on non-updatable view ...". Usually I get it. But I don't get it if the view's SELECT has a GROUP BY clause.

How to repeat:
mysql> create table t7 (s1 int);
Query OK, 0 rows affected (0.00 sec)

mysql> create view v7 as select sum(s1) from t7 with check option;
ERROR 1368 (HY000): CHECK OPTION on non-updatable view 'db3.v7'

mysql> create view v7 as select sum(s1) from t7 group by s1 with check option;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'check option' at line 1
[11 Apr 2005 1:15] MySQL Verification Team
Thank you for the bug report.
[5 Oct 2005 18:34] 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/internals/30726
[24 Jul 2006 15:08] Marc Alff
Investigated the issue (per Konstantin request), documenting analysis.

- Reproduced with 5.1 BK as of 2006-07-24
- The syntax error itself is indeed generated by bison, not explicitly
by calling my_error

- There is a SHIFT/REDUCE conflict in the GROUP_CLAUSE rule :
On WITH, the parser has 2 choices :

a) SHIFT the WITH in hope to find either a CUBE or a ROLLUP, and reduce
later a non empty olap clause

b) REDUCE the current group_clause (and reduce all the way up to view_tail),
to use the parsed WITH for a view_check_option rule.

The grammar as written is not LALR(1), it's in fact LALR(2),
since 2 look ahead tokens ('WITH', plus the next one) are needed to
resolve this conflict.

The choice taken was a shift, causing the bug in this case.
Taking the reduce would cause other bugs anyway (LALR(2), can't do much
about it).

Root cause analysis

- The 'group by' syntax implemented by previous releases of MySQL (3.23, 4.0)
contains a syntax extention compared to the SQL 99 standard :

http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html

The 'WITH ROLLUP' is an extention compared to the SQL 99 syntax :
http://savage.net.au/SQL/sql-99.bnf.html#group%20by%20clause
GROUP BY ... ROLLUP ( ... )

Before MySQL 5.0, this syntax change did not cause any issues.
With 5.0 and views, the (correct) implementation of the 'view_check_option'
rule causes a conflict in MySQL, that does not exist in SQL 99 (and SQL 2003).

Suggested fix :

- the previous syntax 'WITH ROLLUP' in used in production, and has to be supported.

- Implement the olap rules as follows :

1) immediately change WITH CUBE in olap to just use CUBE

2) change the yacc grammar for ROLLUP as follows :

rollup_list:
[1]   ROLLUP_SYM '(' ordinary_grouping_set_list ')'
[2]   | ROLLUP_SYM /* MySQL extention */
[3]   | WITH_ROLLUP_SYM /* MySQL extention, deprecated */
;

The rule [1] should have a "not yet implemented" kind of action,
as a place holder for the full SQL-99 / SQL-2003 syntax (optional).

The rule [2] is the preferred one.

The rule [3] should raise a deprecation warning.

WITH_ROLLUP_SYM is a 'fake' token that needs to be returned by MYSQLlex(),
to resolve the LALR(2) issues :

When lex finds a WITH, it should look further and if a ROLLUP is also found,
return a single token WITH_ROLLUP_SYM.

That way, the bison grammar stays clean, and the LALR(2) issue is avoided.
No shift/reduce or reduce/reduce conflicts should remain in the group by rule.

Note that the suggestion about 'WITH ROLLUP' is to deprecate this syntax,
to avoid other potential conflicts when the SQL syntax evolves again.
It's not strictly necessary and is a point to discuss.
[25 Oct 2006 9: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/14333

ChangeSet@1.2291, 2006-10-25 02:52:42-07:00, acurtis@xiphis.org +4 -0
  Bug#9801
    "WITH CHECK OPTION doesn't parse with GROUP BY"
    Implement L(2) lookahead to disambiguate WITH token
[25 Oct 2006 19:26] 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/14398

ChangeSet@1.2291, 2006-10-25 12:26:31-07:00, acurtis@xiphis.org +4 -0
  Bug#9801
    "WITH CHECK OPTION doesn't parse with GROUP BY"
    Implement L(2) lookahead to disambiguate WITH token
[25 Oct 2006 19:35] 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/14401

ChangeSet@1.2291, 2006-10-25 12:34:34-07:00, acurtis@xiphis.org +4 -0
  Bug#9801
    "WITH CHECK OPTION doesn't parse with GROUP BY"
    Implement L(2) lookahead to disambiguate WITH token
[1 Nov 2006 2:25] Marc Alff
Sent review comments by email.
[3 Aug 2007 16:34] Antony Curtis
Is this bug still an outstanding issue or is it to be resolved differently.
[6 Mar 2008 19:14] 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/43551

ChangeSet@1.2608, 2008-03-06 12:12:55-07:00, malff@lambda.hsd1.co.comcast.net. +6 -0
  Bug#9801 (Views: imperfect error message)
  
  The root cause of this bug is that the grammar for GROUP BY clauses,
  when using WITH CUBE or WITH ROLLUP, cause conflicts with the grammar
  for VIEW, when using WITH CHECK OPTION.
  
  The solution is to implement two token look ahead when parsing a WITH token,
  to disambiguate the non standard WITH CUBE and WITH ROLLUP syntaxes.
  
  Patch based on code from Marc Alff and Antony Curtis
[14 Mar 2008 19:10] 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/44034

ChangeSet@1.2608, 2008-03-14 13:07:46-06:00, malff@lambda.hsd1.co.comcast.net. +6 -0
  Bug#9801 (Views: imperfect error message)
  
  The root cause of this bug is that the grammar for GROUP BY clauses,
  when using WITH CUBE or WITH ROLLUP, cause conflicts with the grammar
  for VIEW, when using WITH CHECK OPTION.
  
  The solution is to implement two token look ahead when parsing a WITH token,
  to disambiguate the non standard WITH CUBE and WITH ROLLUP syntaxes.
  
  Patch based on code from Marc Alff and Antony Curtis
[14 Mar 2008 19:22] Antony Curtis
Discussed and approved.
[19 Mar 2008 19:15] 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/44254

ChangeSet@1.2604, 2008-03-19 13:13:54-06:00, malff@lambda.hsd1.co.comcast.net. +6 -0
  Bug#9801 (Views: imperfect error message)
  
  The root cause of this bug is that the grammar for GROUP BY clauses,
  when using WITH CUBE or WITH ROLLUP, cause conflicts with the grammar
  for VIEW, when using WITH CHECK OPTION.
  
  The solution is to implement two token look ahead when parsing a WITH token,
  to disambiguate the non standard WITH CUBE and WITH ROLLUP syntaxes.
  
  Patch based on code from Marc Alff and Antony Curtis
[31 Mar 2008 13:58] Bugs System
Pushed into 6.0.5-alpha
[31 Mar 2008 18:19] Paul Dubois
Note in 6.0.5 changelog.

The grammar for GROUP BY, when used with WITH CUBE or WITH ROLLUP,
caused a conflict with the grammar for view definitions that included
WITH CHECK OPTION.
[30 Oct 2009 18: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/88799

2933 Marc Alff	2009-10-30
      Bug#9801 Views: imperfect error message
      
      Backport for 5.5
      
      The root cause of this bug is that the grammar for GROUP BY clauses,
      when using WITH CUBE or WITH ROLLUP, cause conflicts with the grammar
      for VIEW, when using WITH CHECK OPTION.
      
      The solution is to implement two token look ahead when parsing a WITH token,
      to disambiguate the non standard WITH CUBE and WITH ROLLUP syntaxes.
      
      Patch based on code from Marc Alff and Antony Curtis
[2 Nov 2009 16:33] 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/88965

2935 Marc Alff	2009-11-02
      Bug#9801 Views: imperfect error message
      
      Backport for 5.5
      
      The root cause of this bug is that the grammar for GROUP BY clauses,
      when using WITH CUBE or WITH ROLLUP, cause conflicts with the grammar
      for VIEW, when using WITH CHECK OPTION.
      
      The solution is to implement two token look ahead when parsing a WITH token,
      to disambiguate the non standard WITH CUBE and WITH ROLLUP syntaxes.
      
      Patch based on code from Marc Alff and Antony Curtis
[11 Nov 2009 17:37] 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/90135

2946 Marc Alff	2009-11-11
      Bug#9801 Views: imperfect error message
      
      Backport to 5.5: adjusted the test outputs in the funcs_1 test suite
[20 Nov 2009 12:58] Bugs System
Pushed into 6.0.14-alpha (revid:kostja@sun.com-20091120124947-yi6h2jbgw0kbciwm) (version source revid:alik@sun.com-20091112120759-v03qas4w2bgbj93s) (merge vers: 6.0.14-alpha) (pib:13)
[22 Nov 2009 19:11] Paul Dubois
Already fixed in 6.0.x.

Setting report to NDI pending push into 5.5.x (5.6.x?).
[11 Dec 2009 6:04] Bugs System
Pushed into 5.6.0-beta (revid:alik@sun.com-20091211055628-ltr7fero363uev7r) (version source revid:alik@sun.com-20091211055453-717czhtezc74u8db) (merge vers: 5.6.0-beta) (pib:13)
[11 Dec 2009 19:26] Paul Dubois
Noted in 5.6.0 changelog.
[6 Mar 2010 11:08] Bugs System
Pushed into 5.5.3-m3 (revid:alik@sun.com-20100306103849-hha31z2enhh7jwt3) (version source revid:vvaintroub@mysql.com-20091211201717-03qf8ckwiw0np80p) (merge vers: 5.6.0-beta) (pib:16)
[6 Mar 2010 18:53] Paul Dubois
Moved 5.6.0 changelog entry to 5.5.3.