| 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: | |
| 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
[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.
