[MCOL-3528] Update on numeric column using decimal values would set values to NULL Created: 2019-09-30  Updated: 2019-10-15  Resolved: 2019-10-15

Status: Closed
Project: MariaDB ColumnStore
Component/s: DMLProc
Affects Version/s: 1.4.0
Fix Version/s: 1.4.1

Type: Bug Priority: Major
Reporter: Daniel Lee (Inactive) Assignee: Bharath Bokka (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Sprint: 2019-06

 Description   

Build tested: 14.0-1

engine commit:
1f47534

test case: autopilot.systemTest.datatypes

MariaDB [mytest]> create table t1 (snDECIMAL4_2 DECIMAL(4,2), unDECIMAL4_2 DECIMAL(4,2) unsigned) engine=columnstore;
 
Query OK, 0 rows affected (0.433 sec)
 
 
 
MariaDB [mytest]> desc t1;
 
+--------------+-----------------------+------+-----+---------+-------+
 
| Field        | Type                  | Null | Key | Default | Extra |
 
+--------------+-----------------------+------+-----+---------+-------+
 
| snDECIMAL4_2 | decimal(4,2)          | YES  |     | NULL    |       |
 
| unDECIMAL4_2 | decimal(4,2) unsigned | YES  |     | NULL    |       |
 
+--------------+-----------------------+------+-----+---------+-------+
 
2 rows in set (0.001 sec)
 
 
 
MariaDB [mytest]> insert t1 values (10.55, 12.89);
 
Query OK, 1 row affected (0.295 sec)
 
 
 
MariaDB [mytest]> select * from t1;
 
+--------------+--------------+
 
| snDECIMAL4_2 | unDECIMAL4_2 |
 
+--------------+--------------+
 
|        10.55 |        12.89 |
 
+--------------+--------------+
 
1 row in set (0.094 sec)
 
 
 
MariaDB [mytest]> update t1 set snDECIMAL4_2 = 11.99, unDECIMAL4_2 = 17.99;
 
Query OK, 0 rows affected (0.322 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0
 
 
 
MariaDB [mytest]> select * from t1;
 
+--------------+--------------+
 
| snDECIMAL4_2 | unDECIMAL4_2 |
 
+--------------+--------------+
 
|         NULL |         NULL |
 
+--------------+--------------+
 
1 row in set (0.010 sec)
 
 
 
MariaDB [mytest]> update t1 set snDECIMAL4_2 = 11, unDECIMAL4_2 = 17;
 
Query OK, 0 rows affected (0.162 sec)
 
Rows matched: 0  Changed: 0  Warnings: 0
 
 
 
MariaDB [mytest]> select * from t1;
 
+--------------+--------------+
 
| snDECIMAL4_2 | unDECIMAL4_2 |
 
+--------------+--------------+
 
|        11.00 |        17.00 |
 
+--------------+--------------+
 
1 row in set (0.042 sec)



 Comments   
Comment by Daniel Lee (Inactive) [ 2019-09-30 ]

It in not just happening on decimal columns

MariaDB [mytest]> create table t2 (c1 int) engine=columnstore;
Query OK, 0 rows affected (0.243 sec)

MariaDB [mytest]> insert t2 values (5);
Query OK, 1 row affected (0.239 sec)

MariaDB [mytest]> select * from t2
-> ;
------

c1

------

5

------
1 row in set (0.084 sec)

MariaDB [mytest]> update t2 set c1=5.8;
Query OK, 0 rows affected (0.255 sec)
Rows matched: 0 Changed: 0 Warnings: 0

MariaDB [mytest]> select * from t2;
------

c1

------

NULL

------
1 row in set (0.034 sec)

Comment by Andrew Hutchings (Inactive) [ 2019-10-07 ]

Was fixed in https://github.com/mariadb-corporation/mariadb-columnstore-engine/pull/880

Comment by Bharath Bokka (Inactive) [ 2019-10-15 ]

Verified on,
Build: 1.4.1-1

engine commit:
f93c3f5

Update on numeric column using decimal values now doesn't set values to NULL on the above build.

Ex:
MariaDB [test]> create table numeric_dt(b boolean, i1 tinyint, i2 smallint, i3 int, i4 bigint, dc1 decimal, dc2 decimal(4,2),f float(4,2), db double) engine =columnstore;
Query OK, 0 rows affected (0.220 sec)

MariaDB [test]> show create table numeric_dt;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Table Create Table

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

numeric_dt CREATE TABLE `numeric_dt` (
`b` tinyint(1) DEFAULT NULL,
`i1` tinyint(4) DEFAULT NULL,
`i2` smallint(6) DEFAULT NULL,
`i3` int(11) DEFAULT NULL,
`i4` bigint(20) DEFAULT NULL,
`dc1` decimal(10,0) DEFAULT NULL,
`dc2` decimal(4,2) DEFAULT NULL,
`f` float(4,2) DEFAULT NULL,
`db` double DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.000 sec)

MariaDB [test]> insert into numeric_dt values(1,-126,-1112,67286287,987297298729872,29727.26626227,2.5383863,8.831513,3.9363);
Query OK, 1 row affected, 2 warnings (0.283 sec)

MariaDB [test]> select * from numeric_dt;
---------------------------------------------------------

b i1 i2 i3 i4 dc1 dc2 f db

---------------------------------------------------------

1 -126 -1112 67286287 987297298729872 29727 2.54 8.83 3.9363

---------------------------------------------------------
1 row in set (0.056 sec)

MariaDB [test]> update numeric_dt set b=0.9, i1=112.56, i2=111.98, i3=68662.34221, i4=62862.9292, dc1=27628628.9, dc2=17.869, f=86289.82682,db=2626.28528;
Query OK, 0 rows affected, 1 warning (0.233 sec)
Rows matched: 0 Changed: 0 Warnings: 1

MariaDB [test]> select * from numeric_dt;
-------------------------------------------------------

b i1 i2 i3 i4 dc1 dc2 f db

-------------------------------------------------------

1 113 112 68662 62863 27628629 17.87 86289.83 2626.28528

-------------------------------------------------------
1 row in set (0.067 sec)

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