Bug #77751 errmsg #1690: wrong field type

• Submitted: 16 Jul 2015 20:14
• Modified: 21 Jul 2015 14:17
• Reporter: Marcell Nagy
• Status: Verified
• Category: MySQL Server: Errors
• Severity: S3 (Non-critical)
• Version: 5.5/5.6/5.7
• OS: Any
• CPU Architecture: Any

[16 Jul 2015 20:14] Marcell Nagy

Description:
Regardless of the type of the integer field, MySQL displays "BIGINT" in error message #1690 when the value is out of range.
The test code below causes:
#1690 - BIGINT UNSIGNED value is out of range in '(`db`.`test`.`a` - 1)'
When it should be:
#1690 - SMALLINT UNSIGNED value is out of range in '(`db`.`test`.`a` - 1)'

How to repeat:
CREATE TABLE test (
  a SMALLINT(5) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test VALUE(0);

UPDATE test SET a = a-1;

[21 Jul 2015 14:13] MySQL Verification Team

[miguel@vbcentos65 bin]$ ./mysql -uroot --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test
Database changed
mysql> CREATE TABLE test (
    ->   a SMALLINT(5) UNSIGNED NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> INSERT INTO test VALUE(0);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> UPDATE test SET a = a-1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`test`.`a` - 1)'

[21 Jul 2015 14:15] MySQL Verification Team

C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.8-rc Source distribution PULL: 2015/06/28

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > CREATE TABLE test (
    ->   a SMALLINT(5) UNSIGNED NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.18 sec)

mysql 5.7 >
mysql 5.7 > INSERT INTO test VALUE(0);
Query OK, 1 row affected (0.05 sec)

mysql 5.7 >
mysql 5.7 > UPDATE test SET a = a-1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`test`.`a` - 1)'
mysql 5.7 >

[21 Jul 2015 14:17] MySQL Verification Team

Thank you for the bug report.

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.45-log Source distribution PULL: 2015/06/05

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > use test
Database changed
mysql 5.5 > CREATE TABLE test (
    ->   a SMALLINT(5) UNSIGNED NOT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)

mysql 5.5 >
mysql 5.5 > INSERT INTO test VALUE(0);
Query OK, 1 row affected (0.06 sec)

mysql 5.5 >
mysql 5.5 > UPDATE test SET a = a-1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`test`.`a` - 1)'
mysql 5.5 >