How can we help you today?
EXISTS statement different behaviour in 5.1 (works in 4.3)
Rob Parker
This could be the same as a bug which we have a fix for which hasn't yet been released. If you email to vistadbsupport@gibraltarsoftware.com (or create a ticket through the Tickets portal here on the web site) we can send you a hotfix version to test whether it is fixed or needs to be investigated.
If that doesn't fix it then we'll need a sample database which demonstrates the problem in order to debug why it is happening.
Rob Parker
This was reminding me of another problem reported with EXISTS (SELECT ...)--but with a different and unrelated symptom--which we had actually fixed back in 5.1.0, so it wouldn't be the same bug as your report, after all.
This bug may also be related to an open ticket with IN (SELECT ...)--and particularly using a correlated sub-query as your query does--which has not yet been solved.
Rob Parker
We've helped Cem find a simple workaround. For anyone else who runs into this problem, the bug seems to be specific to the use of a correlated sub-query (depends on a table of the parent query) as the target of an EXISTS or IN; non-correlated sub-queries (independent of the parent query) don't have the problem. In this case the EXISTS and correlated sub-query can be equivalently written as an IN with a non-correlated sub-query.
For the example query above, replace the OR EXISTS ( ... ) condition with:
OR [AccessControlEntry].[GroupId] IN (SELECT [GroupId] FROM [Membership] WHERE [UserId] = @UserId)
Notice that the sub-query here can run on its own (assuming that @UserId is defined and assigned) because the reference to the [AccessControlEntry] table has be moved out of the sub-query.
We're still working on fixing the bug for the correlated sub-query case because not all queries can necessarily be easily rewritten into a non-correlated structure.
Rob Parker
Update: The problem was corrected in the 5.2.2.1587 release.
Performance will still tend to be better with a non-correlated sub-query, however.
Cem Alacayir
Do you have any idea why the below query is interpreted differently in v5.1?
SELECT [AccessControlEntry].[AccessControlListId], [AccessControlEntry].[UserId], [AccessControlEntry].[GroupId] FROM [AccessControlList] INNER JOIN [RootFolder] ON [AccessControlList].[RootFolderId] = [RootFolder].[Id] LEFT JOIN [AccessControlEntry] ON [AccessControlList].[Id] = [AccessControlEntry].[AccessControlListId] AND ( [AccessControlEntry].[UserId] = @UserId OR EXISTS ( SELECT m.[GroupId] FROM [Membership] m WHERE m.[GroupId] = [AccessControlEntry].[GroupId] AND m.[UserId] = @UserId ) )In v4.3, it was working as expected, ie. it returned rows that either matched @UserId or matched a group for @UserId. In v5.1, it seems the EXISTS statement is skipped and only rows with @UserID are returned.