MySQL Deprecation – User Variables in Expressions Deprecated in MySQL 8

deprecationMySQL

I have this:

SELECT
   @foo1 := UDF1(0, a, b, c, d) AS Foo1,
   @foo2 := UDF1(1, a, b, c, d) AS Foo2,
   @foo3 := UDF1(2, a, b, c, d) AS Foo3,
   @foo4 := UDF1(3, a, b, c, d) AS Foo4,
   @foo5 := UDF2( @foo1, @foo2, @foo3, @foo4) AS Foo5,
   @foo6 := UDF3( @foo1, @foo2, @foo3, @foo4) AS Foo6,
   @foo8 := UDF4( @foo5, @foo7, x, y, z) AS Foo8
FROM MyTable;

As you can see it's quite complicated and a, b, c, d, x, y and z are field names which are quite long [The names express their functionality].

I receive now this error message on MySQL 8.0.20:

X Setting user variables within expressions is deprecated and will be
removed in a future release. Consider alternatives: 'SET
variable=expression, …', or 'SELECT expression(s) INTO
variables(s)'.

OK, this is the wrong place to discuss if it makes sense that @var := value is deprecated, so I have to move on and I want to assure that the program doesn't stop working if the next MySQL update is installed.

I could solve it like this:

SELECT
   UDF1(0, a, b, c, d) AS Foo1,
   UDF1(1, a, b, c, d) AS Foo2,
   UDF1(2, a, b, c, d) AS Foo3,
   UDF1(3, a, b, c, d) AS Foo4,
   UDF2(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d)) AS Foo5,
   UDF3(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d) ) AS Foo5,
   UDF3(UDF2(UDF1(0, a, b, c, d) , UDF1(1, a, b, c, d) , UDF1(2, a, b, c, d) , UDF1(3, a, b, c, d) ) ,
        UDF3(UDF1(0, a, b, c, d), UDF1(1, a, b, c, d), UDF1(2, a, b, c, d), UDF1(3, a, b, c, d) ), x, y, z) AS Foo6
FROM MyTable;

Honestly, doesn't this hurt and, what I find worst, it becomes so unreadable and changing any call I have to maintain many times –> buggy.

Also, in the current version the length of the SELECT grows from 2'334 bytes to 3'504 bytes.


I am trying to work with a temporary table but to fill the table is a quite long and [useless] complicated SELECT using LEFT JOIN as @foo5 depends on @foo1-4 and @foo6 depends on @foo5.

This works, but I am wondering if there may be another solution I am not capable to see. The suggested SELECT expression(s) INTO variables(s) I don't understand how this should help in my case.

As I wrote in the beginning, I don't understand why this feature is deprecated as it apparently can solve lot of troubles and makes complicated SELECT statements simpler…

Any suggestions?

Best Answer

WITH 
cte1 AS ( SELECT UDF1(0, a, b, c, d) AS Foo1,
                 UDF1(1, a, b, c, d) AS Foo2,
                 UDF1(2, a, b, c, d) AS Foo3,
                 UDF1(3, a, b, c, d) AS Foo4,
                 x, y, z
          FROM MyTable ),
cte2 AS ( SELECT Foo1, Foo2, Foo3, Foo4, x, y, z,
                 UDF2(Foo1, Foo2, Foo3, Foo4) AS Foo5,
                 UDF3(Foo1, Foo2, Foo3, Foo4) AS Foo6
          FROM cte1 ),
SELECT Foo1, Foo2, Foo3, Foo4, Foo5, Foo6,
       UDF4(Foo5, Foo6, x, y, z ) AS Foo8
FROM cte2;