Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Задачка на rollup и grouping_id  [new]
over
Member

Откуда: *.msk.ru
Сообщений: 258
Вобщем задачка такая - есть два запроса:
SELECT grouping_id(city,
                   city || 'gavgav',
                   street),
       MAX(num1),
       MAX(num2),
       street,
       city
  FROM (SELECT 10 AS num1,
               20 AS num2,
               'LENIN' AS street,
               'MSK' AS city
          FROM dual)
 GROUP BY ROLLUP(city,
                 city || 'gavgav',
                 street)
HAVING grouping_id(city, city || 'gavgav', street) IN (0, 3)
и
SELECT grouping_id(city,
                   city || 'gavgav',
                   street),
       MAX(num1),
       MAX(num2),
       street,
       city
  FROM (SELECT 10 AS num1,
               20 AS num2,
               'LENIN' AS street,
               'MSK' AS city
          FROM dual)
 GROUP BY ROLLUP(city,
                 city || 'gavgav',
                 street)
HAVING grouping_id(city, city || 'gavgav', street) IN (SELECT 0
                                                         FROM dual
                                                       UNION
                                                       SELECT 3 FROM dual)
Первый ничего не возвращает, второй возвращает 2 строки - почему так ?
15 янв 07, 14:41    [3642137]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 119561
Да, тут явно пахнет багом ...
У Вас какая версия ?
Я попробовал в 10.2.0.1
WHERE grouping ... = 0 отрабатывает,
а ни IN, ни OR ничего не возвращают.
А решив попробовать с UNION ALL,
я вообще получил ORA-600 :-(

12:46:01  ORA-00600: Interner Fehlercode, Argumente: [qctcte1], [0], [], [], [], [], [], []

В металинке надо глянуть ...
15 янв 07, 14:49    [3642207]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15498
Глюки, глюки..
Группировки по юнионам от дуала больны ими на всю голову..
(версию, я бы таки указал ;) Вот на первом, что попалось мне (9.2.0.7)
первый запрос работает, если вместо IN (0, 3) написать IN (0)
15 янв 07, 14:51    [3642233]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
over
Member

Откуда: *.msk.ru
Сообщений: 258
Он отработает и если сделать вот так:

SELECT grouping_id(city,
                   city || 'gavgav',
                   street),
       MAX(num1),
       MAX(num2),
       street,
       city
  FROM (SELECT 10 AS num1,
               20 AS num2,
               'LENIN' AS street,
               'MSK' AS city
          FROM dual)
 GROUP BY ROLLUP(city,
                 city || 'gavgav',
                 street)
HAVING grouping_id(city, street) IN (0, 3) -- , city || 'gavgav' убрал

А версия 9.2.0.7.0
15 янв 07, 14:55    [3642276]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
А на план посмотреть, вообще супер :)

SQL>  explain plan for SELECT grouping_id(city,
  2                     city || 'gavgav',
  3                     street),
  4         MAX(num1),
  5         MAX(num2),
  6         street,
  7         city
  8    FROM (SELECT 10 AS num1,
  9                 20 AS num2,
 10                 'LENIN' AS street,
 11                 'MSK' AS city
 12            FROM dual)
 13   GROUP BY ROLLUP(city,
 14                   city || 'gavgav',
 15                   street)
 16  HAVING grouping_id(city, city || 'gavgav', street) IN  (0,3);

Объяснено.

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

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

