[MCOL-4547] Regression: Constant decimal math not working Created: 2021-02-19  Updated: 2021-06-11  Resolved: 2021-06-11

Status: Closed
Project: MariaDB ColumnStore
Component/s: MDB Plugin
Affects Version/s: 6.1.1
Fix Version/s: 6.1.1

Type: Bug Priority: Blocker
Reporter: David Hall (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: regression


 Description   

See working_tpch1_compareLogOnly/misc/bug571.sql

drop table if exists `p2_loaded_uh_p_xml_fsc_2x_for_agg_201310_valid_sess`;
 
create table `p2_loaded_uh_p_xml_fsc_2x_for_agg_201310_valid_sess` ( `session_duration` decimal(13,3) not null,`session_idle_down_duration` decimal(13,3) not null, `session_q5_down` int(11) not null ) engine=columnstore default charset=latin1;
 
insert into p2_loaded_uh_p_xml_fsc_2x_for_agg_201310_valid_sess values (1032632.1 , 86382, 1273564557);
 
select  session_duration as session_duration,  session_idle_down_duration as session_idle_down_duration,  session_q5_down as session_q5_down,  session_duration*session_q5_down as result_1, (session_duration*session_q5_down)*8 as result_2, (1032632.1*1273564557)*8 as result_3 from p2_loaded_uh_p_xml_fsc_2x_for_agg_201310_valid_sess t1;


| session_duration | session_idle_down_duration | session_q5_down | result_1             | result_2              | result_3 |
 
+------------------+----------------------------+-----------------+----------------------+-----------------------+----------+
 
|      1032632.100 |                  86382.000 |      1273564557 | 1315123642980479.700 | 10520989143843837.600 |      0.0 |

result_3 should be 10520989143843837.6, not 0.0

This is a regression since 5.5

result_3 is a bunch of constants with math. In 5.5, because it's a constant, the math is done in the Server and the answer inserted somehow into the result. I assume this is still how it is supposed to work, but it doesn't anymore. I guess something to do with decimal(38) is confusing it.

 Comments   
Comment by David Hall (Inactive) [ 2021-03-24 ]

Here's an easier reproduction:

create table t1 (c1 int)engine=columnstore;
 
insert into t1 values (1);
 
select 1032632.1*1273564557*1, c1 from t1;
 
+------------------------+------+
 
| 1032632.1*1273564557*1 | c1   |
 
+------------------------+------+
 
|                    0.0 |    1 |
 
+------------------------+------+

Interestingly, if you take the final *1 off, you get a correct answer:

select 1032632.1*1273564557, c1 from t1;
 
+----------------------+------+
 
| 1032632.1*1273564557 | c1   |
 
+----------------------+------+
 
|   1315123642980479.7 |    1 |
 
+----------------------+------+

Integers work:

select 10326321*1273564557*1, c1 from t1;
 
+-----------------------+------+
 
| 10326321*1273564557*1 | c1   |
 
+-----------------------+------+
 
|     13151236429804797 |    1 |
 
+-----------------------+------+

Smaller decimal numbers also work:

 select 1032632.1*127356455*1, c1 from t1;
 
+-----------------------+------+
 
| 1032632.1*127356455*1 | c1   |
 
+-----------------------+------+
 
|     131512363575205.5 |    1 |
 
+-----------------------+------+

Comment by David Hall (Inactive) [ 2021-03-24 ]

Where t2 is InnoDB, it works:

create table t2 (c1 int);
 
insert into t2 values (1);
 
select 1032632.1*1273564557*1, c1 from t2;
 
+------------------------+------+
 
| 1032632.1*1273564557*1 | c1   |
 
+------------------------+------+
 
|     1315123642980479.7 |    1 |
 
+------------------------+------+

Comment by Alexander Barkov [ 2021-05-18 ]

develop-5 as of 2021-05-18 seems to work fine:

drop table if exists `p2_loaded_uh_p_xml_fsc_2x_for_agg_201310_valid_sess`;
 
create table `p2_loaded_uh_p_xml_fsc_2x_for_agg_201310_valid_sess` ( `session_duration` decimal(13,3) not null,`session_idle_down_duration` decimal(13,3) not null, `session_q5_down` int(11) not null ) engine=columnstore default charset=latin1;
 
insert into p2_loaded_uh_p_xml_fsc_2x_for_agg_201310_valid_sess values (1032632.1 , 86382, 1273564557);
 
select  session_duration as session_duration,  session_idle_down_duration as session_idle_down_duration,  session_q5_down as session_q5_down,  session_duration*session_q5_down as result_1, (session_duration*session_q5_down)*8 as result_2, (1032632.1*1273564557)*8 as result_3 from p2_loaded_uh_p_xml_fsc_2x_for_agg_201310_valid_sess t1;


+------------------+----------------------------+-----------------+----------------------+-----------------------+---------------------+
 
| session_duration | session_idle_down_duration | session_q5_down | result_1             | result_2              | result_3            |
 
+------------------+----------------------------+-----------------+----------------------+-----------------------+---------------------+
 
|      1032632.100 |                  86382.000 |      1273564557 | 1315123642980479.700 | -7925754929865714.016 | 10520989143843837.6 |
 
+------------------+----------------------------+-----------------+----------------------+-----------------------+---------------------+


drop table if exists t1;
 
create table t1 (c1 int)engine=columnstore;
 
insert into t1 values (1);
 
select 1032632.1*1273564557*1, c1 from t1;


+------------------------+------+
 
| 1032632.1*1273564557*1 | c1   |
 
+------------------------+------+
 
|     1315123642980479.7 |    1 |
 
+------------------------+------+

Comment by Alexander Barkov [ 2021-06-10 ]

develop as of 2021-06-10 also seems to work fine.

Comment by David Hall (Inactive) [ 2021-06-10 ]

I believe this may have been caused by something in server that has been fixed. No PR from us.

Comment by Daniel Lee (Inactive) [ 2021-06-11 ]

Build verified: 6.1.1 ( Drone #2573)

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