patternsqlMinor
MySQL 8 - user variables within expressions is deprecated [UDF calls with lot of parameters]
Viewed 0 times
callswithuserexpressionsdeprecatedwithinlotmysqlvariablesparameters
Problem
I have this:
As you can see it's quite complicated and
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
I could solve it like this:
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
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 `@foSolution
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.