Behaviour using * (wildcard) in Advanced SQL

Hey guys,

So a while back I found something out which was quite interesting and I figured I should share it with all of you!

So I was writing some advanced SQL and for some reason I decided to use only an * to select my data.

To my surprise the data that was returned to my output structure did not match at all with was in the database.... 

Using {Entity}.* seemed to fix this problem, but now that it has been found out, I had to know why this happened!

Looking directly into the database it seemed that the order of the columns/fields in the database had a different order than the one setup as an Entity in Outsystems. Which was the order of results I was getting when only using an * to select data.

So to summise:

- When you use only an * to select data from an Advanced Query, you are fetching data using the order of the database itself.

- When using {Entity}.* you are using the Outsystems Entity order of fields to fetch data (I think Outsystems just uses all fields you have defined in your Entity when you use something like {Entity}.* instead of actually querying * on the database.

Hi Joey,

Your findings are mostly correct. I will try to explain a bit more why/how that happens.

  • When you use just * in a SQL tool, that is passed directly to the database engine (the Executed SQL will use the *), so all columns in the table are returned in the order they are defined (at database level).
  • When you use {Entity}.* in a SQL tool, you're telling the platform to fetch the attributes defined for the Entity at the time it executes. If you analyse the Executed SQL property in Service Studio you will notice it replaces {Entity}.* with {Entity}.[Attribute1], {Entity}.[Attribute2], ...,  {Entity}.[AttributeN]

Why are they different and that error is raised? Because whenever you change an Entity's attributes (for instance when you delete one), the platform doesn't perform destructive changes to the database model, so all columns will be there, but the platform will store meta-information regarding which of those are currently mapped to the Entity's Attributes. So if you use * you are fetching more columns (or in a different and potentially incompatible order) than the ones in use by the current Entity definition.