[MCOL-5241] MariaDB Columnstore produces wrong averages on extracted null-datetime fields (like year) Created: 2022-09-29  Updated: 2024-02-07

Status: Confirmed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 22.08.2, 5.6.3, 23.10
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: andreas eschbacher Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

10.5.13
+ OS Red Hat Enterprise Linux release 8.5 (Ootpa)
+ Kernel 4.18.0-348.12.2.el8_5.x86_64

Attachments: File EBI-677.sql     File EBI-677_string_lengths.sql    

 Description   

See the code attached.

Averaging over YEAR of a datetime-column that contains null-values gives 999.999 as average.

Only happens with Columnstore, InnoDB does everything correctly.
Occured on EBI03. No other server tested so far.

also affects MariaDB Version 10.6.9 CS Version 6.4.3

TODO: Interesting: does the error also occur on other functions, say, length of strings?
UPDATE: no, using length function on varchar fields does NOT show the error, attached the second SQL file for demonstration.

 Comments   
Comment by andreas eschbacher [ 2022-09-29 ]

UPDATE: no, using length function on varchar fields does NOT show the error, attached the second SQL file for demonstration.

Comment by alexey vorovich (Inactive) [ 2022-09-29 ]

drrtuy JFYI

Comment by Richard Stracke [ 2023-06-12 ]

It is eailiy reproducable

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (id INT, avgseek DATETIME) ENGINE = columnstore;
 
INSERT INTO t1 (id, avgseek) VALUES (1,'2023-06-12 14:47:37');
 
INSERT INTO t1 (id, avgseek) VALUES (1,NULL);


MariaDB [garmin]> select avg(year(avgseek)) from t1;
 
+--------------------+
 
| avg(year(avgseek)) |
 
+--------------------+
 
|           999.9999 |
 
+--------------------+
 
1 row in set (0,006 sec)

Generated at Thu Feb 08 02:56:24 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.