¿bug en Oracle 10g con ROLLUP y ORDER BY?

[MutenRo]

Buenas,

unos resultados no me cuadraban en unas consultas que estaba haciendo y tras hacer varias pruebas y simplificar la consulta al máximo, me da la impresión de que hay un bug cuando en una consulta utilizamos conjuntamente ROLLUP, COUNT(DISTINCT ...) y ORDER BY.

La consulta simplificada, para que cualquiera pueda probarla y ver el supuesto bug, es ésta:

Código:

SELECT A, B, COUNT(DISTINCT C) AS C
FROM (
SELECT 1 AS A, 3 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 1 AS A, 3 AS B, 3 AS C FROM DUAL
UNION ALL
SELECT 2 AS A, 1 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 2 AS A, 2 AS B, 3 AS C FROM DUAL
)
GROUP BY ROLLUP(A), B 
ORDER BY B

El resultado que me da, sorprendentemente, es éste:

A B C
2 1 1
2 2 1
1 3 2
null 1 1
null 2 1
null 3 2

Es decir, ¡¡no lo ha ordenado por la columna B!!. Es como si hubiese aplicado el order by de manera separada, primero al resultado que daría con el GROUP BY y luego aparte a las 3 líneas debidas al ROLLUP.

Curiosamente si en lugar de COUNT(DISTINCT C) solicitamos SUM(C), entonces sí que lo hace bien, éste sería el resultado:

A B C
2 1 2
null 1 2
2 2 3
null 2 3
1 3 5
null 3 5

¿Parece un bug en toda regla, no? ¿qué opinais?

[matanga]

Hola,

Estuve dandole varias vueltas a la consulta y sus posibles variaciones y revisando los planes de ejecucion en cada caso, en el consulta particular que planteas, no se hace un order by mas alla del rollup, la ultima operacion de sorting es SORT GROUP BY ROLLUP cuando deberia ser SORT ORDER BY.

De todos modos, no entiendo si estas planteando un caso real o solo estas haciendo pruebas, porque dos cosas no tienen sentido para mi

1. GROUP BY ROLLUP(A), B que hace b fuera del rollup?
2. Si el rollup es un totalizador, para que desordenar lo que el ya ordeno?, es decir, nunca me hizo falta un order by despues de un rollup.

Saludos

[MutenRo]

Hola,

te agradezco el interés, matanga.

Estoy planteando un caso real, es decir me ha ocurrido mientras hacía pruebas para optimizar las consultas de una aplicación, lo que ocurre es que he simplificado la consulta a su mínima expresión y la he hecho independiente de mi modelo de datos para mostrar lo más claramente posible el bug.

1. GROUP BY ROLLUP(A), B que hace b fuera del rollup?

Hacer un ROLLUP de un solo campo del GROUP BY tiene perfecto sentido, para obtener totales por ese campo mientras agrupas por los demás.

2. Si el rollup es un totalizador, para que desordenar lo que el ya ordeno?, es decir, nunca me hizo falta un order by despues de un rollup.

El GROUP BY y ROLLUP no garantizan un orden concreto, si uno quiere las filas ordenadas de una manera concreta, tiene que asegurarse siempre de indicarlo con un ORDER BY. En el caso que he presentado es claro que no hace bien la ordenación, por eso creo que estamos ante un bug.

Saludos.

[matanga]

Hola,

Si piensas que estas frente a un bug, intenta llevar la version de la base de datos al ultimo parche, y si aun crees tener el bug debes abrir un SR (Service Request) en soporte de Oracle.

Perdon, per sigo sin ver el sentido de los puntos que comente, en cuanto al orden esta garantizado por la operacion SORT GROUP BY ROLLUP.

Para ver el plan de ejecucion y todas las operaciones de sorting, puedes utilizar el siguiente comando

Código:

oracle@10G> explain plan for
  2  select * from dual;

Explicado.

Transcurrido: 00:00:00.18
oracle@10G> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     2 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 filas seleccionadas.

Saludos

[MutenRo]

Hola,

voy a intentar aclarar algunas cosas.

Perdon, per sigo sin ver el sentido de los puntos que comente, en cuanto al orden esta garantizado por la operacion SORT GROUP BY ROLLUP.

El punto 1 creo que está claro, no es lo mismo hacer GROUP BY ROLLUP(A), B que hacer GROUP BY ROLLUP(A,B). El primero es para obtener totales independientemente de A pero agrupados por B y el segundo es para obtener totales parciales agrupados por A independientemente de B y además los totales independientemente de A y B. Un ejemplo sería:

Código:

SELECT A, B, COUNT(C) AS C
FROM (
SELECT 1 AS A, 3 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 1 AS A, 3 AS B, 3 AS C FROM DUAL
UNION ALL
SELECT 2 AS A, 1 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 2 AS A, 3 AS B, 3 AS C FROM DUAL
)
GROUP BY ROLLUP(A, B)

esto nos devuelve:

A B C
1 3 2
1 null 2
2 1 1
2 3 1
2 null 2
null null 4

y en el EXPLAIN PLAN vemos que efectivamente hace un SORT GROUP BY ROLLUP, de manera que los totales parciales aparecen ordenados y al final el 'total de totales', pero eso no significa que esté ordenado como yo quiero (punto 2), de hecho si añado un ORDER BY B obtengo:

A B C
2 1 1
2 3 1
1 3 2
1 null 2
null null 4
2 null 2