----------------------------------------------------------------------------
| Id  | Operation                    |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |       |     2 |
|*  1 |  FILTER                      |             |       |       |       |
|   2 |   SORT GROUP BY NOSORT ROLLUP|             |     1 |       |     2 |
|   3 |    TABLE ACCESS FULL         | DUAL        |     1 |       |     2 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   1 - filter(GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING('MSK',2,0,SYS_OP_BITVEC
              ),SYS_OP_GROUPING('MSKgavgav',1,0,SYS_OP_BITVEC),SYS_OP_GROUPING('LENIN',1,0

              ,SYS_OP_BITVEC)))=0 OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING('MSK',2,0,SYS_

              OP_BITVEC),SYS_OP_GROUPING('MSKgavgav',1,0,SYS_OP_BITVEC),SYS_OP_GROUPING('L

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

              ENIN',1,0,SYS_OP_BITVEC)))=3)

20 строк выбрано.

SQL> explain plan for SELECT grouping_id(city,
  2                     city || 'gavgav',
  3                     street),
  4         MAX(num1),
  5         MAX(num2),
  6         street,
  7         city
  8    FROM (SELECT 10 AS num1,
  9                 20 AS num2,
 10                 'LENIN' AS street,
 11                 'MSK' AS city
 12            FROM dual)
 13   GROUP BY ROLLUP(city,
 14                   city || 'gavgav',
 15                   street)
  16  HAVING grouping_id(city, city || 'gavgav', street) IN  (3);

Объяснено.

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

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

---------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |     1 |       |     2 |
|*  1 |  FILTER               |             |       |       |       |
|   2 |   SORT GROUP BY NOSORT|             |     1 |       |     2 |
|   3 |    TABLE ACCESS FULL  | DUAL        |     1 |       |     2 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):

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

   1 - filter(3=3)

16 строк выбрано.
Причем при правильном (втором) варианте план (фильтр) тоже своеобразный на мой вкус :)
15 янв 07, 14:55    [3642278]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
over
Member

Откуда: *.msk.ru
Сообщений: 258
Сработает так-же если вместо in (0) поставить = 0, или 3, или 1 :-)
15 янв 07, 14:57    [3642292]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
ИМХО это более простая модификация бага "Bug 5055892" :))) Вот если бы на 10.2.0.3 кто-нибудь посмотрел...
15 янв 07, 15:01    [3642345]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
Maxim Demenko
Member

Откуда: Munich, Germany
Сообщений: 954
orawish
Глюки, глюки..
Группировки по юнионам от дуала больны ими на всю голову..


Похоже опять клиентские приколы? Попробуйте оба запроса как CTAS (у меня на 10.2.0.2 нормально отрабатывает, на 9.2.0.6 - первый запрос даёт 1 строку в sqlplus , в не Оракловском клиенте оба отрабатывают правильно). CTAS генерирует правильные результаты для обеих версий.

Best regards

Maxim
15 янв 07, 15:02    [3642353]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 119561
Jannny
ИМХО это более простая модификация бага "Bug 5055892" :))) Вот если бы на 10.2.0.3 кто-нибудь посмотрел...


Да я ради такого дела даже на apex залез :)))
10.1.0.4 - полет нормальный . Подчистили :-)
15 янв 07, 15:04    [3642370]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
Maxim Demenko
Member

Откуда: Munich, Germany
Сообщений: 954
Maxim Demenko
orawish
Глюки, глюки..
Группировки по юнионам от дуала больны ими на всю голову..


у меня на 10.2.0.2 нормально отрабатывает


Maxim


Пардон, у меня на 10.2.0.3 нормально отрабатывает

Best regards

Maxim
15 янв 07, 15:05    [3642380]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
dmidek
Jannny
ИМХО это более простая модификация бага "Bug 5055892" :))) Вот если бы на 10.2.0.3 кто-нибудь посмотрел...
Да я ради такого дела даже на apex залез :)))
10.1.0.4 - полет нормальный . Подчистили :-)
Да?
15 янв 07, 15:06    [3642387]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 119561
Jannny
dmidek
Jannny
ИМХО это более простая модификация бага "Bug 5055892" :))) Вот если бы на 10.2.0.3 кто-нибудь посмотрел...
Да я ради такого дела даже на apex залез :)))
10.1.0.4 - полет нормальный . Подчистили :-)
Да?

Хм... Да, Вы правы
"Сумбур вместо музыки" :-)
15 янв 07, 15:07    [3642407]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15498
Maxim Demenko
orawish
Глюки, глюки..
Группировки по юнионам от дуала больны ими на всю голову..


Похоже опять клиентские приколы?..

