Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
Sonic86
Member

Откуда:
Сообщений: 43
Здравствуйте!
Я - быдлокодер, пешу код под Oracle.

Пусть T - таблица со столбцами A,B,C.
Как-то я написал процедуру вида
create or replace procedure TTT as
begin
  for rec in (
    select A, B, sum(C) as SC
    from T
    group by A -- B потеряно!
  ) loop
    null;
  end loop;
end;

скомпилил - и процедура скомпилилась!
Хотя совершенно очевидно, что работать она никогда не будет и выдаст ORA-00979 not a group by expression.
Аналогично компилируется любой сложный запрос, в котором список не группируемых полей не совпадает с их списком в клаузе group by.
Вопрос в том, почему статический анализатор PLSQL не отлавливает ORA-00979 not a group by expression при компиляции? Неужели есть какой-то хитрый вариант запроса с group by и неверным списком полей. Или это баг Oracle? В последнее верится с трудом, ибо это какой-то совсем тупой баг. Или я чего-то еще не понимаю?
Вопрос этот для меня как для быдлокодера имеет значение, поскольку при использовании методологии разработки "херак-херак и в продакшн" простейшая потеря поля приводит к тому, что поставленный клиенту функционал тупо не работает.

Я честно погуглил (правда, только в русскоязычном сегменте) и поискал на форуме по словам group by, нашел 5 страниц тем, но не увидел аналогичной темы, потому создал свою и надеюсь на Ваш ответ.
29 апр 17, 21:20    [20446475]     Ответить | Цитировать Сообщить модератору
 Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 7738
Покажите листинг SQL*Plus, где вы создали таблицу,
выполнили успешную компиляцию процедуры
и получили ошибку выполнения.

Без этого все ваши слова - это пустой паровой зеленый свисток.
30 апр 17, 00:20    [20446649]     Ответить | Цитировать Сообщить модератору
 Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8499
Скорее всего из серии "обзову я PL/SQL переменную или параметр так-же как и имя поля таблицы":

SQL> create or replace procedure TTT as
  2  ename varchar2(20);
  3  begin
  4    for rec in (
  5      select empno, ename, sum(sal) as SC
  6      from emp
  7      group by empno
  8    ) loop
  9      null;
 10    end loop;
 11  end;
 12  /

Procedure created.

SQL> create or replace procedure TTT(ename varchar2) AS
  2  begin
  3    for rec in (
  4      select empno, ename, sum(sal) as SC
  5      from emp
  6      group by empno
  7    ) loop
  8      null;
  9    end loop;
 10  end;
 11  /

Procedure created.

SQL>


SY.
30 апр 17, 00:59    [20446664]     Ответить | Цитировать Сообщить модератору
 Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8499
Хотя, таки-да, компилируется, но ошибка отложена до выполнeния:

SQL> create or replace procedure TTT as
  2  begin
  3    for rec in (
  4      select empno, ename, sum(sal) as SC
  5      from emp
  6      group by empno
  7    ) loop
  8      null;
  9    end loop;
 10  end;
 11  /

Procedure created.

SQL> EXEC TTT
BEGIN TTT; END;

*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
ORA-06512: at "SCOTT.TTT", line 3
ORA-06512: at line 1


SQL>


SY.
30 апр 17, 01:08    [20446666]     Ответить | Цитировать Сообщить модератору
 Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
xtender
Member

Откуда: Мск
Сообщений: 4329
Sonic86
статический анализатор PLSQL не отлавливает ORA-00979 not a group by expression при компиляции

вкратце и очень грубо говоря, потому что во время компиляции PL/SQL объектов для таких запросов выполняются только синтаксический и семантический анализ, а этот тип ошибок не проверяется ни на синтаксическом, ни на семантическом анализе, а только на этапе оптимизации.
1. синтаксический анализ - это анализ конструкций языка (например, "select * form dual" прервется с ошибкой на этом этапе)
2. семантический анализ - это проверка типов объектов, столбцов таблиц и вьюх, грантов на них, и тд., т.е. тут с ошибкой прервутся уже селекты по несуществующим или не грантованным таблицам и тд
3. оптимизация - грубо говоря, трансформации запроса и построение плана. Вот тут-то как раз прервется по этой ORA-00979.

Если в деталях, то читай у Льюиса "Oracle core" раздел 7 "Parsing and optimizing".
30 апр 17, 03:55    [20446690]     Ответить | Цитировать Сообщить модератору
 Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
