two things: 1) obeyweakrelations is a legacy flag. It was introduced before we introduced join hints. I.o.w.: forget obeyweakrelations and set join hints. 2) when you specify a subtype's PK field in the projection, it will add a type filter. This is because you indicated that the subtype's rows have to be fetched. See it like this: Person <- Employee and Person <- Customer. I then add Employee.Id and Employee.Name to a typedlist with Employee. I then fetch this typed list.

Both fields are defined in Person. However, because I indicated I want employee fields, I don't want Id and Name fields which belong to a Customer, a sibling in the inheritance hierarchy. This means that a typefilter is added. Otherwise you would have to add the typefilter yourself, which is silly, you specifically stated employee.id and employee.name.

If you don't want the type filter, specify the supertype's PK field. In my example above, if I want all names and all Ids, I should add Person and select Person.Id and Person.Name instead of Employee.Id and Employee.Name.

I hope this describes why the framework appends the type filter.

Now to the left/right joins and the problem with this: there's a conflict. The subtype is in another table. It's determined to be a subtype if the pk field of the subtype's table isn't null. (what the type filter does). However, due to the left join, it can also be null if there's simply no data, i.e. due to the left join.

This is something that can't be solved: the type filter is needed, but due to the left join it limits rows due to the left join. Removing it also includes sibling rows and supertype rows in the result, something you don't want, you specified subtype fields.

It can be the query can be formulated differently. If you could describe the query intention, it might be we can help you formulate the query differently so you get the results you want and it works as expected.

The scenario you describe here is an 'is a' relationship. My scenario pertains to a 'has a' relationship involving inherited types. So I want a Complaints Typed List with all complaints. Complaints have 0/1 Beneficiaries, 0/1 Subjects, 0/1 Complainants. Those types are 'persons'. the typed list will include fields from those types. In this case if the LEFT join hint flag is raised (especially if every table in the typed list has a left hint) I would think LLBLGEN would be able to simply skip the where clause filter?? It certainly isn't necessary from a strictly sql point-of-view.

Complaint 'has a' optional Complainant, Beneficiary, Subject Person <- Complainant Person <- Subject Person <- Beneficiary

As they're all subtypes, the subtype filter is applied, as I described above. The filter can't be removed, because the data joined will also be including Person data, and other Person subtypes not in the typed list, because there's no type filter. That's the problem I was trying to describe: a row which is a person but not a Complainant has the fields set to NULL, but they're also null when the left join is used: so the code can't decide what it is: is it a Person row (and not a Complianant row!) or is it null because of the left join? As there's just 1 value, NULL, it's impossible to say.

