2

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?

3

2 Answers 2

1
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;
7
  • I didn't mention I have lot of WHERE conditions in the SELECT. Do I have to repeat them in each 'WITH ... SELECT as well, e.q. for cte1 and cte2? May 29, 2020 at 21:34
  • @PeterVARGA What is the point of asking one question when you need an answer to a completely different question?
    – Akina
    May 30, 2020 at 10:48
  • I don't consider it as a completely different question. The keyword is MCVE. As I don't know the answer/solution [otherwise I wouldn't ask here], I started with the smallest code version. Reading your answer I asked you regarding the WHERE and you are now insulted? It's a progress here. Somebody asks a questions, then he gets replies, because of this the OP realizes he should provide more information and so on until - hopefully - a good question/solution is presented by the community. May 30, 2020 at 10:51
  • And, in my very first version of the question I wrote the original query is >2k bytes long, so you can imagine how dramatically I must have reduced it in order to make it readable. I still don't believe it makes sense to post here the original SELECT as it's very difficult to understand without any further explanations. May 30, 2020 at 10:59
  • @PeterVARGA Variables technique iterates the whole table performing chain calculations and does not use indexes. CTE technique allows to divide this process to steps, each performs a part of operations over data array and uses indexes (for CTEs - autoindexes). The use of UDV was safe in most cases because the set of tools was too poor, now you have a lot of additional functions (window functions, for example). Try to divide your task to those steps. If you need to integrate the conditions into any CTE then it is possible that your task dividing was performed by not optimal way.
    – Akina
    May 30, 2020 at 11:13
0

I can give you a more concrete example as to what was actually deprecated.

Seven years ago, I wrote the answer to Get the rank of a user in a score table

I make use of variables to find out the rank of a score table and assign the same value for rank in the event that more than one user has the same score (i.e. in case there are ties):

SET @rnk=0; SET @rank=0; SET @curscore=0;
SELECT score,ID,rank FROM
(
    SELECT AA.*,BB.ID,
    (@rnk:=@rnk+1) rnk,
    (@rank:=IF(@curscore=score,@rank,@rnk)) rank,
    (@curscore:=score) newscore
    FROM
    (
        SELECT * FROM
        (SELECT COUNT(1) scorecount,score
        FROM scores GROUP BY score
    ) AAA
    ORDER BY score DESC
) AA LEFT JOIN scores BB USING (score)) A;

This SQL Statement is an expression and will not work in MySQL 8.0.

Try to imagine trying rewriting this without variables. I feel your pain !!!

Aggressive queries that use variables in this fashion is what is being deprecated.

SUGGESTION

Assuming a,b,c,d,x,y,z are columns from MyTable

You simply rewrite it as

SELECT
   UDF1(0, a, b, c, d)
   UDF1(1, a, b, c, d)
   UDF1(2, a, b, c, d)
   UDF1(3, a, b, c, d)
INTO
    @foo1,@foo2,@foo3,@foo4
FROM MyTable;
SELECT
    UDF2( @foo1, @foo2, @foo3, @foo4)
    UDF3( @foo1, @foo2, @foo3, @foo4)
INTO
    @foo5,@foo6;
SELECT UDF4( @foo5, @foo7, x, y, z)
    INTO @foo8
FROM MyTable;

GIVE IT A TRY !!!

1
  • Thank you for your answer. In my case the answer from @Akina fits better. Cheers May 31, 2020 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.