Vladimir Filin
Member

Откуда: Москва
Сообщений: 63
Sonic86,
в явном виде ответа на твой вопрос в доках не видел.
Но есть намёк почему:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/cursor_for_loop_statement.htm#CJAIGGIA

For select_statement, PL/SQL declares, opens, fetches from, and closes an implicit cursor.

Т.е. до выполнения select не задекларирован
30 апр 17, 04:34    [20446693]     Ответить | Цитировать Сообщить модератору
 Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
xtender
Member

Откуда: Мск
Сообщений: 4329
о.О
как из
Vladimir Filin
For select_statement, PL/SQL declares, opens, fetches from, and closes an implicit cursor.
получилось это
Vladimir Filin
Т.е. до выполнения select не задекларирован
?

для лучшего понимания:
+
-- тут синтаксический разбор прошел с ошибкой:
SQL> create table t(a int, b int, c int);
SQL> create or replace procedure p_syntactic is
  2     cursor c is select a,b,sum(c) sum_c form t group by a;
  3  begin
  4     null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> sho error;
Errors for PROCEDURE P_SYNTACTIC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16     PL/SQL: SQL Statement ignored
2/40     PL/SQL: ORA-00923: FROM keyword not found where expected

-- тут семантический разбор прошел с ошибкой:
SQL> create or replace procedure p_semantic is
  2     cursor c is select a,b,sum(blabla) sum_c from t group by a;
  3  begin
  4     null;
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> sho error;
Errors for PROCEDURE P_SEMANTIC:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16     PL/SQL: SQL Statement ignored
2/31     PL/SQL: ORA-00904: "BLABLA": invalid identifier

-- а тут и синтаксический, и семантический анализы ошибок не вернули:
SQL> create or replace procedure p_valid is
  2     cursor c is select a,b,sum(c) sum_c from t group by a;
  3  begin
  4     null;
  5  end;
  6  /

SQL> sho error;
No errors.

-- а теперь угадайте, почему создание вью возвращает ошибку? в чем тут отличие?
-- подсказка будет в спойлере ниже...
SQL> create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a;
create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a
                                 *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

+ подсказка
запустите с трассировкой 10053 пример с p_valid и сравните с такой же трассировкой create view :)))
30 апр 17, 05:16    [20446696]     Ответить | Цитировать Сообщить модератору
 Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
Vladimir Filin
Member

Откуда: Москва
Сообщений: 63
xtender,
ОК,
после праздников посмотрю. Интересно.
30 апр 17, 16:42    [20447169]     Ответить | Цитировать Сообщить модератору
 Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 40890
3995355
1 май 17, 08:43    [20447769]     Ответить | Цитировать Сообщить модератору
 Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
xtender
Member

Откуда: Мск
Сообщений: 4329
andreymx,

