Cast(unsigned arg as decimal) doesn't work properly

[winoros]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

select cast(18446744073709551615 as decimal(65, 0));

or

create table tt(a bigint unsigned);
insert into tt values(18446744073709551615);
select cast(a as decimal(65, 0)) from tt;

2. What did you expect to see?

return 18446744073709551615.

3. What did you see instead?

-1 returned.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

master which commit is 5671382

[zz-jason]

drop table t;
create table t(a bigint unsigned);
insert into t values(18446744073709551615);

TiDB(localhost:4000) > select cast(a as decimal) from t;
+--------------------+
| cast(a as decimal) |
+--------------------+
|                 -1 |
+--------------------+
1 row in set (0.00 sec)

[AndrewDi]

tidb/expression/constant.go

Line 141 in eb0c663

return c.Value.GetInt64(), false, nil

@zz-jason It seems like force convert usigned int to signed int, and hit integer overflow, we may need to add new function to handle unsigned int.

[winoros]

@AndrewDi
Since unsigned is treated as a flag in MySQL, TiDB does this too currently.
The direct reason of this bug is that we forget to set the UnsignedFlag of CAST's fieldtype to true somewhere.

[AndrewDi]

@AndrewDi
Since unsigned is treated as a flag in MySQL, TiDB does this too currently.
The direct reason of this bug is that we forget to set the UnsignedFlag of CAST's fieldtype to true somewhere.

You are right, but I think there are two problems here:

tidb/expression/builtin_cast.go

Lines 483 to 501 in eb0c663

	func (b *builtinCastIntAsDecimalSig) evalDecimal(row chunk.Row) (res *types.MyDecimal, isNull bool, err error) {
	val, isNull, err := b.args[0].EvalInt(b.ctx, row)
	if isNull || err != nil {
	return res, isNull, errors.Trace(err)
	}
	if !mysql.HasUnsignedFlag(b.tp.Flag) {
	res = types.NewDecFromInt(val)
	} else if b.inUnion && val < 0 {
	res = &types.MyDecimal{}
	} else {
	var uVal uint64
	uVal, err = types.ConvertIntToUint(val, types.UnsignedUpperBound[mysql.TypeLonglong], mysql.TypeLonglong)
	if err != nil {
	return res, false, errors.Trace(err)
	}
	res = types.NewDecFromUint(uVal)
	}
	res, err = types.ProduceDecWithSpecifiedTp(res, b.tp, b.ctx.GetSessionVars().StmtCtx)
	return res, isNull, errors.Trace(err)

1. evalDecimal has handle cast unsigned int here, but due to UnsignedFlag has never been set, so it will never go into

   tidb/expression/builtin_cast.go

   Lines 493 to 495 in eb0c663

   	var uVal uint64
   	uVal, err = types.ConvertIntToUint(val, types.UnsignedUpperBound[mysql.TypeLonglong], mysql.TypeLonglong)
   	if err != nil {

   .

2. val, isNull, err := b.args[0].EvalInt(b.ctx, row) has lost unsigned value before.

[winoros]

@AndrewDi
Though it's a int64. We can use uint64(val) to easily get the correct unsigned value.

So if the unsigned flag is not lost, the behavior will always be right.

Add yes, a new function to handle unsigned should be a good idea. But it will be a lot of code change. So it may not be the best solution to this bug.