[MCOL-5537] Issue when using Month or Year paired with convert_tz of an attribute Created: 2023-07-13  Updated: 2023-07-20

Status: Confirmed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Khaled Hesham Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None


 Description   

I am running sql query fetching month and year of a created_at field in a table and i get wrong month and year result
MariaDB [fit_fix_analytics]> select CONVERT_TZ(created_at, '+00:00', '+03:00') as date, YEAR(CONVERT_TZ(created_at, '+00:00', '+03:00')) as year, MONTH(CONVERT_TZ(created_at, '+00:00', '+03:00')) as month from table limit 10;
------------------------------

date year month

------------------------------

2020-09-11 00:36:58 0 1
2020-09-11 01:03:49 0 1
2020-09-11 01:30:08 0 1
2020-09-11 01:41:44 0 1
2020-09-11 21:14:55 0 1
2020-09-11 21:15:54 0 1
2020-09-11 21:18:36 0 1
2020-09-11 21:21:15 0 1
2020-09-12 22:10:58 0 1
2020-09-13 11:57:37 0 1

------------------------------

 Comments   
Comment by Alice Sherepa [ 2023-07-19 ]

I could not repeat it on a freshly installed 10.6.8:

MariaDB [test]> select version();
 
+----------------+
 
| version()      |
 
+----------------+
 
| 10.6.8-MariaDB |
 
+----------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> create or replace table `tb1` (created_at varchar(100) );
 
Query OK, 0 rows affected (0.045 sec)
 
 
 
MariaDB [test]> insert into tb1 values ('2020-09-11 00:36:58'), ('2020-09-11'),('2020-09-11  01:30:08');
 
Query OK, 3 rows affected (0.012 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select CONVERT_TZ(created_at, '+00:00', '+03:00') as date, YEAR(CONVERT_TZ(created_at, '+00:00', '+03:00')) as year, MONTH(CONVERT_TZ(created_at, '+00:00', '+03:00')) as month from tb1 limit 10;
 
+----------------------------+------+-------+
 
| date                       | year | month |
 
+----------------------------+------+-------+
 
| 2020-09-11 03:36:58.000000 | 2020 |     9 |
 
| 2020-09-11 03:00:00.000000 | 2020 |     9 |
 
| 2020-09-11 04:30:08.000000 | 2020 |     9 |
 
+----------------------------+------+-------+
 
3 rows in set (0.000 sec)

Could you please provide more details or the way to repeat it - show create table,etc.

Comment by Khaled Hesham [ 2023-07-19 ]

This is the complete version of maria db running:
mariadb Ver 15.1 Distrib 10.6.8-MariaDB, for Linux (x86_64) using readline 5.1

This is the table details I am using, it is using columnstore engine:
MariaDB [fit_fix_analytics]> SHOW TABLE STATUS WHERE Name ='dim_order_0';
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

dim_order_0 Columnstore 10 Dynamic 2000 0 0 0 0 0 NULL NULL NULL NULL utf8mb3_general_ci NULL     0 N

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This is the column I am filtering:

created_at datetime NO   NULL  

This is a sample of a data in the table:
---------------------

created_at

---------------------

2020-09-10 21:36:58
2020-09-10 22:03:49
2020-09-10 22:30:08
2020-09-10 22:41:44
2020-09-11 18:14:55
2020-09-11 18:15:54
2020-09-11 18:18:36
2020-09-11 18:21:15
2020-09-12 19:10:58
2020-09-13 08:57:37

---------------------

Comment by Daniel Lee (Inactive) [ 2023-07-20 ]

Build tested: ColumnStore 6.4.6-1, 23.02.4

Confirmed it is an issue when ColumnStore engine is used. It worked fine with InnoDB.

MariaDB [mytest]> select version();
 
+-----------------------------+
 
| version()                   |
 
+-----------------------------+
 
| 10.6.8-4-MariaDB-enterprise |
 
+-----------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [mytest]> create table `tb1` (created_at varchar(100) ) engine=columnstore;
 
Query OK, 0 rows affected (0.141 sec)
 
 
 
MariaDB [mytest]> insert into tb1 values ('2020-09-11 00:36:58'), ('2020-09-11'),('2020-09-11  01:30:08');
 
Query OK, 3 rows affected (0.124 sec)
 
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [mytest]> select CONVERT_TZ(created_at, '+00:00', '+03:00') as date, YEAR(CONVERT_TZ(created_at, '+00:00', '+03:00')) as year, MONTH(CONVERT_TZ(created_at, '+00:00', '+03:00')) as month from tb1 limit 10;
 
+----------------------------+------+-------+
 
| date                       | year | month |
 
+----------------------------+------+-------+
 
| 0000-00-00 00:00:00.000000 |    0 |     0 |
 
| 0000-00-00 00:00:00.000000 |    0 |     0 |
 
| 0000-00-00 00:00:00.000000 |    0 |     0 |
 
+----------------------------+------+-------+
 
3 rows in set (0.039 sec)

The issue also exists in the latest release of ColumnStore, 23.02.4.

MariaDB [mytest]> select version();
 
+------------------------------+
 
| version()                    |
 
+------------------------------+
 
| 10.6.14-9-MariaDB-enterprise |
 
+------------------------------+
 
1 row in set (0.000 sec)

Generated at Thu Feb 08 02:58:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.