Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
1.5.2
-
None
Description
After upgrading from a Columnstore docker image to the Columnstore plugin (v1.5.2-1) under MariaDB 10.5.4, I've found that previously working queries now fail. I've narrowed the issue down to our use of the timestamp data type within CTEs. It appears that WHERE clause comparisons between timestamps and data strings within CTE subqueries return empty sets.
I've attached a SQL script that creates an example Columnstore table (bigint, timestamp) and queries that I believe should all work. There is a comment in that script to switch to InnoDB. The queries that fail are CTEs comparing the timestamp to a date string inside the subquery. Those failing queries pass when the engine is InnoDB.
Would using an unsigned integer for now be better than timestamp under Columnstore?
Test script output when using Columnstore engine (3 queries returning empty sets):
mysql < cs_cte_timestamp_test.sql
|
|
|
dump table:
|
id sampled UNIX_TIMESTAMP(sampled) |
0 2020-08-04 00:00:00 1596499200
|
1 2020-08-04 02:00:00 1596506400
|
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date (FROM_UNIXTIME): |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date (Date string): |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date (Date string), but wrapped in CTE (WITH): |
|
|
filter table by greather than date (Date string), but wrapped in CTE (WITH) using UNIX_TIMESTAMP: |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date, but wrapped in CTE (WITH): |
|
|
filter table by greather than date, but wrapped in a subquery: |
|
|
filter table by greather than date, but wrapped in CTE (WITH) using UNIX_TIMESTAMP rather than FROM_UNIXTIME: |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date, but wrapped in subquery using UNIX_TIMESTAMP rather than FROM_UNIXTIME: |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
Test script output when using InnoDB engine (all passing):
mysql < cs_cte_timestamp_test.sql
|
|
|
dump table:
|
id sampled UNIX_TIMESTAMP(sampled) |
0 2020-08-04 00:00:00 1596499200
|
1 2020-08-04 02:00:00 1596506400
|
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date (FROM_UNIXTIME): |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date (Date string): |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date (Date string), but wrapped in CTE (WITH): |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date (Date string), but wrapped in CTE (WITH) using UNIX_TIMESTAMP: |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date, but wrapped in CTE (WITH): |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date, but wrapped in a subquery: |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date, but wrapped in CTE (WITH) using UNIX_TIMESTAMP rather than FROM_UNIXTIME: |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|
|
|
filter table by greather than date, but wrapped in subquery using UNIX_TIMESTAMP rather than FROM_UNIXTIME: |
id sampled UNIX_TIMESTAMP(sampled) |
2 2020-08-04 04:00:00 1596513600
|
4 2020-08-04 06:00:00 1596520800
|