Ora 01841 ошибка

Коллеги-разработчики с радостью сообщили, что наконец-то сломали 12-й Oracle обнаружили проблему, а Александр Шакура подготовил отличный тесткейс:

12.1.0.2@ SQL> create table partit_Tab tablespace users
  2  PARALLEL ( DEGREE 16 INSTANCES 1 ) PARTITION BY RANGE(DT) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
  3  ( PARTITION p0 VALUES LESS THAN (TO_DATE('01-01-2005', 'DD-MM-YYYY')))
  4  as
  5  select sysdate dt from dual
  6  union all
  7  select sysdate-300 dt from dual
  8  union all
  9  select sysdate+300 dt from dual
 10  /
 
Table created
 
SQL> select * from partit_Tab ch
  2   where ch.dt < to_date('01-SEP-15','DD-MON-YY')
  3      or ch.dt between to_date('01-SEP-15','DD-MON-YY') and to_date('30-SEP-15','DD-MON-YY')
  4  /
 
DT
-----------
03.01.2015
 
SQL> select * from partit_Tab ch
  2   where ch.dt < date '2015-09-01'
  3      or ch.dt between date '2015-09-01' and date '2015-09-30'
  4  /
 
select * from partit_Tab ch
 where ch.dt < date '2015-09-01'
    or ch.dt between date '2015-09-01' and date '2015-09-30'
 
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

В процессе обследования выяснилось, что:

1) ORA-01841 возникает только для 4-х значных форматов года (YYYY), включая Date Literal вида date’2015-09-01′
2) Explain Plan возвращает ту же ошибку, при этом трейс оптимизатора драматически обрывается на преобразовании Table Expansion (TE):

Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
implied predicates generated: "CH"."START_DT"<TO_DATE('2015-09-01','yyyy-mm-dd') OR "CH"."START_DT">=TO_DATE('2015-09-01','yyyy-mm-dd') AND "CH"."START_DT"<=TO_DATE('2015-09-30','yyyy-mm-dd')

, а запрос при отключении этого преобразования select /*+ NO_EXPAND_TABLE(ch) */ … работает без ошибок

3) проблема характерна именно для 12.1.0.2, в предыдущих версиях оптимизатора проблем не обнаружено:

SQL> select/*+ optimizer_features_enable('12.1.0.1') */ * from partit_Tab ch
  2   where ch.dt < date '2015-09-01'
  3      or ch.dt between date '2015-09-01' and date '2015-09-30'
  4  /
 
DT
-----------
03.01.2015

С помощью тех.поддержки удалось найти свежее улучшение, оформленное в виде фикса:

SQL> @fix 14558315
 
   BUGNO VALUE SQL_FEATURE                DESCRIPTION                                             OPTIMIZER_FEATURE_ENABLE
-------- ----- -------------------------- ------------------------------------------------------- ------------------------
14558315     1 QKSFM_CARDINALITY_16615686 table expansion support for interval partitioned tables 12.1.0.2

, отключение которого на уровне системы, сессии или запроса устраняет проблему

Bug 14558315 (TABLE EXPANSION DOES NOT WORK WITH INTERVAL PARTITIONING) я уже упоминал в
Особенности преобразования Table Expansion, и с помощью кейса из этой заметки легко проверить, что баг 14558315 действительно уже исправлен в 12.1.0.2:

12.1.0.2.@ SQL> alter session set "_fix_control"='14558315:on'; -- включен (по умолчанию)

Session altered.

SQL> select/*+ expand_table(t)*/ * from t where x = 10; -- подсказка форсирует TE несмотря на Cost

         X          Y
---------- ----------
        10          1

1 row selected.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','',format => '+outline'));

PLAN_TABLE_OUTPUT
-------------------------------------------------
SQL_ID  81uwwbx7xp974, child number 0
-------------------------------------
select/*+ expand_table(t)*/ * from t where x = 10

