[MCOL-194] math on Date columns is broken Created: 2016-06-24  Updated: 2023-10-25  Resolved: 2023-10-25

Status: Closed
Project: MariaDB ColumnStore
Component/s: ExeMgr, PrimProc
Affects Version/s: 1.2.4
Fix Version/s: Icebox

Type: Bug Priority: Minor
Reporter: Justin Swanhart (Inactive) Assignee: Leonid Fedorov
Resolution: Won't Fix Votes: 1
Labels: None

Epic Link: ColumnStore Compatibility Improvements

 Description   

create table fact (
seq bigint,
i1 tinyint,
i2 smallint,
– i3 mediumint,
i4 int,
i8 bigint,
i1u tinyint unsigned,
i2u smallint unsigned,
– i3u mediumint unsigned,
i4u int unsigned,
i8u bigint unsigned,
– d1 decimal(65),
– d2 decimal(65,1),
– d3 decimal(65,30),
d5 decimal(15,5),
f1 float,
f2 float(2,1),
f23 float(23,4),
f24 float(24,10),
f53 float(53,20),
p1 double,
p2 double(2,1),
p23 double(23,4),
p24 double(24,10),
p53 double(53,20),
r1 real,
– r23 real(23,4),
– r24 real(24,10),
– r53 real(53,20),
– dp1 double precision,
– b0 bit(0),
– b1 bit(1),
– b64 bit(64),
d date,
dt datetime,
– ts timestamp,
– t time,
– y2 year(2),
– y4 year(4),
– c0 char(0),
c1 char(1),
c2 char(255),
– vc0 varchar(0),
vc1 varchar(1),
vc2 varchar(255)
– vb0 varbinary(0),
– vb1 varbinary(1),
– vb2 varbinary(255),
– bi0 binary(0),
– bi1 binary(1),
– bi2 binary(255)
– bl0 blob(0),
– bl1 blob(1),
– bl2 blob(255),
– tx0 text(0),
– tx1 text(1),
– tx2 text(255),
– e1 enum('a'),
– e2 enum(' ', 'a'),
– e3 enum(' ', 'a', 'abc'),
– s1 set('a'),
– s2 set(' ', 'a'),
– s3 set(' ', 'a', 'abc')
) ENGINE=COLUMNSTORE;

