Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
22.08.2, 6.4.2, 23.02.3
-
None
-
None
-
MariaDB 10.6.9 CS 6.4.3 RHEL 8
-
2024-1
Description
We plan to store null in the timestamps, and use a Pentaho-specific view to null-replace them.
When trying this out, we found (again) a discrepancy between InnoDB and Columnstore:
what should work (and does in InnoDB) yields incorrect query results in Columnstore.
In detail: using YEAR(...) = 1970 in a WHERE condition on a null-replaced date yields no rows in case of Columstore-tables-based view, but the correct result set when the view is based on a InnoDB table.
See the attached SQL.
Attachments
Issue Links
- is blocked by
-
MCOL-5759 Wrong filter ordering for query with sub select
-
- Closed
-
Repro'd on an older build. Verified on 'commit_hash | 7ec8f3d' build.
create table t1 (a varchar(1000), b datetime, c int) ENGINE=Columnstore DEFAULT CHARSET=utf8;
insert into t1 values ('abc', null, 1);
insert into t1 values ('xyz', str_to_date('2022-09-22 00:00:00', '%Y-%m-%d %H:%i:%s'), 1);
create view v1 as select a, NVL(b, str_to_date('1970-01-01 00:00:00', '%Y-%m-%d %H:%i:%s')) as b, c from t1;
MariaDB [testMcs]> select count
from v1 where YEAR(b) = 1970;
----------
----------
----------
FROM v1 WHERE MONTH(b) = 00;
1 row in set (0.050 sec)
MariaDB [testMcs]> SELECT COUNT
----------
----------
----------
1 row in set (0.011 sec)
MariaDB [testMcs]> SELECT COUNT
FROM v1 WHERE DAY(b) = 00;
----------
----------
----------
1 row in set (0.010 sec)
MariaDB [testMcs]> SELECT COUNT
FROM v1 WHERE b = '1970-01-01 00:00:00';
----------
----------
----------
1 row in set (0.009 sec)