consatan consatan - 26 days ago 13
MySQL Question

MySQL assign user-defined variable in where clause

The 1st query sql will converted to:

SELECT name,(@num)
FROM test WHERE 1


In select clause, the
(@num : @num + 1)
return 1, so is mean the last query sql equal to:

SELECT name,(@num)
FROM test WHERE 1 <= 1


?

If yes, why the 2nd query only return first record?

If no, what is
(@num := @num + 1)
in
WHERE
clause?

Why
@num
in 3rd query is
4
?

CREATE TABLE test (
id int(1),
name varchar(10)
);

INSERT INTO test (id, name) VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd');

SET @num := 0;
SELECT name, @num FROM test WHERE (@num := 1) <= 1;
-- return all records.

SET @num := 0;
SELECT name, @num FROM test WHERE (@num := @num + 1) <= 1;
-- return first record.

SET @num := 0;
SELECT name, @num FROM test WHERE (@num := @num + 1) <= 1 ORDER BY name;
-- return 1 record, name = a, @num = 4

Answer

Case 1st Query:

The first query resolves into the following equivalent query:

SET @num := 0;
SELECT name, @num FROM test WHERE (@num := 1) <= 1;
                V
SET @num := 0;
SELECT name, @num FROM test WHERE 1 <= 1;
                V
SET @num := 0;
SELECT name, @num FROM test WHERE TRUE;

So, you will get all the records. In every iteration 1 is assigned to @num. So, @num doesn't get change.

Case 2nd Query:

In case of the second query in the first iteration it resolves into the above query.

1st Iteration:

SELECT name, @num FROM test WHERE (@num := @num + 1) <= 1;
                     V
@num is 1
SELECT name, @num FROM test WHERE 1 <= 1;

2nd Iteration:

@num is 2 
SELECT name, @num FROM test WHERE 2 <= 1;

3rd Iteration:

@num is 3    
SELECT name, @num FROM test WHERE 3 <= 1;
........
....
and so on

Look here only the first iteration qualifies the condition in the where clause. Others get dropped.

Comments