Req #77015 [Opn]: Change behaviour of query() to match that of exec() for better performance

Edit report at https://bugs.php.net/bug.php?id=77015&edit=1 ID: 77015 Updated by: cmb@php.net Reported by: love at sickpeople dot se Summary: Change behaviour of query() to match that of exec() for better performance Status: Open Type: Feature/Change Request -Package: PDO MySQL +Package: PDO Core PHP Version: 7.2.11 Block user comment: N Private report: N New Comment: This would be a general PDO issue. Previous Comments: ------------------------------------------------------------------------ [2018-10-15 17:20:31] love at sickpeople dot se Description: ------------ Summary - query() should send a QUERY like exec() does, instead of sending a PREPARE + EXECUTE + CLOSE - It improves performance in a very commonly used method, by avoiding unnecessary round trips - It is a BC break but only a minor one (you get a different error) and one you can easily fix (by switching "query" to "prepare", literaly) .. For comparison 1) query() sends a PREPARE + EXECUTE + CLOSE 2) exec() sends a QUERY 3) prepare() and execute() in concert sends a PREPARE + EXECUTE + CLOSE The general usage of query() is to send a query without parameters and get the result. Query() doesn't work with parameters. So changing query() to send a QUERY like exec() will avoid two unnecessary round trips. Hence this bug report. .. However query() returns a PDOStatement which exec() doesn't. You can actually do this $stmt = $pdo->query("select 1"); $stmt->execute(); $stmt->execute(); $stmt->execute(); And execute the prepared query multiple times, just like prepare() and execute(). I.e. PREPARE + EXECUTE + EXECUTE + EXECUTE + CLOSE. But calling execute() on the statement from query() is odd 1) The manual for query() says (http://php.net/manual/en/pdo.query.php) "For a query that you need to issue multiple times, you will realize better performance if you prepare a PDOStatement object using PDO::prepare() and issue the statement with multiple calls to PDOStatement::execute()." It seems like it's undocumented that you can do the exact same thing with query(), at least with Mysqlnd. 2) You *cannot* use parameters to execute(), even if you have parameters in the query E.g. $stmt = $pdo->query("select ?"); $stmt->execute([1]); This throws a PDOException with 2031 error (https://dev.mysql.com/doc/refman/5.7/en/error-messages-client.html#error_cr_params_not_bound). .. So this will be a BC break because 1) Calling execute() on the PDOStatement from query() should instead throw an exception, stating that the query is not prepared/has ended/there are no query to execute or similar The fix is to simply change $pdo->query(..) to $pdo->prepare(..). 2) Using parameters in query() will lead to a different error Currently if you do $pdo->query("select ?") you will get a PDOException with 2031 error (same as earlier), because PREPARE is used. If query() is changed to use QUERY, you will get an PDOException with syntax error instead. Just like with $pdo->exec("select ?") I've tested with PHP 7.2.11, Mysqlnd "5.0.12-dev - 20150407" (same version number as in master) and MySQL 5.7.23. Test script: --------------- $host = ''; $db = ''; $user = ''; $pass = ''; $options = [ PDO::ATTR_EMULATE_PREPARES => false, /* required */ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, ]; $pdo = new PDO("mysql:host=$host;dbname=$db;charset=utf8mb4", $user, $pass, $options); /* Extracts below are from MySQL general log https://dev.mysql.com/doc/refman/5.7/en/query-log.html */ $pdo->exec('set @foo=1'); /* 2018-10-15T16:57:44.219983Z 5126 Query set @foo=1 */ $pdo->prepare('set @foo=2')->execute(); /* 2018-10-15T16:57:44.220117Z 5126 Prepare set @foo=2 2018-10-15T16:57:44.220192Z 5126 Execute set @foo=2 2018-10-15T16:57:44.220283Z 5126 Close stmt */ $pdo->query ('set @foo=3'); /* 2018-10-15T16:57:44.220311Z 5126 Prepare set @foo=3 2018-10-15T16:57:44.220353Z 5126 Execute set @foo=3 2018-10-15T16:57:44.220431Z 5126 Close stmt */ $stmt = $pdo->query("select 1"); $stmt->execute(); $stmt->execute(); $stmt->execute(); /* 2018-10-15T16:57:44.220523Z 5126 Prepare select 1 2018-10-15T16:57:44.220566Z 5126 Execute select 1 2018-10-15T16:57:44.220706Z 5126 Execute select 1 2018-10-15T16:57:44.220801Z 5126 Execute select 1 2018-10-15T16:57:44.220868Z 5126 Execute select 1 2018-10-15T16:57:44.221214Z 5126 Close stmt */ $stmt = $pdo->query("select ?"); $stmt->execute([1]); // throws PDOException with 2031 ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=77015&edit=1