MySQL assign user-defined variable in where clause

Question

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

1000111 · Accepted Answer · 2016-11-08T09:44:11.083

0

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.

edited Nov 08 '16 at 09:44

answered Nov 08 '16 at 09:37

1000111

13,169
2
28
37

Same explanation (your previous last query) holds for your updated last query. `ORDER BY name` has no effect since your result set contains only one row. – 1000111 Nov 08 '16 at 11:23
I am sorry, I would like to ask, why `@num` in result is `4` – consatan Nov 08 '16 at 12:40
an iteration is equivalent to scanning a row. every time your query scans a row the `@num` variable is incremented. since u have 4 rows your query scans 4 rows. Thus scanning each row `@num` is incremented by 1. and after executing the query `@num` holds 4 as value for this sample data – 1000111 Nov 08 '16 at 14:55
So in 2nd query, the `select clause`'s `@num` column read value from every `where clause`. But in 3rd query, it must waitting for `order clause`, and `order clause` must waitting for `where clause` to filtered record, so the final `@num` value is `4`, is that right? – consatan Nov 08 '16 at 15:17

MySQL assign user-defined variable in where clause

1 Answers1

Linked