MySQL: use a value from a select in the select itself

Question

First, if someone has a better title please help.

If I have let's say a 'calendar' table with a 'day' column. And I have the following query:

SELECT day, day AS testDay, testDay AS test2Day FROM calendar

MySQL will complain that "testDay" is an unknown column. Of course you will tell me that this statement is useless, but my statement looks more like this:

SELECT day, SOME_CRAZY_EXPRESSION_OF(day) AS testDay, EXPRESSION_OF(testDay) AS test2Day FROM calendar

And the point is I don't want to evaluate twice the first expression to use it within the second expression.. So is there a way to use a value calculated in the select as part of the select itself?

Of course I could do:

SELECT day, SOME_CRAZY_EXPRESSION_OF(day) AS testDay, EXPRESSION_OF(SOME_CRAZY_EXPRESSION_OF(day)) AS test2Day FROM calendar

But I'm trying to avoid wasting. If I have no choice, that's what I'll do.

Answer 1

So, you can't use variables, because they are containing value from the previous row (from official docs: https://dev.mysql.com/doc/refman/5.0/en/user-variables.html):

SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;

The reference to b in the HAVING clause refers to an alias for an expression in the select list that uses @aa. This does not work as expected: @aa contains the value of id from the previous selected row, not from the current row.

And thats why i founded a temporary solution that is based on the code fragments. I'll show an example in PHP:

$querySubstitutions = array(
      '%days%' => 'TO_DAYS(table.started_at + INTERVAL (table.period - 1) DAY)'
    );

$query = 'SELECT %days% AS days, <EXPRESSION_OF( %days% )> FROM table';

// Replacing pairs in a query
$query = strtr($query, $querySubstitutions);

This solution helps to compact a query in most cases, but i don't know yet how it affects performance.

Answer 2

In MySQL, you'll want to assign a temporary variable to the value of the expression to be able to reuse it, this should do it;

SELECT day, @tmp:=SOME_CRAZY_EXPRESSION_OF(day) AS testDay, 
    EXPRESSION_OF(@tmp) AS test2Day FROM calendar

See this page for another example with more analysis on the performance impact.