Re: [Modeling-users] toOne relationship returns base class instead of subcl

[Sebastien B. <sbi...@us...>]

I'm currently working on the issue, I thought I could share my current
thoughts.

So the problem w/ bug #1017127 is that the generated SQL is:
>     SELECT DISTINCT t0.ID, t0.FK_PERSON=20
>     FROM ADDRESS t0, PERSON t1
>     WHERE ( PERSON.ID =3D 1 ) AND ( ADDRESS.FK_PERSON =3D PERSON.ID )

while it should take into account the class hierarchy, so that both
PERSON and EMPLOYEE are traversed.

I was initially thinking of making as many SQL queries as there are
pssible paths through the relationships (here, two: ADDRESS to PERSON,
and ADDRESS to EMPLOYEE) --but then I remembered these could SQL queries
could be UNIONed

So:
    ec.fetch('Address', 'Person.id =3D=3D 1')

should trigger the following query on the database [1]:

    SELECT DISTINCT t0.ID, t0.FK_PERSON
      FROM ADDRESS t0
          INNER JOIN EMPLOYEE t1 ON t0.FK_PERSON=3Dt1.ID
      WHERE t1.ID =3D 1
    UNION
    SELECT DISTINCT t0.ID, t0.FK_PERSON
      FROM ADDRESS t0
           INNER JOIN PERSON t1 ON t0.FK_PERSON=3Dt1.ID
      WHERE t1.ID =3D 1


NB: Incidentally, that UNION thing could also be a solution for the
    current limitation of sort orderings that cannot be used w/ the
    isDeep flag being set! Another story, though


Now the question is: suppose I have two plus two paths in the same fetch
spec., such as in:

  AND:
    ec.fetch('Address', 'person.id in [1,2,3] AND person.x.y like...')

  OR:
    ec.fetch('Address', 'person.id in [1,2,3] OR person.x.y like...')

--> In the case of the 'AND' operator, do the two 'Person' refer to the
    same table? In other words, do we get only two SELECTs union'ed?

    In the case of the 'OR' operator, do the two 'Person' refer
    independant tables, either the same or different ones? In other
    words, do we get 2*2=3D4 SELECTs union'ed?=20=20=20
    NB:  2=3D=3Dlen([Person, Employee])

Is this what we want here, I fear I could miss something here?  Any
comments will be appreciated!!

-- S=E9bastien.



[1] w/ INNER JOINs or the equivalent WHERE clause if inner joins aint
    supported.

Sebastien Bigaret <sbi...@us...> writes:
> John Lenton <jl...@gm...> wrote:
> > On 29 Jul 2004 10:30:02 +0200, Sebastien Bigaret
> > <sbi...@us...> wrote:
> > >=20
> > >   Again, could you be a little more explicit, or exhibit a short
> > > example?  Sorry for getting back on this, but the only way I think th=
is
> > > can happen is the one I already posted (see below); if it's not the
> > > case, this is a serious bug I definitely do not want to let escape.
> >=20
> > sorry for the delay in answering, and the confusion when doing so. I
> > feel terribly embarrassed :(
>=20
>   Please don't feel embarrassed, that was worth waiting for it.
>=20
> > HOWever, here goes.
> >=20
> > With the attached model (which is ugly, and breaks some of your naming
> > conventions, but I just hacked out the attributes and renamed things
> > from the model being used by a team I'm helping to get up to speed),
> > using vainilla MDL 0.9-pre17.1 and NF 0.7:
> >=20
> >=20
> > >>> from Modeling.EditingContext import EditingContext
> > >>> import Store
> > >>> ec=3DEditingContext()
> > >>> ec.fetch('Address')[0]
> > <Address.Address object at 0x4071c74c>
> > >>> a =3D _
> > >>> p =3D ec.fetch('Person', isDeep=3D1)[0]
> > <Employee.Employee object at 0x4072d88c>
> > >>> p =3D _
> > >>> a.getPerson()
> > <Employee.Employee object at 0x4072d88c>
> > >>> a.getPerson().getId()
> > 1
> > >>> a.isFault()
> > False
> > >>> p.getAddress()
> > <Modeling.FaultHandler.AccessArrayFaultHandler instance at 0x40733a4c>
> > >>> list(p.getAddress())
> > [<Address.Address object at 0x4071c74c>]
> > >>> ec.fetch('Address', 'Person.id =3D=3D 1')
> > []
> > >>> ec.fetch('Address', 'Person.id =3D=3D 1', isDeep=3D1)
> > []
>=20
>   Wooo, I didn't even realize how serious this was.  The 'isDeep' flag
>   is unfortunately of no help here, because the problem is not with
>   Address and its underlying hierarchy (controlled by 'isDeep' flag) but
>   because of the generated SQL statement which  obviously doesn't take
>   into account the sub-entities for entity Person:
>=20
>     SELECT DISTINCT t0.ID, t0.FK_PERSON=20
>     FROM ADDRESS t0, PERSON t1
>     WHERE ( PERSON.ID =3D 1 ) AND ( ADDRESS.FK_PERSON =3D PERSON.ID )
>=20
> I've just submitted bug item #1017127 at
> https://sourceforge.net/tracker/index.php?func=3Ddetail&aid=3D1017127&gro=
up_id=3D58935&atid=3D489335,
> with your model and the corresponding code attached.
>=20
>   I'll search a definitive fix soon --my current thinking being about
>   the way that would allow even more complicated qualifiers to be
>   correctly handled as well, such as with: person.addresses.id in [2,3]
>   or w/ a qualifier traversing two or more relationships involving
>   entities with sub-entities.
>=20
>         Thanks a lot for the precise report,=20
>=20
> -- S=E9bastien.