Description:
In the process of using ifnull (expr1, expr2) function, there are some scenarios where the return results do not meet the expectations
MySQL version:5.7.31
mysql [localhost:5731] {msandbox} (test) > select id,id1,ifnull(id1,0) from t2;
+---------------+---------------------+---------------------+
| id | id1 | ifnull(id1,0) |
+---------------+---------------------+---------------------+
| 1111111111111 | 9425267378135531774 | 9223372036854775807 |
+---------------+---------------------+---------------------+
How to repeat:
mysql [localhost:5731] {msandbox} (test) > create table t2( id bigint(20),id1 bigint(20) unsigned);
Query OK, 0 rows affected (0.03 sec)
mysql [localhost:5731] {msandbox} (test) > insert into t2 values(1111111111111,9425267378135531774);
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5731] {msandbox} (test) > select id,id1,ifnull(id1,1) from t2;
+---------------+---------------------+---------------------+
| id | id1 | ifnull(id1,1) |
+---------------+---------------------+---------------------+
| 1111111111111 | 9425267378135531774 | 9223372036854775807 |
+---------------+---------------------+---------------------+
1 row in set, 1 warning (0.02 sec)
mysql [localhost:5731] {msandbox} (test) > select id,id1,ifnull(id1,0) from t2;
+---------------+---------------------+---------------------+
| id | id1 | ifnull(id1,0) |
+---------------+---------------------+---------------------+
| 1111111111111 | 9425267378135531774 | 9223372036854775807 |
+---------------+---------------------+---------------------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost:5731] {msandbox} (test) > show variables like 'sql_mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode | |
+---------------+-------+
1 row in set (0.00 sec)
When the value of Id1 is greater than the upper limit of bigint, ifnull (id1,1) returns the upper limit of bigint instead of Id1
Suggested fix:
Refer to MariaDB:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.5.15-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use test;
Database changed
MariaDB [test]> create table t2(id bigint(20),id1 bigint(20) unsigned);
Query OK, 0 rows affected (0.008 sec)
MariaDB [test]> insert into t2 values(111111111,9425267378135531774);
Query OK, 1 row affected (0.002 sec)
MariaDB [test]> commit;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test]> select id,id1,ifnull(id1,1) from t2;
+-----------+---------------------+---------------------+
| id | id1 | ifnull(id1,1) |
+-----------+---------------------+---------------------+
| 111111111 | 9425267378135531774 | 9425267378135531774 |
+-----------+---------------------+---------------------+
1 row in set (0.000 sec)
Description: In the process of using ifnull (expr1, expr2) function, there are some scenarios where the return results do not meet the expectations MySQL version:5.7.31 mysql [localhost:5731] {msandbox} (test) > select id,id1,ifnull(id1,0) from t2; +---------------+---------------------+---------------------+ | id | id1 | ifnull(id1,0) | +---------------+---------------------+---------------------+ | 1111111111111 | 9425267378135531774 | 9223372036854775807 | +---------------+---------------------+---------------------+ How to repeat: mysql [localhost:5731] {msandbox} (test) > create table t2( id bigint(20),id1 bigint(20) unsigned); Query OK, 0 rows affected (0.03 sec) mysql [localhost:5731] {msandbox} (test) > insert into t2 values(1111111111111,9425267378135531774); Query OK, 1 row affected (0.00 sec) mysql [localhost:5731] {msandbox} (test) > select id,id1,ifnull(id1,1) from t2; +---------------+---------------------+---------------------+ | id | id1 | ifnull(id1,1) | +---------------+---------------------+---------------------+ | 1111111111111 | 9425267378135531774 | 9223372036854775807 | +---------------+---------------------+---------------------+ 1 row in set, 1 warning (0.02 sec) mysql [localhost:5731] {msandbox} (test) > select id,id1,ifnull(id1,0) from t2; +---------------+---------------------+---------------------+ | id | id1 | ifnull(id1,0) | +---------------+---------------------+---------------------+ | 1111111111111 | 9425267378135531774 | 9223372036854775807 | +---------------+---------------------+---------------------+ 1 row in set, 1 warning (0.00 sec) mysql [localhost:5731] {msandbox} (test) > show variables like 'sql_mode%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | sql_mode | | +---------------+-------+ 1 row in set (0.00 sec) When the value of Id1 is greater than the upper limit of bigint, ifnull (id1,1) returns the upper limit of bigint instead of Id1 Suggested fix: Refer to MariaDB: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 3 Server version: 10.5.15-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.000 sec) MariaDB [(none)]> use test; Database changed MariaDB [test]> create table t2(id bigint(20),id1 bigint(20) unsigned); Query OK, 0 rows affected (0.008 sec) MariaDB [test]> insert into t2 values(111111111,9425267378135531774); Query OK, 1 row affected (0.002 sec) MariaDB [test]> commit; Query OK, 0 rows affected (0.000 sec) MariaDB [test]> select id,id1,ifnull(id1,1) from t2; +-----------+---------------------+---------------------+ | id | id1 | ifnull(id1,1) | +-----------+---------------------+---------------------+ | 111111111 | 9425267378135531774 | 9425267378135531774 | +-----------+---------------------+---------------------+ 1 row in set (0.000 sec)