|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[2021-01-21 18:47 UTC] gman dot n at xrbr dot com
Description: ------------ When using MySQLi prepared statement, everything works as expected when using only a single BIGINT UNSIGNED column by itself: Using one column only: "... WHERE `val_hash`=?" test value A: 8969302881072144 => result: works. test value B: 13326067295508650029 => result: works. The query returns the expected result in both cases. However, when adding a second column, which in my scenario is a TINYINT column, the SELECT query will work for test value A, but not yield any results for test value B. This occurs probabaly because the test value B ist a BIGINT UNSIGNED value is higher than PHP_INT_MAX. Using two columns: "... WHERE `val_type`=? AND `val_hash`=?" test value A: 8969302881072144 => result: works. test value B: 13326067295508650029 => result: DOES NOT WORK. Therefore, there must be a bug that messes up the BIGINT UNSIGNED value when higher than PHP_INT_MAX. Test script: --------------- Full script: https://pastebin.com/g4ZCG3sB Shorted version: <?php $connection = new \mysqli(...); $statement1 = $connection->prepare('SELECT `val_id`, `val_type` FROM `copy_pw_values` WHERE `val_type`=? AND `val_hash`=?'); $type3 = 3; $hash3 = '13326067295508650029'; $statement1->bind_param('is', $type3, $hash3); $statement1->execute(); echo $statement1->get_result()->num_rows; // Returns 0, should return 1. $type4 = '3'; $hash4 = '13326067295508650029'; $statement1->bind_param('ss', $type4, $hash4); $statement1->execute(); echo $statement1->get_result()->num_rows; // Returns 0, should return 1. Expected result: ---------------- 1111 11 11 Actual result: -------------- 1100 11 11 PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||
Copyright © 2001-2025 The PHP GroupAll rights reserved. |
Last updated: Mon May 05 12:01:29 2025 UTC |
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.