A part of the question states:
I found that using a parameter generates a quoted factor
So, it'd been my guess that the queries weren't necessarily written that way, but the quotes were added automatically by the query processor. Even if the quotes weren't added by the query processor, the output obtained resembled the behavior that would otherwise be seen with quotes explicitly added. Thus, in a way, implying that the result obtained was more implicit than as a consequence of some careless play on the OP's part. However, it remained to be seen. And I didn't want to spur up the debate or comment further without a verifiable example. So, here it goes....
Program 1:
$servername = "localhost";
$username = "test";
$password = "test";
$dbname = "testdb";
$array = array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1);
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("SELECT ROUND(1.945,2) AS test_round,
ROUND((ROUND((7002) / ?) * ?) / 3600,2) AS param_rounded,
ROUND(
(7002 / ?)* ?
/ 3603,5
)AS param_rounded_5_places,
ROUND(
(7002 / ?)* ?
/ 3603,4
)AS param_rounded_4_places,
ROUND(
(7002 / ?)* ?
/ 3603,3
)AS param_rounded_3_places,
ROUND((ROUND((7002) / 1) * 1) / 3600,2) AS hard_rounded,
ROUND((ROUND((7002) / CAST(? AS DECIMAL(10,2))) * CAST(? AS DECIMAL(10,2))) / 3600,2) AS param_rounded_modified"
);
$stmt->bindParam(1, $array[0]);
$stmt->bindParam(2, $array[1]);
$stmt->bindParam(3, $array[2]);
$stmt->bindParam(4, $array[3]);
$stmt->bindParam(5, $array[4]);
$stmt->bindParam(6, $array[5]);
$stmt->bindParam(7, $array[6]);
$stmt->bindParam(8, $array[7]);
$stmt->bindParam(9, $array[8]);
$stmt->bindParam(10, $array[9]);
$stmt->execute();
var_dump($stmt->fetchAll(PDO::FETCH_ASSOC));
}
catch(PDOException $e){
echo "Diagnostic: ".$e;
}
Program 2:
This program would be exactly the same as the first program, except:
$stmt->bindParam(1, $array[0], PDO::PARAM_INT);
$stmt->bindParam(2, $array[1], PDO::PARAM_INT);
$stmt->bindParam(3, $array[2], PDO::PARAM_INT);
$stmt->bindParam(4, $array[3], PDO::PARAM_INT);
$stmt->bindParam(5, $array[4], PDO::PARAM_INT);
$stmt->bindParam(6, $array[5], PDO::PARAM_INT);
$stmt->bindParam(7, $array[6], PDO::PARAM_INT);
$stmt->bindParam(8, $array[7], PDO::PARAM_INT);
$stmt->bindParam(9, $array[8], PDO::PARAM_INT);
$stmt->bindParam(10, $array[9], PDO::PARAM_INT);
Now, most users are accustomed to writing the queries the former way:
$stmt->bindParam(1, $array[0]);
This works fine for perhaps most arithmetic operations (at least in my personal experience so far), but ROUND seems to bring to the fore a possible issue. An undesirable truncation / rounding down...
Computed Expected Obtained
1.945 1.95 1.94
Demo - Though it is only a MySQL query directly, but the behavior is exactly comparable to PDO.
An interesting observation:
It was perhaps worthwhile to check what the manifestation of this whole thing would look like with a recurring fraction or alike: param_rounded_5_places and param_rounded_4_places which were basically computed as 7002 / 3603 ===> 1.943380516...
Computed Expected Obtained
param_rounded 1.945 1.95 1.94 --> Rounded down
param_rounded_5_places 1.943380516 1.94338 1.94338
param_rounded_4_places 1.943380516 1.9434 1.9434 --> NOT Rounded down
It can be seen that the with quotes (and with PDO bindParam() without specifying datatype), the problem persists when the precision is limited to two places after the decimal point, but seems to disappear (at least in this case) when the precision is checked beyond the second decimal place. Is this an issue? I don't know.....
Although, for the purpose of this answer, I haven't run the same test with mysqli_*() PHP functions, but it might quite likely be so that MySQLi Prepared Queries too behave the same way.
FIXES:
One obvious fix is to specify the datatype with bindParm(). See mysqli_stmt_bind_param() for the MySQLi equivalent:
$stmt->bindParam(1, $array[0], PDO::PARAM_INT);
If, for some reason, it's too cumbersome to change the program, then an indirect fix would be to use CAST in the query. For example:
ROUND((ROUND((7002) / CAST(? AS DECIMAL(10,2))) * CAST(? AS DECIMAL(10,2))) / 3600,2)
The result of this can be seen in param_rounded_modified, although that would come at the expense of adding a small layer of complexity to the query.
In summary, I feel, the conclusion would be a bit twisted. The error is more of a matter of popular convention (binding without specifying data-types) which, as the situation like this would expose, isn't always right. We obviously can't squarely call it a bug (albeit there is an anomaly as noted above), because MYSQL does provide the means to specify the datatypes in parameterised queries, even though it is generally not explicitly required (in PDO) for rather commonly used arithmetic operations.
