implicit conversion question 1

[jaxtell]

This query ran fine in 10.2.0.4. Now that I'm using 11g, It gives me an error ORA-01790

SELECT   emp.oracle_user_id AS value_passed,
         emp.full_name AS display_name
    FROM employee_ref emp
   WHERE EXISTS(SELECT 'x'
                  FROM action_history ah
                 WHERE ah.assigned_to_user_id = emp.oracle_user_id
                   AND ah.status = 'OPEN')
      OR EXISTS(SELECT 'x'
                  FROM MANUFACTURER_VW mfr
                 WHERE mfr.product_manager_user_id = emp.oracle_user_id)               
ORDER BY emp.full_name ASC

The problem is product_manager_user_id is stored as a varchar(150), all other user ids are stored as a number. What I think is strange is that adding to_number to the first subquery fixes the problem

SELECT   emp.oracle_user_id AS value_passed,
         emp.full_name AS display_name
    FROM employee_ref emp
   WHERE EXISTS(SELECT 'x'
                  FROM action_history ah
                 WHERE to_number(ah.assigned_to_user_id) = to_number(emp.oracle_user_id)
                   AND ah.status = 'OPEN')
      OR EXISTS(SELECT 'x'
                  FROM MANUFACTURER_VW mfr
                 WHERE mfr.product_manager_user_id = emp.oracle_user_id)               
ORDER BY emp.full_name ASC

This doesn't seem right to me. Is this the expected behavior? Why did the original query break when moved to 11g? Thanks.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Dagon]

Why are you adding TO_NUMBER to both sides when it's only assigned_to_user_id which is VARCHAR2 ?

For Oracle-related work, contact me through Linked-In.

 

[SantaMufasa]

Jaxtell,

I don't have an 11g available to test this out, but it would be interesting to know the results on your 11g of this query:

select 'It works' from dual where 1 = to_char(1);

If 'It works' displays, then 11g is doing an implicit conversion of one operand or the other.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

Santa,

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select 'It works' from dual where 1 = to_char(1);

'ITWORKS
--------
It works

SQL>

I have to stick my oar in here and say that implicit casts are like implicit insert statements - a bug, albeit sometimes dormant ones. That's why I can't resist pointing the "finger of blame" at any query that relies on implicit casting to work. The author of said query has effectively given up control of what it does.

Can I rant off without turning it on in the first place?

Regards

T

 

[Dagon]

Agreed. Even if it doesn't cause direct errors, implicit conversion can cause horrendous performance problems by preventing index use.

For Oracle-related work, contact me through Linked-In.

 

[jaxtell]

Santa and Tharg,
I'm not disagreeing with you. I simply wanted to know if it is supposed to work differently in 11g than 10g, or if it is a bug or configuration issue.

Dagon,
I wouldn't actually do that in production code. But one of my coworkers did it and thought he identified a different problem. I just wanted to know why it worked, and if that was the expected behavior.

I know a solution to the problem. I am only interested in why it wasn't an issue in 10g but is in 11g.

Thanks.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[karluk]

Jaxtell, I am unable to reproduce your ORA-01790 in my 11.1.0.7.0 database using the datatypes you indicate. Can you double check that the datatypes of the id columns are as you specified earlier?

SQL> select version from v$instance;

VERSION
-----------------
11.1.0.7.0

SQL> create table employee_ref (oracle_user_id number, full_name varchar2(100));

Table created.

SQL> create table action_history (assigned_to_user_id number, status varchar2(10));

Table created.

SQL> create table manufacturer_vw (product_manager_user_id varchar2(150));

Table created.

SQL> insert into employee_ref values (1234, 'KARLUK');

1 row created.

SQL> insert into action_history values (1234, 'OPEN');

1 row created.

SQL> insert into manufacturer_vw values('1234');

1 row created.

SQL> SELECT   emp.oracle_user_id AS value_passed,
  2           emp.full_name AS display_name
  3      FROM employee_ref emp
  4     WHERE EXISTS(SELECT 'x'
  5                    FROM action_history ah
  6                   WHERE ah.assigned_to_user_id = emp.oracle_user_id
  7                     AND ah.status = 'OPEN')
  8        OR EXISTS(SELECT 'x'
  9                    FROM MANUFACTURER_VW mfr
 10                   WHERE mfr.product_manager_user_id = emp.oracle_user_id)   
 11  ORDER BY emp.full_name ASC;


        1234   KARLUK

 

[jaxtell]

Sorry about the formatting. I rarely use SQLPlus, and just discovered its dos only for 11g.

SQL> select version from v$instance;

VERSION
-----------------
11.2.0.1.0

SQL> select table_name, column_name, data_type, data_length from dba_tab_cols wh
ere table_name in ('EMPLOYEE_REF', 'ACTION_HISTORY', 'MANUFACTURER_
VW') and column_name in ('ASSIGNED_TO_USER_ID','ORACLE_USER_ID','PRODUCT_MANAGER
_USER_ID')
  2  ;

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
DATA_TYPE
--------------------------------------------------------------------------------

DATA_LENGTH
-----------
MANUFACTURER_VW             PRODUCT_MANAGER_USER_ID
VARCHAR2
        150

EMPLOYEE_REF                ORACLE_USER_ID
NUMBER
         22

TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
DATA_TYPE
--------------------------------------------------------------------------------

DATA_LENGTH
-----------

ACTION_HISTORY          ASSIGNED_TO_USER_ID
NUMBER
         22


SQL> SELECT   emp.oracle_user_id AS value_passed,
  2           emp.full_name AS display_name
  3      FROM employee_ref emp
  4     WHERE EXISTS(SELECT 'x'
  5                    FROM action_history ah
  6                   WHERE ah.assigned_to_user_id = emp.oracle_user_id
  7                     AND ah.status = 'OPEN')
  8        OR EXISTS(SELECT 'x'
  9                    FROM MANUFACTURER_VW
 10                   WHERE product_manager_user_id = emp.oracle_user_id)

 11  ORDER BY emp.full_name ASC;
                 WHERE ah.assigned_to_user_id = emp.oracle_user_id
                       *
ERROR at line 6:
ORA-01790: expression must have same datatype as corresponding expression


SQL>

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[karluk]

I don't have any 11gR2 databases right now - although I plan on installing it in my R&D environment in the next few weeks - so I can't test this. I also can't find any indication on Oracle's web site that it's a known bug (or alternatively that 11gR2 has stricter datatype comparison rules). But to me this looks like a bug. It's especially strange that your workaround requires you to convert numeric columns to numeric.

If you are willing to spend time pursing this, rather than simply using your workaround, I suggest you open a Service Request with Oracle support. They could help you determine whether it's expected behavior or a newly introduced bug.

 

[jaxtell]

The "workaround" I've shown isn't what I'll actually use. I only came across it because my co-worker thought it was a problem comparing a number to a number(15). My guess was that it was a bug, but I'm not a fan of filing tars with oracle.

Thanks for the help.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[jaxtell]

Apparently it's a known bug. Workarounds include

alter session set "_optimizer_squ_bottomup"=false;

or

alter session set "_optimizer_unnest_all_subqueries"=false;

or

alter session set "_optimizer_unnest_disjunctive_subq"=false;

or use TO_NUMBER for explicit conversion.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/