INSERT INTO fact VALUES (1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
INSERT INTO fact VALUES (2,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126,-126);
INSERT INTO fact VALUES (3,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126,126);
INSERT INTO fact VALUES (4,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127,127);
INSERT INTO fact VALUES (5,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128,128);
INSERT INTO fact VALUES (6,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253,253);
INSERT INTO fact VALUES (7,'255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255','255');
INSERT INTO fact VALUES (8,'-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128','-128');
INSERT INTO fact VALUES (9,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0,1.0);
INSERT INTO fact VALUES (10,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0,-1.0);
INSERT INTO fact VALUES (11,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555,-5555.5555);
INSERT INTO fact VALUES (12,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555,-5555.55555);
INSERT INTO fact VALUES (13,'-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555','-5555.5555');
INSERT INTO fact VALUES (14,'-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555','-5555.55555');
INSERT INTO fact VALUES (15,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555,55555.555);
INSERT INTO fact VALUES (16,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295);
INSERT INTO fact VALUES (17,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295,-4294967295);
INSERT INTO fact VALUES (18,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647,2147483647);
INSERT INTO fact VALUES (19,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647,-2147483647);
INSERT INTO fact VALUES (20,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296,4294967295.4294967296);
INSERT INTO fact VALUES (21,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296,-4294967295.4294967296);
INSERT INTO fact VALUES (22,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600,18446744073709551600);
INSERT INTO fact VALUES (23,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600,-18446744073709551600);
INSERT INTO fact VALUES (24,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800,9223372036854775800);
INSERT INTO fact VALUES (25,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800,-9223372036854775800);
INSERT INTO fact VALUES (26,'0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f','0xdeadb33f');
INSERT INTO fact VALUES (27,'swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany','swany');
INSERT INTO fact VALUES (28,'??????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','????????????????????????','??????????????????????????');
INSERT INTO fact VALUES (29,'%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%','%');
INSERT INTO fact VALUES (30,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890,12345678.1234567890);
INSERT INTO fact VALUES (31,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890,-12344567.1234567890);
INSERT INTO fact VALUES (32,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001,0.0000000001);
INSERT INTO fact VALUES (33,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099,0.0000000099);
INSERT INTO fact VALUES (34,'','','','','','','','','','','','','','','','','','','','','','','','','','');
INSERT INTO fact VALUES (35,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1);
INSERT INTO fact VALUES (36,'NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL','NOT NULL');
INSERT INTO fact VALUES (37,'1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30','1978_04_30');
INSERT INTO fact VALUES (38,'1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30','1978-04-30');
INSERT INTO fact VALUES (39,'1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00','1978-04-30 2:22:00');
INSERT INTO fact VALUES (40,'2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00','2:22:00');
INSERT INTO fact VALUES (41,'1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345','1978-04-30 2:22:00.12345');

select 1.0 * d from fact;
select -1.0 * d from fact;
select -5555.5555 * d from fact;
select -5555.55555 * d from fact;
select 1/ d from fact;
select 1.0 + d from fact;

All return:
1815 - Internal error: An unexpected condition within the query caused an internal processing error within InfiniDB. Please check the log files for more details. Additional Information: error in BatchPrimitiveProces

 Comments   
Comment by David Thompson (Inactive) [ 2016-10-07 ]

The internal error is:
Oct 6 05:58:18 centos7 PrimProc[3566]: 18.858784 |0|0|0| C 28 CAL0000: TreeNode::getDecimalVal: Invalid conversion from datetime.

I.e. the system doesn't support conversion from dates to decimals. This probably is not a primary use case in the real world.

Comment by David Hall (Inactive) [ 2019-07-11 ]

It is true that we don't support decimal math on date columns. Never have.
To show it, run the following instead of all the hoops above:

MariaDB [tpch1]> select l_shipdate, 3 * l_shipdate from lineitem order by l_shipdate limit 10;
--------------------------+

l_shipdate 3 * l_shipdate

--------------------------+

1992-01-02 391655994
1992-01-02 391655994
1992-01-02 391655994
1992-01-02 391655994
1992-01-02 391655994
1992-01-02 391655994
1992-01-02 391655994
1992-01-02 391655994
1992-01-02 391655994
1992-01-02 391655994

--------------------------+
10 rows in set (26.546 sec)

MariaDB [tpch1]> select l_shipdate, 3.0 * l_shipdate from lineitem order by l_shipdate limit 10;
ERROR 1815 (HY000): Internal error: An unexpected condition within the query caused an internal processing error within Columnstore. Please check the log files for more details. Additional Information: error in BatchPrimitivePro
MariaDB [tpch1]>

In treenode.h
IDB_Decimal TreeNode::getDecimalVal()
.
.
.
case CalpontSystemCatalog::DATE:
throw logging::InvalidConversionExcept("TreeNode::getDecimalVal: Invalid conversion from date.");

Note also that for integers, Columnstore gives a different answer than InnoDB.

Comment by David Hall (Inactive) [ 2019-07-11 ]

MariaDB gives consistent answers for integer and decimal types:

MariaDB [tpch1]> select l_shipdate, 3.0 * l_shipdate from lineitem order by l_shipdate limit 10;
----------------------------+

l_shipdate 3.0 * l_shipdate

----------------------------+

1992-01-02 59760306.0
1992-01-02 59760306.0
1992-01-02 59760306.0
1992-01-02 59760306.0
1992-01-02 59760306.0
1992-01-02 59760306.0
1992-01-02 59760306.0
1992-01-02 59760306.0
1992-01-02 59760306.0
1992-01-02 59760306.0

----------------------------+
10 rows in set (2.521 sec)

MariaDB [tpch1]> select l_shipdate, 3 * l_shipdate from lineitem order by l_shipdate limit 10;
--------------------------+

l_shipdate 3 * l_shipdate

--------------------------+

1992-01-02 59760306
1992-01-02 59760306
1992-01-02 59760306
1992-01-02 59760306
1992-01-02 59760306
1992-01-02 59760306
1992-01-02 59760306
1992-01-02 59760306
1992-01-02 59760306
1992-01-02 59760306

--------------------------+
10 rows in set (2.502 sec)

Comment by Valerii Kravchuk [ 2023-09-13 ]

Consider also this example:

MariaDB [(none)]> create database test;
 
Query OK, 1 row affected (0.001 sec)
 
 
 
MariaDB [(none)]> use test
 
Database changed
 
MariaDB [test]> create table tts(c1 datetime, c2 datetime) engine=Columnstore;
 
Query OK, 0 rows affected (15.977 sec)
 
 
 
MariaDB [test]> insert into tts values ('2023-09-12', '2023-09-13');
 
Query OK, 1 row affected (7.920 sec)
 
 
 
MariaDB [test]> insert into tts values ('2023-09-13 00:00:00', '2023-09-13 00:00:01');
 
Query OK, 1 row affected (3.119 sec)
 
 
 
MariaDB [test]> insert into tts values ('2023-09-13 00:00:00', '2023-09-13 00:00:10');
 
Query OK, 1 row affected (1.114 sec)
 
 
 
MariaDB [test]> select c1, c2, c2 - c1, timestampdiff (second, c1,c2), cast(c2 as integer) from tts;
 
+---------------------+---------------------+--------------+-------------------------------+---------------------+
 
| c1                  | c2                  | c2 - c1      | timestampdiff (second, c1,c2) | cast(c2 as integer) |
 
+---------------------+---------------------+--------------+-------------------------------+---------------------+
 
| 2023-09-12 00:00:00 | 2023-09-13 00:00:00 | 274877906944 |                         86400 |      20230913000000 |
 
| 2023-09-13 00:00:00 | 2023-09-13 00:00:01 |      1048576 |                             1 |      20230913000001 |
 
| 2023-09-13 00:00:00 | 2023-09-13 00:00:10 |     10485760 |                            10 |      20230913000010 |
 
+---------------------+---------------------+--------------+-------------------------------+---------------------+
 
3 rows in set (0.403 sec)
 
 
 
MariaDB [test]> alter table tts engine=InnoDB;
 
Query OK, 3 rows affected (8.423 sec)              
Records: 3  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select c1, c2, c2 - c1, timestampdiff (second, c1,c2), cast(c2 as integer) from tts;
 
+---------------------+---------------------+---------+-------------------------------+---------------------+
 
| c1                  | c2                  | c2 - c1 | timestampdiff (second, c1,c2) | cast(c2 as integer) |
 
+---------------------+---------------------+---------+-------------------------------+---------------------+
 
| 2023-09-12 00:00:00 | 2023-09-13 00:00:00 | 1000000 |                         86400 |      20230913000000 |
 
| 2023-09-13 00:00:00 | 2023-09-13 00:00:01 |       1 |                             1 |      20230913000001 |
 
| 2023-09-13 00:00:00 | 2023-09-13 00:00:10 |      10 |                            10 |      20230913000010 |
 
+---------------------+---------------------+---------+-------------------------------+---------------------+
 
3 rows in set (0.006 sec)

that shows that DATETIME subtraction works differently in Columnstore comparing to InnoDB, with no explanations here:

https://mariadb.com/kb/en/type-conversion/#rules-for-conversion-on-dyadic-arithmetic-operations

While it's clear where InnoDB values come from, for Colunnstore that 256G, 1M and 10M as c2 - c1 makes literally zero sense (or I can not guess it).

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