Bug #80653 [Opn->Csd]: MySQL BIGINT UNSIGNED value in prepared statement treated incorrectly

Edit report at https://bugs.php.net/bug.php?id=80653&edit=1 ID: 80653 Updated by: dharman@php.net Reported by: gman dot n at xrbr dot com Summary: MySQL BIGINT UNSIGNED value in prepared statement treated incorrectly -Status: Open +Status: Closed Type: Bug Package: MySQLi related Operating System: Ubuntu 20.04.1 LTS PHP Version: 7.4.14 -Assigned To: +Assigned To: dharman Block user comment: N Private report: N New Comment: Reported upstream with MySQL. Previous Comments: ------------------------------------------------------------------------ [2021-01-22 10:58:12] kieran at supportpal dot com Suggest to close. MySQL have verified a regression in 8.0.22+ ------------------------------------------------------------------------ [2021-01-21 22:54:35] gman dot n at xrbr dot com Done: https://bugs.mysql.com/bug.php?id=102338 ------------------------------------------------------------------------ [2021-01-21 21:43:13] dharman@php.net Thanks, I can see that there is a problem. I have been looking into the issue for the past hour and I still haven't narrowed it down, but I have some more findings I would like to keep a record of here. It looks like the only affected version is MySQL 8.0.22. I am 90% sure that the bug is on MySQL side... What I have found out so far is: - It affects all PHP versions - Engine is irrelevant - As you have noticed the type (both column and binding) of the second column impacts the result - Only certain numbers are affected. There is a pattern, but it's unclear to me what that pattern is. The bigger the numbers get the more irregular the pattern becomes. e.g. numbers between 13326067295508630 and 13326067295508660 (00 fail, 11-success): 11 13326067295508630 00 13326067295508631 11 13326067295508632 11 13326067295508633 11 13326067295508634 00 13326067295508635 11 13326067295508636 11 13326067295508637 11 13326067295508638 00 13326067295508639 11 13326067295508640 11 13326067295508641 11 13326067295508642 00 13326067295508643 11 13326067295508644 11 13326067295508645 11 13326067295508646 00 13326067295508647 11 13326067295508648 11 13326067295508649 11 13326067295508650 00 13326067295508651 11 13326067295508652 11 13326067295508653 11 13326067295508654 00 13326067295508655 11 13326067295508656 11 13326067295508657 11 13326067295508658 00 13326067295508659 ----------------------------- - The type used in binding makes huge difference, regardless of what it is or which parameter it is. For example, this works fine: $type3 = 30; $statement1->bind_param('ss', $hash3, $type3); $statement1->execute(); echo $statement1->get_result()->num_rows; $type4 = 30; $statement1->bind_param('ss', $hash3, $type4); $statement1->execute(); echo $statement1->get_result()->num_rows; but this does not $type3 = 30; $statement1->bind_param('si', $hash3, $type3); $statement1->execute(); echo $statement1->get_result()->num_rows; $type4 = 30; $statement1->bind_param('ss', $hash3, $type4); $statement1->execute(); echo $statement1->get_result()->num_rows; This leads me to believe that the problem is on MySQL side with the way the optimizer handles type casting in prepared statements. MySQL 8.0.22 has changed the way that prepared statements are parsed and optimized. This looks like an unintentional bug. The only explanation I have for this erratic behaviour is that there is a bug in the new way that MySQL handles PS and type casting. I see no way that mysqli or mysqlnd could produce such bug. Therefore, I would kindly ask you to report it to Oracle (https://bugs.mysql.com/) as soon as possible. ------------------------------------------------------------------------ [2021-01-21 21:24:28] kieran at supportpal dot com This issue looks very similar to one that I'm experiencing. Here's a reproducer: https://phpize.online/?phpses=10da649bcd37236fa6f1498f6adcdba0&sqlses=aa6be73c673b7971675e7fc46729579d&php_version=php8&sql_version=mysql80 Change int unsigned to int and the query works: https://phpize.online/?phpses=eab1ced5f425974278c22777c0e79f58&sqlses=aa6be73c673b7971675e7fc46729579d&php_version=php8&sql_version=mysql80 The issue only occurs on MySQL 8 (not 5.x versions), and affects all PHP versions. Emulating prepared statements seems to be a workaround to the problem. ------------------------------------------------------------------------ [2021-01-21 19:33:46] gman dot n at xrbr dot com Thanks for checking this out! Using your phpize.online link, when I change to MySQL 8.0 it does no longer work. I immediately checked and I'm using MySQL 8.0.22-0ubuntu0.20.04.3 (should have added that in the beginning, sorry!). So is it an issue with PHP or MySQL? Updated link: https://phpize.online/?phpses=ff46fef22cb57979f1280d43f0ad2729&sqlses=null&php_version=php7&sql_version=mysql80 ------------------------------------------------------------------------ The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at https://bugs.php.net/bug.php?id=80653 -- Edit this bug report at https://bugs.php.net/bug.php?id=80653&edit=1