Bug #33889 | Subselect fails with purge master logs in 5.0.51 | ||
---|---|---|---|
Submitted: | 16 Jan 2008 15:54 | Modified: | 7 Feb 2008 13:30 |
Reporter: | Dan Tulovsky | Email Updates: | |
Status: | Won't fix | Impact on me: | |
Category: | MySQL Server: Documentation | Severity: | S2 (Serious) |
Version: | 5.0.54, 5.0.51 | OS: | Linux (2.6.18.5) |
Assigned to: | Jon Stephens | CPU Architecture: | Any |
[16 Jan 2008 15:54]
Dan Tulovsky
[17 Jan 2008 4:35]
Valeriy Kravchuk
Thank you for a problem report. According to the manual, http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html, you should NOT use any kind of expressinos in BEFORE clause, formally. So, this is indeed a change in undocumented(!) behaviour: mysql> purge master logs before (select date_sub(now(), INTERVAL 4 DAY)); 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 'selec t date_sub(now(), INTERVAL 4 DAY))' at line 1 I'd consider this report as a request for proper documentation: if expressions can be used, and this is intended, it should be documented, including limitations and changes in 5.0.51 etc. If documentation is correct, then it is a verified bug in server also. Note that there is a workaround that still works in 5.0.54: mysql> purge master logs before date_sub(now(), INTERVAL 4 DAY); Query OK, 0 rows affected (0.01 sec)
[7 Feb 2008 13:30]
Jon Stephens
There is currently a proposal to support expressions more widely in place of discrete, but there is as yet no target for its implementation. The proposal suggests, however, that subqueries should *not* be allowed to take the place of discrete values except as shown in http://dev.mysql.com/doc/refman/5.1/en/subqueries.html. In this particular case: (a) The SELECT is unnecessary; as already noted, the DATE_SUB() call is quite sufficient (b) There was never (AFAICT) any promise made in the docs that it would be supported The best I can do for you now is to *suggest* that (A) An expression for the BEFORE datetime value is likely to continue to work (B) It appears unlikely that a SELECT (particularly one that's not necessary) will ever be supported here However, I honestly cannot (and therefore will not) *promise* that (A) and/or (B) are or will be true; I can say only that they appear likely. This is not an attempt to be evasive; it reflects the true situation regarding this issue, which is now under discussion and affects a large number of (My)SQL statements. For some examples, see these related bug reports: Bug#6673 Expressions not available as LIMIT parameters Bug#8094 Variables in the LIMIT Clause Bug#11918 SP does not accept variables in LIMIT clause Bug#13870 Allow variable filename for INTO OUTFILE clause Bug#17832 GROUP_CONCAT() separator parameter doesn't accept variables Bug#28406 GRANT using a stored procedure variable as PASSWORD fails Since the behaviour you relied upon was never supported (and we never claimed in the documentation that it was), I've marked this bug as WONTFIX. Thanks, Jon Stephens MySQL Documentation Team