Dan Rossi <mysql@stripped> wrote on 12/27/2005 11:39:57 PM:
> Hi there i am trying to use usewr variables in a select statement to
> add to a where clause in a sub query. Ie
>
> select @id:=id,@month:=month, (select SUM(totals) from table where
> id=@id and month=@month) as totals from table
>
> its happened on other occasions ie with calculations and sums, whats
> happened in mysql5 ? It used to work in mysql4 , something i am doing
> is wrong ? Please let me know thanks.
>
>
Is there a great reason why you are using a subquery? I could rewrite this
to avoid the subquery and probably eliminate your particular problem:
SELECT id, month, sum(totals) totals FROM TABLE group by id, month;
Unless(!) you oversimplified your original example. In which case, you
should post your actual query and I can give you a better response.
Technically, the values of the variables should not be determined until
AFTER the row is processed which means that you shouldn't be able to use
them for your subquery (at least that's how I remember the SQL:2003 spec
but it's late and I could very well be wrong in my recollection)
Personally, I am not that big a fan of subqueries anyway. There are a few
types of queries where they make the SQL to achieve a result rather
compact and elegant. However, I have never seen a subquery actually
outperform a properly constructed linear query. They sometimes match
linear performance but most often perform worse to much worse.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
