CODEWITHSUNDEEP
mysqlsqlgo
Mohammed Gadiwala
Mohammed Gadiwala

Reputation: 53

SQL variable not updating in the select query in go client

I am running the following queries in SQL.

SET @thisid=0;
SET @serial=0;

SELECT @serial := IF((@thisid != `places`.`id`), @serial + 1, @serial) as `serial`, @thisid := `places`.`id`, `places`.`id` FROM `places`;

The variable @serial basically increments only if the new id is not the same as the last one. Upon running these queries in the terminal and printing the values of @serial and @thisid, the value received is @thisid='id6' @serial=6.

I executed this query in my go code:

if _, err = repo.db.ExecContext(ctx, "SET @thisid=0;"); err != nil {
    return
}

if _, err = repo.db.ExecContext(ctx, "SET @serial=0;"); err != nil {
    return
}

rows, err = repo.db.QueryContext(ctx, fmt.Sprintf(
    "SELECT @serial := IF((@thisid != `places`.`id`), @serial + 1, @serial) as `serial`, @thisid := `places`.`id`, `places`.`id` FROM `places`;",
))
if err != nil {
    fmt.Println("error here")
    return
}

if err = repo.db.QueryRow("SELECT @serial").Scan(&that); err != nil {
    return
}

if err = repo.db.QueryRow("SELECT @thisid").Scan(&this); err != nil {
    return
}

Upon printing the values of @thisid and @serial, the value of @thisid is received the same where the value of @serial is received as 0. It doesn't seem to update dynamically.

Upvotes: 0

Views: 372

Answers (2)

Jonathan Hall
Jonathan Hall

Reputation: 79794

Go uses a connection pool, which means each query may happen on a different connection. variables like that are scoped to the connection. If you need them to last between queries, you need to use a transaction to ensure you stay within the same connection.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Your query is really arbitrary. MySQL does not guarantee the order of evaluation of expressions in a select. Nor does it guarantee the ordering of the result set.

So, I think you want:

select p.*,
       (@rn := if(@id = id, @rn + 1,
                  if(@id := id, 1, 1)
                 )
       ) as serial
from (select p.*
      from places p
      order by p.id
     ) p cross join
     (select @id := 0, @rn := 0) params;

Upvotes: 1

Related Questions