Bug in Aggregate filter?

  

In this post, we found what it seems to be a bug in the aggregate.

The filter is like this:

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 tested this a little and it seems that if there are more thatn ONE "search" variable in the filter, like in the example give in the topic (and copied here), the filter is completely ignored when generating the SQL. I tested using a single variable and it works.

In fact, the Executed SQL shown in the aggregate misses the WHERE clause when something like above is used.

Is this really a bug or it is just me that am missing something here?

Cheers,
Eduardo Jauch

I don't get it what you mean?

as you can see, in aggregate I created, I did something similra and this is the resulted executed query (somewhat shorted ofcourse)


SELECT TOP (10) ENApp_Definition_Module.[ID] o0, [...] WHERE (ENApplication.[IS_ACTIVE] = 1) AND (((ENApplication.[NAME] LIKE ((N'%' + @Search1) + N'%')) AND (ENEspace.[NAME] LIKE ((N'%' + @Search2) + N'%'))) AND (ENEspace_Configuration.[NAME] LIKE ((N'%' + @Search3) + N'%')))ORDER BY ENApplication.[NAME] ASC 


I am testing on Version 10.0.603.0 btw.



Hello J.

I'm testing in a personal.
Here is the Data Model:

Here is the Sources from the aggregate:

And here what is in the Filter and the test.

Here is the complete QUERY as it is executed by the system. You will notice that there is no WHERE clause:

SELECT TOP (32) ENCompetency.[ID] o0, ...
FROM (([X2PXKM031].DBO.[OSUSR_9BL_WORKER] ENWorker
Left JOIN [X2PXKM031].DBO.[OSUSR_9BL_FUNCTION] ENFunction ON (ENWorker.[FUNCTIONID] = ENFunction.[ID])) Left JOIN [X2PXKM031].DBO.[OSUSR_9BL_COMPETENCY] ENCompetency ON (ENWorker.[ID] = ENCompetency.[WORKERID])) 


In the test above, the only line that should be showing in the results is the line where the Name is 'Ricardo', but as there is NO where clause generated, all the lines are showing up.

If I replace all the search variables in the filter by, for example, var1, than it works as expected:


And the executed query is very different, presenting the WHERE clause:

SELECT TOP (32) ENCompetency.[ID] o0, ...
FROM (([X2PXKM031].DBO.[OSUSR_9BL_WORKER] ENWorker
Left JOIN [X2PXKM031].DBO.[OSUSR_9BL_FUNCTION] ENFunction ON (ENWorker.[FUNCTIONID] = ENFunction.[ID])) 
Left JOIN [X2PXKM031].DBO.[OSUSR_9BL_COMPETENCY] ENCompetency ON (ENWorker.[ID] = ENCompetency.[WORKERID])) 
WHERE (((ENWorker.[NAME] LIKE ((N'%' + @Var1) + N'%')) OR (ENFunction.[NAME] LIKE ((N'%' + @Var1) + N'%'))) OR (ENCompetency.[NAME] LIKE ((N'%' + @Var1) + N'%')))

I would say that there is something wrong with the SQL generator of the aggregate in the first case, as I am not seeing a reason to it to generate the first query.

Cheers,
Eduardo Jauch

tbh it's logical if you are using "or"...

no need to generate that part of the where clause if you know if one of those variables is empty.

the result will be all records (because everything matches like "%%") 

 the whole "filter" became pointless.

Hello J.

This doesn't make any sense.

First, the filter will be applied only after the joins were done. If there are no matching between Worker and the other entities, the JOIN will return all lines of Worker, and the fields for the other two entities will be empty (both are being LEFT JOINED).

Assuming that all my three search variables are empty, when the filter is applied to each line, if you are using AND instead of OR, you will return 0 records, as only one of the three comparisons will return True, as the other two will be comparing NULL with "%%" and it will always be false, making the entire filter false.

This is why we use the OR

In any case, the difference between the two aggregate filters is not AND and OR, is the variables.

Cheers,
Eduardo Jauch

since when are we comparing nulls in outsystems?

afaik, there are no nulls in outsystems, just empty strings

J. wrote:

since when are we comparing nulls in outsystems?

afaik, there are no nulls in outsystems, just empty strings

Since when the query is executed in the Database, not in OutSystems.


Eduardo Jauch wrote:

J. wrote:

since when are we comparing nulls in outsystems?

afaik, there are no nulls in outsystems, just empty strings

Since when the query is executed in the Database, not in OutSystems.


