First row error with @rownum mysql

Question

I am trying to generate rownum for a mysql query. This is my code.

select @rownum:=@rownum+1 rowno, (`plgap_nos`.`no_id` - 1) AS `no_id`
from `plgap_nos`,
     (SELECT @rownum:=0) r
limit 0, 10

While everything works fine, the rownum of first row shoes 1621 and not 0.

It tried with a differnt code as below. still same error persists.

select COALESCE(@rownum:=@rownum+1,0) rank,
       (`cnxnifty_plgap_nos`.`no_id` - 1) AS `no_id`
from `money_database`.`cnxnifty_plgap_nos`,
     (SELECT @rownum:=0) r
limit 0, 10

It seems i am committing some silly error which i am not being able to figure. Can anyone help.

Additional info- this is how the data looks-

Rank     no_id
1621     0
1           7
2           18
3           21
4           33
5           37
6           45

Answer 1

I just want to be clear. The following code exhibits the same problem?

select (@rn := @rn + 1) as rowno, (p.no_id - 1) AS no_id
from plgap_nos p cross join
     (SELECT @rn := 0) params
order by p.no_id
limit 0, 10;

The variable assignment in the select should be executed after the from initialization. I have not seen MySQL do initializations in the wrong order (which would be one reason for the behavior you are seeing).

The changes I made to the query (cross join instead of comma, order by, and table alias) should not affect the result set. However, the scoping semantics of cross join are different from the ,, so there is a small, small possibility that the cross join might end up fixing the problem.