From:
Operating system: Ubuntu 11.04
PHP version: Irrelevant
Package: MySQLi related
Bug Type: Bug
Bug description:Mysql explain command with prepared statement
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 bug report at http://bugs.php.net/bug.php?id=55001&edit=1
--
Try a snapshot (PHP 5.2):
http://bugs.php.net/fix.php?id=55001&r=trysnapshot52
Try a snapshot (PHP 5.3):
http://bugs.php.net/fix.php?id=55001&r=trysnapshot53
Try a snapshot (trunk):
http://bugs.php.net/fix.php?id=55001&r=trysnapshottrunk
Fixed in SVN:
http://bugs.php.net/fix.php?id=55001&r=fixed
Fixed in SVN and need be documented:
http://bugs.php.net/fix.php?id=55001&r=needdocs
Fixed in release:
http://bugs.php.net/fix.php?id=55001&r=alreadyfixed
Need backtrace:
http://bugs.php.net/fix.php?id=55001&r=needtrace
Need Reproduce Script:
http://bugs.php.net/fix.php?id=55001&r=needscript
Try newer version:
http://bugs.php.net/fix.php?id=55001&r=oldversion
Not developer issue:
http://bugs.php.net/fix.php?id=55001&r=support
Expected behavior:
http://bugs.php.net/fix.php?id=55001&r=notwrong
Not enough info:
http://bugs.php.net/fix.php?id=55001&r=notenoughinfo
Submitted twice:
http://bugs.php.net/fix.php?id=55001&r=submittedtwice
register_globals:
http://bugs.php.net/fix.php?id=55001&r=globals
PHP 4 support discontinued: http://bugs.php.net/fix.php?id=55001&r=php4
Daylight Savings: http://bugs.php.net/fix.php?id=55001&r=dst
IIS Stability:
http://bugs.php.net/fix.php?id=55001&r=isapi
Install GNU Sed:
http://bugs.php.net/fix.php?id=55001&r=gnused
Floating point limitations:
http://bugs.php.net/fix.php?id=55001&r=float
No Zend Extensions:
http://bugs.php.net/fix.php?id=55001&r=nozend
MySQL Configuration Error:
http://bugs.php.net/fix.php?id=55001&r=mysqlcfg
