[#MCOL-4242] CTEs that filter timestamps against date strings return empty sets

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

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

[MCOL-4242] CTEs that filter timestamps against date strings return empty sets Created: 2020-08-11 Updated: 2021-01-14
Status:	Open
Project:	MariaDB ColumnStore
Component/s:	None
Affects Version/s:	1.5.2
Fix Version/s:	Icebox

[MCOL-4242] CTEs that filter timestamps against date strings return empty sets Created: 2020-08-11 Updated: 2021-01-14