Aggregate Filter with joined tables

Aggregate Filter with joined tables

  

Hi 

I have entities Medewerker, Fuctie, Competentie and Opleiding The last three all have a (left join) relation to Medewerker (with fields entityname.MedewerkerId related to Medewerker.Id. My filter is:  

Medewerker.Naam like "%" + Session.WorkspotSearch_Medewerker + "%" and 
Functie.Omschrijving like "%" + Session.WorkspotSearch_Functie + "%" and
Competentie.Omschrijving like "%" + Session.WorkspotSearch_Competentie + "%" and 
Opleiding.Omschrijving like "%" + Session.WorkspotSearch_Opleiding + "%"

I only get results (filter outcome) when all three tables have a record that contains a MedewerkerId. I thought that (because of the left join) I also would get results if only one of the three tables holds such a record. Am I missing something?

Regards, Harry




Hello Harry,

Your filter doesn't seem to be the problem.

It is more likely to be the joins. Which join are you using? To do the left relationship you should use a With or Without join.

If you are using it already, can you attach an oml with your aggregate so we can take a look? 

Or post here an image of the sources.

Cheers,

Eduardo Jauch

Hi Eduardo,


The joins seems to be alright. Could it be that is has something to do with using NullIdentifiers in the filter? The SQL output seems to be allright too. I also attached the OML.

Regards, Harry 

Hello Harry,

Sorry. Too early in the morning.
I think the problem is really in the filter.

Let's say that for a line in Medewerker, there isn't any match in Opleiding

The filter will be translated as something like this, when seeing by the database as SQL, assuming that session variables are empty

Harry like "%%" and 
a_Functie_Omschrijving like "%%" and
a_Competentie_Omschrijving like "%%" and 
NULL like "%%"

Tha last one (NULL like "%%") will always return FALSE, and because you are using 'AND', your entire filter will fail and the line will not be returned.

So, insetad of AND, use OR. It will work than, as long at least ONE of the attributes used in this field exist (like Medewerker.Naam, that I assume will always exist).

I can't test it, but I think it will do the trick.

Cheers,
Eduardo Jauch

Hi Eduardo,

I've already tried OR but then I don't get the right result either: In the test below I only expect one record ('Weijtens)' but all records (3) appear.

Regards, Harry

Hello Harry,

Using the OR in the filter is the right way of doing this.

The multiple records is something else.
Maybe related with your data.
I'm doing some tests on my own here, but your OML was not uploaded.

Cheers,
Eduardo Jauch

OML


Regards, Harry 

Eduardo,


it seems that uplodaing the OML (700KB) is not going well (it keeps saying 'processing upload')


Regards, Harry

Harry,

It seems you found a BUG in the Aggregate...
Using DIFFERENT variables in the filter is causing the system to simply IGNORE the filters.

As a workround, if you really need to use different variables for the search filter, you can do this in the JOINS instead, like this:

Medewerker.FunctieId = Functie.Id AND Functie.Omschrijving like "%" + Session.WorkspotSearch_Functie + "%"

You do like this for all the joins, and in the filter you let only the filter for the main table

Medewerker.Naam like "%" + Session.WorkspotSearch_Medewerker + "%"

I think this will work as a workaround.

Cheers,
Eduardo Jauch

Hi Eduardo,

I will look into that solution. Thanks for all your research. One question:

>>Using DIFFERENT variables in the filter is causing the system to simply IGNORE the filters.

Could you also use ONE session var to do this (and how)?


Regards, Harry


Hi,

Assuming that you have a single input in a search bar, and you wants to type something and find all the records that the Medewerker name, OR the Functie name, OR... include that search string, you sould use a single session variable, like this:

  

Medewerker.Naam like "%" + Session.Search + "%" or 
Functie.Omschrijving like "%" + Session.Search + "%" or
Competentie.Omschrijving like "%" + Session.Search + "%" or 
Opleiding.Omschrijving like "%" + Session.Search + "%"


Cheers,
Eduardo Jauch

Hi,

Ah, I see what you mean, however that's not what I want exactly. I'll go with your first suggestion.

BTW: is there a place where can report the bug?

Regards, Harry

btw, if you are going for OR, you will end up with multiple records, because it's either empty which will give you a hit, or the one you selected.

imho, it should be "AND" in any case




J. wrote:

btw, if you are going for OR, you will end up with multiple records, because it's either empty which will give you a hit, or the one you selected.

imho, it should be "AND" in any case




I disagree, J.

Using 'AND' in a filter used to filter by a search string, like what happens usually in List Screens made with scaffolding, where you want to find records looking in fields of different entities, will return results ONLY if ALL the fields in a line contain the string. 

This is not what we want (nor Harry). And this is why the scaffolding generate it using OR.

Here, the problem is that the SQL being generated is not correct, because there are multiple search strings, like I show here.

Otherwise, the OR would work as expected.

Cheers,
Eduardo Jauch


Hello Harry,

Just to clarify.
In your aggregate you want to have results for ALL the Medewerker that match the criteria, and than join with the other entities that match the other criteria, or you wants to have ONLY the Medewerker if all the criteria are met?

If it is the first case, you have to use the approach I suggest.
If it is the second case, than separating the filters will do the trick.

Cheers,
Eduardo Jauch


Hi Eduardo,

The second case, separating the filters did the trick indeed.


Thanks for your help, 

Harry

Has There been a defect filed on this with OS and an eta on a fix?  I am running into that OS is implementing every left join with a filter as as an inner join.

Join:   Primary With or Without Secondary

           Primary.Id = Secondary.PrimaryId

Filter:  Secondary.SomeCondition = True OR Secondary.Id = NullIdentifier()


Condition:  Secondary.SomeCondition = True (Implies Secondary.Id <> NullIdentifier)

Results:    Data


Condition:  Secondary.ID = NullIdentifier()

Results:  No Data



      


I logged a ticket with OS, Id#2031160.

The good news is that seems to be a work around (or I have been doing this wrong since day 1).  The work around is to move the filter condition into the join condition.  Any NullIdentifier() conditions in the filter can be left out of the join condition because they seem to be covered by the WithOrWithout join type.


Failing example:

   Join:   Primary With or Without Secondary

             Primary.Id = Secondary.PrimaryId

  Filter:  Secondary.SomeCondition = True OR Secondary.Id = NullIdentifier()


Workaround example:

   Join:   Primary With or Without Secondary

             Primary.Id = Secondary.PrimaryId and  Secondary.SomeCondition = True

  Filter:  None

Erik -

That is NOT a bug, this is expected behavior. The correct approach is indeed to do the filter in the join condition if this is the behavior you want (in fact, that was one of the big advantages of the "Aggregates" vs. "Simple Queries", that we could start doing this).

Changing this behavior now would likely result in every OutSystems application ever written being broken upon upgrading to a "fixed" version...

J.Ja



Justin James wrote:

Erik -

That is NOT a bug, this is expected behavior. The correct approach is indeed to do the filter in the join condition if this is the behavior you want (in fact, that was one of the big advantages of the "Aggregates" vs. "Simple Queries", that we could start doing this).

Changing this behavior now would likely result in every OutSystems application ever written being broken upon upgrading to a "fixed" version...

J.Ja

Hi Justin,

You are around much longer than me, so if you say that this is "expected behavior", I believe.
But I'm trying to understand "why" this is the expected behavior, and I'm failing miserably.

While an Aggregate is much more limited than an Advanced SQL, the purpose is the same: fetching data from database. And the way this is done is the same as in an Advanced SQL: SQL.

I understand that many of the limitations are design choices to keep it simple and allow for transparency to the user, that do not have to worry about the underlying database engine. Also, the aggregate, being a much more easier interface to define the rules for fetching the data, uses a different naming scheme (like a "left join" becoming a "with or without").

Everything leads to a sort of "aggregates and advanced queries are two different things" feeling.

But at least for 99% of it, they aren't.

For example, Aggregates joins are all at the same level, and you need to understand how this works in order to have the expected results, exactly like in a SQL query.
If you want to LEFT JOIN B ON A, and INNER JOIN C ON B, using both joins at the same level, if the INNER JOIN is before or after the LEFT JOIN will potentially return different results, both in an aggregate as in an advanced query.

In other words, in a very consistently way (as I would expect), almost everything in an aggregate will work exactly like if I just typed the query in an advanced query. 

One could argue that the NULL is different, as there is no NULL concept in OutSystems, but there are in SQL.
I would say this is not true. 

The point is that OutSystems "replace" empty values, both when "inserting/updating" as well as when "reading".

And while the query is being executed, a LEFT JOIN will make it possible to the NULL to exist, and is very common to need to filter this.

And the platform knows that. If you have a filter for an identifier in the form ID = NullIDentifier() the platform will replace it in the generated query by an IS NULL. This is what make it possible to filter, in an aggregate based on the presence or not of the right side of a LEFT JOIN.

But here, what is happening is that the aggregate optimizer is ignoring this possibility (LEFT JOIN), and assuming that because the platform replaces empty values by default ones when inserting/updating/reading, there are no NULLs in the database, and depending on the values of the search strings, is simply removing the filters from the final query the is sent to the database.

But this is not true, as the NULLs will be perfectly possible during the query execution.

This seems to me a "border case", with a simple workaround (using the filters in the JOIN clauses).
And I agree that changing this optimizer rule will possibly break applications.

But I fail to see, after looking to all other behaviors of aggregates, what is the reasoning behind this optimizer design choice.

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Justin James wrote:

Erik -

That is NOT a bug, this is expected behavior. The correct approach is indeed to do the filter in the join condition if this is the behavior you want (in fact, that was one of the big advantages of the "Aggregates" vs. "Simple Queries", that we could start doing this).

Changing this behavior now would likely result in every OutSystems application ever written being broken upon upgrading to a "fixed" version...

J.Ja

Hi Justin,

You are around much longer than me, so if you say that this is "expected behavior", I believe.
But I'm trying to understand "why" this is the expected behavior, and I'm failing miserably.

While an Aggregate is much more limited than an Advanced SQL, the purpose is the same: fetching data from database. And the way this is done is the same as in an Advanced SQL: SQL.

I understand that many of the limitations are design choices to keep it simple and allow for transparency to the user, that do not have to worry about the underlying database engine. Also, the aggregate, being a much more easier interface to define the rules for fetching the data, uses a different naming scheme (like a "left join" becoming a "with or without").

Everything leads to a sort of "aggregates and advanced queries are two different things" feeling.

But at least for 99% of it, they aren't.

For example, Aggregates joins are all at the same level, and you need to understand how this works in order to have the expected results, exactly like in a SQL query.
If you want to LEFT JOIN B ON A, and INNER JOIN C ON B, using both joins at the same level, if the INNER JOIN is before or after the LEFT JOIN will potentially return different results, both in an aggregate as in an advanced query.

In other words, in a very consistently way (as I would expect), almost everything in an aggregate will work exactly like if I just typed the query in an advanced query. 

One could argue that the NULL is different, as there is no NULL concept in OutSystems, but there are in SQL.
I would say this is not true. 

The point is that OutSystems "replace" empty values, both when "inserting/updating" as well as when "reading".

And while the query is being executed, a LEFT JOIN will make it possible to the NULL to exist, and is very common to need to filter this.

And the platform knows that. If you have a filter for an identifier in the form ID = NullIDentifier() the platform will replace it in the generated query by an IS NULL. This is what make it possible to filter, in an aggregate based on the presence or not of the right side of a LEFT JOIN.

But here, what is happening is that the aggregate optimizer is ignoring this possibility (LEFT JOIN), and assuming that because the platform replaces empty values by default ones when inserting/updating/reading, there are no NULLs in the database, and depending on the values of the search strings, is simply removing the filters from the final query the is sent to the database.

But this is not true, as the NULLs will be perfectly possible during the query execution.

This seems to me a "border case", with a simple workaround (using the filters in the JOIN clauses).
And I agree that changing this optimizer rule will possibly break applications.

But I fail to see, after looking to all other behaviors of aggregates, what is the reasoning behind this optimizer design choice.

Cheers,
Eduardo Jauch

Since I do not (and never have) worked there, I cannot explain the reasoning behind it. I think this is behavior that everyone has been surprised by at least once. So it is not "expected behavior" if you are new to the platform, but it is definitely "expected behavior" if you have been using it. I agree with you that it's not great. But again, it will break (not a "possibility", it is a guarantee) a zillion applications if it ever changes, so we are stuck with it. :(

J.Ja

Not sure if these are current or even implemented, but OutSystems seems to have sponsored these papers related to this topic: 

OutSystems Compiler Optimizations

THE VIEW UPDATE PROBLEM IN THE OUTSYSTEMS AGGREGATE LANGUAGE

I think You have to do like that

(Medewerker.Naam like "%" + Session.WorkspotSearch_Medewerker + "%" or Session.WorkspotSearch_Medewerker="") and 
(Functie.Omschrijving like "%" + Session.WorkspotSearch_Functie + "%" or Session.WorkspotSearch_Functie="") and
(Competentie.Omschrijving like "%" + Session.WorkspotSearch_Competentie + "%" or Session.WorkspotSearch_Competentie="") and (
Opleiding.Omschrijving like "%" + Session.WorkspotSearch_Opleiding + "%" or Session.WorkspotSearch_Opleiding ="")

Justin James wrote:

Since I do not (and never have) worked there, I cannot explain the reasoning behind it. I think this is behavior that everyone has been surprised by at least once. So it is not "expected behavior" if you are new to the platform, but it is definitely "expected behavior" if you have been using it. I agree with you that it's not great. But again, it will break (not a "possibility", it is a guarantee) a zillion applications if it ever changes, so we are stuck with it. :(

J.Ja


Sorry Justin.

The "expected behavior" got me confused (cultural differences? xD)
And as I said, I agree that changing this now would be bad... (even if in the beginning when we discussed this before I didn't noticed this).

Cheers,
Eduardo Jauch

erik berg wrote:

Not sure if these are current or even implemented, but OutSystems seems to have sponsored these papers related to this topic: 

OutSystems Compiler Optimizations

THE VIEW UPDATE PROBLEM IN THE OUTSYSTEMS AGGREGATE LANGUAGE

Hello erik. Both links point to the same paper (the last).
But at least this last one I think it is not related to what has being discussed here (but is very interesting)

Cheers,
Eduardo Jauch