When ANSI SQL Join Syntax Does Not Work in Oracle

I have lost confidence in Oracle’s ANSI style SQL. Eric, in his comment to the post Back to basics: anti-joins and semi-joins, warned me of the following gotcha:

If you are writing queries that select from many tables, like denormalizing data for warehousing, and the sum of the columns in those tables exceeds 1,050, you’ll always get ORA-01445. This occurs from 9i through 10g2, and has happened often enough to me that I avoid my preference for ANSI syntax on Oracle databases, unless I’m just selecting from a few tables.

Well, it just happened to me. Below are two queries. They both select from standard Oracle E-Business Suite (11.5.10) tables. The two queries are identical except that the first uses the ANSI style syntax, and the second uses the Oracle syntax. As you can see below, the query that uses ANSI joins failed miserably with the error:

ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table.

This looks like a bug to me. Interestingly, if you change the SELECT NULL to SELECT COUNT(*), the query executes successfully. 

APPS@sigma> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod
PL/SQL Release 10.1.0.5.0 - Production
CORE    10.1.0.5.0      Production
TNS for Linux: Version 10.1.0.5.0 - Production
NLSRTL Version 10.1.0.5.0 - Production

APPS@sigma> SELECT NULL
  2    FROM oe_order_headers_all header INNER JOIN oe_order_lines_all line
  3         ON header.header_id = line.header_id
  4         INNER JOIN ra_customers cust ON header.sold_to_org_id = cust.customer_id
  5         INNER JOIN mtl_system_items_b item
  6         ON item.inventory_item_id = line.inventory_item_id
  7       AND item.organization_id = line.ship_from_org_id
  8         INNER JOIN org_organization_definitions org
  9         ON org.organization_id = line.ship_from_org_id
 10         LEFT OUTER JOIN ra_salesreps_all salesrep
 11         ON salesrep.salesrep_id = header.salesrep_id
 12       AND salesrep.org_id = header.org_id
 13         LEFT OUTER JOIN hr_locations_all sales_loc
 14         ON sales_loc.location_id = salesrep.attribute1
 15   WHERE header.orig_sys_document_ref = 'OE_ORDER_HEADERS_ALL74846'
 16     AND header.order_source_id = 0;

       LEFT OUTER JOIN hr_locations_all sales_loc
                       *
ERROR at line 13:
ORA-01445: cannot select ROWID from, or sample, a join view without a
key-preserved table

Now, using the good old Oracle SQL syntax:

APPS@sigma> SELECT NULL
  2    FROM oe_order_headers_all header,
  3         oe_order_lines_all line,
  4         ra_customers cust,
  5         mtl_system_items_b item,
  6         org_organization_definitions org,
  7         ra_salesreps_all salesrep,
  8         hr_locations_all sales_loc
  9   WHERE header.header_id = line.header_id
 10     AND header.sold_to_org_id = cust.customer_id
 11     AND item.inventory_item_id = line.inventory_item_id
 12     AND item.organization_id = line.ship_from_org_id
 13     AND org.organization_id = line.ship_from_org_id
 14     AND salesrep.salesrep_id(+) = header.salesrep_id
 15     AND salesrep.org_id(+) = header.org_id
 16     AND sales_loc.location_id(+) = salesrep.attribute1
 17     AND header.orig_sys_document_ref = 'OE_ORDER_HEADERS_ALL74846'
 18     AND header.order_source_id = 0;

N
-


2 rows selected.

Another interesting observation is that I could not find any reference to the ORA-01445 error in the Oracle Database Error Messages document for versions 10.1 and 10.2. I did however find the following references:

Possibly related:

Tagged , | Post a Comment