Advanced Query Pitfalls - using alias and wildcards

AcacioPN

Staff

Question

A common development problem that I find while working @ Product Support is with the use of Advanced Queries in two very similar scenarios:

Alias (for more complex queries);
Wildcards.

Documentation actually explains this very well for wildcards (the use of {Entity}.* instead of *) but misses an explanation about alias. It also does not explain what happens when you fail to use the recommended patterns.

You can follow this explanation with the attached OML, if you want.

Let me start out with a simple example: let's look at a very simple Contact entity and populate it with some data:

The above screenshot is from the following query:

SELECT {Contact}.*
from {Contact}

However, since this is the initial structure (first publish of the eSpace), the following query will yield the exact same result.

SELECT *
from {Contact}

In the next step, I will move the Email attribute - it now comes after Phone instead of before:

After publishing the new version of the eSpace, the same (correct) advanced query works OK - but now the email will appear at the end:

SELECT {Contact}.*
from {Contact}

However, if you now use the version with a simple *, things are not looking so good:

SELECT *
from {Contact}

What happens is that:

When you changed the order of the columns in the entity, the database table was not changed - then only thing that happens is that the Entity object (in the eSpace) now has the fields in a different order;
When you write {Contact}.* in an advanced query, what is actually being sent to the database is {Contact}.[Id], {Contact}.[Name], {Contact}.[Phone], {Contact}.[Email] , and thus you get the fields in the correct order;
However, when you just write *, the Agile Platform does nothing, and you get whatever fields are coming from the database, in whatever order they are sent.

Note that this only worked because both columns Phone and Email are of type Text - if there were type mismatches you would have an error message.

This gets even stranger if you add or delete columns. In my next example I will delete the Email and Phone attributes and add a new attributes StreetAddress and ZIPCode.

As expected, the (correct) query will show me the empty columns (I did not bootstrap them):

SELECT {Contact}.*
from {Contact}

However, with the other query, it will try to re-use the existing data from the other columns!

SELECT *
from {Contact}

A similar effect happens when you use alias in your queries, for the exact same reason - the Agile Platform will not try to parse them.
So an innocently similar construct will also result in problems:

SELECT contactlist.*
from {Contact} contactlist

If you do need to use alias (in a much more complex query than this one) make sure to expand the fields one-by-one.

If you have anything to add to this post, feel free to do so.
Cheers,

AdvQueriesPitfalls.oml

27 Dec 2011

Evert van der Zalm

MVP

Hello Acácio,

Nice post, very clear how the agile platform works with advanced queries. I'm glad mine advanced queries are ok :).

Two questions regarding you're post:

1) You say that "SELECT {Contact}.* from {Contact}" is transformed by the platform, is this also making the query (a little) slower since it needs to be converted?

2) Regarding the alias in queries: If I'm correct the sql code, generated in a simple queries when testing, also uses alias table names? Does this mean that when you use this sql code in a advanced query (copy pase) and don't change the alias table names to the original table names that it gives the same problems? Or is this solved since a simple query always generate the select with all attributes instead of other table name?

I've see people who uses simple queries to make the sql code and set it in a advanced query and changes some things (select attributes and group by statements), but leave the join tables names the same.

Kind regards,
Evert

28 Dec 2011

AcacioPN

Staff

Hi Evert

About your questions:

1) You say that "SELECT {Contact}.* from {Contact}" is transformed by the platform, is this also making the query (a little) slower since it needs to be converted?
The conversion (from {Entity}.* to an enumeration of all the attributes in the correct order) is done in compile time (when you do 1CP of the eSpace), so this will not make it slower - the final result is actually the same as if you wrote {Entity}.[Attr1], {Entity}.[Attr2].... in your query.

2) Regarding the alias in queries: If I'm correct the sql code, generated in a simple queries when testing, also uses alias table names?
Yes, it does.

Does this mean that when you use this sql code in a advanced query (copy pase) and don't change the alias table names to the original table names that it gives the same problems? Or is this solved since a simple query always generate the select with all attributes instead of other table name?
The code generated by simple queries is something like:

SELECT ENContact.[ID] o1, ENContact.[NAME] o2, ENContact.[STREETADDRESS] o3, ENContact.[ZIPCODE] o4
FROM {Contact} ENContact
ORDER BY ENContact.[NAME] ASC

