MySQL Bugs: #103992: BIGINT UNSIGNED vs VARCHAR comparisson

Bug #103992	BIGINT UNSIGNED vs VARCHAR comparisson
Submitted:	12 Jun 2021 16:28	Modified:	14 Jun 2021 12:54
Reporter:	Dmitry Kustov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	BIGINT, UNSIGNED

Description:
This was noticed by me in MariaDB (https://jira.mariadb.org/browse/MDEV-25862), but most likely will be true for MySQL as well (unfortunately, can't easily test this right now due to ongoing development).

The issue in short is that BIGINT UNSIGNED does not seem to be treated as UNSIGNED and is limited at 9223372036854775807 value (that is issues are present only for values larger than 9223372036854775807). The easiest replicable way to confirm this is mentioned in "How to repeat".

Besides that, there seems to be inconsistences when BIGINT-like values are used: initially issue was encountered, because despite query_log showing that rows with BIGINT UNSINGED were inserted, they were not present in the table. My assumption is that they were removed by DELETE queries in the same table due to WHERE not working correctly for BIGINT UNSIGNED values, thus removing extra rows. What's worse, this is true even if the said columns are converted to VARCHAR(20). Unfortunately, I can't consistently replicate this in a manual mode.

Furthermore, query like

SELECT `freecompanyid` FROM `ffxiv__freecompany` WHERE `freecompanyid`=9236038410806755490

Returns way more than the actual 1 record after the BIGINT UNSIGNED column was converted to VARCHAR(20), and behaves similar to

SELECT `freecompanyid` FROM `ffxiv__freecompany` WHERE `freecompanyid` LIKE '923603841080675%'

but with less rows. Which implies, that there is some weird casting/comparison going on here as well.

How to repeat:
CREATE TABLE `bigIntTest` ( `testfield` BIGINT(20) UNSIGNED NOT NULL ) ENGINE = InnoDB;
ALTER TABLE `bigIntTest` ADD PRIMARY KEY(`testfield`);
INSERT INTO `bigIntTest`(`testfield`) VALUES ('9226608999087051356'), ('9226608999087051357');
ALTER TABLE `bigIntTest` CHANGE `testfield` `testfield` BIGINT(20) UNSIGNED NOT NULL;

The above will result in
#1062 - Duplicate entry '9226608999087051356' for key 'PRIMARY'

Please check with MySQL 8.0.25 and print here the results. Thanks.

Looks like in MySQL VARCHAR does get converted to BIGINT UNSINGED normally, but... Issue with behavior for SELECT, when column is VARCHAR, but value passed is BIGING UNSIGNED is still incorrect;

mysql> CREATE TABLE `bigIntTest` ( `testfield` VARCHAR(20) NOT NULL ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER TABLE `bigIntTest` ADD PRIMARY KEY(`testfield`);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `bigIntTest`(`testfield`) VALUES ('9226608999087051356'), ('9226608999087051357');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `bigIntTest` CHANGE `testfield` `testfield` BIGINT(20) UNSIGNED NOT NULL;
Query OK, 2 rows affected, 1 warning (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------------------------------------+
| Level   | Code | Message                                                                      |
+---------+------+------------------------------------------------------------------------------+
| Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO `bigIntTest` (`testfield`) VALUES ('9236038410806755330'), ('9236038410806755331'), ('9236038410806755337'), ('9236038410806755342'), ('9236038410806755348'), ('9236038410806755354'), ('9236038410806755374'), ('9236038410806755375'), ('9236038410806755377'), ('9236038410806755389'), ('9236038410806755393'), ('9236038410806755398'), ('9236038410806755399'), ('9236038410806755402'), ('9236038410806755407'), ('9236038410806755411'), ('9236038410806755412'), ('9236038410806755415'), ('9236038410806755416'), ('9236038410806755420'), ('9236038410806755426'), ('9236038410806755431'), ('9236038410806755440'), ('9236038410806755447'), ('9236038410806755464'), ('9236038410806755469'), ('9236038410806755478'), ('9236038410806755487'), ('9236038410806755490'), ('9236038410806755860'), ('9236038410806755868'), ('9236038410806755872'), ('9236038410806755878'), ('9236038410806755884'), ('9236038410806755885'), ('9236038410806755888'), ('9236038410806755889'), ('9236038410806755892'), ('9236038410806755899'), ('9236038410806755910'), ('9236038410806755918'), ('9236038410806755946'), ('9236038410806755947'), ('9236038410806755948'), ('9236038410806755962'), ('9236038410806755983'), ('9236038410806756328'), ('9236038410806756329'), ('9236038410806756339'), ('9236038410806756346'), ('9236038410806756364'), ('9236038410806756365'), ('9236038410806756852'), ('9236038410806756854'), ('9236038410806757328'), ('9236038410806757338'), ('9236038410806757341'), ('9236038410806757369'), ('9236038410806757389'), ('9236038410806757391'), ('9236038410806757409'), ('9236038410806757429'), ('9236038410806757430'), ('9236038410806758343'), ('9236038410806758345'), ('9236038410806758362'), ('9236038410806758371'), ('9236038410806758393'), ('9236038410806758401'), ('9236038410806758433'), ('9236038410806758450'), ('9236038410806758470'), ('9236038410806759393'), ('9236038410806759398'), ('9236038410806759829'), ('9236038410806759871');
Query OK, 76 rows affected (0.00 sec)
Records: 76  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE `bigIntTest` CHANGE `testfield` `testfield` VARCHAR(20) NOT NULL;
Query OK, 78 rows affected (0.05 sec)
Records: 78  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM `bigIntTest` WHERE `testfield`=9236038410806755490;
+---------------------+
| testfield           |
+---------------------+
| 9236038410806755330 |
| 9236038410806755331 |
| 9236038410806755337 |
| 9236038410806755342 |
| 9236038410806755348 |
| 9236038410806755354 |
| 9236038410806755374 |
| 9236038410806755375 |
| 9236038410806755377 |
| 9236038410806755389 |
| 9236038410806755393 |
| 9236038410806755398 |
| 9236038410806755399 |
| 9236038410806755402 |
| 9236038410806755407 |
| 9236038410806755411 |
| 9236038410806755412 |
| 9236038410806755415 |
| 9236038410806755416 |
| 9236038410806755420 |
| 9236038410806755426 |
| 9236038410806755431 |
| 9236038410806755440 |
| 9236038410806755447 |
| 9236038410806755464 |
| 9236038410806755469 |
| 9236038410806755478 |
| 9236038410806755487 |
| 9236038410806755490 |
| 9236038410806755860 |
| 9236038410806755868 |
| 9236038410806755872 |
| 9236038410806755878 |
| 9236038410806755884 |
| 9236038410806755885 |
| 9236038410806755888 |
| 9236038410806755889 |
| 9236038410806755892 |
| 9236038410806755899 |
| 9236038410806755910 |
| 9236038410806755918 |
| 9236038410806755946 |
| 9236038410806755947 |
| 9236038410806755948 |
| 9236038410806755962 |
| 9236038410806755983 |
| 9236038410806756328 |
| 9236038410806756329 |
| 9236038410806756339 |
| 9236038410806756346 |
+---------------------+
50 rows in set (0.00 sec)

You have changed the test case and yourself say the original report isn't a bug and now you expect a varchar be treated as bigint :), anyway I signed a co-worked for it.

I copied wrong line for initial "How to repeat", where the table created was BIGINT, not VARCHAR, where I tried to convert VARCHAR to BIGINT, that's my bad. This part turned out to be MariaDB's specific (or perhaps present in older MySQL version, but fixed somehow in current one).
For the second part, I do not want VARCHAR to be treated as BIGINT or vice versa, that's the point. Conversion to VARCHAR on my side was done as an attempt to work around lines with BIGINT disappearing. Now THAT does start to make sense with the issue described for SELECT, but in reverse: original issue was discovered while using PHP, which does not support native BIGINT in prepared statements, thus I have to use strings, when querying from it:
DELETE FROM `ffxiv__freecompany_character` WHERE `freecompanyid`='9226608999087051355' AND `ffxiv__freecompany_character`.`characterid` NOT IN ('23038801','18185027');
while having BIGINT UNSINGED in the actual table. If BIGINT UNSIGNED comparison against VARCHAR works incorrectly the first part of the above WHERE can take extra lines, which also explains some timings for missing rows.
And luckily, this behavior I was able to replicate consistently in the second part of my previous post.

I'm sorry if my blabbing is still confusing: feel free to ask me to clarify anything.

Changed title to what seems a bit more appropriate with the new test results.

Hi Mr. Kustov,

Thank you for your bug report.

However, this is not a bug.

Comparison between VARCHAR and BIGINT is not expected to provide reliable results. An issue can be raised that MySQL should throw a syntax error on the query like that one. 

However, MySQL does not strong-typed SQL. There is a possibility that in the future it might support it, but, right now, strong typing would break too many of the current applications.

Not a bug.

Ok, that does make sense, but... What's the solution for PDO though? Specifically the one used by PHP? It does not have support for BIGINT out of the box. It will fail if we try to use a BIGINT as an INT, since PHP's INT is smaller than BIGINT. So far the only option is to use STRING (VARCHAR), but if BIGINT can't be compared reliably...
What I'm saying is, that PDO drivers need to be updated to allow biding of BIGINT values somehow. I can raise a "request" for PHP, but as far as I know PDO driver for MySQL is provided by MySQL/Oracle, thus I believe some actions need to be taken from developers' side as well.