DataManager Query bug?

Support
,
#1

Hi,

We had a Query like this:

dataManager.loadValue
(“select u.email from User u where u.username in (select ugu.username from bpm_UserGroup ug join bpm_UserGroupUser ugu ON ug.id = ugu.userGroup.id)”, String.class)

With this query, the following exception happened:
column t1.username does not exist, did you mean t0.username?

Since I assume t0 is u, t1 must be ug and t2 must be ugu, the query generates the wrong t# for ugu in ugu.username

If we changed the order to

bpm_UserGroupUser ugu join bpm_UserGroup ug

then it was working properly.

Not sure if we did something wrong, but I wanted to report it in case it’s a bug.

Regards,
Gergő

#2

And you also need a Where condition, because without a where, it generates the following where condition:

WHERE (t3.DELETED_DATE IS NULL))

which also throws an exception because t3 doesn’t exist

missing FROM-clause entry for table "t3"

#3

Query depends of your business logic. What are you quering for ? A list of email of users having at least one BPM group ?
Then, first of all, use loadValues but not loadValue and use where:

List<KeyValueEntity> kvValues = dataManager
   .loadValues("select u.email from User u where u.username in (select ugu.username from bpm_UserGroup ug join bpm_UserGroupUser ugu where ug.id = ugu.userGroup.id)")
   .property("email")
   .list();

Then why you compare id ? Its work:

select u.email from User u where u.username in (select ugu.username from bpm_UserGroup ug join bpm_UserGroupUser ugu where ug = ugu.userGroup)

Moreover JPA know how to join this two tables:

select u.email from User u where u.username in (select ugu.username from bpm_UserGroupUser ugu join bpm_UserGroup ug)

Depending from your business logic may be this your choise:

select distinct u.email from User u, bpm_UserGroupUser ugu where u.username = ugu.username

P.S. I do not have BPM license so may be make mistake somewhere but you catch the idea.

#4

Thanks for your answer, this works and also satisfies the logic we wanted.

We used a kinda similar approach already, this happened while we were trying to find a solution so the question is not about finding a working solution, I was just curious why did the ugu.username got a wrong variable (t1.username) when it was supposed to be t2.username in:

bpm_UserGroup ug join bpm_UserGroupUser ugu

while it was working in:

bpm_UserGrouUser ugu join bpm_UserGroupUser ug

and also why did the auto generated where condition (WHERE (t3.DELETED_DATE IS NULL))
wanted to use t3 which didn’t exist

#5

Looks like the problem is reproduced only with ON clause for soft-deleted entities in a sub-query, which is quite rare situation and has a simple workaround.
Please let us know if it is really important in some case.