Bug #103110 EXTRACT(WEEK from '0000-00-00') gets wrong result
Submitted: 25 Mar 12:12 Modified: 26 Mar 13:49
Reporter: Yukun Liang Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[25 Mar 12:12] Yukun Liang
WEEK(0000-00-00) will return NULL, but EXTRACT(WEEK from 0000-00-00) return a strange results

How to repeat:
mysql> create table t1 (d date, dt datetime, t timestamp, c char(10));
Query OK, 0 rows affected (0.31 sec)

mysql> insert ignore into t1 values ("0000-00-00", "0000-00-00", "0000-00-00", "0000-00-00");
Query OK, 1 row affected, 3 warnings (0.08 sec)

mysql> select week(dt) from t1;
| week(dt) |
|     NULL |
1 row in set (0.01 sec)

mysql> select extract(week from dt) from t1;
| extract(week from dt) |
|             613566757 |
1 row in set (0.01 sec)

Suggested fix:
Get date of child item with TIME_NO_ZERO_DATE flag in Item_extract::val_int() if interval type is WEEK.
[26 Mar 13:49] MySQL Verification Team
Hi Mr. Liang,

Thank you for your bug report.

However, this is not a bug.

Simply, humanity went from 1st year BC to 1st year AD and 0th year never existed.

Not a bug.