Нет.. Вот - из той же бочки можно тестануть >= 10.2.0.2
(сам тестил, 9.x.x и 10.2.0.1 - результат на разных версиях серверов разный, но ни на одном только не видел правильный)
Такая вот штука с Group By...
15 янв 07, 15:13    [3642463]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
Maxim Demenko
Member

Откуда: Munich, Germany
Сообщений: 954
Продолжаю упорствовать ;-)

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
PL/SQL Release 9.2.0.6.0 - Production
CORE    9.2.0.6.0       Production
TNS for Linux: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production

SQL> drop table t;
drop table t
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table t(COL1 NUMBER,
  2                 COL2 NUMBER,
  3                 COL3 NUMBER,
  4                 STREET varCHAR2(5),
  5                 CITY varCHAR2(3));

Table created.

SQL> insert into t
  2    SELECT grouping_id(city, city || 'gavgav', street),
  3           MAX(num1),
  4           MAX(num2),
  5           street,
  6           city
  7      FROM (SELECT 10 AS num1, 20 AS num2, 'LENIN' AS street, 'MSK' AS city
  8              FROM dual)
  9     GROUP BY ROLLUP(city, city || 'gavgav', street)
 10    HAVING grouping_id(city, city || 'gavgav', street) IN (0, 3);

2 rows created.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE    10.1.0.5.0      Production
TNS for Linux: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production

SQL> drop table t;

Table dropped.

SQL> create table t(COL1 NUMBER,
  2                 COL2 NUMBER,
  3                 COL3 NUMBER,
  4                 STREET varCHAR2(5),
  5                 CITY varCHAR2(3));

Table created.

SQL> insert into t
  2    SELECT grouping_id(city, city || 'gavgav', street),
  3           MAX(num1),
  4           MAX(num2),
  5           street,
  6           city
  7      FROM (SELECT 10 AS num1, 20 AS num2, 'LENIN' AS street, 'MSK' AS city
  8              FROM dual)
  9     GROUP BY ROLLUP(city, city || 'gavgav', street)
 10    HAVING grouping_id(city, city || 'gavgav', street) IN (0, 3);

2 rows created.


SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> drop table t;

Table dropped.

SQL> create table t(COL1 NUMBER,
  2                 COL2 NUMBER,
  3                 COL3 NUMBER,
  4                 STREET varCHAR2(5),
  5                 CITY varCHAR2(3));

Table created.

SQL> insert into t
  2    SELECT grouping_id(city, city || 'gavgav', street),
  3           MAX(num1),
  4           MAX(num2),
  5           street,
  6           city
  7      FROM (SELECT 10 AS num1, 20 AS num2, 'LENIN' AS street, 'MSK' AS city
  8              FROM dual)
  9     GROUP BY ROLLUP(city, city || 'gavgav', street)
 10    HAVING grouping_id(city, city || 'gavgav', street) IN (0, 3);

2 rows created.

Best regards

Maxim
15 янв 07, 15:28    [3642628]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
Alex from SPb
Member

Откуда:
Сообщений: 448
Maxim

insert into t SELECT ... = insert into t select * from (SELECT ...)

оберни свой первый селект и он должен вернуть 2 строчки
15 янв 07, 15:45    [3642775]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
Ловец Стрекоз
Member

Откуда: Москва
Сообщений: 236
а может ли быть такое что запись (0, 3) воспринимается как вектор, а не как множество?
15 янв 07, 16:07    [3643018]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 119561
Ловец Стрекоз
а может ли быть такое что запись (0, 3) воспринимается как вектор, а не как множество?

Остроумное предположение , но с OR эффект точно такой же :-)
15 янв 07, 16:13    [3643078]     Ответить | Цитировать Сообщить модератору
 Re: Задачка на rollup и grouping_id  [new]
Andrei Fomichev
Member

Откуда: Москва
Сообщений: 453
На 9.2.0.4 результаты обоих запросов совпадают - оба запроса возвращают две строки.
15 янв 07, 17:19    [3643771]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить