MySQL Bugs: #112792: Documentation bug: CURDATE

Bug #112792	Documentation bug: CURDATE - return type
Submitted:	21 Oct 2023 11:28	Modified:	3 Nov 2023 22:38
Reporter:	Thorsten Kettner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	curdate

Description:
According to https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_curdate CURDATE() "Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in string or numeric context".

This is wrong. CURDATE() does not return a string or number. It returns a DATE, just as its name suggests. 

How to repeat:
create table t as
  select curdate() as dt;

show create table t;

    CREATE TABLE `t` (
      `dt` date NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Demo: https://dbfiddle.uk/sz6pvJOF

Suggested fix:
Either just:

  CURDATE()

  Returns the current date. The returned data type is DATE.

(I would prefer that.) Or:

  CURDATE()

  Returns the current date. The returned data type is DATE. When used in a string or numeric context, dates get implicitly converted into a value in 'YYYY-MM-DD' or YYYYMMDD format.

  SELECT CONCAT(CURDATE(), '');
    -> 2023-10-21

  SELECT CURDATE() + 0;
    -> 20231021

The same problem applies to CURTIME.

Hi Mr. Kettner,

Thank you for your bug report.

However , this is not a bug.

Yes, that function returns a date, but not in the DATE domain, but in two different formats. String and numeric. 

You can try comparing the string format and DATE column values and you will see that everything is documented properly.

Not a bug.

Can you explain, or give at least 1 example, when `curdate()` does NOT give a value to make this "not in the date domain" ?

Hi,

This is indeed a documentation-only bug.

t is correct that it can be used as a string or a numeric value, but this depends on the context it is used in, like any other temporal expression.

Verifed as a documentation bug.

This is all explained at 

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-type-syntax.html

Not a bug.

Also, an example:

mysql> SELECT CURDATE() INTO @x;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @x, DATE_ADD(@x, INTERVAL 12 WEEK), REVERSE(@x);
+------------+--------------------------------+-------------+
| @x         | DATE_ADD(@x, INTERVAL 12 WEEK) | REVERSE(@x) |
+------------+--------------------------------+-------------+
| 2023-11-03 | 2024-01-26                     | 30-11-3202  |
+------------+--------------------------------+-------------+
1 row in set (0.00 sec)

This demonstrates that the description in the Manual is correct: DATE_ADD() treats its (unquoted) first argument as a DATE, but REVERSE() treats the exact same value as a string.

cheers

jon.

Thank you, Jon.