MySQL Bugs: #68310: Unexpected behaviour with variables in where clause

Bug #68310	Unexpected behaviour with variables in where clause
Submitted:	7 Feb 2013 21:56	Modified:	8 Feb 2013 10:12
Reporter:	alpha beta	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	5.5.28	OS:	Linux (ubuntu/debian 64bit)
Assigned to:		CPU Architecture:	Any
Tags:	session variables, where clause

Description:
Let we have a table like that:

word,hash:

...
vicespeaker, fgeuw5890
vicepresident, asjfkl34
Vitzin, gfasw1p2
vicecounsil, q3i4pfas
viceanin, air9020
...

In next three cases I expect values of @a and @b

For beginning:
SET @a=0;
SET @b=0;

Case #1: -- -------------

SELECT *
FROM
`words`
WHERE
(`word` LIKE "vice%" AND @a:=@a+1) OR (`hash`='q3i4pfas' AND @b:=@b+1)

Case 2: -- ---------

SELECT *
FROM
`words`
WHERE
(`word`="vicecounsil" AND @a:=`word`) OR (`hash`='q3i4pfas' AND @b:=`hash`)

Result:
case 1: @a=5,@b=1; -- OK
case 2: @a="vicecounsil",@b="q3i4pfas" -- WTF?

Why do condition at the right of OR evaluates if left it true?

Next one case 3: make a mistake at left part

SELECT *
FROM
`words`
WHERE
(`word`="mistaken123" AND @a:=`word`) OR (`hash`='q3i4pfas' AND @b:=`hash`)

Result is satisfied: @a=0,@b="q3i4pfas".  -- OK

How to repeat:
Run next SQLs:
-- ---- definition

CREATE TABLE `words` (`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`word` VARCHAR(255) NOT NULL,
`hash` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=MyISAM;

INSERT INTO `words` (`word`,`hash`) VALUES
('vicespeaker', 'fgeuw5890'),
('vicepresident', 'asjfkl34'),
('Vitzin', 'gfasw1p2'),
('vicecounsil', 'q3i4pfas'),
('viceanin', 'air9020');

-- ----------- run next cases

SET @a=0;
SET @b=0;

SELECT *
FROM
`words`
WHERE
(`word` LIKE "vice%" AND @a:=@a+1) OR (`hash`='q3i4pfas' AND @b:=@b+1);

SELECT @a,@b;
-- ----------- see result @a,@b

SET @a=0;
SET @b=0;

SELECT *
FROM
`words`
WHERE
(`word`="vicecounsil" AND @a:=`word`) OR (`hash`='q3i4pfas' AND @b:=`hash`);

SELECT @a,@b;
-- ----------- see result @a,@b

SET @a=0;
SET @b=0;

SELECT *
FROM
`words`
WHERE
(`word`="mistaken123" AND @a:=`word`) OR (`hash`='q3i4pfas' AND @b:=`hash`);
-- ----------- see result @a,@b

Suggested fix:
I don't know. Make it be correct in case #2.

In case#1 @a=4, not @a=5. But it does not matter, my typo.

Yep, it is not bug! It is correct behavior when doing convertion string to integer.
The same as i do it explicit:
CAST((@a="some string") AS SIGNED) = 0

So right part around AND evaluates to 0 and ever get FALSE result

> Why do condition at the right of OR evaluates if left it true?

Because the left term isn't true, @a is evaluated first, and is still 0, only then it gets the new value assigned ... same for @b on the right side

that query actually doesn't return any result rows, right?

Simple test: 

mysql> select 1 and @a:='foo';
+-----------------+
| 1 and @a:='foo' |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

correcting myself, assignment comes before evaluation, but the real problem is that you are using the variables in a boolean context, so the assigned non-numeric string value gets cast to integer 0 and then to boolean false, making the full AND expression false on both sides:

mysql> select 1 and @a:='0';
+---------------+
| 1 and @a:='0' |
+---------------+
|             0 |
+---------------+
1 row in set (0.00 sec)

mysql> select 1 and @a:='1abc';
+---------------+
| 1 and @a:='1' |
+---------------+
|             1 |
+---------------+
1 row in set (0.00 sec)