надо было и ссылку сюда указать, т.к. там-то ответа так и нет.
А на самом деле там еще и запутались...
Грубо говоря, есть 4 вида ошибок:

  • Синтаксические ошибки, например:
    ORA-00923: FROM keyword not found where expected
    ORA-00924: missing BY keyword
    и тд

  • Семантические ошибки, например:
    ORA-00913: Too many values
    ORA-00960 ambiguous column naming in select list
    ...

  • Ошибки выявляемые при оптимизации, т.е. ошибки которые оракл выявляет при анализе трансформаций, типа ошибки из топика (ORA-00979, ORA-00937,...)

  • Рантайм-ошибки, т.е. фазы execution. (ORA-01722, ORA-01427,...)

    При ошибках семантического и синтаксического анализа, оракл прерывает с ошибкой до этапа оптимизации, т.е. трассы 10053 для них не будет.

    При рантайм ошибках план уже будет, т.е. трасса 10053 сформируется без ошибок.

    При PL/SQL компиляции оракл выполняет только синтаксический и семантический анализ, поэтому ошибок последних двух типов он не обнаруживает.

    1. Из документации: Syntactic and Semantic Checking
    syntactic checking verifies that keywords, object names, operators, delimiters, and so on are placed correctly in your SQL statement....
    semantic checking verifies that references to database objects and host variables are valid and that host-variable datatypes are correct.

    2. Если нет книги Oracle Core, то гляньте этот кусок тут:
  • 1 май 17, 11:11    [20447873]     Ответить | Цитировать Сообщить модератору
     Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
    andreymx
    Member

    Откуда: Запорожье
    Сообщений: 40890
    На самом деле некритично, но бывает обидно - скомпилировалось, но не работает :)
    1 май 17, 18:37    [20448246]     Ответить | Цитировать Сообщить модератору
     Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
    Vint
    Member

    Откуда: Таган-москва
    Сообщений: 4317
    andreymx,
    обидно бывает только если ты этот запрос отдельно не выполнял, а писал в слепую на скорую руку быстрее быстрее лишь бы отвязались)))
    2 май 17, 10:20    [20448938]     Ответить | Цитировать Сообщить модератору
     Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
    andreymx
    Member

    Откуда: Запорожье
    Сообщений: 40890
    Vint
    andreymx,
    обидно бывает только если ты этот запрос отдельно не выполнял, а писал в слепую на скорую руку быстрее быстрее лишь бы отвязались)))
    было такое, было
    когда расчет, и прибегают - добавь пару полей, срочно
    и добавляешь
    2 май 17, 11:47    [20449370]     Ответить | Цитировать Сообщить модератору
     Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
    xtender
    Member

    Откуда: Мск
    Сообщений: 4329
    andreymx,

    Так почему обидно-то? Все равно ведь очевидно, что без внимательной проверки/тестирования не обнаружить как минимум рантайм ошибки...
    2 май 17, 11:58    [20449437]     Ответить | Цитировать Сообщить модератору
     Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
    Elic
    Member

    Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
    Сообщений: 26878
    xtender
    Так почему обидно-то? Все равно ведь очевидно, что без внимательной проверки/тестирования не обнаружить как минимум рантайм ошибки...
    рантайм ошибки провоцируются характерными данными. Несгруппированность выражения никакими данными не исправится - незачем оттягивать ошибку.
    2 май 17, 12:20    [20449538]     Ответить | Цитировать Сообщить модератору
     Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
    Vint
    Member

    Откуда: Таган-москва
    Сообщений: 4317
    andreymx,
    то есть, кроме того, что написал не глядя еще и ни разу не запустил для тестирования?) ну и кто после этого злобный буратино?)
    2 май 17, 12:20    [20449545]     Ответить | Цитировать Сообщить модератору
     Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
    andreymx
    Member

    Откуда: Запорожье
    Сообщений: 40890
    Vint
    andreymx,
    то есть, кроме того, что написал не глядя еще и ни разу не запустил для тестирования?) ну и кто после этого злобный буратино?)
    конечно, ты прав

    но иногда для какой-то процедуры расчета не имеет смысла проводить отдельное тестирование, ибо сама по себе она ничего не показывает
    при этом сам полный расчет занимает час-два, а их-то и нету
    вот и приходится совмещать тестирование и продакшн
    2 май 17, 12:34    [20449605]     Ответить | Цитировать Сообщить модератору
     Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
    xtender
    Member

    Откуда: Мск
    Сообщений: 4329
    Elic
    рантайм ошибки провоцируются характерными данными.
    ну не только же... как минимум, есть еще ошибки/опечатки, приводящие ко всяким устойчивым ошибкам независимо от данных


    Elic
    Несгруппированность выражения никакими данными не исправится - незачем оттягивать ошибку.
    :) кстати, касательно ошибки из топика: я где-то уже видел, когда в запросе поле таблицы именовалось так же как и функция, ну и редко-редко получали плавающие ошибки - не те данные получали
    2 май 17, 12:40    [20449635]     Ответить | Цитировать Сообщить модератору
     Re: Почему статанализатор PLSQL не отлавливает ORA-00979 not a group by expression  [new]
    Sonic86
    Member

    Откуда:
    Сообщений: 43
    Товарищи, спасибо большое за ответы! Тему ту я не нашел, к сожалению, сразу. Из текстов обеих тем ответ мне стал понятен.
    Жаль, сейчас книжку Льюиса на русском негде скачать.
    (Интересно, считается ли это багом, или Oracle вообще это обрабатывать не собираются? Ну хотя бы warning какой-нибудь выдать. Хотя конечно кто их знает.)

    Ошибки выявляемые при оптимизации, т.е. ошибки которые оракл выявляет при анализе трансформаций, типа ошибки из топика (ORA-00979, ORA-00937,...)
    А какие еще ошибки такого рода есть? Это тоже в 7-й главе Льюиса смотреть? Или это только в хардкорной документации есть?
    4 май 17, 22:37    [20458621]     Ответить | Цитировать Сообщить модератору
    Все форумы / Oracle Ответить