I understand that :)

You are making my brain hurts on a saturday. I don't have that deep insight of how outsystems works.

I am still convinced it's not a bug but a interesting feature.

reason behind it, outsystems does not insert null into the database(?), so why would a aggregate do that when you are left-joining. Because it does, you will create a mismatch on how the whole systems works.

I tested it with 3 variables as well, and placed in every test-value some string and the where-clause appears.

So, for now my guess is, that because the dogma that outsystems uses empty-strings as nulls, it is still removing that specific filter before it creates the sql and executes it in the database.

so for people with a sql-background it can be confusing, I admit that :)

lol

Regarding how things work "under the hood" :)

When you create a new record in an entity, as far as I know, yes, OutSystems replace the empty values by default values (exception to Foreign Keys). 

The problem is that during the execution of the query, when you are left joining another entity to your, for the rows where there is no match, the right entity will be "null" on all the fields. 

After the OutSystems recover this info, it replaces anything "empty" with default values. 

But the Filters will be applied during the execution of the query, in database, where the fields are "null".
And comparing a field that has is NULL with anything other than NULL, will always return false. 

This is one of the reasons why in the scenario above, you can't use an AND in the filter. Must be an OR.

Now, regarding this being a bug or not.

I vote for the bug! lol
My reasoning is simple. 

When the OutSystems converts the aggregate info into a query, it simply CAN'T assume that the variables I'm using will be empty or not, thus, it CAN'T simply make my filter disappear from the query. 

In both examples, the queries should be exactly the same. The only difference is that on one I'm using the same variable three time, in the other I'm using three variables. The system simply can't assume in one case it is ok, let's put the filter, and in the other just erase it from the query. :)

This is a bug. I'm pretty sure. Maybe I'm wrong, but I don't think so.

To end this shrort long story...

The aggregate is just an interface to create queries. While it is stated that someone without extense knowledge on SQL can use it, and this is true, the thruth is that you need to know SQL and how it works in database, under OutSystems, to be able to do anything different than what is done in scaffolding...

But this is my opinion :)

Cheers,
Eduardo Jauch

P.S. I promisse that I'll let our weekend alone starting now ;) 

Hi

I did some more testing and it seems that when filters are added one by one it is working like expected.


So seperating the filters did the trick.

Regards, Harry


Harry de Boer wrote:

Hi

I did some more testing and it seems that when filters are added one by one it is working like expected.


So seperating the filters did the trick.

Regards, Harry


Hello Harry. 

Yes, separating the filters will cause the filter be present in the query sent to the database.
The problem is that now, ALL the filters must return TRUE, to each line of the joins to be present in the results. You can think of each separate filter as having an AND to the others. 

Thus, this solution is not a workround to the scenario above where you need "OR" between the conditions. 

The workaround, in this case, is to use the JOINS itself, using an AND and the search condition there, leaving in the filter only the condition for the main entity. 

But this is a workaround, to a bug :)

Or at least I think it is a bug.

Cheers,
Edaurdo Jauch

Eduardo Jauch wrote:

Now, regarding this being a bug or not.

I vote for the bug! lol
My reasoning is simple. 

When the OutSystems converts the aggregate info into a query, it simply CAN'T assume that the variables I'm using will be empty or not, thus, it CAN'T simply make my filter disappear from the query. 

In both examples, the queries should be exactly the same. The only difference is that on one I'm using the same variable three time, in the other I'm using three variables. The system simply can't assume in one case it is ok, let's put the filter, and in the other just erase it from the query. :)

Ok, this is where we differ for sure..

I think aggregates are generated RUNTIME and not "publish/design" time.

so,in a way, the where clause is added with an expression that is  "expanded-inline", like an advanced query.

Outsystems builds at least the where-clause runtime as a string and then executes the whole "sql-string".


enjoy the weekend :)

Solution

Hi guys,

The platform does optimise some parts of the generated SQL in runtime, attempting not to send filters to the database which it already knows will evaluate to TRUE for all records. It probably is not accounting for the fact that the attributes can have the database value NULL which, as Eduardo said, will make the likes evaluate to FALSE.

I agree with J. that for Harry's scenario ANDs or separate filters are what he wants, but clearly only the separate filters are having the intented behavior, so there seems to be a bug out there.

I'll make sure it is added to our maintenance backlog, thanks for troubleshooting it so thoroughly.

Regards,

Paulo Ferreira

Solution

Hello Paulo,

