User-defined variable

Bug reports
1

Hi,

When using a request like:
select
my_var:=avg(salary), my_var+100 as my_test
from salaries
where {{country}}

First time I hit Get results button, my_test is empty
If I Refresh, then my_test has the proper value
If I change country, my_test doesn’t change, if I change country again, my_test changes to take the value related to the previous country.

So it seems like my_test has always the value of the previous calculation.

Any idea? Is it a bug?

Thanks.

2

Hi @fvafva

Which version of Metabase and which database do you connect to?

Is country a required variable? You’re also missing a { but that was probably a mistype?

You can check in your browser console to see the actual query being sent to the database.
Go to Network tab, run the query again to make the POST request show, select the request, go to Response sub-tab, find data>native_form>query

3

Hi @flamber,

Metabase version v0.31.2.
And there is no typo… (after I edited it :wink: ).
country can be required or not, I tested both, same result.

From the browser console, I can see the following, looks good:
SELECT ↵ @my_var := AVG(salary), @my_var+5.0↵ ↵↵↵FROM salaries ↵↵↵WHERE 1 = 1

4

@fvafva
I’m setting system variables and doing a lot of other really bad stuff in some of my queries.
I’m using SQL 2008 - what database are you connecting to?

5

We are using MySQL (Percona 5.6 to be precise).
@flamber Do you manage to have user-defined sql variables working well? Like in the example I wrote?

6

@fvafva

The MySQL manual says something interesting:

The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

In addition, the default result type of a variable is based on its type at the beginning of the statement. This may have unintended effects if a variable holds a value of one type at the beginning of a statement in which it is also assigned a new value of a different type.

To avoid problems with this behavior, either do not assign a value to and read the value of the same variable within a single statement, or else set the variable to 0, 0.0, or '' to define its type before you use it.

https://dev.mysql.com/doc/refman/8.0/en/user-variables.html

7

wouldn’t it be simpler to just use a subquery? SQL is a set based languague, this idea of using variables just seems ‘wrong’.

select av, av+5.0 from (
SELECT AVG(salary) as av
FROM salaries ) a
8

Thanks @flamber for that search you did. That probably explains the behavior.
And thanks @AndrewMBaines, your solution looks… obvious :wink: I will try to implement this.

1 Like
9

How obvious it looks just depends upon where your background. I do a lot of work with SQL, so everything looks like a SQL problem to me :grin: