Can you please provide your CAST, CONVERT, TIMEDIFF, STR_TO_DATE, NULLIF, MAKEDATE, DATE_FORMAT, DATEDIFF and TIMESTAMPDIFF tests?

The following were moved to MCOL-1461 as they are broken for DATETIME too and it appear to be by initial design:

period_add()
degrees()
inet_ntoa()

This puts them out of scope of MCOL-392.

Have a pull request that fixes everything else. Just need feedback from dleeyh on the requested functions.

CAST, CONVERT, TIMEDIFF, STR_TO_DATE, NULLIF, MAKEDATE, DATE_FORMAT, DATEDIFF and TIMESTAMPDIFF do not seem to cause a problem in develop any more.

Pull request in engine fixes most of the problem functions. The rest are either no longer a problem or are in MCOL-1461 which is a lower priority.

Some functions still returning incorrect results

1. CAST, CONVERT, STR_TO_DATE

When casting/converting TIME to DATE and DATETIME, it returns NULL. It should return DATE and DATETIME value, respectively.

1 --------------
2 select cidx, CTIME, CAST(CTIME AS DATE) from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME CAST(CTIME AS DATE)
6 1 13:00:00 NULL
7 1 row in set
8
9 --------------
10 select cidx, CTIME, CAST(CTIME AS DATETIME) from datatypetestm order by cidx
11 --------------
12
13 cidx CTIME CAST(CTIME AS DATETIME)
14 1 13:00:00 NULL
15 1 row in set

[root@localhost functions]# cat -n CNX/STR_TO_DATE.DM.sql.tst.log
1 --------------
2 select cidx, CTIME, STR_TO_DATE(DATE_FORMAT(CTIME,GET_FORMAT(DATE,'USA')), GET_FORMAT(DATE,'USA')) from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME STR_TO_DATE(DATE_FORMAT(CTIME,GET_FORMAT(DATE,'USA')), GET_FORMAT(DATE,'USA'))
6 1 13:00:00 NULL
7 1 row in set
.
.

42 select cidx, CTIME, STR_TO_DATE(DATE_FORMAT(CTIME,GET_FORMAT(DATETIME,'USA')), GET_FORMAT(DATETIME,'USA')) from datatypetestm order by cidx
43 --------------
44
45 cidx CTIME STR_TO_DATE(DATE_FORMAT(CTIME,GET_FORMAT(DATETIME,'USA')), GET_FORMAT(DATETIME,'USA'))
46 1 13:00:00 NULL
47 1 row in set

2. TIMEDIFF

When timediff with a literal date time value, it returns the minimum TIME value, instead of NULL

[root@localhost functions]# cat -n CNX/TIMEDIFF.DM.sql.tst.log
1 --------------
2 select cidx, CTIME, TIMEDIFF(CTIME,'2007-02-28 22:23:0') from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME TIMEDIFF(CTIME,'2007-02-28 22:23:0')
6 1 13:00:00 -838:59:59
7 1 row in set

[root@localhost functions]# cat -n CNX/TIMEDIFF.DM.sql.ref.log
1 --------------
2 select cidx, CTIME, TIMEDIFF(CTIME,'2007-02-28 22:23:0') from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME TIMEDIFF(CTIME,'2007-02-28 22:23:0')
6 1 13:00:00 NULL
7 1 row in set

3. NULLIF

CTIME and CDATE are not the same they it should not return NULL

[root@localhost functions]# cat -n CNX/NULLIF.DM.sql.tst.log
1 --------------
2 select cidx, CTIME, NULLIF(CTIME,CDATE) from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME NULLIF(CTIME,CDATE)
6 1 13:00:00 NULL
7 1 row in set

[root@localhost functions]# cat -n CNX/NULLIF.DM.sql.ref.log
1 --------------
2 select cidx, CTIME, NULLIF(CTIME,CDATE) from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME NULLIF(CTIME,CDATE)
6 1 13:00:00 13:00:00
7 1 row in set

4. MAKEDATE

[root@localhost functions]# cat -n CNX/MAKEDATE.DM.sql.tst.log
1 --------------
2 select cidx, CTIME, MAKEDATE(2010, CTIME) from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME MAKEDATE(2010, CTIME)
6 1 13:00:00 NULL
7 1 row in set
8
9 --------------
10 select cidx, CTIME, MAKEDATE(2011, CTIME) from datatypetestm order by cidx
11 --------------
12
13 cidx CTIME MAKEDATE(2011, CTIME)
14 1 13:00:00 NULL
15 1 row in set