About Harry's scenario, he needs the standard "OR".

For what he told in the other post, he wants to filter his main entity Med... by a search string and return all records that match the search string, even if the other entities conditions are not met. 

If you use an AND you will get results only when all conditions are true.

Ex.

He wants all Med... records whose name has "RON" on it, and for those records, he wants to left join all the records on the O... entity if they met the second criteria, like "XYZ", and so on.

I may understood wrong, but this is what we have being discussing there.

In any case, this is really a bug and needs to be addressed.
Thanks!

Cheers,
Eduardo Jauch

Hi Eduardo,

Yes, I understand that given the current behavior, the results are not satisfactory using an AND. But I think the intent/expected behavior of using Medewerker.Naam like "%%" in OutSystems is that it returns TRUE regardless if it is the empty string or a database NULL. The OutSystems language doesn't have the concept of NULLs that databases have, and so it should abstract that away as much as possible.

Now, that's my personal view, and that's why I think using ANDs makes sense here. In practice, fixing this to one behavior or the other will clearly be a breaking change, since one behavior occurs with separate filters and the other with ANDs.

Out of curiosity, was that behavior expected to you before digging into the problem and the details of SQL?

Regards,

Paulo Ferreira

Hello Paulo,

Looking back on the problem I understand what you and J. were meaning.

The behavior I was expecting was the same if I was doing the query in an Advanced Query (where, I think, OutSystems doesn't optimize)

Lets assume a simple example.

Entity A (ID, Name)
1 Ricardo
2 João
3 Maria
4 Rickson

Entity B (ID, Name, AId)
1 Carro, 1
2 Moto, 1

Now, I want to find ALL records in A where Name contains a string "Ric", and left join with B, but only with those B whose Name contains a string "oto".

The result I am expecting is this:

A.Id, A.Name, B.Id, B.Name, B.AId
1, Ricardo, 2, Moto, 1
4, Rickson, 0, "", 0

My initial thought was to use OR in the Filter, like when we have a SINGLE search string. But this approach would not work here, because it would not prevent lines in B that do not match the search string to be in the results. Using OR it would have created a results with Ricardo being joined with both records in Entity B, as the whole filter would be true to both matches.

<EDIT>

Using AND would in fact solve the problem for this specific case, as you both pointed, as all the joins are LEFT JOINS. In fact, here, using AND is the solution.

But as the filter is being removed, it not work.

Looking better to the example, the AND in the filters would also not give the correct results.
As while it would work for the record Ricardo, it would not work for the record Rickson, that does not have any entry in Entity B. The AND here would exclude this record from the results.
</EDIT>

As workround, to do this in SQL or Aggregate (works on both) I suggested Harry to put the filters in the joins:

SELECT *
FROM A
    LEFT JOIN B ON B.AId = A.Id AND B.Name Like "%" + @search_2" + "%"
WHERE
    A.Name Like "%" + @search_1 + "%"

Changing the behaviour of the optimizer, I understand that it is a break change.
In fact this can cause a lot of problems to those are relaying on this behavior. 

Thank you both for helping me understand the mistake relative to the OR condition in this specific case :)

Hi guys. Nice work discussing this issue. I just wanted to give my 2 cents here.

Changes in the optimizer should never be a breaking change. If they are, then something bad happened on the optimization.

I think the query A.Name LIKE '%%' would already be optimized by the database, so I don't see much value into having a runtime optimization into the aggregate. I know this could be false because of prepared statements having to plan the query in advance, before knowing that it's actually an empty LIKE expression. Still, I'm not sure if the database would be dumb enough to make a full table scan just because of that reason.

If you must optimized these cases, then replacing a A.Name LIKE '%%' by A.Name IS NOT NULL will provide a safe and correct optimization.

Last, please remember that, although OutSystems does not have the concept of NULL values, external entities could have, as well as LEFT joins.

Leonardo Fernandes wrote:

Last, please remember that, although OutSystems does not have the concept of NULL values, external entities could have, as well as LEFT joins.

On the LEFT JOINS:

The whole aggregates will become very shaky and inconsistent what to expect in the view-point of an outsystems-developer, which nowadays don't have a database-background.

Outsystems-developers are not aware of the existence of nulls in the database and they don't have to know that like "%%" will act differently on a null.

if they should be aware of null, well, then the whole null-not-null conversation becomes invalid and the outsystems should incorporate nulls 100%.





Well, NULLs certainly do exist, whether you like them or not.

