Bug #78914 Flags info in result meta data is incorrect ( negative num w/ "UNSIGNED")
Submitted: 22 Oct 2015 6:08 Modified: 5 Nov 2015 15:28
Reporter: Su Dylan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7.8, 5.7.9, 5.6.28, 5.5.47 OS:Any
Assigned to: CPU Architecture:Any

[22 Oct 2015 6:08] Su Dylan
Description:
Output:
=======
mysql> drop table t1; create table t1(a year, b decimal(10,2) unsigned);
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values (2015, 123456.78);
Query OK, 1 row affected (0.00 sec)

mysql> select a - b from t1;
Field   1:  `a - b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     12
Max_length: 10
Decimals:   2
Flags:      UNSIGNED BINARY

+------------+
| a - b      |
+------------+
| -121441.78 |
+------------+
1 row in set (0.00 sec)

mysql> select version();
Field   1:  `version()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  ? (45)
Length:     32
Max_length: 8
Decimals:   31
Flags:      NOT_NULL

+-----------+
| version() |
+-----------+
| 5.7.8-rc  |
+-----------+
1 row in set (0.00 sec)

Problem:
========
The glags for "a-b" is 
Flags:      UNSIGNED BINARY
while the result number values is -121441.78, which is a negative number and can't be UNSIGNED.

How to repeat:
drop table t1; create table t1(a year, b decimal(10,2) unsigned);
insert into t1 values (2015, 123456.78);
select a - b from t1;

Suggested fix:
"UNSIGNED" should be removed from FLAGS in meta data of "a-b".
[22 Oct 2015 7:57] Umesh Shastry
Hello Su Dylan,

Thank you for the report.
Observed with 5.5.47, 5.6.28 and 5.7.8/5.7.9 builds.

Thanks,
Umesh
[22 Oct 2015 7:58] Umesh Shastry
// 5.7.9

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock --column-type-info
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.9 MySQL Community Server (GPL)

.
mysql> select a - b from t1;
Field   1:  `a - b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     12
Max_length: 10
Decimals:   2
Flags:      UNSIGNED BINARY NUM

+------------+
| a - b      |
+------------+
| -121441.78 |
+------------+
1 row in set (0.00 sec)

// 5.6.28

mysql> select a - b from t1;
Field   1:  `a - b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     12
Max_length: 10
Decimals:   2
Flags:      UNSIGNED BINARY NUM

+------------+
| a - b      |
+------------+
| -121441.78 |
+------------+
1 row in set (0.00 sec)

// 5.5.47

mysql> select a - b from t1;
Field   1:  `a - b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     12
Max_length: 10
Decimals:   2
Flags:      UNSIGNED BINARY NUM

+------------+
| a - b      |
+------------+
| -121441.78 |
+------------+
1 row in set (0.00 sec)

mysql>
[22 Oct 2015 8:08] Su Dylan
Hi Umesh,

Thanks for the reply.
In this issue, the inconsistency is not correct(negative value with flags UNSIGNED).
However, what should be the correct behavior?
There are at least two options here:
1. a-b is treated as a UNSIGNED decimal, and the negative value result should be rejected or truncated due to "out of range".
2. result flags for "a-b" is not with UNSIGNED.

Which one is correct? 

Thanks.
[22 Oct 2015 10:15] Tor Didriksen
Posted by developer:
 
create table t2(a int unsigned, b decimal(10,2) unsigned);
insert into t2 values (2015, 123456.78);

mysql> select a - b from t2;
Field   1:  `a - b`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       NEWDECIMAL
Collation:  binary (63)
Length:     14
Max_length: 10
Decimals:   2
Flags:      UNSIGNED BINARY NUM 

+------------+
| a - b      |
+------------+
| -121441.78 |
+------------+
1 row in set (4,19 sec)

create table t3(a int unsigned, b int unsigned);
insert into t3 values (2015, 123456);
mysql> select a - b from t3;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`t3`.`a` - `test`.`t3`.`b`)'

The decimal subtraction should have thrown an error as well.
[5 Nov 2015 15:28] Paul Dubois
Note in 5.8.0 changelogs.

Subtraction of an unsigned decimal could return a negative value, but
with metadata type information of UNSIGNED BINARY. Subtraction for
unsigned decimal subtraction now is handled the same way as for
unsigned integer: Produce an ER_DATA_OUT_OF_RANGE error if the result
is negative, unless the NO_UNSIGNED_SUBTRACTION SQL mode is enabled.