[root@localhost functions]# cat -n CNX/MAKEDATE.DM.sql.ref.log
1 --------------
2 select cidx, CTIME, MAKEDATE(2010, CTIME) from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME MAKEDATE(2010, CTIME)
6 1 13:00:00 2365-12-05
7 1 row in set
8
9 --------------
10 select cidx, CTIME, MAKEDATE(2011, CTIME) from datatypetestm order by cidx
11 --------------
12
13 cidx CTIME MAKEDATE(2011, CTIME)
14 1 13:00:00 2366-12-05
15 1 row in set

5. DATE_FORMAT

[root@localhost functions]# cat -n CNX/DATE_FORMAT.DM.sql.tst.log
1 --------------
2 select cidx, CTIME, DATE_FORMAT(CTIME,'%W %M %Y') from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME DATE_FORMAT(CTIME,'%W %M %Y')
6 1 13:00:00 NULL
7 1 row in set
8
9 --------------
10 select cidx, CTIME, DATE_FORMAT(CTIME,'%H:%i:%s') from datatypetestm order by cidx
11 --------------
12
13 cidx CTIME DATE_FORMAT(CTIME,'%H:%i:%s')
14 1 13:00:00 NULL
15 1 row in set
16
17 --------------
18 select cidx, CTIME, DATE_FORMAT(CTIME,'%D %y %a %d %m %b %j') from datatypetestm order by cidx
19 --------------
20
21 cidx CTIME DATE_FORMAT(CTIME,'%D %y %a %d %m %b %j')
22 1 13:00:00 NULL
23 1 row in set

[root@localhost functions]# cat -n CNX/DATE_FORMAT.DM.sql.ref.log
1 --------------
2 select cidx, CTIME, DATE_FORMAT(CTIME,'%W %M %Y') from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME DATE_FORMAT(CTIME,'%W %M %Y')
6 1 13:00:00 Wednesday June 2018
7 1 row in set
8
9 --------------
10 select cidx, CTIME, DATE_FORMAT(CTIME,'%H:%i:%s') from datatypetestm order by cidx
11 --------------
12
13 cidx CTIME DATE_FORMAT(CTIME,'%H:%i:%s')
14 1 13:00:00 13:00:00
15 1 row in set
16
17 --------------
18 select cidx, CTIME, DATE_FORMAT(CTIME,'%D %y %a %d %m %b %j') from datatypetestm order by cidx
19 --------------
20
21 cidx CTIME DATE_FORMAT(CTIME,'%D %y %a %d %m %b %j')
22 1 13:00:00 20th 18 Wed 20 06 Jun 171
23 1 row in set

6. DATEDIFF

The DATEDIFF output is off by 1

[root@localhost functions]# cat -n CNX/DATEDIFF.DM.sql.tst.log
1 --------------
2 select cidx, CTIME, DATEDIFF(CTIME,'2007-02-28') from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME DATEDIFF(CTIME,'2007-02-28')
6 1 13:00:00 4131
7 1 row in set
8
9 --------------
10 select cidx, CTIME, DATEDIFF(CTIME,'2007-07-04') from datatypetestm order by cidx
11 --------------
12
13 cidx CTIME DATEDIFF(CTIME,'2007-07-04')
14 1 13:00:00 4005
15 1 row in set

[root@localhost functions]# cat -n CNX/DATEDIFF.DM.sql.ref.log
1 --------------
2 select cidx, CTIME, DATEDIFF(CTIME,'2007-02-28') from datatypetestm order by cidx
3 --------------
4
5 cidx CTIME DATEDIFF(CTIME,'2007-02-28')
6 1 13:00:00 4130
7 1 row in set
8
9 --------------
10 select cidx, CTIME, DATEDIFF(CTIME,'2007-07-04') from datatypetestm order by cidx
11 --------------
12
13 cidx CTIME DATEDIFF(CTIME,'2007-07-04')
14 1 13:00:00 4004

All of the flagged problems in Daniel's comment should now be fixed in the MCOL-1433 branch.

Build tested: Github source

