Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
23.10.2
-
None
Description
to reproduce:
CREATE TABLE `t1` (
|
`yyyymmdd` date NOT NULL
|
) ENGINE=Columnstore;
|
|
|
insert into t1 (yyyymmdd) values('2024-07-01'); |
insert into t1 (yyyymmdd) values('2024-07-20'); |
|
|
select CASE WHEN DAY(yyyymmdd) >= 15 THEN LAST_DAY(yyyymmdd) ELSE LAST_DAY(DATE_SUB(yyyymmdd, INTERVAL 1 month)) END AS effective_month_end_case, |
DAY(yyyymmdd), LAST_DAY(yyyymmdd),LAST_DAY(DATE_SUB(yyyymmdd, INTERVAL 1 month)) |
from t1;
|
|
|
+--------------------------+---------------+--------------------+------------------------------------------------+
|
| effective_month_end_case | DAY(yyyymmdd) | LAST_DAY(yyyymmdd) | LAST_DAY(DATE_SUB(yyyymmdd, INTERVAL 1 month)) | |
+--------------------------+---------------+--------------------+------------------------------------------------+
|
| 0000-00-00 | 1 | 2024-07-31 | 2024-06-30 | |
| 0000-00-00 | 20 | 2024-07-31 | 2024-06-30 | |
+--------------------------+---------------+--------------------+------------------------------------------------+
|
2 rows in set (0.008 sec) |
|
Same functions outside of "case... when" gives correct results.
With disabling select handler , it works also
|
|
MariaDB [d1]> set columnstore_select_handler = 0; |
Query OK, 0 rows affected (0.000 sec) |
|
|
MariaDB [d1]> select CASE WHEN DAY(yyyymmdd) >= 15 THEN LAST_DAY(yyyymmdd) ELSE LAST_DAY(DATE_SUB(yyyymmdd, INTERVAL 1 month)) END AS effective_month_end_case, DAY(yyyymmdd), LAST_DAY(yyyymmdd),LAST_DAY(DATE_SUB(yyyymmdd, INTERVAL 1 month)) from t2; |
+--------------------------+---------------+--------------------+------------------------------------------------+
|
| effective_month_end_case | DAY(yyyymmdd) | LAST_DAY(yyyymmdd) | LAST_DAY(DATE_SUB(yyyymmdd, INTERVAL 1 month)) | |
+--------------------------+---------------+--------------------+------------------------------------------------+
|
| 2024-06-30 | 1 | 2024-07-31 | 2024-06-30 | |
| 2024-07-31 | 20 | 2024-07-31 | 2024-06-30 | |
+--------------------------+---------------+--------------------+------------------------------------------------+
|
2 rows in set (0.000 sec) |
|
|
|
|
|