[MCOL-4632] CAST(hugeNegativeWideDecimal AS SIGNED) returns 0 or NULL Created: 2021-03-23  Updated: 2023-09-22  Resolved: 2023-08-15

Status: Closed
Project: MariaDB ColumnStore
Component/s: PrimProc
Affects Version/s: 5.6.1, 6.1.1
Fix Version/s: 23.10.0

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Roman
Resolution: Fixed Votes: 0
Labels: beginner-friendly

Issue Links:
Relates
relates to MCOL-4361 Replace pow(10.0, (double)scale) expr... Closed
relates to MCOL-4648 CAST(UBIGINTNULL_inWideDecimal AS UNS... Closed
relates to MCOL-641 Full DECIMAL support in ColumnStore Closed
Sprint: 2023-8
Assigned for Review: Roman Roman
Assigned for Testing: Daniel Lee Daniel Lee (Inactive)

 Description    

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (d1 DECIMAL(30,0), d2 DECIMAL(30,0) NOT NULL) ENGINE=ColumnStore;
 
INSERT INTO t1 VALUES (-1e20,-1e20),(1e20,1e20);
 
SELECT d1, CAST(d1 AS SIGNED), CAST(d2 AS SIGNED) FROM t1;


+------------------------+---------------------+---------------------+
 
| d1                     | CAST(d1 AS SIGNED)  | CAST(d2 AS SIGNED)  |
 
+------------------------+---------------------+---------------------+
 
| -100000000000000000000 |                NULL |                   0 |
 
|  100000000000000000000 | 9223372036854775807 | 9223372036854775807 |
 
+------------------------+---------------------+---------------------+

The expected result is:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (d1 DECIMAL(30,0), d2 DECIMAL(30,0) NOT NULL) ENGINE=InnoDB;
 
INSERT INTO t1 VALUES (-1e20,-1e20),(1e20,1e20);
 
SELECT d1, CAST(d1 AS SIGNED), CAST(d2 AS SIGNED) FROM t1;


+------------------------+----------------------+----------------------+
 
| d1                     | CAST(d1 AS SIGNED)   | CAST(d2 AS SIGNED)   |
 
+------------------------+----------------------+----------------------+
 
| -100000000000000000000 | -9223372036854775808 | -9223372036854775808 |
 
|  100000000000000000000 |  9223372036854775807 |  9223372036854775807 |
 
+------------------------+----------------------+----------------------+



 Comments   
Comment by Daniel Lee (Inactive) [ 2023-04-25 ]

This issue still exists after the fix for MCOL-271.

Comment by Daniel Lee (Inactive) [ 2023-08-14 ]

Build tested: develop branch, latest

engine: c97c8b672e33e09e9cf66db39e6c013d398c97e3
server: 62d6100a913699fec9ff48284a76bfe6226e70bc
buildNo: 8420

Reproduced the reported issue in release 23.02.4.

In this latest build, unsupported negative value is returned:

MariaDB [mytest]> SELECT d1, CAST(d1 AS SIGNED), CAST(d2 AS SIGNED) FROM t1;
 
+------------------------+----------------------+----------------------+
 
| d1                     | CAST(d1 AS SIGNED)   | CAST(d2 AS SIGNED)   |
 
+------------------------+----------------------+----------------------+
 
| -100000000000000000000 | -9223372036854775807 | -9223372036854775807 |
 
|  100000000000000000000 |  9223372036854775807 |  9223372036854775807 |
 
+------------------------+----------------------+----------------------+
 
2 rows in set (0.066 sec)

According to data types information in this page https://mariadb.com/docs/columnstore/sql/features/data-types/enterprise-columnstore/

The smallest negative number supported in ColumnStore is -9223372036854775806. The follow test validated this information:

MariaDB [mytest]> CREATE TABLE t2 (d1 bigint, d2 bigint NOT NULL) ENGINE=ColumnStore;
 
Query OK, 0 rows affected (1.250 sec)
 
 
 
MariaDB [mytest]> insert into t2 values (9223372036854775807, 9223372036854775807);
 
Query OK, 1 row affected (0.266 sec)
 
 
 
MariaDB [mytest]> insert into t2 values (-9223372036854775808, -9223372036854775808);
 
ERROR 1264 (22003): CAL0001: MCS-2025: Data truncated for column 'd1', 'd2' 
 
 
MariaDB [mytest]> insert into t2 values (-9223372036854775807, -9223372036854775807);
 
ERROR 1264 (22003): CAL0001: MCS-2025: Data truncated for column 'd1', 'd2'   
MariaDB [mytest]> select * from t2;
 
 
 
MariaDB [mytest]> insert into t2 values (-9223372036854775806, -9223372036854775806);
 
Query OK, 1 row affected (0.191 sec)
 
 
 
MariaDB [mytest]> select * from t2;
 
+----------------------+----------------------+
 
| d1                   | d2                   |
 
+----------------------+----------------------+
 
|  9223372036854775807 |  9223372036854775807 |
 
| -9223372036854775806 | -9223372036854775806 |
 
+----------------------+----------------------+
 
2 rows in set (0.021 sec)

This build returned -9223372036854775807, which is not supported

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