/root/columnstore/mariadb-columnstore-server
commit 6b44f0d9c453ede53024f525b7ddf32b5323171b
Merge: 7db44a7 853a0f7
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Thu Sep 27 20:37:03 2018 +0100
Merge pull request #134 from mariadb-corporation/versionCmakeFix
port changes for mysql_version cmake to fix columnstore RPM packaging

/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit 3326be00de5f53ec365910f07a7fd882ba193d4d
Merge: ebbeb30 5cab6c4
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Tue Sep 18 13:57:17 2018 +0100
Merge pull request #565 from drrtuy/MCOL-1601
MCOL-1601 GROUP BY now supports subqueries in HAVING.
Regression test test001 passed, but test014 failed. Further investigation indicated that test014 failed due to a syntax error in the mcol1428.sql script. I manually verified the sublime() works. The failure is not related to this ticket and the sublime() function.

Some of the functions were fixed. There are still a few with non-matching results. They are TIMEDIFF(), MAKE_DATE(), and DATE_FORMAT().

STR_TO_DATE() seems to be correct. The test return incorrect result because the DATE_FORMAT is used in the query.

TIMEDIFF()

Reference:

MariaDB [mytest]> select cidx, CTIME, TIMEDIFF(CTIME,'2007-02-28 22:23:0') from datatypetestm order by cidx;
--------------------------------------------------

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

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

ColumnStore:

MariaDB [mytest]> select cidx, CTIME, TIMEDIFF(CTIME,'2007-02-28 22:23:0') from datatypetestm order by cidx;
--------------------------------------------------

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

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

MAKEDATE()

Reference:

MariaDB [mytest]> select cidx, CTIME, MAKEDATE(2010, CTIME) from datatypetestm order by cidx;
-----------------------------------

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

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

MariaDB [mytest]> select cidx, CTIME, MAKEDATE(2011, CTIME) from datatypetestm order by cidx;
-----------------------------------

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

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

ColumnStore:

MariaDB [mytest]> select cidx, CTIME, MAKEDATE(2010, CTIME) from datatypetestm order by cidx;
-----------------------------------

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

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

MariaDB [mytest]> select cidx, CTIME, MAKEDATE(2011, CTIME) from datatypetestm order by cidx;
-----------------------------------

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

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

DATE_FORMAT()

Reference:

MariaDB [mytest]> select cidx, CTIME, DATE_FORMAT(CTIME,'%W %M %Y') from datatypetestm order by cidx;
-------------------------------------------

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

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

MariaDB [mytest]> select cidx, CTIME, DATE_FORMAT(CTIME,'%D %y %a %d %m %b %j') from datatypetestm order by cidx;
-------------------------------------------------------

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

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

ColumnStore:

MariaDB [mytest]> select cidx, CTIME, DATE_FORMAT(CTIME,'%W %M %Y') from datatypetestm order by cidx;
-------------------------------------------

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

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

MariaDB [mytest]> select cidx, CTIME, DATE_FORMAT(CTIME,'%D %y %a %d %m %b %j') from datatypetestm order by cidx;
-------------------------------------------------------

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

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

I've moved DATE_FORMAT() to MCOL-1768 since it will require a complete re-write to support MariaDB's behaviour. Not something we intent to do for 1.2.0. The rest will be fixed here

Fixed MAKEDATE and TIMEDIFF.

TIMEDIFF doesn't like mixed DATETIME/TIME type input in MariaDB so we now block that. MAKEDATE was using the wrong integer representation of time for the conversion.

Build tested: 1.2.0-1 source

Build verified: 1.2.0-1 source
/root/columnstore/mariadb-columnstore-server
commit 6b44f0d9c453ede53024f525b7ddf32b5323171b
Merge: 7db44a7 853a0f7
Author: Andrew Hutchings <andrew@linuxjedi.co.uk>
Date: Thu Sep 27 20:37:03 2018 +0100
Merge pull request #134 from mariadb-corporation/versionCmakeFix
port changes for mysql_version cmake to fix columnstore RPM packaging
/root/columnstore/mariadb-columnstore-server/mariadb-columnstore-engine
commit ca7cb9a7b13cdfd7a5dc0e72efcaf7bb1a4a9a7d
Merge: 6ee1224 a127f84
Author: David.Hall <david.hall@mariadb.com>
Date: Mon Oct 8 11:56:26 2018 -0500
Merge pull request #581 from mariadb-corporation/MCOL-1433b
MCOL-1433 Fix TIME for MAKEDATE/TIMEDIFF

test and reference results matched.