[MDEV-9370] convert(-1, unsigned) returns different result with and without aggregate functions at neighborhood select item Created: 2016-01-06  Updated: 2022-12-15

Status: Confirmed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.3.13, 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9
Fix Version/s: 10.1, 10.3

Type: Bug Priority: Minor
Reporter: Dylan Su Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Output:

mysql> drop table if exists t1;
 
reate tQuery OK, 0 rows affected (0.01 sec)
 
 
 
mysql> create table t1(c1 int);
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
mysql> insert into t1 values(1);
 
Query OK, 1 row affected (0.01 sec)
 

mysql> select convert(-1, unsigned) from t1;
 
+-----------------------+
 
| convert(-1, unsigned) |
 
+-----------------------+
 
|  18446744073709551615 |
 
+-----------------------+
 
1 row in set, 1 warning (0.00 sec)
 
 
 
mysql> select convert(-1, unsigned), count(1) from t1;
 
+-----------------------+----------+
 
| convert(-1, unsigned) | count(1) |
 
+-----------------------+----------+
 
|   9223372036854775807 |        1 |
 
+-----------------------+----------+
 
1 row in set, 1 warning (0.00 sec)
 

mysql>
 
mysql> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.1.10-MariaDB |
 
+-----------------+
 
1 row in set (0.00 sec)

Recreate:

drop table if exists t1;
 
create table t1(c1 int);
 
insert into t1 values(1);
 
select convert(-1, unsigned) from t1;
 
select convert(-1, unsigned), count(1) from t1;

Problem:
========
convert(-1, unsigned) should return 18446744073709551615 , no matter with or without aggregate function at neighborhood.

 Comments   
Comment by Elena Stepanova [ 2016-01-13 ]

Thanks for the report and the test case.

Reproducible with InnoDB, while MyISAM works okay.

In both queries, the same warning is produced:

+-------+------+-------------------------------------------------------------------------+
 
| Level | Code | Message                                                                 |
 
+-------+------+-------------------------------------------------------------------------+
 
| Note  | 1105 | Cast to unsigned converted negative integer to it's positive complement |
 
+-------+------+-------------------------------------------------------------------------+

Yet, the result is different as described above.

Could not reproduce with MySQL 5.5, 5.6, 5.7.

Generated at Thu Feb 08 07:34:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.