Variable not loading right away

I have quite a comlicated SQL including internal variables:

              @MSR_rank := IF(@current_to_place = g1.to_place, @MSR_rank + 1, 1) AS MSR_rank,
              @current_to_place := g1.to_place 

which is included in left join (select…)

The problem is that when the chart is loaded, it seems to ignore the variable. But after one (sometimes 2-3) reloads of the page, it catches up and displays properly.

Does anybody have any idea why this unexpected behaviour? Is it a bug?

Hi @ivobrabec
Post “Diagnostic Info” from Admin > Troubleshooting, and which database you are querying.
Could it be that you’re using multiple statements? That’s currently no supported very well: - upvote by clicking :+1: on the first post

Yes, definitely I have multiple statements in the SQL (the SQL is selecting top X results from group by and counting the reminder as ‘other’).
Nevertheless, evidently Metabase is able to handle it, I just don’t understand why I need to reload before it kicks in.
The database info is:
“browser-info”: {
“language”: “en-US”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 11_2_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/89.0.4389.82 Safari/537.36”,
“vendor”: “Google Inc.”
“system-info”: {
“file.encoding”: “UTF-8”,
“”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.10+9”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “”,
“java.version”: “11.0.10”,
“”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.10+9”,
“”: “Linux”,
“os.version”: “4.15.0-135-generic”,
“user.language”: “en”,
“user.timezone”: “GMT”
“metabase-info”: {
“databases”: [
“hosting-env”: “unknown”,
“application-database”: “mysql”,
“application-database-details”: {
“database”: {
“name”: “MySQL”,
“version”: “5.7.33”
“jdbc-driver”: {
“name”: “MariaDB Connector/J”,
“version”: “2.6.2”
“run-mode”: “prod”,
“version”: {
“date”: “2021-03-17”,
“tag”: “v0.38.2”,
“branch”: “release-x.38.x”,
“hash”: “91f0ed6”
“settings”: {
“report-timezone”: “Europe/Brussels”

@ivobrabec There are many different ways to write a query, but it doesn’t sound like you are using multiple statements.
Try doing EXPLAIN ANALYZE or enable debugging logs on MySQL to see why it behaves like that.

Thanks again, unfortunately my database is not upgrade to run analyze and explain does not give me much info. But the issue is that when I run the sql on mysql directly, this issue does not happen. Its metabase only which presents different results in appx. 50% cases. Is there a way to debug such behaviour?

@ivobrabec Not without debugging what the database does, since Metabase sends the query to the database. Otherwise create a function or view on the database and just call that from Metabase.

OK thanks. Just to clarify, I cannot use a mysql procedure in metabase, right? Since I cannot use multiple statements in one chart, correct? (building the SQL with a function is a challenge…)

@ivobrabec You can use (call) a procedure in Metabase, but you cannot create and call, since that would be multiple statements.