Edit report at https://bugs.php.net/bug.php?id=55001&edit=1
ID: 55001 Updated by: u...@php.net Reported by: enrico dot triolo at gmail dot com Summary: Mysql explain command with prepared statement -Status: Assigned +Status: Bogus Type: Bug Package: MySQLi related Operating System: Ubuntu 11.04 PHP Version: Irrelevant Assigned To: mysql Block user comment: N Private report: N New Comment: Works fine with mysqlnd. That's a libmysql issue. Use mysqlnd instead. Because it works with mysqlnd, its not a mysqli issue either. Setting to "Bogus" as bugs.php.net is not for libmysql bug reports. Please, report over at bugs.mysql.com. -------------- script ------------------------------------------------------ nixnutz@linux-fuxh:~/php/php-src/branches/PHP_5_4> cat foo.php <?php $sql = 'explain SELECT id FROM mytest_table WHERE idParent <> -1 AND idParent NOT IN ( SELECT id FROM mytest_table)'; $link = mysqli_connect("localhost", 'root', '', 'test'); $link->query("DROP TABLE IF EXISTS mytest_table"); $link->query("CREATE TABLE mytest_table(id INT PRIMARY KEY NOT NULL, idParent INT)"); $link->query("INSERT INTO mytest_table(id, idParent) VALUES (1, -1), (2, 1)"); printf("Using prepared statement functions...\n"); $stmt = mysqli_stmt_init($link); mysqli_stmt_prepare($stmt, $sql); mysqli_stmt_execute($stmt); mysqli_stmt_store_result($stmt); $result = mysqli_stmt_result_metadata($stmt); printf("Fields:\n"); while($field = mysqli_fetch_field($result)) printf("\t%s(%d)\n", $field->name, $field->length); mysqli_free_result($result); mysqli_stmt_bind_result($stmt, $id, $select_type, $table, $type, $possible_keys, $key, $key_len, $ref, $rows, $extra); while(mysqli_stmt_fetch($stmt)) printf("Type field value: %s\n", $type); printf("\nUsing mysqli_query...\n"); $result = mysqli_query($link, $sql); while($row = mysqli_fetch_array($result)) printf("Type field value: %s\n", $row['type']); mysqli_free_result($result); mysqli_close($link); -------------------------- libmysql ------------------------------------- nixnutz@linux-fuxh:~/php/php-src/branches/PHP_5_4> sapi/cli/php -i | grep mysql Configure Command => './configure' '--with-mysql=/home/nixnutz/ftp/mysql-5.6.2-m5/install' '--with-mysqli=/home/nixnutz/ftp/mysql-5.6.2-m5/install/bin/mysql_config' '--with-pdo-mysql=/home/nixnutz/ftp/mysql-5.6.2-m5/install/bin/mysql_config' '--enable-debug' '--with-openssl' '--enable-pcntl' nixnutz@linux-fuxh:~/php/php-src/branches/PHP_5_4> sapi/cli/php foo.php Using prepared statement functions... Fields: id(3) select_type(19) table(64) type(10) possible_keys(4096) key(64) key_len(4096) ref(1024) rows(10) Extra(255) Type field value: ALL Type field value: unique_subq Using mysqli_query... Type field value: ALL Type field value: unique_subquery -------------------- mysqlnd ---------------------------- nixnutz@linux-fuxh:~/php/php-src/trunk> sapi/cli/php foo.php Using prepared statement functions... Fields: id(3) select_type(19) table(64) type(10) possible_keys(4096) key(64) key_len(4096) ref(1024) rows(10) Extra(255) Type field value: ALL Type field value: unique_subquery Using mysqli_query... Type field value: ALL Type field value: unique_subquery nixnutz@linux-fuxh:~/php/php-src/trunk> sapi/cli/php -i | grep mysqlnd Configure Command => './configure' '--with-mysql=mysqlnd' '--with-mysqli=mysqlnd' '--with-pdo-mysql=mysqlnd' '--enable-debug' '--enable-maintainer-zts' '--enable-pcntl' Previous Comments: ------------------------------------------------------------------------ [2011-06-08 08:48:20] enrico dot triolo at gmail dot com I'm using libmysql. Here's the output of the "php --ri mysqli" command: $php --ri mysqli mysqli MysqlI Support => enabled Client API library version => 5.1.54 Active Persistent Links => 0 Inactive Persistent Links => 0 Active Links => 0 Client API header version => 5.1.54 MYSQLI_SOCKET => /var/run/mysqld/mysqld.sock Directive => Local Value => Master Value mysqli.max_links => Unlimited => Unlimited mysqli.max_persistent => Unlimited => Unlimited mysqli.allow_persistent => On => On mysqli.default_host => no value => no value mysqli.default_user => no value => no value mysqli.default_pw => no value => no value mysqli.default_port => 3306 => 3306 mysqli.default_socket => no value => no value mysqli.reconnect => Off => Off mysqli.allow_local_infile => On => On ------------------------------------------------------------------------ [2011-06-08 02:17:22] johan...@php.net Are you using mysqlnd or libmysql. If libmysql which version? (check phpinfo() output or `php --ri mysqli` from command line) ------------------------------------------------------------------------ [2011-06-06 16:30:24] enrico dot triolo at gmail dot com Description: ------------ Hi, I found out an anomaly executing an "explain" command using prepared statements functions. Using "standard" functions works as expected. In short, if I execute an "explain" on a query with a subquery and fetch the resulting "type" field, I get "unique_subq" instead of "unique_subquery". Please view the attached code snippet. I'm using PHP 5.3.5-1ubuntu7.2, but other versions have the same behaviour too. The attached script uses a table created with this instruction: CREATE TABLE IF NOT EXISTS `mytest_table` ( `id` int(11) NOT NULL AUTO_INCREMENT, `idParent` int(11) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; Test script: --------------- $sql = 'explain SELECT id FROM mytest_table WHERE idParent <> -1 AND idParent NOT IN ( SELECT id FROM mytest_table)'; $link = mysqli_connect("localhost", 'user', 'password', 'dbName'); printf("Using prepared statement functions...\n"); $stmt = mysqli_stmt_init($link); mysqli_stmt_prepare($stmt, $sql); mysqli_stmt_execute($stmt); mysqli_stmt_store_result($stmt); $result = mysqli_stmt_result_metadata($stmt); printf("Fields:\n"); while($field = mysqli_fetch_field($result)) printf("\t%s(%d)\n", $field->name, $field->length); mysqli_free_result($result); mysqli_stmt_bind_result($stmt, $id, $select_type, $table, $type, $possible_keys, $key, $key_len, $ref, $rows, $extra); while(mysqli_stmt_fetch($stmt)) printf("Type field value: %s\n", $type); printf("\nUsing mysqli_query...\n"); $result = mysqli_query($link, $sql); while($row = mysqli_fetch_array($result)) printf("Type field value: %s\n", $row['type']); mysqli_free_result($result); mysqli_close($link); Expected result: ---------------- Using prepared statement functions I'd expect the second row value for the "type" field being "unique_subquery". Actual result: -------------- I get "unique_subq" instead of "unique_subquery". Using mysqli_query (i.e. without prepared statement functions), I get the expected result: Using prepared statement functions... ... Type field value: unique_subq Using mysqli_query... ... Type field value: unique_subquery ------------------------------------------------------------------------ -- Edit this bug report at https://bugs.php.net/bug.php?id=55001&edit=1