Обсуждение: Unexpected behavior with CASE statement

Suppose I have the following table named "metrics":

metric_type | val
------------+-----
 0          | 1
 0          | 1
 1          | 0
 1          | 3

Now suppose I run the following simple query:

select
   metric_type,
   case metric_type
      when 0 then
         sum (1 / val)
      when 1 then
         sum (val)
   end as result
from metrics
group by metric_type

I expect to get the following result set:

metric_type | result
------------+-------
    0       |   2
    1       |   3

But in reality I get the following error:

    ERROR: division by zero
    SQL state: 22012

So it appears that Postgres executes all cases and select the result
in the end. Is this expected behavior?

Thanks
- Jimmy

On 10/3/07, Jimmy Choi <yhjchoi@gmail.com> wrote:
> I expect to get the following result set:
>
> metric_type | result
> ------------+-------
>     0       |   2
>     1       |   3

Try:

SELECT   metric_type
       , SUM(CASE metric_type
               WHEN 0
                 THEN 1 / val
               WHEN 1
                 THEN val
             END) AS RESULT
    FROM metrics
GROUP BY metric_type
ORDER BY metric_type

This will work for this particular example. But what if my case
statement is more complicated than that? Example:

select
   metric_type,
   case metric_type
      when 0 then
         sum (1 / val)
      when 1 then
         sum (val)
      when 2 then
         max (val)
      when 3 then
         min (val)
   end as result
from metrics
group by metric_type

Thanks!

On 10/3/07, Rodrigo De León <rdeleonp@gmail.com> wrote:
> On 10/3/07, Jimmy Choi <yhjchoi@gmail.com> wrote:
> > I expect to get the following result set:
> >
> > metric_type | result
> > ------------+-------
> >     0       |   2
> >     1       |   3
>
> Try:
>
> SELECT   metric_type
>        , SUM(CASE metric_type
>                WHEN 0
>                  THEN 1 / val
>                WHEN 1
>                  THEN val
>              END) AS RESULT
>     FROM metrics
> GROUP BY metric_type
> ORDER BY metric_type
>

Jimmy Choi escribió:
> This will work for this particular example. But what if my case
> statement is more complicated than that? Example:
>
> select
>    metric_type,
>    case metric_type
>       when 0 then
>          sum (1 / val)
>       when 1 then
>          sum (val)
>       when 2 then
>          max (val)
>       when 3 then
>          min (val)
>    end as result
> from metrics
> group by metric_type

This doesn't make sense.  Use separate output columns for the different
aggregates.

--
Alvaro Herrera                  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"Crear es tan difícil como ser libre" (Elsa Triolet)

"Jimmy Choi" <yhjchoi@gmail.com> writes:
> select
>    metric_type,
>    case metric_type
>       when 0 then
>          sum (1 / val)
>       when 1 then
>          sum (val)
>    end as result
> from metrics
> group by metric_type

The reason this does not work is that the aggregate functions are
aggregated without any knowledge of how they might ultimately be used
in the final output row.  The fact that the CASE might not actually
demand the value of an aggregate at the end doesn't stop the system from
having to compute it.

You could use a CASE *inside* the SUM() to prevent division by zero
while forming the sum, but on the whole this query seems rather badly
designed.  Consider

SELECT 0, sum(1/val) FROM metrics WHERE metric_type = 0
UNION ALL
SELECT 1, sum(val) FROM metrics WHERE metric_type = 1
UNION ALL
...


            regards, tom lane