Plan hash value: 3514974290

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |         |       |       |   122 (100)|          |       |       |
|   1 |  VIEW                                        | VW_TE_1 |     3 |    78 |   122   (3)| 00:00:01 |       |       |
|   2 |   UNION-ALL                                  |         |       |       |            |          |       |       |
|   3 |    PARTITION RANGE SINGLE                    |         |     1 |     8 |     2   (0)| 00:00:01 |     1 |     1 |
|*  4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T       |     1 |     8 |     2   (0)| 00:00:01 |     1 |     1 |
|*  5 |      INDEX RANGE SCAN                        | T_I     |     1 |       |     1   (0)| 00:00:01 |     1 |     1 |
|   6 |    PARTITION RANGE SINGLE                    |         |     1 |     8 |    41   (3)| 00:00:01 |     2 |     2 |
|*  7 |     TABLE ACCESS FULL                        | T       |     1 |     8 |    41   (3)| 00:00:01 |     2 |     2 |
|   8 |    PARTITION RANGE INLIST                    |         |     1 |     8 |    79   (3)| 00:00:01 |       |       |
|*  9 |     TABLE ACCESS FULL                        | T       |     1 |     8 |    79   (3)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('optimizer_index_caching' 10)
      ALL_ROWS
      ...
      EXPAND_TABLE(@"SEL$1" "T"@"SEL$1")      -- TE работает, см.план
      ...
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T"."Y"<2)
   5 - access("X"=10)
   7 - filter(("X"=10 AND "T"."Y">=2 AND "T"."Y"<3))
   9 - filter("X"=10)


SQL> alter session set "_fix_control"='14558315:off'; -- fix_control выключен

Session altered.

SQL> select/*+ expand_table(t)*/ * from t where x = 10;

         X          Y
---------- ----------
        10          1

1 row selected.

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor('','',format => '+outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
SQL_ID  81uwwbx7xp974, child number 1
-------------------------------------
select/*+ expand_table(t)*/ * from t where x = 10

Plan hash value: 3557914527

--------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |    79 (100)|          |       |       | -- TE не работает
|   1 |  PARTITION RANGE ALL|      |     1 |     8 |    79   (3)| 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | T    |     1 |     8 |    79   (3)| 00:00:01 |     1 |1048575|
--------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('optimizer_index_caching' 10)
      OPT_PARAM('_fix_control' '14558315:0')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      END_OUTLINE_DATA
  */

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("X"=10)

Из описания бага до сих пор следует, что исправление планируется в 12.2, Bugs fixed in each 12.1.0.2 Patch Set Update также не упоминает баг 14558315, разгадываем загадки с support-ом

UPD Bug 21606703 : ORA-1841 SELECTING FROM INTERVAL-PARTITIONED TABLE
Base Bug 21467081 INCONSISTENT RESULTS WHEN NOT USING TO_DATE FUNCTION

BETWEEN TO_DATE('20190401', 'yyyymmdd') AND TO_DATE('20190501', 'yyyymmdd')

Load 7 more related questions

Show fewer related questions

TO_DATE(TO_CHAR(datevalue, 'DD/MM/YYYY'),'DD/MM/YYYY') is removing any time component which is effectively the same as: TRUNC( datevalue ).

Then TO_DATE(SYSDATE , 'DD/MM/YYYY') is probably where your error lies as TO_DATE( stringvalue, format_model ) takes a string as the first argument so you are effectively doing:

TO_DATE(
  TO_CHAR(
    SYSDATE,
    ( SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT' )
  ),
  'DD/MM/YYYY'
)

It doesn’t make sense as SYSDATE is already of the DATE data type so you don’t need to use TO_DATE with it.

Finally, TO_CHAR(SYSDATE - datevalue)*-1 Why are you converting it to a string then multiplying it by a number when you can just do:

(SYSDATE - datevalue)*-1

But you don’t even need the *-1 as you can just swap the terms around:

(datevalue - SYSDATE)

Tidying it all up you want something like:

SELECT  MYTABLE." ID ", 
        NVL(
          MAX( TRUNC( ADD_MONTHS(MYTABLE." XISSU_DT " ,MYTABLE." XTNR ") ) ),
          SYSDATE
        ) MAXLASTINSDATE,
        SYSDATE, 
        ( NVL(
            MAX( TRUNC( ADD_MONTHS(MYTABLE." XISSU_DT " ,MYTABLE." XTNR ") ) ),
            SYSDATE
          )
          - SYSDATE
        ) AS MaturityPeriod
FROM   MYTABLE
where  MYTABLE." STATUS " = 'A'
group by MYTABLE." ID "