CODEWITHSUNDEEP
sqloracle-database
Lokomotywa
Lokomotywa

Reputation: 2844

Oracle "Select Level from dual" does not work as expected with to_number result

Why does 

select *
from (
   SELECT LEVEL as VAL
   FROM DUAL
   CONNECT BY LEVEL <= 1000
   ORDER BY LEVEL
) n
left outer join (select to_number(trim(alphanumeric_column)) as nr from my_table
where NOT regexp_like (trim(alphanumeric_column),'[^[:digit:]]')) d
on n.VAL = d.nr
where d.nr is null 
and n.VAL >= 100

throw a ORA-01722 invalid number (reason is the last row, n.VAL), whereas the similar version with numeric columns im my_table works fine:

 select *
    from (
       SELECT LEVEL as VAL
       FROM DUAL
       CONNECT BY LEVEL <= 1000
       ORDER BY LEVEL
    ) n
    left outer join (select numeric_column as nr from my_table) d
    on n.VAL = d.nr
    where d.nr is null 
    and n.VAL >= 100

given that numeric_column is of type number and alphanumeric_column of type nvarchar_2. Note that the upper example works fine without the numerical comparison (n.VAL >= 100).

Does anybody know?

Upvotes: 3

Views: 927

Answers (2)

tbone
tbone

Reputation: 15473

I believe when it comes to the Predicate (where) clause, Oracle can/will reorder the entire plan as it sees fit. So with regard to the predicate, it will short-circuit (as OldProgrammer noted) the evaluation however it wants, and you wont be able to guarantee the exact order it occurs.

In your current SQL, you are depending on the predicate to remove non numbers. One option would be to not use "WHERE NOT regexp_like ..." and instead use regexp_substr with coalesce. For example:

create table t_tab2
(
  col varchar2(10)
);

create index t_tab2_idx on t_tab2(col);

insert into t_tab2
select level from dual
connect by level <= 100;

insert into t_tab2 values ('123ABC456');
commit;

-- select values > 95 (96->100 exclude non numbers)
select d.* from 
(
  select COALESCE(TO_NUMBER(REGEXP_SUBSTR(trim(col), '^\d+$')), 0) as nr
  from t_tab2
) d
where d.nr > 95;

This should run without throwing invalid number error. Note that the coalesce will return the number 0 for any non numbers coming from the data, you may want to change that based on your needs and data.

Upvotes: 1

OldProgrammer
OldProgrammer

Reputation: 12169

This problem was driving me crazy. I narrowed the problem to a simpler query

SELECT *
  FROM (SELECT TO_NUMBER(TRIM (alphanumeric_column)) AS nr
          FROM my_table
         WHERE NOT REGEXP_LIKE (TRIM (alphanumeric_column), '[^[:digit:]]')) d
 WHERE d.nr > 1

With alphanumeric_colum values of ('100','200','XXXX'); Running the above statement gave the "invalid number" error. I then made a slight change to the query to use the CAST function instead of TO_NUMBER:

SELECT *
  FROM (SELECT CAST (TRIM (alphanumeric_column) AS NUMBER) AS nr
          FROM my_table
         WHERE NOT REGEXP_LIKE (TRIM (alphanumeric_column), '[^[:digit:]]')) d
 WHERE d.nr > 1

And this correctly returned - 100, 200. I would think that those functions would be similar in behavior. It almost appears as though oracle is trying to evaluate the d.nr > 1 constraint before the view is constructed, which makes no sense. If anyone can shed light on why this is happening, I would be grateful. See SQLFiddle example

UPDATE: I did some more digging, because I don't like not knowing why something just works. I ran EXPLAIN PLAN on both queries and got some interesting results.

For the query that failed, the predicate information looks like this:

   1 - filter(TO_NUMBER(TRIM("ALPHANUMERIC_COLUMN"))>1 AND  NOT 
              REGEXP_LIKE (TRIM("ALPHANUMERIC_COLUMN"),'[^[:digit:]]'))

You will notice that the TO_NUMBER function is called first in the AND condition, then the regexp to exclude alpha values. I am thinking oracle maybe does a short-circuit evaluation with the AND condition, and since it is executing TO_NUMBER first, it fails.

However, when we use the CAST function, the evaluation order is swapped, and the regexp exclusion is evaluated first. Since for the alpha values, it is false, then the second part of the AND clause is not evaluated, and the query works.

   1 - filter( NOT REGEXP_LIKE (TRIM("ALPHANUMERIC_COLUMN"),'[^[:digit:]
              ]') AND CAST(TRIM("ALPHANUMERIC_COLUMN") AS NUMBER)>1)

Oracle can be strange sometimes.

Upvotes: 3

Related Questions