Getting an error while elasticsearch variable in my mysql query

I have an elasticsearch variable which I have included in MySQL query. But the MySQL query is failing in case the variable is empty. I am getting the following error

You have an error in your SQL syntax; check the manual that corresponds to your Mysql server version for the right syntax to use near ‘))’ at line 1

How can we fix this issue? Any suggestions should be helpful

please post what you see when you click Query Inspector?

And the query you are using

select count(name) from users where (name IN (‘Mathew’,‘Alysa’) AND name NOT IN ()) is what coming in Query inspector.

My actual query is

select count(name) from users where (name IN ($users) AND name NOT IN ($unused_users))

1 Like

Try a length check or null check specific to the sql language you use before using that variable like

(Isnull($unused_users) or len($unused_users) = 0)
 or name NOT IN ($unused_users))

@yosiasz Now I am getting the following in the query inspector.

NOT IN (Isnull() or len() = 0) or name NOT IN ()))

1 Like

yuck, then try this

if Isnull($unused_users) or len($unused_users) = 0
  begin
     select count(name) 
       from users 
     where (name IN ($users) )
  end
else
  begin
     select count(name) 
       from users 
     where (name IN ($users) AND name NOT IN ($unused_users))
  end

or whatever it is for your mysql sql query language.
your question is mysql and not grafana, so for better expertise post ina mysql forum.

@yosiasz I posted in grafana since the variable does not return a null or empty string when nothing matches. It just returns an empty which will cause an issue in MySQL or any data storage we are using.

1 Like

what does mean it just returns an empty? empty what?

@yosiasz It doesn’t return anything. If we check the query. ISNULL itself will give syntax error as nothing in that.

NOT IN (Isnull() or len() = 0) or name NOT IN ()))

So you need to have it return something when there is nothing cause grafana cant do anything when there is nothing :laughing: