List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 23 2002 5:33pm
Subject:Re: Strange behaviour with SQL vars and order by
View as plain text  
I see no column named  Site.nome showing that it is wrong.

ds wrote:

>Hi,
>
>I'm having a strange problem using SQL vars (@variable) and an order by
>expression.
>
>I have a query that returns a record for each day. 
>Each record has a date (data), a column with the daily total value
>(uv_views) and other with the accumulated value until the current day
>(acc_views). The other columns are IDs.
>
>The problem is that if i only order by date, the results are ok...
>If i order by "date,Site.nome"  (you would say Site.name) 
>the results are wrong and i don't know where it got those results. 
>
>Here is the output. 
>I have limited the values to 4 (it only returns rows with 0 but it
>doesn't matter).
>In the second "big" (not so big) query, look at "acc_views" accumulated
>value... How can it be ?!?!
>Thank you in advance for trying to help.
>
>
>mysql> select @diario_views:=0,@acc_views:=0;
>+------------------+---------------+
>| @diario_views:=0 | @acc_views:=0 |
>+------------------+---------------+
>|                0 |             0 |
>+------------------+---------------+
>1 row in set (0.00 sec)
>
>mysql>  SELECT data,
>  @diario_views:=uv_views as views, 
>  @acc_views:=@acc_views+@diario_views as acc_views, 
>  secloc.site_id,secloc.section_id,secloc.location_id 
>FROM secloc,Site,Section,Location 
>WHERE secloc.campaign_id='9' 
>  and secloc.site_id=Site.site_id 
>  and secloc.section_id=Section.section_id 
>  and secloc.location_id=Location.location_id 
>ORDER BY data LIMIT 4;
>+------------+-------+-----------+---------+------------+-------------+
>| data       | views | acc_views | site_id | section_id | location_id |
>+------------+-------+-----------+---------+------------+-------------+
>| 2001-09-20 |     0 |         0 |       2 |          1 |           4 |
>| 2001-09-20 |     0 |         0 |       3 |          1 |           4 |
>| 2001-09-20 |     0 |         0 |       4 |          1 |           4 |
>| 2001-09-20 |     0 |         0 |       6 |          1 |           4 |
>+------------+-------+-----------+---------+------------+-------------+
>4 rows in set (0.02 sec)
>
>mysql> select @diario_views:=0,@acc_views:=0;
>+------------------+---------------+
>| @diario_views:=0 | @acc_views:=0 |
>+------------------+---------------+
>|                0 |             0 |
>+------------------+---------------+
>1 row in set (0.00 sec)
>
>mysql>  SELECT data,
>  @diario_views:=uv_views as views,
>  @acc_views:=@acc_views+@diario_views as acc_views, 
>  secloc.site_id,secloc.section_id,secloc.location_id 
>FROM secloc,Site,Section,Location 
>WHERE secloc.campaign_id='9' 
>  and secloc.site_id=Site.site_id 
>  and secloc.section_id=Section.section_id 
>  and secloc.location_id=Location.location_id 
>ORDER BY data,Site.nome LIMIT 4;
>+------------+-------+-----------+---------+------------+-------------+
>| data       | views | acc_views | site_id | section_id | location_id |
>+------------+-------+-----------+---------+------------+-------------+
>| 2001-09-20 |     0 |    227815 |       3 |          1 |           4 |
>| 2001-09-20 |     0 |    263822 |       4 |          1 |           4 |
>| 2001-09-20 |     0 |         0 |       2 |          1 |           4 |
>| 2001-09-20 |     0 |    331278 |       6 |          1 |           4 |
>+------------+-------+-----------+---------+------------+-------------+
>4 rows in set (0.04 sec)
>
>mysql> 
>


Thread
Strange behaviour with SQL vars and order byds22 Apr
  • Re: Strange behaviour with SQL vars and order byGerald Clark23 Apr