MySQL query returns wrong results. DataGrip caching?

Question

A few weeks ago I posted about a problem I’ve been having with MySQL not returning the correct results when my queries / triggers run.

I think DataGrip is applying some kind of caching, or something that acts like caching, to my user variables.

Update:

Can anyone explain the results of these two queries?

Query 1: Returns the correct results when the conditions match. It returns nothing when the conditions don’t match, which is still correct.

Query 2: Returns the correct results inside the user variable when the conditions match, but when the conditions don’t match, it still returns the results from the previous match. Closing the connection, and opening a new one fixes the problem.

Why is @results1 not blank when the query doesn’t have a match?? It’s driving me nuts!

— Query1

SELECT results
from tb1 where
'1' = tb1.condition1 and
'2' = tb1.condition2;

— Query2

SELECT results
into
@results1
from tb1 where
'1' = tb1.condition1 and
'2' = tb1.condition2;

SELECT  @results1;

DataGrip video: https://filebin.net/i4b3azha59ckt3gh

Answer 1

That the way MySQL works with SQL parameters wihtin the same session. So, the second result set is null (empty result set) and MySQL doesn't rewrite it's value.

For proper work you need to reinitialize SQL variables, e.g. by setting them to null or by closing sessions explicitly.

Here is the way I did it:

set @results1 = null;
set @results2 = null;