MySQL Bugs: #4433: using a string in interval part of DATE

Bug #4433	using a string in interval part of DATE_ADD
Submitted:	7 Jul 2004 12:08	Modified:	22 Dec 2005 18:03
Reporter:	[ name withheld ]	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	4.0.16	OS:	Sun Cobalt 2.4.19C13_V
Assigned to:		CPU Architecture:	Any

Description:
According to the documentation of the DATE_ADD function 
DATE_ADD(date,INTERVAL expr type)
it is possible to use an expr type combination.
It would however be nice if it is possible to use also the following syntax
DATE_ADD(date,INTERVAL expr )

Why? Because if you use a table (named interval) which contains several date intervals 
like
ID | interval 
1 |  1 DAY
2 | 7 DAY
3 | 1 MONTH

and the following table named test
ID | startdate| interval
1  | 2004-04-06 | 1
2 | 2004-06-06 | 2

then the following query could be used.
SELECT t.startdate,
DATE_ADD(t.startdate,INTERVAL i.interval)
FROM test AS t,
interval AS i
WHERE t.ID=i.ID
AND t.ID=1

The result should be:
2004-04-06, 2004-04-07

How to repeat:
Not possible due to feature request

Suggested fix:
extend the used grammar for mysql with an optional TYPE instead of requiring always a TYPE
Thus: 
function ::=
rest of the functions 
|
DATE_ADD LPAREN date, INTERVAL expr type RPAREN
|
DATE_ADD LPAREN date, INTERVAL expr RPAREN

It would really be great if this is possible an a next release.
Unfortunately I can't find the exact used grammer , just the yacc decleration in sql/sql_yacc.yy

Thank you for a feature request. I do not think it is reasonable to add this behaviour (expr as a string containing number and type of time unit), because it is error-prone. There are other ways to solve your problem in, say, MySQL 5.0.x with stored procedures (functions), prepared statements etc.