Bug #81933 select timestamp reports an error

• Submitted: 20 Jun 2016 12:21
• Modified: 24 Dec 2019 13:32
• Reporter: 帅 Bang
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.6, 5.6.31, 5.7.13
• OS: Linux
• CPU Architecture: Any
• Tags: regression

[20 Jun 2016 12:21] 帅 Bang

Description:
mysql> select timestamp '2009-07-16';
ERROR 1525 (HY000): Incorrect DATETIME value: '2009-07-16'

IMHO, it is very bad to report an error here since it is a select clause.

How to repeat:
 select timestamp '2009-07-16';

Suggested fix:
select timestamp '2009-07-16';  returns 2009-07-16 00:00:00 (highly recommended )or NULL with a warning (ugly but also acceptable)

[20 Jun 2016 13:02] MySQL Verification Team

Hello Bang,

Thank you for the report and test case.
Observed this with 5.6.31/5.7.13 builds.

Thanks,
Umesh

[20 Jun 2016 14:30] Peter Laursen

-- howeever all thsoe work
elect TIMESTAMP('2009-07-16');
SELECT DATE('2009-07-16');
SELECT TIME('2009-07-16');

-- but this fails with a syntax error. To my surprise it looks like there is no datetime() functon. 
SELECT DATETIME('2009-07-16');

-- Peter
-- not a MySQL/Oracle person.

[20 Jun 2016 14:31] Peter Laursen

correction:

-- howeever all those work
SELECT TIMESTAMP('2009-07-16');
SELECT DATE('2009-07-16');
SELECT TIME('2009-07-16');

[24 Dec 2019 13:32] Roy Lyseng

Posted by developer:
 
This is not a bug.
According to SQL standard, literal presented to TIMESTAMP must have exact syntax and represent a valid date and time.
Otherwise, an exception should be reported.