I came up with this junk when I was trying to clean my repository by finding a DQL that I could use to delete more that 150,000 junk user objects from my repository. Ignore that statement as it is again a junk. What I came up is not very new. It’s just the weirdness in the behavior of the DQLs. Is this weirdness only with the repeating attributes? I would like the DQLs to do much of the talking as I feel they can explain their pain much better. So here it goes:
A1)SELECT COUNT(DISTINCT user_name) FROM dm_user
>> 168241
A2)SELECT COUNT(DISTINCT users_names) FROM dm_group
>> 2916
A3)SELECT COUNT(DISTINCT user_name) FROM dm_user
WHERE user_name NOT IN
(SELECT DISTINCT users_names from dm_group)
>> 0
Isn’t that weird?
B1)SELECT COUNT(DISTINCT user_name) FROM dm_user, dm_group
WHERE ANY dm_group.users_names = dm_user.user_name
>> 2915
A2 VS B1: again weird!
C1)SELECT COUNT(DISTINCT user_name) FROM dm_user
WHERE user_name NOT IN
(SELECT DISTINCT user_name FROM dm_user, dm_group
WHERE ANY dm_group.users_names = dm_user.user_name)
>> 165326
(A2, A3) VS (B1, C1): any explanation?
But that’s a relief indeed. That’s the result I needed. A1 – B1 = C1. Under the current circumstances, that’s encouraging enough to get into some more meddling.
D1)SELECT COUNT(users_names) FROM dm_group
WHERE ANY users_names NOT IN
(SELECT user_name FROM dm_user)
>> 5
What the hell!!!
D2)SELECT users_names FROM dm_group
WHERE ANY users_names NOT IN
(SELECT user_name FROM dm_user)
>> TestUser1 test3 test2 orts test1x
hmmmm…
D3)SELECT COUNT(*) FROM dm_user
WHERE user_name IN
(SELECT users_names FROM dm_group
WHERE ANY users_names NOT IN
(SELECT user_name FROM dm_user))
>> 4
This is insane. Isn’t D3 a contradiction in itself? Can I challenge EMC to explain that? Can someone come to my rescue?
D4)SELECT user_name FROM dm_user
WHERE user_name IN
(SELECT users_names FROM dm_group
WHERE ANY users_names NOT IN
(SELECT user_name FROM dm_user))
>> TestUser1 test3 test2 orts
Here is something that helps; but it doesn’t explain the insanity though.
ConsistencyChecker Report:
Checking for users belonging to groups not in dm_userWARNING CC-0002: User ‘test1x’ is referenced in dm_group with id ‘12000d808004a500’ but does not have a valid dm_user object
Rows Returned: 1
Summery:
Weirdness No. 1: (A1, A2, A3)
Weirdness No. 2: A2 VS B1: Explained by the ConsistencyChecker Report.
Weirdness No. 3: (A2, A3) VS (B1, C1)
Weirdness No. 4: D3: The Contradiction in itself.
I could get an explanation only for Weirdness No. 2 which I guess is not a weirdness at all. I hope someone reading this post would try explaining the other three. 1 & 2 I guess are contributed by repeating attributes but remains unexplained anyway. 4, the D3 is an absolute marvel. Is there a bug in the way DQL works?
C1 is the undisputed winner as it provides the expected result.
Leave a comment