#17 order of statements matters when using bind variables

[Alexander Velkov]

Tested with JoSQL ver. 2.1
Two objects: user and userprop
parent <-> child situation: The user object contains a java.util.Set of userprop objects.
I'm using bind variables to process the variables in the query.

Two queries:
SELECT * FROM user WHERE username = ? AND ( SELECT * FROM userProperties WHERE name = ? AND value= ? ).size > 0
query.setVariable(1, "johnnyTester");
query.setVariable(2, "key");
query.setVariable(3, "val");

(This query returns the correct results)

AND the same query but the order of the filters changed

SELECT * FROM user WHERE ( SELECT * FROM userProperties WHERE name = ? AND value= ? ).size > 0 AND username = ?
query.setVariable(1, "key");
query.setVariable(2, "val");
query.setVariable(3, "johnnyTester");

(This query returns no results and no exception is thrown)

[Gary Bentley]

Hi,

Thanks for reporting this.

This is due to the way that inner selects work. Because they cannot be inited until execution time the anonymous bind variable has not been allocated an internal name, so in effect (for the 2nd query) "johnnyTester" becomes bind variable 1 and then "key" becomes 2 and "val" becomes 3. For the first query it just so happens that the bind variables will be in the correct order at execution time.

The fix for this will take a while and probably needs to be placed into the parser (where there is at least a single place where the anonymous names can be allocated). For now I'd recommend that you use named variables which won't have the same issue.

Thanks,

Gary