ManyToMany - Tries to select not existing column in Mapping Table

[mayermart]

Hi,

i try to setup a ManyToMany Reference between two Tables using a Mapping Table in Oracle:

CREATE TABLE "A" (
    "ID"    NUMBER NOT NULL ENABLE,
    "VALUE" VARCHAR2(20 BYTE),
    CONSTRAINT "A_PK" PRIMARY KEY ( "ID" ) ENABLE
);

CREATE TABLE "B" (
    "ID"    NUMBER NOT NULL ENABLE,
    "VALUE" VARCHAR2(20 BYTE),
    CONSTRAINT "B_PK" PRIMARY KEY ( "ID" ) ENABLE
);

CREATE TABLE "AB" (
    "A" NUMBER NOT NULL ENABLE,
    "B" NUMBER NOT NULL ENABLE,
    CONSTRAINT "AB_PK" PRIMARY KEY ( "A", "B" ) ENABLE,
    CONSTRAINT "AB_FK1" FOREIGN KEY ( "A" ) REFERENCES "A" ( "ID" ) ON DELETE CASCADE ENABLE,
    CONSTRAINT "AB_FK2" FOREIGN KEY ( "B" ) REFERENCES "B" ( "ID" ) ON DELETE CASCADE ENABLE
);

With following Mapping

    public class AMap : ClassMapping<A>
    {
        public AMap()
        {
            this.Table("A");
            this.Id(x => x.Id, mapper => { mapper.Column("ID"); });
            this.Set(
                x => x.Bs,
                collectionMapping =>
                {
                    collectionMapping.Table("AB");
                    collectionMapping.Key(k => k.Column("A"));
                },
                map => map.ManyToMany(m => { m.Column("B"); }));
        }
    }
    public class BMap : ClassMapping<B>
    {
        public BMap()
        {
            this.Table("B");
            this.Id(x => x.Id, mapper => { mapper.Column("ID"); });
            this.Set(
                x => x.As,
                collectionMapping =>
                {
                    collectionMapping.Table("AB");
                    collectionMapping.Key(k => k.Column("B"));
                },
                map => map.ManyToMany(m => { m.Column("A"); }));
        }
    }

If i try to do a future select with SelectMode.ChildFetch to fill the whole graph i get an Error because NHibernate creates a SQL containing a column which does not exist:

IList<A> list;
using (var session = sessionFactory.OpenSession())
{
	session.QueryOver<B>().Fetch(SelectMode.ChildFetch, b => b.As).Future();
	var futureAs = session.QueryOver<A>().Fetch(SelectMode.ChildFetch, a => a.Bs).Future();
	list = futureAs.ToList();
}

=>

ORA-00904: "AS2_"."ID": ungültige ID
SELECT
    this_.id AS id1_2_1_,
    as2_.id  AS id1_0_0_, -- column does not exist
    a3_.id   AS id1_0_0_
FROM
    b  this_
    LEFT OUTER JOIN ab as2_ ON this_.id = as2_.b
    LEFT OUTER JOIN a  a3_ ON as2_.a = a3_.id

The same definition works if i run it with SelectMode.Fetch:

IList<A> list;
using (var session = sessionFactory.OpenSession())
{
	session.QueryOver<B>().Fetch(SelectMode.Fetch, b => b.As).Future();
	var futureAs = session.QueryOver<A>().Fetch(SelectMode.Fetch, a => a.Bs).Future();
	list = futureAs.ToList();
}

=>

SELECT
    this_.id AS id1_0_1_,
    bs2_.a   AS a1_1_3_, -- rudundancy here too?
    b3_.id   AS b2_1_3_,
    b3_.id   AS id1_2_0_ -- rudundancy here too?
FROM
    a  this_
    LEFT OUTER JOIN ab bs2_ ON this_.id = bs2_.a
    LEFT OUTER JOIN b  b3_ ON bs2_.b = b3_.id

Where does that "as2_.id AS id1_0_0_" come from? Do i have some error in my Mapping? Am i missing or misunderstanding something?
If i change the name of the primary key of the the tables A and B the queried column name changes accordingly.

As there are actually multiple ManyToMany relations i'd prefere not to do a whole fetch on each relation.

Thanks in advance
Martin

[bahusoid]

Seems ChildFetch is broken for many-to-many case. Though I don't think you are using it correctly. ChildFetch should be applied for parent object. More realistic scenario would look something like

session.QueryOver<B>().Future(); //Preload all Bs in separate query

var futureBs = session.QueryOver<B>()
	//We already loaded all Bs, so skip it from loading but keep id to properly child fetch others B associations
	.Fetch(SelectMode.ChildFetch, b => b)
	// List of associations to fetch for B:
	.Fetch(SelectMode.Fetch, b => b.As) 
	.Future();

[mayermart]

Thanks for clearifying the usage of SelectMode.ChildFetch. I now also found the Tests using it that way at SelectModeTests.cs

But with your example the graph will be filled in one direction only.
b.As will be populated
b.As.First().Bs.Any() will throw LazyInitializationException

Do i have to query in both directions even if all information should be already available or is there another point i missed?

[hazzik]

Fixed in #3140 (5.3.13)