Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
23.02.3
-
None
-
None
-
2023-6
Description
QUARTER(q.claim_closing_date) as QUARTER,
and
EXTRACT(QUARTER FROM claim_closing_date) as EXTRACT_QUARTER
should return the same values, but they do not from a columnstore table:
|
|
show create table extract_quarter_example\G
|
*************************** 1. row ***************************
|
Table: extract_quarter_example
|
Create Table: CREATE TABLE `extract_quarter_example` (
|
`claim_id` int(11) NOT NULL,
|
`claim_name` varchar(11) DEFAULT NULL,
|
`claim_closing_date` date DEFAULT NULL
|
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb3
|
|
|
select count(1) from extract_quarter_example;
|
+----------+
|
| count(1) |
|
+----------+
|
| 500000 |
|
+----------+
|
|
|
SELECT q.claim_id,
|
q.claim_closing_date,
|
QUARTER(q.claim_closing_date) as QUARTER,
|
EXTRACT(QUARTER FROM claim_closing_date) as EXTRACT_QUARTER
|
FROM extract_quarter_example q limit 10;
|
+-----------+--------------------+---------+-----------------+
|
| claim_id | claim_closing_date | QUARTER | EXTRACT_QUARTER |
|
+-----------+--------------------+---------+-----------------+
|
| 234751530 | 2014-06-11 | 2 | 2 |
|
| 14834870 | 2012-07-28 | 3 | 2 |
|
| 36617706 | 2019-11-13 | 4 | 3 |
|
| 2170626 | 2022-06-12 | 2 | 2 |
|
| 124181396 | 2021-06-25 | 2 | 2 |
|
| 128071009 | NULL | NULL | NULL |
|
| 177692964 | NULL | NULL | NULL |
|
| 193813621 | 2015-07-02 | 3 | 2 |
|
| 305626077 | 2012-07-15 | 3 | 2 |
|
| 59257621 | 2022-06-22 | 2 | 2 |
|
+-----------+--------------------+---------+-----------------+
|
A factor in this might be that some rows include null values for the date column.
I have attached a script you can use to populate the table with sample data using cpimport.