Inconsistent query results when inner joining with {User} entity

Inconsistent query results when inner joining with {User} entity


I have a custom entity with user related data that I am inner joining with the system {User} entity to retrieve the user name, something like, ... INNER JOIN {MyEntity} ON {MyEntity}.[UserId] = {User}.[Id] ...
Running this query from within the platform seems to exclude some rows but running it against the database yields the expected results.
However if I change the join to an outer join all expected rows are returned and, in fact, all rows from the {User} entity are not NULL, example, ... LEFT OUTER JOIN {MyEntity} ON {MyEntity}.[UserId] = {User}.[Id] ...
Can anyone shed some light on this?
Can you share your code? The inner join should return the same you see when doing this query in the database.

Hi José,

Here is the code/SQL from the AdvancedQuery action,

    INNER JOIN {User} ON {ProcessInstance}.[UpdatedBy] = {User}.[Id]
    INNER JOIN {MachineStatus} ON {ProcessInstance}.[StatusId] = {MachineStatus}.[Id]
    {ProcessInstance}.[Id] IN (@qProcessInstanceIds)
    {ProcessInstance}.[Id] DESC
Running this query from ServiceStudio with the parameter set to two ProcessInstanceIds,

@qProcessInstanceIds = "14475,14476"
Returns only one row but running this same query from the MS SQL Magamenet Studio returns the two ProcessInstance rows and associated {User} information.

I noticed the missing rows are those where the user id equals to 1, I wonder if this is some OutSystems security feature. Meanwhile I managed to work around the issue and this is no longer a problem, though it would be interesting to understand why.

Nuno -

Makes sense. User ID 1 is the default admin user for logging into Service Center, and if you are using a different User Provider for your application than "System" then that user is being filtered out of the query results.

As JJ says, queries in the platform only return users your application can see, i.e. users from your user provider eSpace. Cheers,
Hello. Thanks for your reply.