Failing Linq query on element index

[kaltz1974]

I am using Nhibernate over Oracle and I am experiencig a failure on a query over a Linq statement of this kind:

Nhibernate.ISession session;
... start the session somehow

session.Query<Products>()
.Query<Product>()
.Where(p => p.OrderDetails.Any(od => od.Order.OrderDetails[0] == od))
.Count()

In this scenario Order has a sorted list of OrderDetail called OrderDetails.
Each Order Detail refers to a Product which has a bag of OrderDetail called OrderDetails.

The SQL wrongly generated is

    select
        cast(count(*) as NUMBER(10,
        0)) as col_0_0_ 
    from
        PRODUCTS product0_ 
    where
        exists (
            select
                orderdetai1_.ORDER_DETAIL_ID 
            from
                ORDER_DETAILS orderdetai1_ 
            left outer join
                ORDERS order2_ 
                    on orderdetai1_.ORDER_ID=order2_.ORDER_ID,
                ORDER_DETAILS orderdetai3_,
                ORDER_DETAILS orderdetai3_ 
            where
                product0_.PRODUCT_ID=orderdetai1_.PRODUCT_ID 
                and order2_.ORDER_ID=orderdetai3_.ORDER_ID 
                and orderdetai3_.INDEX_NUMBER = :p0 
                and orderdetai3_.ORDER_DETAIL_ID=orderdetai1_.ORDER_DETAIL_ID
        );

Oracle error is ORA-00918, but SQL is clearly wrong.

Thanks for your help.

Lorenzo

Classes

using System.Collections.Generic;

namespace Foo
{
    public class Order
    {
        private IList<OrderDetail> _orderDetails =
            new List<OrderDetail>();

        public virtual int Id { get; protected set; }

        public virtual IList<OrderDetail> OrderDetails
        {
            get { return _orderDetails; }
            protected set { _orderDetails = value; }
        }
    }

    public class Product
    {
        private IList<OrderDetail> _orderDetails =
            new List<OrderDetail>();

        public virtual int Id { get; protected set; }
        public virtual string Name { get; protected set; }

        public virtual IList<OrderDetail> OrderDetails
        {
            get { return _orderDetails; }
            protected set { _orderDetails = value; }
        }
    }

    public class OrderDetail
    {
        public virtual int Id { get; protected set; }

        public virtual Order Order { get; protected set; }
        public virtual Product Product { get; protected set; }
    }
}

Mappings

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns='urn:nhibernate-mapping-2.2'
                   assembly='Foo'
                   namespace='Foo'
                   >
  <class name='Order' table='ORDERS' >
    <id name='Id' column='ORDER_ID' >
      <generator class='assigned' />
    </id>

    <list name='OrderDetails' lazy="false" inverse='false' table='ORDER_DETAILS' >
      <key column='ORDER_ID' />
      <index column='INDEX_NUMBER' />
      <one-to-many class='OrderDetail' />
    </list>

  </class>
  
  <class name='Product' table='PRODUCTS' >
    <id name='Id' column='PRODUCT_ID' >
      <generator class='assigned' />
    </id>
    <property name='Name' column='NAME' />

    <bag name='OrderDetails' lazy="false" inverse='true' table='ORDER_DETAILS' >
      <key column='PRODUCT_ID' />
      <one-to-many class='OrderDetail' />
    </bag>

  </class>

  <class name='OrderDetail' table='ORDER_DETAILS'>
    <id name='Id' column='ORDER_DETAIL_ID' >
      <generator class='assigned' />
    </id>

    <many-to-one name='Order' class='Order' column='ORDER_ID' />
    <many-to-one name='Product' class='Product' column='PRODUCT_ID' />

  </class>

</hibernate-mapping>

[fredericDelaporte]

Fixed by #2890