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: | |
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
[22 Oct 2015 7:57]
MySQL Verification Team
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]
MySQL Verification Team
// 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.