On our project we have needs to make data federalization. We need split one table on separate tables depending of specialty. To work with those tables I use nhibernate feature union-subclass.
For example we have 2 base entities: Animal and Box. Animal has reference to Box.
To split tables we need to do some steps:
1. Create 2 separate tables CatBox and DogBox instead of Box with fields (Id,Name) and tables Cat and Dog instead of Animal (ID, Name, BoxId).
Create sequences for ids: animal_id_seq and box_id_seq.
2. Create new classes Cat and Dog and inherit it from Animal. Same for CatBox and DogBox
3. Create mappings.
I have 2 scenarios of using:
1. Select special type of animal, for example Cats
2. Select all animals
When I do that without additional conditions all works good. But when I try add alias and condition to referred class Box, then happen strange thing.
1. In the first scenario all work like I expected.
2. In the secont scenario here is an mistake:
You see here is 2 same alias name and of course SQL error
I Think in this case inner join should be inside of UNION group. Here will be mistake even if we will have different names for property box because of inner join which should be inside of unions.