Oracle - Inline View - External WHERE condition applied inline

Question

We have a table where dates are stored as VARCHAR2(It's legacy data table which we have no control on!) in format of YYYYMMDD. We have only SELECT privilege on table (Not feasible to write procedure/functions).

Need to select all rows from table where date is > sysdate.

We apply regular expression check for valid format and additional checks to ensure that day is valid for given month. All this works great! Our inline view selection ensures that only records with valid date strings are picked.

But, when we apply condition to check > sysdate as an external clause - we get invalid date for given month error even though inline view selection ensures that no such records are picked.

It looks like query execution is applying the condition from outer clause before inline view conditions are applied. Appreciate any comments on the behavior; and, how can we ensure that conditions from outside are applied only once inline conditions are met?

Data and Used Queries:

CREATE TABLE TEST_DATA_TABLE
(
  DATESTRING  VARCHAR2(20 BYTE)
);

Insert 3 rows with values:

19960322 --Valid Date in past
19831131 --Invalid Date 11/31
20180224 --Valid Date > SYSDATE

Valid data selection: (Where clause 1 ensures format and clause 2 ensures valid date for month):

SELECT datestring AS i_dob
  FROM test_data_table
 WHERE     REGEXP_LIKE (TRIM (datestring),
                        '(19|20)\d\d(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])')
       AND TRIM (datestring) <=
              TO_CHAR (
                 LAST_DAY (
                    TO_DATE (SUBSTR (TRIM (datestring), 1, 6) || '01',
                             'YYYYMMDD')),
                 'YYYYMMDD')

Above query works fine and return valid rows with valid date string,

To select records having date string > SYSDATE, above data is used inline and we apply condition >SYSDATE as below.

SELECT i_dob
  FROM (
    SELECT datestring AS i_dob
      FROM test_data_table
     WHERE     REGEXP_LIKE (TRIM (datestring),
                            '(19|20)\d\d(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])')
           AND TRIM (datestring) <=
                  TO_CHAR (
                     LAST_DAY (
                        TO_DATE (SUBSTR (TRIM (datestring), 1, 6) || '01',
                                 'YYYYMMDD')),
                     'YYYYMMDD')
) X
 WHERE TO_DATE (X.I_DOB, 'YYYYMMDD') > SYSDATE

It starts throwing error: ORA-01839: date not valid for month specified

Looks like the conditions are applied before all inline view conditions are checked.

OK, this is an interesting question - thanks for posting it! Two observations so far (I will keep looking...) FIRST: instead of the WHERE clause as you have it, you could check for X.I_DOB > TO_CHAR(SYSDATE, 'YYYYMMDD'). Of course, this doesn't answer your question (and perhaps you need the date for other computations, where you really need to convert it to a date); just pointing out that for this specific query there's a workaround. — mathguy
SECOND Looking into hints; I thought /*+ ORDERED_PREDICATES */ should work, and indeed it does if you combine the two WHERE clauses into one (no subquery), but with the subquery - outer query structure it does not. I'll write back if I can figure out the correct hint for this (I thought NO_QUERY_TRANSFORMATION should work but it doesn't or I didn't use it correctly). — mathguy

Boneist Boneist · Accepted Answer · 2017-02-09T16:48:07

You can get around this by making the inline view be actioned before the outer query by adding an additional rownum column to it (the presence of the rownum column means that Oracle needs to calculate that column for the subquery it applies to before it can further filter on it.

So, your query would become:

SELECT i_dob
  FROM (
    SELECT datestring AS i_dob,
           rownum rn
      FROM test_data_table
     WHERE     REGEXP_LIKE (TRIM (datestring),
                            '(19|20)\d\d(0[1-9]|1[012])(0[1-9]|[12][0-9]|3[01])')
           AND TRIM (datestring) <=
                  TO_CHAR (
                     LAST_DAY (
                        TO_DATE (SUBSTR (TRIM (datestring), 1, 6) || '01',
                                 'YYYYMMDD')),
                     'YYYYMMDD')
) X
 WHERE TO_DATE (X.I_DOB, 'YYYYMMDD') > SYSDATE;

I would add a comment into your query explaining the presence of this column, otherwise some developer in the future may think "eh? This does nothing; I'll remove it!".

(Also of note is in 12.2, they have added/amended functions to allow for easier data validation, which would simplify your query when you get to 12.2!)

Oracle - Inline View - External WHERE condition applied inline

1 Answers