So you are not affected by the problem of alias because each attribute is individually declared (even if with an alias). As long as you individually declare each attribute you are SELECTing, alias are safe.
However, you will be affected if, after creating the query, you change the order of the attributes - since that operation will not swap the order of the attributes in the query text like {Entity}.* would do.

That is actually one of the reasons why advanced queries selecting individual attributes should always use custom structures instead of the query records.

There are still other (safe) constructs you could use, though these could make your queries (slightly - or not) slower:

SELECT {Contact}.*
FROM {Contact}
WHERE {Contact}.[ID] in (SELECT [...] /* your arbitrarily complex query here */)
ORDER BY ENContact.[NAME] ASC

Cheers,

28 Dec 2011

Pedro Coelho

MVP

Acácio, got a quick question as on the conversion from {Entity}.* to an enumeration of all the attributes.

I was just trying something in an Advanced Query that would be something like:

SELECT {Contact}.*, Count({Contact}.[ID])
FROM {Contact}
WHERE /* your arbitrarily complex query here */
GROUP BY {Contact}.*

Does the platform also converts the {Entity}.* in the GROUP BY clause? Cause I didn't get an error while testing it, but i currently don't have any data on the entities to be sure of the results.

Best regards,
PC

03 Jan 2012

4 replies

Last reply 26 Jan 2012

Show thread

Hide thread

Pedro Coelho

MVP

*Bump*
Any answer so far?

Best regards,
PC

12 Jan 2012

Joop Stringer

Acacio,

To me it sounds stange that changing order of attributes in eSpace does not reflect order in the database.
This seems like a mistake in the platform and should be adjusted.

If I change order of attributes I do this with a probable reason. Now I have to take care of my SQL in a way the platform understands.
In fact the platform should be able to determine if I do select * from {contact} or select {contact}.* from {contact}

Standard SQL can handle that, so why does the platform not handle this correct ?
In fact it should/could/must not make a difference.

This all has to do with the abstract layer around the database where the platform is relying on !
So this needs a fix from OutSystems side and not a "remember this" action for the programmer.

13 Jan 2012

AcacioPN

Staff

Hi

I have been having some pretty busy weeks, but let me get up to date on this matter:

The Agile Platform converts patterns {ENTITY}, {ENTITY}.* and {ENTITY}.[validattributename] anywhere in your text query. Think of it as find-replace being done in complie time.
Though in your particular case, unless ID is not unique for some reason, I'd say the query will list each record individuall and then a 1 at the end ;-)

Cheers,

26 Jan 2012

AcacioPN

Staff

Hi Joop

Let me break this down into pieces:

---

To me it sounds stange that changing order of attributes in eSpace does not reflect order in the database.
This seems like a mistake in the platform and should be adjusted.

I confess I had never thought of this. Why isn't the Agile Platform fixing the order of columns when that is changed in the Entity model.
I went searching a little and found some links (SQL Server 1, SQL Server 2, Oracle 1, Oracle 2). So it seems that there is no SQL instruction to re-order columns in a table - in both SQL Server and Oracle. I didn't know, but it does make sense - I never saw that option in any GUI, or did I ever see such an option in the ALTER TABLE statement which I frequently use.

All discussions seem to point in the direction that actual physical order is irrelevant from a database point of view (except for very specific data models) and that the only way to reorder columns physically is to drop & recreate the table - as you can imagine, this would not be very good and would pose a whole lot of other problems.

---

If I change order of attributes I do this with a probable reason. Now I have to take care of my SQL in a way the platform understands.
In fact the platform should be able to determine if I do select * from {contact} or select {contact}.* from {contact}

I am not going to argue with that - the platform could have an advanced SQL syntax parser in advanced queries to recognize actual syntax in them. But the fact is that... it doesn't! An advanced query will accept basically whatever you put in there - which allows you to build any arbitrarily complex query, use any built-in primitives the database may have, in some situations even call stored procedures (even though it is not a recommended use for the advanced query).

The only thing that advanced queries recognize are the constructs I mentioned in my previous post - {ENTITY}, {ENTITY}.* and {ENTITY}.[valid_attribute_name]. Use of these patternss will comply with TrueChange (you get errors for invalid Entities and/or attributes).
{ENTITY}.* is simply a convinient construct that gives you the power of * with the guarantee that attributes will come in a correct order - since it is replaced with an enumeration of all entity attributes in the correct order, rather than just dumping tablename.* in the query.

---

Standard SQL can handle that, so why does the platform not handle this correct ?
In fact it should/could/must not make a difference.