lo cual es correcto, y además en el EXPLAIN PLAN vemos que tras hacer SORT GROUP BY ROLLUP, también hace SORT ORDER BY (estrictamente sobraba hacer el SORT GROUP BY ROLLUP, pero eso es otro asunto). El resultado es correcto.

Ahora volvamos a la consulta problemática, con el COUNT(DISTINCT C) y ROLLUP(A), B:

Código:

SELECT A, B, COUNT(DISTINCT C) AS C
FROM (
SELECT 1 AS A, 3 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 1 AS A, 3 AS B, 3 AS C FROM DUAL
UNION ALL
SELECT 2 AS A, 1 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 2 AS A, 3 AS B, 3 AS C FROM DUAL
)
GROUP BY ROLLUP(A), B

devuelve:

A B C
2 1 1
1 3 2
2 3 1
null 1 1
null 3 2

lo cual es correcto pero se ve algo extraño: a diferencia de la consulta anterior o de cuando hacemos COUNT(C) simplemente, vemos que los totales están al final, es decir, aunque ha efectuado un SORT GROUP BY ROLLUP, la fila (null,1,1) no está en segundo lugar sino que está al final. No digo que esto sea incorrecto porque en principio no tiene por qué garantizar un orden, pero cuando añadimos ORDER BY B, el resultado es:

A B C
2 1 1
1 3 2
2 3 1
null 1 1
null 3 2

lo cual es claramente incorrecto.

[matanga]

Hola,

Te recomiendo leer esta nota http://asktom.oracle.com/pls/asktom/...:1512805503041 en particular los post titulados ROLLUP AND ORDERING.

Tambien puedes buscar en http://metalink.oracle.com a ver si hay bugs reportados.

Saludos

[MutenRo]

Hola,

gracias por los enlaces, matanga, pero no tengo acceso a http://metalink.oracle.com (parece que hace falta un código para darse de alta).

De todas maneras lo he consultado en askTom.com y aquí teneis (al final de la página) lo que me ha contestado: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:74320098178823

En resumidas cuentas, parece que en la versión 11g está arreglado (no lo he probado) y para la 10g se puede obtener el resultado correcto de esta manera:

Código:

WITH DATA AS
(SELECT /*+ materialize */ A, B, COUNT(DISTINCT C) AS C
FROM (
SELECT 1 AS A, 3 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 1 AS A, 3 AS B, 3 AS C FROM DUAL
UNION ALL
SELECT 2 AS A, 1 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 2 AS A, 3 AS B, 3 AS C FROM DUAL
)
GROUP BY ROLLUP(A), B
ORDER BY B)
SELECT * FROM DATA ORDER BY B;

Saludos.

[matanga]

Hola,

Te dejo el output y plan de ejecucion en 11g (11.1.0.6)

Código:

SQL> SELECT A, B, COUNT(DISTINCT C) AS C
  2  FROM (
  3  SELECT 1 AS A, 3 AS B, 2 AS C FROM DUAL
  4  UNION ALL
  5  SELECT 1 AS A, 3 AS B, 3 AS C FROM DUAL
  6  UNION ALL
  7  SELECT 2 AS A, 1 AS B, 2 AS C FROM DUAL
  8  UNION ALL
  9  SELECT 2 AS A, 2 AS B, 3 AS C FROM DUAL
 10  )
 11  GROUP BY ROLLUP(A), B
 12  ORDER BY B;

         A          B          C
---------- ---------- ----------
         2          1          1
                    1          1
                    2          1
         2          2          1
                    3          2
         1          3          2

6 rows selected.

SQL> explain plan for
  2  SELECT A, B, COUNT(DISTINCT C) AS C
  3  FROM (
  4  SELECT 1 AS A, 3 AS B, 2 AS C FROM DUAL
  5  UNION ALL
  6  SELECT 1 AS A, 3 AS B, 3 AS C FROM DUAL
  7  UNION ALL
  8  SELECT 2 AS A, 1 AS B, 2 AS C FROM DUAL
  9  UNION ALL
 10  SELECT 2 AS A, 2 AS B, 3 AS C FROM DUAL
 11  )
 12  GROUP BY ROLLUP(A), B
 13  ORDER BY B;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

Plan hash value: 1718372963

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     4 |    36 |     9  (12)| 00:00:01 |
|   1 |  SORT ORDER BY        |      |     4 |    36 |     9  (12)| 00:00:01 |
|   2 |   SORT GROUP BY ROLLUP|      |     4 |    36 |     9  (12)| 00:00:01 |
|   3 |    VIEW               |      |     4 |    36 |     8   (0)| 00:00:01 |
|   4 |     UNION-ALL         |      |       |       |            |          |
|   5 |      FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   6 |      FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
|   7 |      FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
|   8 |      FAST DUAL        |      |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

15 rows selected.

Ahora si cierra la consulta con SORT ORDER BY y no con SORT GROUP BY ROLLUP. Habra que ver en 10.2.0.3 y 10.2.0.4.

Saludos

[MutenRo]

Buenas,

en la página de askTom.com que os comenté alguien ha encontrado otra manera muy sencilla de evitar el bug, añadiendo '+ 0' en el order by:

Código:

SELECT A, B, COUNT(DISTINCT C) AS C
FROM (
SELECT 1 AS A, 3 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 1 AS A, 3 AS B, 3 AS C FROM DUAL
UNION ALL
SELECT 2 AS A, 1 AS B, 2 AS C FROM DUAL
UNION ALL
SELECT 2 AS A, 2 AS B, 3 AS C FROM DUAL
)
GROUP BY ROLLUP(A), B
ORDER BY B+0

Curiosamente así funciona bien.