[RFC]: full support unsigned data types[tinyint~int32]

[hustjieke]

Summary

Currently unsigned data type in StoneDB is supported on syntax level, the max positive values are the same withsigned data type, from tinyint to bigint. The work was done in pr #1218 #1220.

Working branch: https://github.com/stoneatom/stonedb/tree/feat_support_unsigned_data_types

In milestone v1.0.3, we'll fully support unsigned data type, just like MySQL does.

• Required Storage and Range for Unsigned Integer Types

Type	Storage (Bytes)	Minimum Value Unsigned	Maximum Value Unsigned
tinyint	1	0	255
smallint	2	0	65535
mediumint	3	0	16777215
int	4	0	4294967295
bigint	8	0	18446744073709551615

Numeric Data Type Syntax(exluding float, double, decimal)

Numeric data types that permit the UNSIGNED attribute also permit SIGNED. However, these data types are signed by default, so the SIGNED attribute has no effect.

As of MySQL 8.0.17, the UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms); you should expect support for it to be removed in a future version of MySQL. Consider using a simple CHECK constraint instead for such columns.

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

A small integer. The signed range is -32768 to 32767. The unsigned range is 0 to 65535.

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215.

INT[(M)] [UNSIGNED] [ZEROFILL]

A normal-size integer. The signed range is -2147483648 to 2147483647. The unsigned range is 0 to 4294967295.

INTEGER[(M)] [UNSIGNED] [ZEROFILL]

This type is a synonym for INT.

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

First impl unsigned [tinyint~int], then impl unsigned int64_t.

Tracking SubTask List:

• feature: impl support for create, insert(delayed/no delayed mode), update, delete, select, support for unsigned [tinyint~int] #1267
• feat: impl loader, exporter for unsigned [tinyint~int] #1329
• impl support expr, agg, support for unsigned [tinyint~int] #1293
• docs: update docs on unsigned data #1336

Check List:

• ValueOrNull return int64_t or unsigned int64_t
• Check OUT_OF_RANGE when doing calculation && truncate value.
• Cast conversion, like cast to time, string ...
• Considering null value, null for int32 & int64.
• if has compact on data pack, dpn
• like max_s, min_s, sum_s extend in DPN，and some serialize function in it, if we should add the unsigned flag in it.
• MysqlExpression convert unsigned data to ValueOrNull.
• TianmuAttr supports unsigned
• If Field2VC need to support unsigned.
• ValueCache class update the unsigned data Pack's min, max, sum value, if has impact?
• When ConstColumn supports that getting unsigned value, if result correct.
• According the condition, filter the suspect Pack data. eg: IN or NOT IN, if results correct.
• Pack of unsigned datatype supports the KG, eg: Histogram, charmap?
• AggregationAlgorithm support unsigned?
• Change exponent number to unsigned data if correct.
• ConditionEncoder::TransformINs
• convert to mysql field in SendRecord()
• TempTable::RecordIterator::PrepareValues()
• join
• group by
• order by
• GetValueInt64()
• Aggregation overflow
• int64_t min_i_, max_i_, sum_i_ in class ValueCache
• static infos in COL_VER_HDR_V3
• int64_t String2NumCastColumn::GetValueInt64Impl(const core::MIIterator &mit)
• ValueCache::CalcIntStats
• TempTable::Attr::SetValueInt64
• When you use subtraction between integer values where one is of type UNSIGNED, the result is unsigned unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled. See Section 12.11, “Cast Functions and Operators”.
• +, -, *, / ... operater && sql logical operator like >,<,=,>=,<=
• Regression bug: UNSIGNED bigint data, return incorrect result set #1203
• Regression feature: expand sum Aggregate Function to int128_t #1226
• Regression bug: The data type of the field is bigint,when you insert a value,maybe inserted with a null value. #1206

[hustjieke]

unsigned int64_t will be supported in the future, maybe after refactor data types module in tianmu engine.