Question about differences in user variable usage between mysql 5.6 and mysql 8.0

Question

I'm trying to upgrade mysql version from 5.6 to 8.0

Some legacy SQL statements contain user defined variables.

my problem is that the result of below query is diffrent between two versions. (it's summary of problem)

SELECT @t 
  FROM ( SELECT @t:=0 ) T
 WHERE @t IS NOT NULL

In the case of version 5.6,
0 is displayed on the result table.

But, on version 8.0 there is no row in the result table.
It look like @t is still NULL(@t is not defined) in the WHERE clause.
I wonder why @t is not defined and assigned in the sub-query in the FROM clause.

Does anyone know the reason?

Answer 1

This happens when the derived table is not materialized first and probably an effect of the optimizer using derived condition pushdown. It basically rewrites your query to

SELECT @t:=0 WHERE @t IS NOT NULL

which more obviously returns no rows (if @t is null to begin with), as the where condition finds no rows, so the setting-part will never be executed.

What can you do?

• You can disable that optimization using optimizer hints, e.g. try

  SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ @t  
  FROM ( SELECT @t:=0 ) T
  WHERE @t IS NOT NULL
  

• You can disable it altogether using the optimizer_switch configuration and setting derived_condition_pushdown=off. This may be useful if you use this a lot and don't want to adjust every query, but will of course also disable that optimization for queries that might profit from it.

• You can initialize the variable before you run that query, e.g. run set @t := 0 first. Will not be applicable in every situation though.

• You can force MySQL to materialize the derived table. This should always work, even if the actual optimization that causes the effect isn't the derived condition pushdown. A simple way to materialize it is to add an arbitrary limit-clause, e.g.

  SELECT @t  
  FROM ( SELECT @t:=0 limit 10000000 ) T
  WHERE @t IS NOT NULL
  

Generally though, be aware that this use of variables (e.g. setting them inside the query) is deprecated (among other reasons also due to effects like in your problem) and will not work anymore in some future version of MySQL. You can usually rewrite those queries using either window functions or recursive ctes, see e.g. this question for an example.