MySQL Bugs: #3126: mysql select @a > @b return different result on win2k and linux

Bug #3126	mysql select @a > @b return different result on win2k and linux
Submitted:	9 Mar 2004 18:36	Modified:	9 Mar 2004 19:41
Reporter:	Ken Tsang	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	4.0.13 and 4.0.16	OS:	Linux (linux and win2k)
Assigned to:		CPU Architecture:	Any

Description:
mysql version 4.0.13 on linux red-hat 9
mysql> select version();
+-----------+
| version() |
+-----------+
| 4.0.13 |
+-----------+
1 row in set (0.00 sec)

mysql> select @a:=itemid from sellitem where itemid = 9;select @b:=itemid
from sellitem where itemid = 11;select @a > @b;
+------------+
| @a:=itemid |
+------------+
| 9 |
+------------+
1 row in set (0.00 sec)

+------------+
| @b:=itemid |
+------------+
| 11 |
+------------+
1 row in set (0.00 sec)

+---------+
| @a > @b |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)

mysql>

mysql version 4.0.16 on win2k
mysql> select version();
+------------------+
| version() |
+------------------+
| 4.0.16-max-debug |
+------------------+
1 row in set (0.01 sec)

mysql> select @a:=itemid from sellitem where itemid = 9;select @b:=itemid
from s
ellitem where itemid = 11;select @a > @b;
+------------+
| @a:=itemid |
+------------+
| 9 |
+------------+
1 row in set (0.00 sec)

+------------+
| @b:=itemid |
+------------+
| 11 |
+------------+
1 row in set (0.00 sec)

+---------+
| @a > @b |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)

mysql>

How to repeat:
select @a:=9999;
select @b:=10000;
select @a > @b will return false in win2k with 4.0.16
and return true in linux with 4.0.13

when i changed the statement to 

set @a:=9999;
set @b:=10000;
the problem gone but what if the var is select from a query ?

select @a:=id from tablea

Are you using in the different result sets, numeric columns with decimal floating
point like float and double ?

Otherwise could you please provide a more complete test case with the table's
schema.

complete test case:

-on win2k with version 4.0.16

D:\mysql\bin>type test.sql
DROP TABLE IF EXISTS testtable;
CREATE TABLE testtable (
  CategoryID int(11) default '0',
) TYPE=InnoDB;
INSERT INTO testtable (categoryid) values (9999);
INSERT INTO testtable (categoryid) values (10000);
select version();
select @a:=categoryid from testtable where categoryid = 9999;
select @b:=categoryid from testtable where categoryid = 10000;
select @a > @b;

D:\mysql\bin>type test.sql | mysql -u root -p -h ptapp01 sqlsite37
Enter password: *******
version()
4.0.16-max-debug
@a:=categoryid
9999
@b:=categoryid
10000
@a > @b
0

D:\mysql\bin>

---------------------------------------------------------------

-on linux redhat 9.x with version 4.0.13

[root@dh15 jakarta-tomcat-5.0.16]# cat test.sql
DROP TABLE IF EXISTS testtable;
CREATE TABLE testtable (
  CategoryID int(11) default '0',
) TYPE=InnoDB;
INSERT INTO testtable (categoryid) values (9999);
INSERT INTO testtable (categoryid) values (10000);
select version();
select @a:=categoryid from testtable where categoryid = 9999;
select @b:=categoryid from testtable where categoryid = 10000;
select @a > @b;
[root@dh15 jakarta-tomcat-5.0.16]# cat test.sql | mysql -u sqlsite37 -p sqlsite37
Enter password:
version()
4.0.13
@a:=categoryid
9999
@b:=categoryid
10000
@a > @b
1
[root@dh15 jakarta-tomcat-5.0.16]#

On Windows XP:

C:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.0.18-max-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> DROP TABLE IF EXISTS testtable;
Query OK, 0 rows affected (0.25 sec)

mysql> CREATE TABLE testtable (
    ->   CategoryID int(11) default '0',
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected (1.66 sec)

mysql> INSERT INTO testtable (categoryid) values (9999);
Query OK, 1 row affected (0.20 sec)

mysql> INSERT INTO testtable (categoryid) values (10000);
Query OK, 1 row affected (0.06 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.0.18-max-debug |
+------------------+
1 row in set (0.10 sec)

mysql> select @a:=categoryid from testtable where categoryid = 9999;
+----------------+
| @a:=categoryid |
+----------------+
|           9999 |
+----------------+
1 row in set (0.03 sec)

mysql> select @b:=categoryid from testtable where categoryid = 10000;
+----------------+
| @b:=categoryid |
+----------------+
|          10000 |
+----------------+
1 row in set (0.00 sec)

mysql> select @a > @b;
+---------+
| @a > @b |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

On Suse 9.0

miguel@hegel:~> /usr/local/mysql/bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.18-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test;
Database changed
mysql> DROP TABLE IF EXISTS testtable;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE testtable (
    ->   CategoryID int(11) default '0',
    -> ) TYPE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO testtable (categoryid) values (9999);
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO testtable (categoryid) values (10000);
Query OK, 1 row affected (0.05 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 4.0.18-log |
+------------+
1 row in set (0.03 sec)

mysql> select @a:=categoryid from testtable where categoryid = 9999;
+----------------+
| @a:=categoryid |
+----------------+
|           9999 |
+----------------+
1 row in set (0.00 sec)

mysql> select @b:=categoryid from testtable where categoryid = 10000;
+----------------+
| @b:=categoryid |
+----------------+
|          10000 |
+----------------+
1 row in set (0.00 sec)

mysql> select @a > @b;
+---------+
| @a > @b |
+---------+
|       0 |
+---------+
1 row in set (0.00 sec)

Thanks for your test case.

I will try to upgrade to 4.0.16/18 in my linux box.