MySQL Bugs: #112569: Deprecating variables within expressions

Bug #112569	Deprecating variables within expressions - re-using calculations within SELECT
Submitted:	28 Sep 2023 20:34	Modified:	29 Sep 2023 13:25
Reporter:	Nuno P	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Other (Docker)
Assigned to:		CPU Architecture:	x86

Description:
Hello,

I have recently migrated from MariaDB 10.8 to MySQL 8.0.

Besides a number of performance issues that I had to go through and resolve, and a few other things like ONLY_FULL_GROUP_BY being stricter than MariaDB was,

One problem I'm having now is with this warning:

> 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)'.

I have a very complex query (but highly optimized/performant) running in the main page of one of my websites. This query is very long, containing a huge algorithm, several UNIONs, several inner/left JOINs, several EXISTS, queries wrapping subqueries, ROW_NUMBER(), etc...

In order to reduce a bit the number of wrapping queries, this query stores some data into variables, inside the many subqueries that UNION between themselves:

    SELECT `col1`, `col2`, ..., @doc_quality:=(`col1` + `col2` + `col3`*2 + `col4`*4 + `col5`*16 + POW(`col6`, 1/1.5) - `col7` - `col8`*100 - `col9`*32)/10 + COALESCE(`col10`, 0) - CASE WHEN `col11`="5" THEN 25 ELSE 0 END AS `doc_quality`, (COALESCE(`col12`/100, 0)*2 + COALESCE(`col13`/100, 0) + `col14`/5 + @doc_quality) AS `doc_rank`, @doc_dt:=CAST(CONCAT_WS("-", `col15`, `col16`, `col17`) AS DATE) AS `doc_dt`, (ABS(DATEDIFF(CURDATE(), @doc_dt)) / 365) AS `doc_age` FROM `mytable` ... 
	
(this is a VERY simplified/minimal example)

I have been researching a bit the error above, but it seems that most/all existing questions I find is about `@i := @i + 1`, and the solution is to use ROW_NUMBER() instead.

I've seen some other questions for other things, and the solution was to use CTEs.

I haven't seen anything similar to my case, where I'm basically reusing/"memoizing" calculations within the SELECT, to be used in another column of the same SELECT, to avoid having to duplicate the whole calculation.

Is the only solution, to this, to add some extra wrapping SELECTs, where the calculations are in a subquery, and the wrapper SELECT uses those results?

To me, this seems to be overkill, when my query is already very complex.

I wonder what's the reasoning for deprecating variables like these, when they're so useful for these scenarios.

And if MySQL developers could consider keeping it.

Thank you very much!

How to repeat:
N/A

Suggested fix:
I would like to ask that MySQL developers could consider not deprecating/removing variables within expressions.

Hi Mr. P,

Thank you for your bug report.

However, we do not have good news for you.

Deprecating user variables remains strictly our policy , but for a very , very good reason.

The reasoning for the decision is that the order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

Simply, SQL Standard does not require that fields in the SELECT list are evaluated in the order in which they are written. If that would be required, many queries would have executed slower.

You have to use: SELECT .... INTO @a ...; and then in the next command SET @a := @a + 1;

Unsupported.

Thank you very much.

I understand the reasonings.

Looks like I'll have to go ahead and wrap this into subqueries, to reuse the calculations in the wrapping query.

I appreciate your reply.

Hi,

You are truly welcome.