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:
None 
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
Description:
After upgrading to 5.0.51-2 (Debian build), a subselect inside a purge master statement no longer works.

5.0.41 works:

root@server1:# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2596672
Server version: 5.0.41-Debian_1-log Debian etch distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> purge master logs before (select date_sub(now(), INTERVAL 4 DAY));
Query OK, 0 rows affected (0.00 sec)

5.0.51 fails:

root@server2# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 85353
Server version: 5.0.51-2-log (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

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 'select date_sub(now(), INTERVAL 4 DAY))' at line 1

How to repeat:
Install mysql 5.0.51.
run: mysql> purge master logs before (select date_sub(now(), INTERVAL 4 DAY));
[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