The fact that an OutSystems developer noticed the difference in query behavior due to the optimization shows that there's something wrong with it, and it can sometimes produce unexpected results.

Remember that any comparison with NULL returns false; so by your reasoning, OutSystems would need to give special treatment to any comparison of an entity involved in a LEFT join, not just LIKE '%%'.


---

Regarding the original use case, I think there's an easy solution:

A.Id, A.Name, B.Id, B.Name, B.AId
1, Ricardo, 2, Moto, 1
4, Rickson, 0, "", 0


Use the following condition:

A.Name LIKE "%...%" AND (B.Id = NullIdentifier() OR B.Name LIKE "%...%")


The B table will have NULL on the primary key only if the LEFT join failed, in which case you can short-circuit the filters for B. That works because, when comparing a primary/foreign key with NullIdentifier, the compiled SQL will actually be B.Id IS NULL (instead of B.Id = 0).


Let me know if that works.

J. wrote:

On the LEFT JOINS:

The whole aggregates will become very shaky and inconsistent what to expect in the view-point of an outsystems-developer, which nowadays don't have a database-background.

Outsystems-developers are not aware of the existence of nulls in the database and they don't have to know that like "%%" will act differently on a null.

if they should be aware of null, well, then the whole null-not-null conversation becomes invalid and the outsystems should incorporate nulls 100%.

I can't conceive a developer, even an OutSystems developer, without database knowledge. 

This is why when we (here) are training people that do not have IT background to be OutSystems developers, we teach them SQL. They first learn how to use SQL to fetch the data. Only later they learn how to do it using aggregates.

My personal view on aggregates is that they are simply a visual tool to prepare a query. Sure, it optimizes the query and is easy to understand, besides the fact that they help making database "things" transparent. And all of this is cool and nice.

Right now, aggregates are a limited tool. You can't do in an aggregate everything you can with SQL. And many times this is very limiting. 

The solution is to use SQL or make a more complex logic, possibly using multiple aggregates and cycles and data processing. 

The last usually ends in bad performance and code that is harder to maintain. And that is the reason why all OutSystems projects that I saw use SQL at some point. 

For what I saw, in general (there are exceptions, of course), a developer that knows SQL do a better job than one that doesn't, and this is true also when comparing developers that have experience with languages other than OutSystems. 

OutSystems make life much easier to us. But the less you know about how things work, the worse. You don't need to write SQL most of the time, as well as you don't need to code in JAVA or C# most of the time, but knowing how to do it will hurt no one.

Maybe in the future this will be completely irrelevant. IA is there and probably most of the programming will be done by other programs. Who knows?

But right know, I would say that I would be very uncomfortable to work with someone that does not now SQL and how the database they are using works...

Leonardo Fernandes wrote:

Regarding the original use case, I think there's an easy solution:

A.Id, A.Name, B.Id, B.Name, B.AId
1, Ricardo, 2, Moto, 1
4, Rickson, 0, "", 0


Use the following condition:

A.Name LIKE "%...%" AND (B.Id = NullIdentifier() OR B.Name LIKE "%...%")


The B table will have NULL on the primary key only if the LEFT join failed, in which case you can short-circuit the filters for B. That works because, when comparing a primary/foreign key with NullIdentifier, the compiled SQL will actually be B.Id IS NULL (instead of B.Id = 0).

Hi Leonardo,

It won't work. The record #1 will be returned, but not the record #4, because the B.iD = Nullidentifier() will be false, and than the B.Name LIKE "%...%" is false, making the entire filter false because of the END.

But we want the two records of the main search, and the entities in the left joined only when the conditions are met.

In this case, I think the only "easy" way is to put the conditions for the search in the left joins entities in the left joins conditions.

Cheers,
Eduardo Jauch

I'm pretty sure that B.iD = Nullidentifier() will be true in that case.

Leonardo Fernandes wrote:

I'm pretty sure that B.iD = Nullidentifier() will be true in that case.

Hum... Only if there is no matchs between the A and B for a specific line in A. I was talking about the following situation, where now there is a match between A and B, but is a B that I don't want (But I want the A):

Entity A (ID, Name)
1 Ricardo
2 João
3 Maria
4 Rickson

Entity B (ID, Name, AId)
1 Carro, 1
2 Moto, 1
3 Carro, 4

In the situation above, assuming the search string for A.Name is "Ric" and for B.Name is "oto", but I want The A whatever the B exists or not, the form bellow will not work:

