msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

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 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:


Filed in Joins, Oracle on 14 Jun 07 | Tags: ,


Reader's Comments

  1. |

    Yes its a bug. Although I haven’t had any success getting official confirmation of that.

    My workaround is to continue using ANSI join syntax but to use in-line tables in my from clause.

    e.g. instead of

    FROM table_a a JOIN table_b b ON a.fk=b.pk

    I use FROM (SELECT fk, other_col FROM table_a) a JOIN (SELECT pk, other_other_col FROM table_b) b ON a.fk = b.pk

    Its a nasty bodge but still gives much more readable SQL (IMHO of course).

  2. |

    Sorry, where I said readable SQL what I should have added is that it is more readable because it still allows you to separate the join predicates from the actual where predicates.

    Which is my major problem with the ‘good old’ Oracle syntax.

  3. |

    Eep definitely a bug. Does it happen when the total number of columns selected exceeds 1050, or if the total number of columns in the tables exceeds 1050 (regardless of which columns are actually specified)?

  4. |

    Judging from the example Eddie it is the total number of colums, regardless of how many columns are actually selected. Select null from … tells me that it doesn’t matter how many columns are actually selected ;-). I would have to try this out myself, but it appears I don’t want to switch over to Ansi SQL, when there is no need for it. Good pointer, thanks Eddie.

  5. |

    A dump question, but who has 1050 columns in 1 table or 7 tables? That are still 150 each!

    I would question the data model…

    Patrick

  6. |

    Let’s see how many total columns exist in the tables I select from in my example above:

    APPS@sigma> SELECT COUNT (*)
      2    FROM all_tab_columns
      3   WHERE table_name IN
      4            ('OE_ORDER_HEADERS_ALL',
      5             'OE_ORDER_LINES_ALL',
      6             'RA_CUSTOMERS',
      7             'MTL_SYSTEM_ITEMS_B',
      8             'ORG_ORGANIZATION_DEFINITIONS',
      9             'RA_SALESREPS_ALL',
     10             'HR_LOCATIONS_ALL'
     11            );
    
      COUNT(*)
    ----------
          1072
    

    The table OE_ORDER_LINES_ALL alone has 340 columns. These are all standard Oracle EBS tables.

  7. |

    As I already said, “interesting” data model :-)

    Never had to do anything with Oracle Applications, probably SAP will not be better from a data model point of view.

    Patrick

  8. |

    what about this…

    select u1.object_name, u1.object_type, u2.object_type, u2.object_type from all_objects u1 full outer join all_objects u2 on (u1.object_name=u2.object_name);

    from all_objects u1 full outer join all_objects u2 on * ERROR at line 2: ORA-00904: “XML_SCHEMA_NAME_PRESENT”.”IS_SCHEMA_PRESENT”: invalid identifier

  9. |

    well, to make sense the query condition should contain

    and u1.owner=’FOO’ and u2.owner=’BAR’

    but the bug is the same… not found on metalink yet

  10. |

    Thanks for pointing out another ANSI join error Laurent. Too bad there is no corresponding FULL OUTER JOIN syntax in the “good old” Oracle join syntax, so we can compare. I wonder if Oracle DB 11g had these bugs fixed.

  11. |

    I just had this problem reported to me and as I was provided nice little(ish) test case I was able to take an export of the schema involved and try it on Oracle 11g.

    I’m happy to say that the query that was failing in Oracle 10.2.0.3.0 interim patch 12 works fine in Oracle 11.1.0.6.0.

    I haven’t found the bit of documentation that stated if the 1050 limit has been increased but I suspect this it what has changed.

    I suspect also that the Oracle pseudo columns are included in the limit as the number of columns in the query that was failing was only just over 1000.

  12. |

    Nice looking site providing detailled information about sql syntax: http://www.sqlexikon.de have a look (ooch, this site is german)

  13. |

    I’m experiencing this problem and it erks me that I have to switch my queries from ANSI to non-ANSI. However it erks me even more that I have to deal with a table with over 300 columns!!! I find it staggering that any professional organisation that relies on such a database for mission critical operations involving billions for euros can allow such a thing to happen.

  14. |

    Take a look at the Query itself… putting a (+) on the Left Side does not mean a LEFT OUTER JOIN as you have written… i mean for example in the old way if we put the (+) on RIGHT side that actually should interprete as LEFT OUTER JOIN keeping the Tables in the same sqquence… so please check the Query itself… sould work… simple stuff!

  15. |

    Question? What syntax is more efficient for writing PLSQL… ANSI or the old Oracle way?? When I say efficient is performance, and maintainability wise.. Thanks

  16. |

    Juanky, you meant writng SQL not PL/SQL, right? Performance should be the same unless proven otherwise. I personally prefer ANSI SQL because it’s easier to read.

  17. |

    I personally prefer Oracle’s was because it’s easy to read, and i personally find the ANSI syntax to be very hard to follow especially once it starts to nest.