Bug #5873 User variables don't work after FROM when using JConnector

• Submitted: 4 Oct 2004 9:14
• Modified: 4 Oct 2004 14:09
• Reporter: Tony Bosco
• Status: Can't repeat
• Category: Connector / J
• Severity: S1 (Critical)
• Version: 3.0.14
• OS: Windows (WIndows 2000)
• Assigned to: Mark Matthews
• CPU Architecture: Any

[4 Oct 2004 9:14] Tony Bosco

Description:
Hi,
a user variable's value is not recognised in any way after the FROM.

This error though only occurs when using mysql from JAVA through JConnector.

The example below will work fine if used on mysql server through a command line prompt.

Cheers
Tony

How to repeat:
SELECT 
  @location:=4,
  suburb
FROM ad
WHERE ad.loc BETWEEN @location AND 8;

--------------
The 4 contained in @location will not be passed if used after the FROM. It seems it is passing nothing at all.

Suggested fix:
@location should work after the FROM.

[4 Oct 2004 14:09] Mark Matthews

I'm not able to repeat the behavior where you say this works in the command-line client...are you sure you don't have the user variable defined on the connection's session already (notice the following warning from the docs at http://dev.mysql.com/doc/mysql/en/Variables.html "The general rule is to never assign and use the same variable in the same statement.")?

mysql> SELECT @location:=4, field2 FROM testBug5873 WHERE testBug5873.field1 BET
WEEN @location AND 8;
Empty set (0.00 sec)

mysql> select * from testBug5873;
+--------+---------+
| field1 | field2  |
+--------+---------+
|      5 | abcdefg |
+--------+---------+
1 row in set (0.01 sec)

mysql> SELECT @location:=4, field2 FROM testBug5873 WHERE testBug5873.field1 BET
WEEN @location AND 8;
+--------------+---------+
| @location:=4 | field2  |
+--------------+---------+
|            4 | abcdefg |
+--------------+---------+
1 row in set (0.00 sec)