MySQL Bugs: #28243: An interval might be used in the STARTS and ENDS clause, but is not specified.

Bug #28243	An interval might be used in the STARTS and ENDS clause, but is not specified.
Submitted:	4 May 2007 14:26	Modified:	9 May 2007 7:34
Reporter:	Horst Hunger	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.1.18	OS:	Any
Assigned to:	Jon Stephens	CPU Architecture:	Any

Description:
The actual syntax allows the following:
    AT timestamp [+ INTERVAL interval]
  | EVERY interval [STARTS timestamp] [ENDS timestamp]

But intervals are possible in STARTS and ENDS, which is also described in the text behind.
But the keyword INTERVAL is mandatory! Not as you describe in the examples and text optional!

How to repeat:
see 20.2.2. CREATE EVENT Syntax

Suggested fix:
   AT timestamp [+ INTERVAL interval]
  | EVERY interval [STARTS timestamp [+ INTERVAL interval]]
                   [ENDS timestamp  [+ INTERVAL interval]]

Thank you for a reasonable documentation request. Syntax on this page, http://dev.mysql.com/doc/refman/5.1/en/create-event.html, should be changed.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Corrected the BNF shown in the 5.1 Manual for CREATE EVENT statements as indicated by the submitter.

I don't see where it's suggested that the INTERVAL keyword is optional in isolation, and IMO any such interpretation must be based on a too-hasty or incomplete reading. For example, the description of CREATE EVENT includes the following:

> In order to create an event which occurs at some point in the future relative 
> to the current date and time — such as that expressed by the phrase “three 
> weeks from now” — you can use the optional clause + INTERVAL <interval>.

Here, I think it's pretty clear that it says the *clause* "+ INTERVAL <interval>" is optional (which it is). It does *not* say "the optional keyword INTERVAL" or anything close to that.

As the submitter has noted, the narrative was already correct, only the BNF notation for the statement was incomplete.

The following chapter contains errros concering INTERVAL:

An EVERY clause may also contain an optional STARTS clause. STARTS is followed by a timestamp value which indicates when the action should begin repeating, and may also use + INTERVAL interval in order to specify an amount of time “from now”. For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK means “every three months, beginning one week from now”. Similarly, you can express “every two weeks, beginning six hours and fifteen minutes from now” as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + '6:15' HOUR_MINUTE. Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP — that is, the action specified for the event begins repeating immediately upon creation of the event

That should be:

An EVERY clause may also contain an optional STARTS clause. STARTS is followed by a timestamp value which indicates when the action should begin repeating, and may also use + INTERVAL interval in order to specify an amount of time “from now”. For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means “every three months, beginning one week from now”. Similarly, you can express “every two weeks, beginning six hours and fifteen minutes from now” as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE. Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP — that is, the action specified for the event begins repeating immediately upon creation of the event

The following chapter contains errros concering INTERVAL:

An EVERY clause may also contain an optional STARTS clause. STARTS is followed by a timestamp value which indicates when the action should begin repeating, and may also use + INTERVAL interval in order to specify an amount of time “from now”. For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + 1 WEEK means “every three months, beginning one week from now”. Similarly, you can express “every two weeks, beginning six hours and fifteen minutes from now” as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + '6:15' HOUR_MINUTE. Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP — that is, the action specified for the event begins repeating immediately upon creation of the event

That should be:

An EVERY clause may also contain an optional STARTS clause. STARTS is followed by a timestamp value which indicates when the action should begin repeating, and may also use + INTERVAL interval in order to specify an amount of time “from now”. For example, EVERY 3 MONTH STARTS CURRENT_TIMESTAMP + INTERVAL 1 WEEK means “every three months, beginning one week from now”. Similarly, you can express “every two weeks, beginning six hours and fifteen minutes from now” as EVERY 2 WEEK STARTS CURRENT_TIMESTAMP + INTERVAL '6:15' HOUR_MINUTE. Not specifying STARTS is the same as using STARTS CURRENT_TIMESTAMP — that is, the action specified for the event begins repeating immediately upon creation of the event

Sorry, I sumitted twice. But one hint the test can be found chapter 20.2.2.

Another position where I miss INTERVAL is the last example in that chapter:

CREATE EVENT e_call_myproc
    ON SCHEDULE 
      AT CURRENT_TIMESTAMP + 1 DAY
    DO CALL myproc(5, 27); 

That should be:

CREATE EVENT e_call_myproc
    ON SCHEDULE 
      AT CURRENT_TIMESTAMP + INTERVAL 1 DAY
    DO CALL myproc(5, 27);

I see now.

What you said had me looking for a statement saying that the INTERVAL keyword was optional, instead of looking for the keyword as missing from some examples. I've fixed these.