hello everybody. did anybody have problems with user-defined variables in mysql 3.23.8? i seem to get some weird results..

here are these three queries that are identical except for the HAVING clause: first one doesnt have one, the second has 'HAVING u=1' but produces only a line with u=0, and the last has 'HAVING u=0' but produces unexpected output too. acutally, here u is an alias for a column (and also a variable), but i think the problem is related to user-defined variables..

mysql> SELECT id, @l0:=LOCATE('-0016+', ingr) as l0, @l1:=LOCATE('-0041+', ingr) as l1, @u:=(SUBSTRING(ingr, @l0+12, 3)=5)+(SUBSTRING(ingr, @l1+12, 3)=4) as u from retete; // no HAVING clause +----+----+-----+------+ | id | l0 | l1 | u | +----+----+-----+------+ | 1 | 0 | 196 | 0 | | 2 | 0 | 0 | 0 | | 3 | 0 | 0 | 0 | | 4 | 0 | 0 | 0 | | 5 | 76 | 0 | 0 | | 6 | 0 | 0 | 0 | | 7 | 0 | 0 | 0 | | 8 | 0 | 61 | 1 | | 9 | 0 | 0 | 0 | | 10 | 0 | 0 | 0 | | 11 | 0 | 61 | 0 | +----+----+-----+------+ 11 rows in set (0.00 sec)

mysql> SELECT id, @l0:=LOCATE('-0016+', ingr) as l0, @l1:=LOCATE('-0041+', ingr) as l1, @u:=(SUBSTRING(ingr, @l0+12, 3)=5)+(SUBSTRING(ingr, @l1+12, 3)=4) as u from retete HAVING u=1; +----+----+----+------+ | id | l0 | l1 | u | +----+----+----+------+ | 5 | 76 | 0 | 0 | +----+----+----+------+ 1 row in set (0.01 sec)

mysql> SELECT id, @l0:=LOCATE('-0016+', ingr) as l0, @l1:=LOCATE('-0041+', ingr) as l1, @u:=(SUBSTRING(ingr, @l0+12, 3)=5)+(SUBSTRING(ingr, @l1+12, 3)=4) as u from retete HAVING u=0; +----+----+-----+------+ | id | l0 | l1 | u | +----+----+-----+------+ | 1 | 0 | 196 | 0 | | 2 | 0 | 0 | 0 | | 3 | 0 | 0 | 0 | | 4 | 0 | 0 | 0 | | 5 | 76 | 0 | 0 | | 6 | 0 | 0 | 0 | | 7 | 0 | 0 | 0 | | 8 | 0 | 61 | 1 | | 10 | 0 | 0 | 0 | | 11 | 0 | 61 | 0 | +----+----+-----+------+ 10 rows in set (0.01 sec)