MySQL Bugs: #27369: Incorrect Query Result when using Subqueries and User Defined Varaibles

Bug #27369	Incorrect Query Result when using Subqueries and User Defined Varaibles
Submitted:	22 Mar 2007 9:14	Modified:	26 Mar 2007 9:03
Reporter:	Jeremy Boote	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.33 and 4.1.29	OS:	Linux (Linux and Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	command line, php, subquery, User Defined Varaible

Description:
This bug was originally posted to bugs.php.net

http://bugs.php.net/bug.php?id=40869

They believe it to be a problem with the libmysql.dll and told us to infrom MYSQL Bugs.

When the query in run in a mysql client and via the mysql_query() php function we achieve different results.

How to repeat:
/******************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);

PHP 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];

MYSQL Client Code

SET @j=0;

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 

The PHP method provides result 7
The MYSQL clinet method gives 2

Thank you for the report.

I tested it on Windows with current recommended to download 5.2.1 and MySQL 4.1.23 on Mac, on Linux with "PHP 5.2.2-dev (cli) (built: Mar 5 2007 19:16:35) " and on Mac with PHP6-cvs and with PHP4.4.4 On all machines result was 2.

So I'll mark the report as "Can't repeat".

With 5.0.40 server result was 2 too.

Test script to reproduce the case

Attachment: bug27369.php (application/octet-stream, text), 1.79 KiB.

I also can't repeat it with several PHP/MySQL versions using the script I've uploaded.

Please make sure that you have an ORDER BY in your SELECT statement. Without the ORDER BY, results might be random. 

The return value of 7 indicates that either the SET @j=0 was not successfull or the variable is still set from a previous query run in the session. Are you using persistent connections?

Thank you for your help....

We have discovered the issue, it is a result of mysql_trace_mode being turned on in our .htaccess file.

With it on we get 7.
With it off we get 2.

Not sure where the problem is, or if this is a bug but it does seem rather odd.