[MDEV-10319] EXTRACT(WEEK FROM DATE'0000-00-00') returns a strange value Created: 2016-07-01  Updated: 2019-04-17

Status: Open
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.1

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None


 Description   

EXTRACT(WEEK) returns strange values for dates with zero components :

SET sql_mode=''; -- Allow zeros in dates
 
DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a DATE);
 
INSERT INTO t1 VALUES ('0000-00-00'),('0000-00-01'),('0000-01-00'),('0000-01-01'),('0001-00-00'),('0001-00-01'),('0001-01-00'),('0001-01-01');
 
SELECT a, EXTRACT(WEEK FROM a) FROM t1;

returns

+------------+----------------------+
 
| a          | EXTRACT(WEEK FROM a) |
 
+------------+----------------------+
 
| 0000-00-00 |            613566757 |
 
| 0000-00-01 |            613566757 |
 
| 0000-01-00 |                   52 |
 
| 0000-01-01 |                    1 |
 
| 0001-00-00 |            613566752 |
 
| 0001-00-01 |            613566752 |
 
| 0001-01-00 |                    0 |
 
| 0001-01-01 |                    0 |
 
+------------+----------------------+

EXTRACT(WEEK FROM time_expr) also returns a strange result:

SELECT EXTRACT(WEEK FROM TIME'10:20:30');


+-----------------------------------+
 
| EXTRACT(WEEK FROM TIME'10:20:30') |
 
+-----------------------------------+
 
|                         613566757 |
 
+-----------------------------------+



 Comments   
Comment by Alexander Barkov [ 2018-07-19 ]

In MySQL-8.0, the first script returns the same results.
The second script returns something different:

SELECT EXTRACT(WEEK FROM TIME'10:20:30');


+-----------------------------------+
 
| EXTRACT(WEEK FROM TIME'10:20:30') |
 
+-----------------------------------+
 
|                                28 |
 
+-----------------------------------+

Generated at Thu Feb 08 07:41:18 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.