php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #55001 Mysql explain command with prepared statement
Submitted: 2011-06-06 16:30 UTC Modified: 2011-09-02 13:51 UTC
From: enrico dot triolo at gmail dot com Assigned: mysql (profile)
Status: Not a bug Package: MySQLi related
PHP Version: Irrelevant OS: Ubuntu 11.04
Private report: No CVE-ID: None
 [2011-06-06 16:30 UTC] 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

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2011-06-08 02:17 UTC] johannes@php.net
-Status: Open +Status: Feedback -Assigned To: +Assigned To: mysql
 [2011-06-08 02:17 UTC] johannes@php.net
Are you using mysqlnd or libmysql. If libmysql which version? (check phpinfo() output or `php --ri mysqli` from command line)
 [2011-06-08 08:48 UTC] enrico dot triolo at gmail dot com
-Status: Feedback +Status: Assigned
 [2011-06-08 08:48 UTC] 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-09-02 13:51 UTC] uw@php.net
-Status: Assigned +Status: Bogus
 [2011-09-02 13:51 UTC] uw@php.net
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'
 
PHP Copyright © 2001-2020 The PHP Group
All rights reserved.
Last updated: Sat Feb 29 08:01:27 2020 UTC