I get NULL on a column while creating a view - Using a function to populate the NULL column

Question

EDIT: Please check below for my edit

Following a question I've previously made here, link, I decided to make a variable and a function to return that variable and use the function on the create view statement.

So, I start the variable as zero:

SET @auxvar := 0;

And then I declare the function:

create function auxfunc() 
returns INTEGER DETERMINISTIC NO SQL return @auxvar+1;

Then I do:

CREATE OR REPLACE VIEW vwaux (mac, ip, num_row) 
AS SELECT testing.mac, testing.ip, auxfunc() AS row_number

FROM testing

order by ip;

But the generated view has the following data:

|    mac   |      ip      | row_number |
----------------------------------------
| s23Ssad2 | 192.168.1.1  |   NULL     |
| sd57shgd | 192.168.1.88 |   NULL     |
| adfsfy65 | 192.168.1.91 |   NULL     |
| at56ss34 | 192.168.1.92 |   NULL     |

And so on... but I was expecting to be 1, 2, 3, 4 ... where it has NULL. Any idea on how to fix this NULL?

EDITED PART: ------------------------------

I've managed to get ride of the NULL but still not to the point I need to go. I changed the function to accept an input parameter and inside the function increment it by one unit.

Here's the new function:

CREATE FUNCTION auxfunc(param INT) RETURNS int(11)
    NO SQL
    DETERMINISTIC
begin
set param = param +1;
return param;
end

And then I create the view as:

CREATE OR REPLACE VIEW vwaux (mac, ip, num_row) 
AS SELECT testing.mac, testing.ip, auxfunc(0) AS row_number

FROM testing

order by ip;

But instead of NULL I get '1'. Like this:

|    mac   |      ip      | row_number |
----------------------------------------
| s23Ssad2 | 192.168.1.1  |   1        |
| sd57shgd | 192.168.1.88 |   1        |
| adfsfy65 | 192.168.1.91 |   1        |
| at56ss34 | 192.168.1.92 |   1        |

Still not 1, 2, 3, 4, 5, 6...

Answer 1

mysql functions do not take parameters by reference. Your local function cannot change the global value of a variable. You might instead define a temporary table with row_number as an auto_increment field and select your query into that table:

drop table if exists __temp;
create table __temp (mac text, ip text, row_number integer primary key auto_increment);
insert into __temp (mac, ip) select testing.mac, testing.ip;
select * from __temp;