In Mysql, user variables are taking string values when declared inside select statement.

I have a table named 'a' with one column named 'amount' as shown below

Consider that I want to display null for amount values that are repeating in this table in the inserted order.

I've written the below query to achieve the same :

select 
    CASE
         WHEN (@x != amount) THEN amount 
    END result, 
    CASE 
         WHEN ((@x) != amount) THEN @x:=amount 
    END dummy 
from (select amount, @x:=0 x from a) q;

I'm creating a variable named '@x' in innermost query with default value as 0. When processing each row, I'm taking that row's 'amount' value and setting in the variable. Hence, in next row, I'll use the variable to hold previous row's data.

Using current and previous amount values, I'll only show the data that differ.

problem :

When I execute the query, I got the below output :

This is wrong as only 3rd row must have NULL values. 5th and 6th rows must have values. But, When I execute the query again, I got the below output :

This is the correct result. When subsequently executing the query, it always works fine. Only when it is executed for the first time after connecting to DB, this issue occurs.

Debugging : I've tried setting @x's value before executing the query and it works fine. But, I want to know why it wouldn't work if I set it inside the select query.

I tried printing @x's values in the query and the only difference between the first query result and subsequent query results is alignment of @x's values. I think @x is considered as a string (as it is left aligned) in the first query. Hence, it breaks when comparing with certain bigint values.

When checking the documentation, it is mentioned that referring to a variable that has not been initialized will be taken as 'string'

If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

I believe this is what's happening here. After first query, the variable probably got declared with 'integer' type which made it work in subsequent queryies.

Is it not possible to provide the variable's datatype inside select statement? Kindly advise.

CodePudding user response：

No, to specify the type of your user defined variable you MUST declare it outside of the SELECT statement.

As you have already pointed out the MySQL documentation specifically mentions that you MUST declare the variable first:

9.4 User-Defined Variables
If you refer to a variable that has not been initialized, it has a value of NULL and a type of string.

Beginning with MySQL 8.0.22, a reference to a user variable in a prepared statement has its type determined when the statement is first prepared, and retains this type each time the statement is executed thereafter. Similarly, the type of a user variable employed in a statement within a stored procedure is determined the first time the stored procedure is invoked, and retains this type with each subsequent invocation.

This is very clear, either you formally declare the variable first, or it will be a string that is initialized with a value of NULL.

So just declare the variable first, you can declare variables within your inline SQL scripts, so don't try to fight it, either declare your variable first, or modify your query to use the variable as a string, either by casting amount to a string in the inner most query or cast @x to your numeric type in the comparison:

select 
    CASE
         WHEN (CAST(@x as SIGNED) != amount) THEN amount 
    END result, 
    CASE 
         WHEN (CAST(@x as SIGNED) != amount) THEN @x:=amount 
    END dummy 
from (select amount, @x:=0 x from a) q;