The columnstore distributed version of timediff currently returns the value as a string since the time datatype is not yet supported. The columnstore distributed hour function assumes a string should be converted as a datetime value and so a time values is not considered valid so it ends up null.

Likely this should be fixed when time datatype is added.

As a workaround you could potentially instead use string manipulation functions to extract the hour portion?

FYI, I ended up using SUBSTRING_INDEX(TIMEDIFF(...), ':', 1). This works in both cases.

This is fixed as part of MCOL-392

Build tested: 1.2.0-1 source

/root/columnstore/mariadb-columnstore-server
commit 4334641df0df040e0f53332c11dc8e29dc34b8b7
Merge: 960853c cd5e845
Author: David.Hall <david.hall@mariadb.com>
Date: Mon Apr 9 13:23:44 2018 -0500

Merge pull request #107 from mariadb-corporation/dev-merge-up-20180409

Dev merge up 20180409

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit e79daac8a9b1c8047aeabc3d73ae137cc151f84e
Merge: 8790177 8a3c5e0
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Tue May 15 15:34:19 2018 +0100

Merge pull request #472 from drrtuy/MCOL-1073

MCOL-1073 Removed DecomSrv

ColumnStore returned negative hours and both aris and innodb return a positive hour numbers.

All three table types did return a negative time stamp by TIMEDIFF(MAX(ts), now()).

So is it a bug in the server or in the Columnstore? From what I read, it is pointing to a bug in ColumnStore.

[root@localhost ~]# mcsmysql mytest
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.14-MariaDB-log Columnstore 1.2.0-1

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mytest]> CREATE TABLE test_stats1 (
-> ts DATETIME DEFAULT NULL
-> ) ENGINE = Columnstore;
Query OK, 0 rows affected (0.11 sec)

MariaDB [mytest]> CREATE TABLE test_stats2 (
-> ts DATETIME DEFAULT NULL
-> ) ENGINE = Aria;
Query OK, 0 rows affected (0.00 sec)

MariaDB [mytest]>
MariaDB [mytest]> INSERT INTO test_stats1 VALUES ('2017-07-07 12:00:00');
Query OK, 1 row affected (0.13 sec)

MariaDB [mytest]> INSERT INTO test_stats2 VALUES ('2017-07-07 12:00:00');
Query OK, 1 row affected (0.00 sec)

MariaDB [mytest]> SELECT HOUR(TIMEDIFF(MAX(ts), now())) FROM test_stats1;
--------------------------------

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

--------------------------------
1 row in set (0.04 sec)

MariaDB [mytest]> SELECT HOUR(TIMEDIFF(MAX(ts), now())) FROM test_stats2;
--------------------------------

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

--------------------------------
1 row in set, 1 warning (0.00 sec)

MariaDB [mytest]> SELECT MAX(ts), TIMEDIFF(MAX(ts), now()) FROM test_stats2;
---------------------------------------------+

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

---------------------------------------------+
1 row in set, 1 warning (0.01 sec)

MariaDB [mytest]> SELECT MAX(ts), TIMEDIFF(MAX(ts), now()) FROM test_stats1;
---------------------------------------------+

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

---------------------------------------------+
1 row in set (0.01 sec)

Tried query from the literal date and it also returned positive numbers. That's because the front end evaluated the hour().

Pull request for engine and regression suite. Now HOUR() returns positive time only.

Also updated MariaDB Server's documentation to express this behaviour.

Build verified: 1.2.0-1 source

/root/columnstore/mariadb-columnstore-server
commit 3bfc3ad5f1e91e6f325b89fefaf62a6b38ca889a
Merge: 4334641 742af4d
Author: David.Hall <david.hall@mariadb.com>
Date: Thu May 31 09:40:12 2018 -0500

Merge pull request #119 from mariadb-corporation/1.1-merge-up-20180531

Merge develop-1.1 into develop

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 1caa98ff46777a389cdfd4ae369865391a884c27
Merge: 60a4d14 d9e6ba9
Author: David.Hall <david.hall@mariadb.com>
Date: Mon Jun 11 10:20:49 2018 -0500

Merge pull request #494 from mariadb-corporation/MCOL-1433

MCOL-1433 Fix several functions for TIME handling