HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

MySQL 8 - user variables within expressions is deprecated [UDF calls with lot of parameters]

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
callswithuserexpressionsdeprecatedwithinlotmysqlvariablesparameters

Problem

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 `@fo

Solution

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;

Context

StackExchange Database Administrators Q#268148, answer score: 2

Revisions (0)

No revisions yet.