0

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.

JQKP
  • 75
  • 11
  • 1
    This link might help you. http://stackoverflow.com/questions/16715504/mysql-define-a-variable-within-select-and-use-it-within-the-same-select – Mario Mufasa May 27 '15 at 22:52

1 Answers1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Re grouping and aggregation, note that I am grouping on `days_since_first_date`, which is one-to-one correspondence for each `col1` with `date_`, and also on `col1`. So the grouping should be fine. – JQKP May 28 '15 at 13:54
  • I don't see how this answers the question, which was whether it's safe to define and use the same variable in a subquery and a query. – JQKP May 28 '15 at 13:54