Oracle TO_DATE выдает ORA-01843 при использовании в WHERE, но не в SELECT.

Question

У меня проблема с (относительно) простым запросом, который я не понимаю, и я надеюсь, что кто-нибудь сможет мне в этом помочь.

Здесь у нас есть запрос:

      SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
  FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a
       WHERE  
        trunc(SYSDATE)
        BETWEEN 
         TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
        AND 
         TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')

Любопытная вещь в этом запросе заключается в том, что он выдает ORA-01843 , но только с предложением WHERE. Если я удалю предложение WHERE, ошибка не возникнет.

Так это работает

      SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
  FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a

Поскольку часть WHERE использует те же самые вещи, что и часть SELECT, я спрашиваю себя, как это возможно?

Некоторая справочная информация:

• Тип данных обоих столбцов (ABWENDDAT, ABWBEGDAT) — VARCHAR2(14).
• Я проверил содержимое столбцов, у нас есть только две записи, которые вызовут это исключение (записи: 99999999999999), но эти две записи фильтруются с помощью предложения WHERE ABWABTNR <> 'PASRZ'.
• Я также позаботился о том, чтобы все строки (для каждого оператора) были возвращены, поэтому после выполнения оператора я просматриваю все возвращенные строки (до конца).

---

Я также проверил stackoverflow и нашел несколько вопросов, которые идут в том же направлении, но я не нашел вопроса с ответом, который бы мне помог или объяснил поведение.

Я думаю, что причиной такого поведения может быть план выполнения (или приоритет выполнения). Таким образом, две строки, которые могут вызвать ошибку, фильтруются после WHERE, который выдает ORA-01843, но перед частью SELECT. может ли это быть правдой, и если да, есть ли у кого-нибудь идеи, как я могу изменить запрос, чтобы он работал?

Заранее спасибо!

Answer 1

Возможно, предикат

      TRUNC(sysdate) BETWEEN TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
                    AND TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')

помещается в ваш внутренний запрос, который должен отфильтровывать недаты.

      (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') 

что означает, что этот запрос фактически становится

      SELECT * 
  FROM babw 
 WHERE ABWABTNR <> 'PASRZ'
   AND TRUNC(sysdate) BETWEEN TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
                          AND TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')

причина вашей ошибки

«Лучшие» вещи по порядку:

• Сохраняйте даты только в столбце с типом данных даты.
• Найдите и исправьте неправильные даты.

При отсутствии любого из этих событий вы можете использоватьno_push_predподсказка, чтобы избежать помещения предиката во внутренний запрос

      SELECT /*+ no_push_pred(a) */
      TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
  FROM (SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ') a
       WHERE  
        trunc(SYSDATE)
        BETWEEN 
         TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
        AND 
         TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD')

Answer 2

SQL Engine предпочитает переписать ваш запрос без вложенных подзапросов, чтобы ваш первый запрос эффективно:

      SELECT TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM   babw
WHERE  ABWABTNR <> 'PASRZ'
AND    trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
                      AND     TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD')

иBETWEENпредложение оценивается до того, какABWABTNRсравнение.

Вы можете попробовать использовать подсказки, чтобы решить проблему. Или:

• /*+ no_push_pred(a) */во внешнем запросе; или
• /*+ no_merge */во внутреннем запросе.

Или вы можете материализовать внутренний запрос, используяROWNUM:

      SELECT TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM   (
  SELECT * FROM babw WHERE ABWABTNR <> 'PASRZ' AND ROWNUM > 0
) a
WHERE  trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8), 'YYYYMMDD')
                      AND     TO_DATE (SUBSTR (a.ABWENDDAT, 1, 8), 'YYYYMMDD');

Или вы можете использоватьCASEвыражение:

      SELECT TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD'),
       TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
FROM   babw
WHERE  CASE
       WHEN ABWABTNR <> 'PASRZ'
       AND  trunc(SYSDATE) BETWEEN TO_DATE (SUBSTR (ABWBEGDAT, 1, 8), 'YYYYMMDD')
                           AND     TO_DATE (SUBSTR (ABWENDDAT, 1, 8), 'YYYYMMDD')
       THEN 1
       END = 1;

Answer 3

Это возможно, потому что не только каждая строка в этой таблице имеет как .ABWENDDAT, так и .ABWENDDAT, чтобы их можно было преобразовать в дату с этим форматом. Если у вас версия Oracle >12, вы можете использовать TO_DATE (SUBSTR (a.ABWBEGDAT, 1, 8) по умолчанию, ноль при ошибке преобразования, «ГГГГММДД»). Если у вас есть одна строка, в которой это преобразование будет невозможно, и у вас нет значения по умолчанию, равного нулю при ошибке преобразования, то эта единственная строка вызовет исключение. Или, если версия Oracle <12, вам следует сначала отфильтровать строки, в которых данные в этих столбцах невозможно преобразовать к дате, используя выражение общей таблицы.