php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #40869 Mysql gives incorrect result, using subqueries and variables
Submitted: 2007-03-20 17:40 UTC Modified: 2007-03-20 21:34 UTC
From: robert dot allen at unearthtravel dot com Assigned:
Status: Not a bug Package: MySQL related
PHP Version: 5.2.1 OS:
Private report: No CVE-ID: None
 [2007-03-20 17:40 UTC] robert dot allen at unearthtravel dot com
Description:
------------
Hi

We are getting an odd result when submitting a query to a mysql server (4.1.10), we get a different result when performing the query through php (an incorrect result) as opposed to straight in to a mysql client.

It seems to have performed the sub-query twice, so the variable @j has been incremented twice.

A simplified version of our code is shown below and reproduces the problem, I have also included a sample table.  I have seen it on both windows and linux.

Reproduce code:
---------------
mysql_query("SET @j=0;");
$sql = "select offset from (select @j:=@j+1 as offset, if(element_id=2,1,0) as result from element) as t1 WHERE t1.result=1 ";
$result = mysql_query($sql);
$row = mysql_fetch_row($result);
echo $row[0];


/******************DATABASE****************/

CREATE TABLE `element` (
  `element_id` int(10) unsigned NOT NULL auto_increment,
  `element_type_id` int(10) unsigned NOT NULL default '0',
  `lock_level` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`element_id`),
  KEY `new_index` (`element_type_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

/*Data for the table `element` */

insert  into `element`(`element_id`,`element_type_id`,`lock_level`) values (1,1,0);
insert  into `element`(`element_id`,`element_type_id`,`lock_level`) values (2,10,0);
insert  into `element`(`element_id`,`element_type_id`,`lock_level`) values (3,2,0);
insert  into `element`(`element_id`,`element_type_id`,`lock_level`) values (4,10,1);
insert  into `element`(`element_id`,`element_type_id`,`lock_level`) values (5,2,0);


Expected result:
----------------
2

Actual result:
--------------
7

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2007-03-20 17:45 UTC] tony2001@php.net
Make sure PHP is linked with the correct libmysql, it should be of the same version as the server.
 [2007-03-20 18:07 UTC] robert dot allen at unearthtravel dot com
To be honest I am not sure how to do this, I have tried it on an xampp bundle though and so presume they would have linked them correctly.  I have also tried on our hosted managed server and get the same result in both places.

Thanks for your help

Rob
 [2007-03-20 18:12 UTC] tony2001@php.net
Which OS is that?
 [2007-03-20 18:15 UTC] robert dot allen at unearthtravel dot com
The xampp bundle was installed on windows xp, our hosted server is a linux system
 [2007-03-20 18:23 UTC] tony2001@php.net
Run "select version()" on the server
 [2007-03-20 18:25 UTC] tony2001@php.net
And try php_mysql.dll from here: http://dev.mysql.com/downloads/connector/php/
 [2007-03-20 18:40 UTC] robert dot allen at unearthtravel dot com
the  select version() query gave:
5.0.33

I am also using those dlls now

Thanks
 [2007-03-20 18:45 UTC] tony2001@php.net
Please report it to MySQL people.
 [2007-03-20 18:50 UTC] robert dot allen at unearthtravel dot com
The reason I reported it to php was due to the fact I get a different result when executing it via php than a simple command line client.  

I presume you believe it to be a problem in the mysql dll's and therefore their bug?

Thanks once again

Rob
 [2007-03-20 18:56 UTC] tony2001@php.net
Your PHP uses mysql client libraries of different version, so I believe it's some kind of client/server incompatibility.
 [2007-03-20 21:34 UTC] robert dot allen at unearthtravel dot com
Hey

I have been playing with this some more and have found out that the query returns the results I would expect if run through the mysqli extension. I'm not sure if this gives you any further information as to where the problem is but thought I would let you know in case it indicates the problem is within php.

Thanks again

Rob
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Dec 03 05:01:32 2024 UTC