A.Name LIKE "%...%" AND (B.Id = NullIdentifier() OR B.Name LIKE "%...%")

A.Name LIKE "%...%" will be true for both A.Id 1 and 4. But than, There is a match between A and B for Rickson. This means that 'B.Id = NullIdentifier()' will be false, and B.Name LIKE "%...%" will also be false (B.Name for this record will be Carro, not Moto., making the whole filter False for this record.

In this case, I don't get what I want that is all A records that match the string, left joined with all the records on B that also match its own string...

As the above situation is possible, the filter will not work. In this case, the only solution I see is to put the specific search conditions for the Left joined entities in the Join clause.

Eduardo Jauch wrote:

J. wrote:


On the LEFT JOINS:


The whole aggregates will become very shaky and inconsistent what to expect in the view-point of an outsystems-developer, which nowadays don't have a database-background.

Outsystems-developers are not aware of the existence of nulls in the database and they don't have to know that like "%%" will act differently on a null.

if they should be aware of null, well, then the whole null-not-null conversation becomes invalid and the outsystems should incorporate nulls 100%.

I can't conceive a developer, even an OutSystems developer, without database knowledge. 

This is why when we (here) are training people that do not have IT background to be OutSystems developers, we teach them SQL. They first learn how to use SQL to fetch the data. Only later they learn how to do it using aggregates.

My personal view on aggregates is that they are simply a visual tool to prepare a query. Sure, it optimizes the query and is easy to understand, besides the fact that they help making database "things" transparent. And all of this is cool and nice.

Right now, aggregates are a limited tool. You can't do in an aggregate everything you can with SQL. And many times this is very limiting. 

The solution is to use SQL or make a more complex logic, possibly using multiple aggregates and cycles and data processing. 

The last usually ends in bad performance and code that is harder to maintain. And that is the reason why all OutSystems projects that I saw use SQL at some point. 

For what I saw, in general (there are exceptions, of course), a developer that knows SQL do a better job than one that doesn't, and this is true also when comparing developers that have experience with languages other than OutSystems. 

OutSystems make life much easier to us. But the less you know about how things work, the worse. You don't need to write SQL most of the time, as well as you don't need to code in JAVA or C# most of the time, but knowing how to do it will hurt no one.

Maybe in the future this will be completely irrelevant. IA is there and probably most of the programming will be done by other programs. Who knows?

But right know, I would say that I would be very uncomfortable to work with someone that does not now SQL and how the database they are using works...

I never said they don't have to learn SQL/C#/Js/Css/Html5, but Outsystems provides the means without the need for deeper understandings.

What is meant was, if the like '%%' should exclude the nulls in left joins, outsystems introduces database-logic to the outsystems-platform, which means they should introduce proper nulls as a whole inside the outsystems-platform.

otherwise, if they still want to maintain the non-null philosophy, they should also not introduce null's in aggregates plain and simple. otherwise you end up with sometime null sometime not-null.

(and yes, i don't like nulls :P, will stop now, because this ends up in the usual yes/no philosophy of database-design which i had far too many..)

I agree we are disagreeing on this matter

Eduardo Jauch wrote:

Leonardo Fernandes wrote:

I'm pretty sure that B.iD = Nullidentifier() will be true in that case.

Hum... Only if there is no matchs between the A and B for a specific line in A. I was talking about the following situation, where now there is a match between A and B, but is a B that I don't want (But I want the A):

Entity A (ID, Name)
1 Ricardo
2 João
3 Maria
4 Rickson

Entity B (ID, Name, AId)
1 Carro, 1
2 Moto, 1
3 Carro, 4

In the situation above, assuming the search string for A.Name is "Ric" and for B.Name is "oto", but I want The A whatever the B exists or not, the form bellow will not work:

A.Name LIKE "%...%" AND (B.Id = NullIdentifier() OR B.Name LIKE "%...%")

A.Name LIKE "%...%" will be true for both A.Id 1 and 4. But than, There is a match between A and B for Rickson. This means that 'B.Id = NullIdentifier()' will be false, and B.Name LIKE "%...%" will also be false (B.Name for this record will be Carro, not Moto., making the whole filter False for this record.

In this case, I don't get what I want that is all A records that match the string, left joined with all the records on B that also match its own string...

As the above situation is possible, the filter will not work. In this case, the only solution I see is to put the specific search conditions for the Left joined entities in the Join clause.


Ah now I understand what you want. Having the filter in the JOIN clause is the way to go.