MySQL Bugs: #47514: User Defined Variables documentation contradiction

Bug #47514	User Defined Variables documentation contradiction
Submitted:	22 Sep 2009 11:31	Modified:	24 Sep 2009 13:15
Reporter:	Shlomi Noach (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0, 5.1	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	session variables, user defined variables, user variables

Description:
The documentation at:
http://dev.mysql.com/doc/refman/5.0/en/user-variables.html

Specifies that:
"...The general rule is never to assign a value to a user variable in one part of a statement and use the same variable in some other part of the same statement. You might get the results you expect, but this is not guaranteed..."

Yet also writes the following as an example of usage and return value:
mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

The example clearly contradicts the statement above, since it reads and assigns variables in different parts of the statement. Hence, the result set could be different than described.

How to repeat:
[Dcoumentation bug - nothing to repeat]

Suggested fix:
Replace the example with one which does not contradict the text.

I do not think the example is wrong - rather the text is too loose with the term 'in the same statement'. 

SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;

would be equal to (due to COMMA's in the statement)

SELECT @t1:=(@t2:=1)+@t3:=4;
SELECT @t1;
SELECT @t2;
SELECT @t3;

.. in my understanding

Peter,

not so :)
Perhaps the example itself is overly simplified, since it uses no tables. Your interpretation cannot take place when I select from a multi-row table:
  SELECT @a := host, @a FROM mysql.user
You cannot break this into distinct SELECT statements.
Besides, the text explicitly describes that the distinction you've made is incorrect, since the order of SELECTs cannot be determined in advance.

Regards

Thank you for the report.

Example you provided is not related.

Better could be (depends from what you expect) is:

select @a=@a+1, @b:=@b+1;

It is possible to add such example into user manual, but I am afraid it can confuse even more.

Hi Sveta,

The example provided is exactly related:
instead of:

mysql> SET @t1=0, @t2=0, @t3=0;
mysql> SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+

the output may well be:
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    0 |    0 |    0 |
+----------------------+------+------+------+
because, according to documentation, @t1 may first be evaluated, then @t2, then @t3, and then @t1:=(@t2:=1)+@t3:=4.
This is exactly what the documentation discusses: that the order of evaluation is undefined.

Regards

Shlomi,

thank you for the feedback. But with current version output is same as in manual.

$mysql51 test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1280
Server version: 5.1.40-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET @t1=0, @t2=0, @t3=0;
Query OK, 0 rows affected (0.08 sec)

mysql>  SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
+----------------------+------+------+------+
| @t1:=(@t2:=1)+@t3:=4 | @t1  | @t2  | @t3  |
+----------------------+------+------+------+
|                    5 |    5 |    1 |    4 |
+----------------------+------+------+------+
1 row in set (0.13 sec)

So I still don't see what is problem here and how it can be improved.

verified that the manual contains an example of a query that it says one should never use.

Sveta,

Yes, indeed, the output is as documented; but the document clearly states that this is unreliable. I'm saying you shouldn't given an example which is later described as unreliable.
The example must change.

Shlomi

Shlomi,

thank you for the feedback. Makes sense.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Simpler example:

mysql> SET @t1=1, @t2=2, @t3:=4; 
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;  
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 |
+------+------+------+--------------------+