After analyzing it more (it's not the first time this pops up) we came to the conclusion that there is a way to avoid the where clause, but only if the relationships are directly with the subtypes (so between Complaint and Subject, and not between Complaint and Person), because as the relationship is with the subtype, a supertype and sibling will never be joined over that relationship, so a type filter isn't necessary. If the relationship is between Complaint and Person, the type filter IS necessary, as a subtype field is in the projection, the problem I tried to describe in my previous post.

We think we can build this into the code, the question is, if we do so will that help you out in this problem? Another thing we can add is a flag on the typed list to not to include inheritance filters. This is rather lame, so we want to avoid that for now and go the auto-check route.

Please confirm that the relationships are all directly with the subtypes, and not with Person (so in other words: all subtypes in your typed list are directly related to Complaint, they DONT inherit the relationship from Person).

Well, I'll just give you more information and let you answer that question(safer that way).

Basically I'm just selecting the ComplaintID and the Name of the Complainant. The name of the Complainant is in the person table(the supertype of Complainant). The same thing goes for the Beneficiary which is sometimes null. The Complaint table contains the ComplainantPersonID from the Complainant table. The Complainant table contains the ComplainantPersonId which maps to the PersonID in the Person table. The tables are aliased and I use left joins.

So the designer is just Complaint C left Complainant Com

When LLBL generates the sql - I simply remove the type filter(where clause) to get the correct result set.

Thanks

Correction: My last statement about the correct result set is wrong - it may have brought back all the Complaint id's and other info (and firstname, lastname fields) but still had nulls for the 'fullname' field -.

Thanks. We'll see what we can do. It will be difficult to detect the situation, but we have an idea what to test for and hopefully we can get a working setup which filters when it's needed and doesn't filter when it's not needed Will be tomorrow (tuesday)

Thanks, that sounds very interesting - very confusing to work through for me . I do have a correction to my last correction - I went back and checked that data where I removed the where clause from generated sql and I did in fact get back 'fullname' for Beneficiaries, 'fullname1' for complainants, and 'fullname2' for subjects - so it was as expected actually.

Thanks again and good luck!

I need the actual typed list in your project, so I need your llblgenproj file. Could you mail that to us please? support AT llblgen DOT com.

The problem is that your descriptions above suggest you have fields from the base class in the typed list, which would make it impossible to fix:

Employee <- Manager. Employee <- Clerk. Employee m:1 Department

Say I have a typed list with Department.Name and Manager.Name. I get a query like: SELECT d.Name As DepartmentName, e.Name As ManagerName FROM Department d LEFT JOIN Employee e ON d.DepartmentId = e.WorksForDepartmentId LEFT JOIN Manager m ON e.EmployeeId = m.EmployeeId WHERE m.EmployeeId IS NOT NULL

If I remove the where clause, Clerks also match the query. But the typedlist explicitly specifies: Manager.Name, and a Clerk is a sibling of Manager: each clerk has a row in Employee and works for a department, however they don't have a row in Manager. For each clerk, m.EmployeeId is NULL. To have the filter on the query, it will weed out the clerks as they will have a NULL for m.EmployeeId. The problem is, that managers also can have a NULL for m.EmployeeId, due to the left join. This is the problem.

It can be solved if the relationship between the entities is directly defined with the subtype. In that case, siblings won't be included in the query result. Same hierarchy, but now we'll use Manager m:1 Department, which is defined over Manager.ManagesDepartmentId -> Department.DepartmentId.

SELECT d.Name As DepartmentName, e.Name As ManagerName FROM Department d LEFT JOIN Manager m ON d.DepartmentId = m.ManagesForDepartmentId INNER JOIN Employee e ON e.EmployeeId = m.EmployeeId WHERE m.EmployeeId IS NOT NULL

Here, we can remove the where clause, because due to the direct relationship between Department and Manager, it's not possible to have Clerk's show up in the query: only rows from Employee which are managers are included. Here, the where clause is actually hurting the results, so in this case it should be avoided.

If your typed list is equal to the first, it won't be solvable. If your typed list is equal to the second, it is solvable. As I can't determine from your info in this thread what it is exactly (I think it's equal to the first), I don't know whether a fix for this will help you: if it's equal to the first, a fix for this won't help you.

The point why this isn't fixed in the first place is because it's an edge case and it's a little complex to determine (with the knowledge we have now) which entities need a type filter and which entities don't. For situations like this, we wait till a point release to fix it as there's no need to fix it today (the situation is rare)

I hear you on the complex part! I'll send you a complete package. In short, my situation is more like the second for sure. It really is not that much an edge case though - its simply a 'has a' table. The id columns in the 'has a' table Complaint is an unique identity - 'BeneficiaryPersonID' column, ComplainantPersonId col, SubjectPersonID etc. Just a single person for each. Now in your first example - what you really mean is 'Where getType() == 'ManagerType') so I'm beginning to think Discriminator types aren't so bad afterall - however I don't think the sql would be slow for the alternative type filter 'Where ID IN (Select ID from Manager) since the ID will always be indexed in this case.

Looking at my sql, it seems that when I have one or two 'person' types added to the list - I can simply cut off the WHERE clause and get precisely th correct info back. But when I add the third, I also need to change at least the first inner join to person(the null one) to a left join (the inner join with the supertype can always be a left join equivalently). I'll send those snips as well.

Thanks

Thanks I received the email. We will look into it first thing tomorrow (wednesday). the edge case is the inheritance types in hierarchy type target per entity + inherited fields over supertype relations + left joins.

Anyway, we'll see what we can come up with and hopefully it will correct your scenario and not break other scenario's

I sent a slight correction file to you as well.

Thanks

Fixed It was actually rather easy. 5 lines of code

See attached dll. All our tests run, and we tested it with typedlists of type 1 which indeed append the type filter properly. (e.g. Complainant - PersonAddress - Address, then select FullName from Complainant in field list -> type filter required).

Almost fixed. OK - so the type filter is gone - good. Now, when you do a left join on the subtype its really a left join on the subtype and supertype. So the way the sql is generated the LEFT keyword needs to extend to the supertype as well. So i've been chopping off the type clause and changing the inner keyword to left on the supertype joins.

Ideally, the generated sql would look like the bottom half of my first post. Whichever is easier for llblgen.

Yes, our code uses 'INNER' for subtype join supertype joins. This is equal to subtype LEFT join supertype, as a subtype row always has a supertype row, as the relationship is pk-pk, and a pk can't be nullable.

We'll change the code so it will use the same join type between subtype and supertype as it will use for the normal relationship. SQL looks like this:

SELECT [LPA_C3].[ComplaintID],
       [LPA_C4].[FullName]          AS [ComFullName],
       [LPA_C3].[ComplainantPersonID],
       [LPA_B1].[FullName]          AS [BenFullName],
       [LPA_S6].[FullName]          AS [SubFullName],
       [LPA_S13].[Address1]        AS [SubAddress1],
       [LPA_C12].[Address1]        AS [ComAddress1]
FROM   (((((((((((([MEDIC_CMS].[dbo].[Person] [LPA_B1]
                   RIGHT JOIN [MEDIC_CMS].[dbo].[Beneficiary] [LPA_B2]
                     ON [LPA_B1].[PersonID] = [LPA_B2].[BeneficiaryPersonID])
                  RIGHT JOIN [MEDIC_CMS].[dbo].[Complaint] [LPA_C3]
                    ON [LPA_B2].[BeneficiaryPersonID] = [LPA_C3].[BeneficiaryPersonID])
                 LEFT JOIN [MEDIC_CMS].[dbo].[Complainant] [LPA_C5]
                   ON [LPA_C5].[ComplainantPersonID] = [LPA_C3].[ComplainantPersonID])
                LEFT JOIN [MEDIC_CMS].[dbo].[Person] [LPA_C4]
                  ON [LPA_C4].[PersonID] = [LPA_C5].[ComplainantPersonID])
               LEFT JOIN [MEDIC_CMS].[dbo].[Subject] [LPA_S7]
                 ON [LPA_S7].[SubjectPersonID] = [LPA_C3].[SubjectPersonID])
              LEFT JOIN [MEDIC_CMS].[dbo].[Person] [LPA_S6]
                ON [LPA_S6].[PersonID] = [LPA_S7].[SubjectPersonID])
             LEFT JOIN [MEDIC_CMS].[dbo].[PersonAddress] [LPA_B8]
               ON [LPA_B1].[PersonID] = [LPA_B8].[PersonID])
            LEFT JOIN [MEDIC_CMS].[dbo].[PersonAddress] [LPA_C9]
              ON [LPA_C4].[PersonID] = [LPA_C9].[PersonID])
           LEFT JOIN [MEDIC_CMS].[dbo].[PersonAddress] [LPA_S10]
             ON [LPA_S6].[PersonID] = [LPA_S10].[PersonID])
          LEFT JOIN [MEDIC_CMS].[dbo].[Address] [LPA_B11]
            ON [LPA_B11].[AddressID] = [LPA_B8].[AddressID])
         LEFT JOIN [MEDIC_CMS].[dbo].[Address] [LPA_C12]
           ON [LPA_C12].[AddressID] = [LPA_C9].[AddressID])
        LEFT JOIN [MEDIC_CMS].[dbo].[Address] [LPA_S13]
          ON [LPA_S13].[AddressID] = [LPA_S10].[AddressID])

We now have to run our tests to see if this breaks anything. I don't expect anything to break as the semantic result of subtype left join supertype (or supertype right join subtype) is equal to subtype inner join supertype.

Works. See attached dll.

Perfect!!! Made my day!

Many Thanks!!