[MCOL-1356] CONVERT_TZ Throws "IDB-1815: Unknown Error" in UPDATE or SELECT statement Created: 2018-04-20  Updated: 2021-12-29  Resolved: 2021-08-23

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 1.5.2
Fix Version/s: 6.2.1, 6.2.2

Type: Bug Priority: Blocker
Reporter: Mike Archipley Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS 7 Using MariaDB ColumnStore 1.1

Issue Links:
Duplicate
is duplicated by MCOL-4308 support for CONVERT_TZ Closed
is duplicated by MCOL-4310 CONVERT_TZ seems to be a NOOP for col... Closed
is duplicated by MCOL-4375 Function convert_tz not supported Closed
Sprint: 2021-8, 2021-9, 2021-10

 Description   

Using the CONVERT_TZ command with an UPDATE query throws "IDB-1815: Unknown Error".

Here is an example query that produces the error:

UPDATE mytable SET `dateTimeOriginationConverted`= CONVERT_TZ(FROM_UNIXTIME(`dateTimeOrigination`), @@session.time_zone,'America/New_York') WHERE ID = 1;

This only happens when referencing the field value (dateTimeOrigination is an INT field in the database that contains the UNIX timestamp value). If you use a static INT, the update works fine. The command also works fine as is on a standard MySQL 5.7 server using InnoDB. so this must be specific to MariaDB or ColumnStore.

 Comments   
Comment by Gregory Dorman (Inactive) [ 2021-02-21 ]

It is not just update. select as well.

MariaDB [gjd]> create table t3 (a int, b datetime) engine=Columnstore;
Query OK, 0 rows affected (0.667 sec)
MariaDB [gjd]> insert into t3 values (1,'2008-05-15 12:00:00');
Query OK, 1 row affected (0.165 sec)
MariaDB [gjd]>
MariaDB [gjd]> SELECT CONVERT_TZ(b,'+00:00','+10:00') from t3;
ERROR 1178 (42000): The storage engine for the table doesn't support IDB-1001: Function 'convert_tz' isn't supported.
MariaDB [gjd]>

Indeed, the function actually is there, just doesn't like columns.

MariaDB [gjd]> SELECT CONVERT_TZ('2008-05-15 12:00:00','+00:00','+10:00') from t3;
-----------------------------------------------------
CONVERT_TZ('2008-05-15 12:00:00','+00:00','+10:00')
-----------------------------------------------------
2008-05-15 22:00:00
-----------------------------------------------------

Comment by Daniel Lee (Inactive) [ 2021-08-23 ]

Build verified: 6.2.1 (#2990, develop-6 branch)
(#2996, develop branch)

Reproduced the issue in release 6.1.1.
Verified both SELECT and UPDATE on both branches.

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