Define and use a variable with a subquery?

Question

I know normally "the order of evaluation for expressions involving user variables is undefined" so we can't safely define and use a variable in the same select statement. But what if there's a subquery? As an example, I have something like this:

select col1,
       (select min(date_)from t where i.col1=col1) as first_date,
       datediff(date_, (select min(date_)from t where i.col1=col1)
               ) as days_since_first_date,
       count(*) cnt
from t i
where anothercol in ('long','list','of','values')
group by col1,days_since_first_date;

Is there a way to use (select @foo:=min(date_)from t where i.col1=col1) safely instead of repeating the subquery? If so, could I do it in the datediff function or the first time the subquery appears (or either one)?

---

Of course, I could do

select col1,
       (select min(date_)from t where i.col1=col1) as first_date,
       date_,
       count(*) cnt
from t i
where anothercol in ('long','list','of','values')
group by col1,date_;

and then do some simple postprocessing to get the datediff. Or I can write two separate queries. But those don't answer my question, which is whether one can safely define and use the same variable in a query and a subquery.

Answer 1

First, your query doesn't really make sense, because date_ has no aggregation functions. You are going to get an arbitrary value.

That said, you could repeat the subquery, but I don't see why that would be necessary. Just use a subquery:

select t.col1, t.first_date,
       datediff(date_, first_date),
       count(*)
from (select t.*, (select min(date_) from t where i.col1 = t.col1) as first_date
      from t
      where anothercol in ('long','list', 'of', 'values')
     ) t
group by col1, days_since_first_date;

As I mentioned, though, the value of the third column is problematic.

Note: this does occur additional overhead for materializing the subquery. However, there is a group by anyway, so the data is being read and written multiple times.