Let me disagree with you on this. Standard SQL cannot handle that - what happens is that the querying tools do not care about types, so if your select returns (text,int) instead of (int, text) they just display the results correctly. If you do SELECT * FROM {ENTITY} and provide a structure with the correct number of attributes, all of type text, it will work - you just need to know which column matches the actual columsn you want. Remember that, in an advanced query, you are explicitly returning each column to a specific structure with a given datatype.

Try creating a table TI with 2 columns (text, int), another one IT with 2 columns (in, text), put some values in TI, and then run this statement:

INSERT INTO IT
SELECT * FROM TI;

You will have the same problem - you will need to write something like:

INSERT INTO IT

SELECT int, text FROM TI.

to have the values written correctly.

---

Advanced queries are actually in the limit of the abstaction layer offered by OutSystems, because to use them you need to know something about how these things are implemented - hence the origin of this post. The ultimate goal is getting rid of the Advanced Query node (and having that power in the «simple» Query node), but this is yet another topic.

Cheers,

26 Jan 2012

Nuno Cavalheira Antunes

Hello Acácio,

Thank you for the heads up.

This actualy explain a problem I had in one client with Oracle DB. We made some advanced queries with alias.* and the platform gave warnings about the output being diferent from the entities structures.

As it was a warning, and everithing seemed to work, we disregard it. Now everithing is clear thanks to your article. I think that instead of a warning, an error would be best used in this cases.

Kind regards,

Nuno

13 Jan 2012

Tiago Bernardo

Champion

Hello.
I'm aware of the alias problem in advanced queries.
But how can I avoid using aliases in the following advanced query? Is it possible?
I know I should use simple queries whenever possible. This is just an example of a more complex advanced query.
Thank you.

SELECT
    {MY_ENTITY}.*,
     group1.*,
     group2.*
FROM
    {MY_ENTITY} 
    INNER JOIN {GROUP} group1
        ON (group1.[Id] = {MY_ENTITY}.[Group1_Id])
    INNER JOIN {GROUP} group2
        ON (group2.[Id] = {MY_ENTITY}.[Group2_Id])

14 Sep 2012

3 replies

Last reply 23 Aug 2016

Show thread

Hide thread

Tiago Bernardo

Champion

Hello again.
I've been shown that in this example it's not possible to avoid using aliases.
But to avoid that the advanced query has problems in runtime if the attributes of GROUP are added, deleted, or changed in order, the solution is:

SELECT
{MY_ENTITY}.*,
group1.*,
group2.*
FROM
{MY_ENTITY}
INNER JOIN (SELECT {GROUP}.* FROM {GROUP}) group1

ON (group1.[Id] = {MY_ENTITY}.[Group1_Id])
INNER JOIN (SELECT {GROUP}.* FROM {GROUP}) group2

ON (group2.[Id] = {MY_ENTITY}.[Group2_Id])

Tiago Bernardo wrote:

		SELECT
    {MY_ENTITY}.*,
     group1.*,
     group2.*
FROM
    {MY_ENTITY} 
    INNER JOIN {GROUP} group1
        ON (group1.[Id] = {MY_ENTITY}.[Group1_Id])
    INNER JOIN {GROUP} group2
        ON (group2.[Id] = {MY_ENTITY}.[Group2_Id])

20 Sep 2012

sdb da

Tiago Bernardo wrote:

I have similar problem, anyone has solution for this?

23 Aug 2016

Tiago Bernardo

Champion

I believe the possible solutions are still the previous presented.

sdb da wrote:

Tiago Bernardo wrote:

I have similar problem, anyone has solution for this?

23 Aug 2016

André Vieira

Staff

Hi all,

Imho I'd go to not using wildcards at all in advanced queries :)

Easily you fall into another pitfall when you start putting things like

SELECT
{MY_ENTITY}.*
, group1.*
, group2.*
, {ANOTHER_TABLE_WITH_300_ATTRIBS}.*
, {TABLE_INCLUDING_NTEXT_ATTRIBS}.*
FROM
....

It is a best practice to only return the columns you need; it might give you a little more work but it pays off in the long run.

Cheers,
André

20 Sep 2012

Ricardo Silva

Hello sbd da,

The query example on Tiago's post does not warrant use of Advanced Query. You can do that query using an aggregate without any problem and let the platform generate the correct aliases and attribute expansions.

Do you have a specific example of a query which you can't do on an aggregate which you need help with ?

Ricardo Silva

23 Aug 2016

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines