How does this MySQL statement with assignment and auto-increment work?

Question

I used this code for assign and autoincrement starting from 1 (Serial No.). But actually don't know how it works and what are it's benefits.

    select cast((@cnt := @cnt + 1) as char(10)) as sno, temp1.*
    from
    (
        ..............
    )temp1, (SELECT @cnt := 0) AS dummy

Answer 1

select cast((@cnt := @cnt + 1) as char(10)) as sno, temp1.*
from
(
    ..............
)temp1, (SELECT @cnt := 0) AS dummy

Is the same as:

select cast((@cnt := @cnt + 1) as char(10)) as sno, temp1.*
    from
    (
        ..............
    )temp1
    CROSS JOIN (SELECT @cnt := 0) AS dummy

& please note that the FROM CLAUSE is performed BEFORE the SELECT

So what happens is that a virtual column as a placeholder for @cnt is added to each row of the other table(s). Then in the select clause, @cnt is incremented by 1 for each row in the resultset.

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE Temp1
    (`id` int, `productName` varchar(7), `description` varchar(55))
;

INSERT INTO Temp1
    (`id`, `productName`, `description`)
VALUES
    (11, 'OpenIDM', 'Platform for building enterprise provisioning solutions'),
    (322, 'OpenAM', 'Full-featured access management'),
    (4763, 'OpenDJ', 'Robust LDAP server for Java')
;

Query 1:

select *
from temp1
CROSS JOIN (SELECT @cnt := 0) AS dummy

Results:

|   id | productName |                                             description | @cnt := 0 |
|------|-------------|---------------------------------------------------------|-----------|
|   11 |     OpenIDM | Platform for building enterprise provisioning solutions |         0 |
|  322 |      OpenAM |                         Full-featured access management |         0 |
| 4763 |      OpenDJ |                             Robust LDAP server for Java |         0 |

Query 2:

select cast((@cnt := @cnt + 1) as char(10)) as sno, temp1.*
from temp1
CROSS JOIN (SELECT @cnt := 0) AS dummy

Results:

| sno |   id | productName |                                             description |
|-----|------|-------------|---------------------------------------------------------|
|   1 |   11 |     OpenIDM | Platform for building enterprise provisioning solutions |
|   2 |  322 |      OpenAM |                         Full-featured access management |
|   3 | 4763 |      OpenDJ |                             Robust LDAP server for Java |