[#MCOL-3678] INSERT and UPDATE with out-of-range values caused saturation error

[MCOL-3678] INSERT and UPDATE with out-of-range values caused saturation error Created: 2019-12-17 Updated: 2019-12-18 Resolved: 2019-12-18
Status:	Closed
Project:	MariaDB ColumnStore
Component/s:	DMLProc
Affects Version/s:	1.4.2
Fix Version/s:	Icebox

Type:

Bug

Priority:

Major

Reporter:

Daniel Lee (Inactive)

Assignee:

Unassigned

Resolution:

Not a Bug

Votes:

Labels:

None

Description

Build tested: 1.4.2-1 (first combined build from Azura)
OS: centos 7
Stack: single server

DML (insert and update) with out-of-range values, as well as empty and null marker values (two reserved values in ColumnStore), caused errors:

MariaDB [mytest]> create table t1 (snBIGINT bigint) engine=columnstore;
Query OK, 0 rows affected (0.203 sec)

MariaDB [mytest]> insert into t1 values (10);
Query OK, 1 row affected (0.172 sec)

MariaDB [mytest]> update t1 set snBIGINT = -9223372036854775807;
ERROR 1264 (22003): CAL0002: IDB-2025: Data truncated for column 'snBIGINT'

MariaDB [mytest]> insert into t1 values (-92233720368547758070);
ERROR 1264 (22003): Out of range value for column 'snBIGINT' at row 1

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

snBIGINT

----------

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

In 1.4.1-1, the same statements would cause value saturation with a warnings:

MariaDB [mytest]> create table t1 (snBIGINT bigint) engine=columnstore;
Query OK, 0 rows affected (0.319 sec)

MariaDB [mytest]> insert into t1 values (10);
Query OK, 1 row affected (0.188 sec)

MariaDB [mytest]> update t1 set snBIGINT = -9223372036854775807;
Query OK, 0 rows affected, 1 warning (0.360 sec)
Rows matched: 0 Changed: 0 Warnings: 1

MariaDB [mytest]> show warnings;
---------------------------------------------------------------------

Level

Code

Message

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

Warning

1264

CAL0002: IDB-2025: Data truncated for column 'snBIGINT'

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

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

snBIGINT

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

-9223372036854775806

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

The same error occurred for all supported data types:

testcase: autopilot.systemTest.datatypes

[root@localhost datatypes]# mysql mytest -f < update.emptyMarkers.sql
ERROR 1264 (22003) at line 2: CAL0002: IDB-2025: Data truncated for column 'snBIGINT'
ERROR 1264 (22003) at line 3: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL1'
ERROR 1264 (22003) at line 4: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL4'
ERROR 1264 (22003) at line 5: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL4_2'
ERROR 1264 (22003) at line 6: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL5'
ERROR 1264 (22003) at line 7: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL9'
ERROR 1264 (22003) at line 8: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL9_2'
ERROR 1264 (22003) at line 9: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL10'
ERROR 1264 (22003) at line 10: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL18'
ERROR 1264 (22003) at line 11: CAL0002: IDB-2025: Data truncated for column 'snDECIMAL18_2'
ERROR 1264 (22003) at line 12: CAL0002: IDB-2025: Data truncated for column 'snINTEGER'
ERROR 1264 (22003) at line 13: CAL0002: IDB-2025: Data truncated for column 'snSMALLINT'
ERROR 1264 (22003) at line 14: CAL0002: IDB-2025: Data truncated for column 'snTINYINT'
ERROR 1264 (22003) at line 15: CAL0002: IDB-2025: Data truncated for column 'dDATE'
ERROR 1264 (22003) at line 16: CAL0002: IDB-2025: Data truncated for column 'dDATETIME'
ERROR 1264 (22003) at line 17: CAL0002: IDB-2025: Data truncated for column 'dTIME'
ERROR 1264 (22003) at line 37: CAL0002: IDB-2025: Data truncated for column 'unBIGINT'
ERROR 1264 (22003) at line 38: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL1'
ERROR 1264 (22003) at line 39: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL4'
ERROR 1264 (22003) at line 40: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL4_2'
ERROR 1264 (22003) at line 41: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL5'
ERROR 1264 (22003) at line 42: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL9'
ERROR 1264 (22003) at line 43: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL9_2'
ERROR 1264 (22003) at line 44: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL10'
ERROR 1264 (22003) at line 45: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL18'
ERROR 1264 (22003) at line 46: CAL0002: IDB-2025: Data truncated for column 'unDECIMAL18_2'
ERROR 1264 (22003) at line 47: CAL0002: IDB-2025: Data truncated for column 'unINTEGER'
ERROR 1264 (22003) at line 48: CAL0002: IDB-2025: Data truncated for column 'unSMALLINT'
ERROR 1264 (22003) at line 49: CAL0002: IDB-2025: Data truncated for column 'unTINYINT'

The same test case worked in 1.4.1-1

Comments

Comment by Andrew Hutchings (Inactive) [ 2019-12-18 ]

This is because in < 1.4.2 we were turning off strict mode for MariaDB, all engines. We cannot do this in the converged ColumnStore. The new behaviour is correct behaviour. If you want the old behaviour do:

set sql_mode="ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

The omission of strict mode turns it off.

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

[MCOL-3678] INSERT and UPDATE with out-of-range values caused saturation error Created: 2019-12-17 Updated: 2019-12-18 Resolved: 2019-12-18