ActiveRecord, Oracle oci8 and LEFT OUTER JOIN

Discussion in 'Ruby' started by Brian Candler, Mar 20, 2007.

  1. Hello,

    This is an ActiveRecord question / issue. I hope it's OK to raise it here(*)

    I'm using activerecord-1.15.2 as part of a Rails app. I started development
    using Sqlite3 as the backend for expediency (laptop programming on a
    plane!). I'm now trying to move it to Oracle 10.2. Unfortunately, the eager
    loading of related objects using :include seems to generate SQL which Oracle
    barfs on.

    The error message I get is:
    --------------------------------------------------------------------------
    Showing app/views/entities/show.rhtml where line #1 raised:

    OCIError: ORA-00920: invalid relational operator: SELECT entities.id AS
    t0_r0, entities.key_attribute_id AS t0_r1, entities.key_value AS t0_r2,
    entities.state AS t0_r3, avpairs.id AS t1_r0, avpairs.entity_id AS t1_r1,
    avpairs.attribute_id AS t1_r2, avpairs.value AS t1_r3 FROM entities LEFT
    OUTER JOIN avpairs ON avpairs.entity_id = entities.id WHERE (0 OR
    (entities.key_attribute_id=2 AND entities.key_value='vpn1s4') OR
    (entities.key_attribute_id=3 AND entities.key_value='000001'))
    --------------------------------------------------------------------------

    And the source which generates this query:

    cond = ["0"]
    links.each do |pri, key_attribute_id, key_value|
    cond.first << " OR (entities.key_attribute_id=? AND entities.key_value=?)"
    cond << key_attribute_id << key_value
    end
    entities = Entity.find:)all, :conditions => cond, :include => :avpairs)

    The 'links' array includes a list of keys to Entity objects that I wish to
    load, together with their linked Avpair objects. This all works just dandy
    under Sqlite3.

    Does this mean that AR eager loading can't work at all with Oracle? Or is
    there something specific about this particular query which is causing the
    problem?

    In the "Agile" book I see a footnote on page 362:
    "In fact, it might not work at all! If your database doesn’t support
    left outer joins, you can’t use the feature. Oracle 8 users, for instance,
    will need to upgrade to version 9 to use preloading."

    But then I'd have thought I'd be fine with Oracle 10.2

    The Oracle SQL reference starts at
    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/toc.htm
    and the documentation for SELECT is at
    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2065646

    It shows:

    join_clause ::= table-reference (inner | outer_join_clause)+

    outer_join_clause ::= ... outer_join_type JOIN table_reference ON condition

    outer_join_type ::= (FULL|LEFT|RIGHT) OUTER

    so I can't actually see why it would reject the SQL generated by AR. Any
    ideas?

    Regards,

    Brian.

    (*) At http://ar.rubyonrails.com/ it does say:

    "For other information, feel free to ask on the ruby-talk mailing list
    (which is mirrored to comp.lang.ruby) or contact "

    However if there's a more appropriate ActiveRecord mailing list please point
    me at it. There are a couple of other minor AR issues/suggestions I'd like
    to raise too.
    Brian Candler, Mar 20, 2007
    #1
    1. Advertising

  2. > OCIError: ORA-00920: invalid relational operator: SELECT entities.id AS
    > t0_r0, entities.key_attribute_id AS t0_r1, entities.key_value AS t0_r2,
    > entities.state AS t0_r3, avpairs.id AS t1_r0, avpairs.entity_id AS t1_r1,
    > avpairs.attribute_id AS t1_r2, avpairs.value AS t1_r3 FROM entities LEFT
    > OUTER JOIN avpairs ON avpairs.entity_id = entities.id WHERE (0 OR
    > (entities.key_attribute_id=2 AND entities.key_value='vpn1s4') OR
    > (entities.key_attribute_id=3 AND entities.key_value='000001'))


    Doh! Running the query under yasql shows the error to be at
    WHERE (0 OR ...
    and removing it makes the problem goes away. And this was really just
    laziness on my part in formulating the SQL statement builder.

    Sorry for the noise.

    Regards,

    Brian.
    Brian Candler, Mar 20, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. John Thomas

    LINQ left outer join

    John Thomas, Sep 1, 2009, in forum: ASP .Net
    Replies:
    1
    Views:
    855
    Patrice
    Sep 1, 2009
  2. PW

    Outer join problem

    PW, May 31, 2006, in forum: ASP General
    Replies:
    4
    Views:
    123
    Jason
    May 31, 2006
  3. Jason  Vogel
    Replies:
    4
    Views:
    277
    Jason Vogel
    Nov 21, 2006
  4. Peter Bailey
    Replies:
    11
    Views:
    517
    Vetrivel Vetrivel
    Nov 30, 2009
  5. Feyruz
    Replies:
    4
    Views:
    2,121
    Sherm Pendley
    Oct 14, 2005
Loading...

Share This Page