Flaky test: TestShowGrantsForCurrentUserUsingRole

[karuppiah7890]

Enhancement

The test TestShowGrantsForCurrentUserUsingRole is flaky. I was trying to run all the unit tests in the repo and noticed the TestShowGrantsForCurrentUserUsingRole test fail like this -

=== CONT  TestShowGrantsForCurrentUserUsingRole
    result.go:49: 
        	Error Trace:	result.go:49
        	            				privileges_test.go:2286
        	Error:      	Not equal: 
        	            	expected: "[GRANT USAGE ON *.* TO 'joe'@'%']\n[GRANT UPDATE ON role.* TO 'joe'@'%']\n[GRANT SELECT ON test.* TO 'joe'@'%']\n[GRANT DELETE ON mysql.user TO 'joe'@'%']\n[GRANT 'admins'@'%', 'engineering'@'%', 'otherrole'@'%' TO 'joe'@'%']\n"
        	            	actual  : "[GRANT USAGE ON *.* TO 'joe'@'%']\n[GRANT SELECT ON test.* TO 'joe'@'%']\n[GRANT UPDATE ON role.* TO 'joe'@'%']\n[GRANT DELETE ON mysql.user TO 'joe'@'%']\n[GRANT 'admins'@'%', 'engineering'@'%', 'otherrole'@'%' TO 'joe'@'%']\n"
        	            	
        	            	Diff:
        	            	--- Expected
        	            	+++ Actual
        	            	@@ -1,4 +1,4 @@
        	            	 [GRANT USAGE ON *.* TO 'joe'@'%']
        	            	+[GRANT SELECT ON test.* TO 'joe'@'%']
        	            	 [GRANT UPDATE ON role.* TO 'joe'@'%']
        	            	-[GRANT SELECT ON test.* TO 'joe'@'%']
        	            	 [GRANT DELETE ON mysql.user TO 'joe'@'%']
        	Test:       	TestShowGrantsForCurrentUserUsingRole
        	Messages:   	sql:SHOW GRANTS FOR current_user() USING otherrole;, args:[]

But later while running the same test standalone to check the error properly (as the full unit test log was too long and I didn't search for TestShowGrantsForCurrentUserUsingRole properly) the test passed

Looks like it's a case of a flaky test where the ordering of a query's result rows are causing the test to fail at times. In the above error it shows that [GRANT SELECT ON test.* TO 'joe'@'%'] came prior to GRANT UPDATE ON role.* TO 'joe'@'%'] but the expected was for it to come later

Does ordering of the query results matter in this case? I was assuming it doesn't but I don't know for sure. But if query result row order doesn't matter, we can write the test in such a way that order is ignored as part of the assertion

Let me know what you folks think!

[unconsolable]

Another occurence https://ci.pingcap.net/blue/organizations/jenkins/tidb_ghpr_check_2/detail/tidb_ghpr_check_2/30304/pipeline/62/

[2021-08-31T10:12:10.496Z] === CONT  TestShowGrantsForCurrentUserUsingRole
[2021-08-31T10:12:10.496Z]     result.go:49: 
[2021-08-31T10:12:10.496Z]         	Error Trace:	result.go:49
[2021-08-31T10:12:10.496Z]         	            				privileges_test.go:2399
[2021-08-31T10:12:10.496Z]         	Error:      	Not equal: 
[2021-08-31T10:12:10.496Z]         	            	expected: "[GRANT USAGE ON *.* TO 'joe'@'%']\n[GRANT UPDATE ON role.* TO 'joe'@'%']\n[GRANT SELECT ON test.* TO 'joe'@'%']\n[GRANT DELETE ON mysql.user TO 'joe'@'%']\n[GRANT 'admins'@'%', 'engineering'@'%', 'otherrole'@'%' TO 'joe'@'%']\n"
[2021-08-31T10:12:10.496Z]         	            	actual  : "[GRANT USAGE ON *.* TO 'joe'@'%']\n[GRANT SELECT ON test.* TO 'joe'@'%']\n[GRANT UPDATE ON role.* TO 'joe'@'%']\n[GRANT DELETE ON mysql.user TO 'joe'@'%']\n[GRANT 'admins'@'%', 'engineering'@'%', 'otherrole'@'%' TO 'joe'@'%']\n"
[2021-08-31T10:12:10.496Z]         	            	
[2021-08-31T10:12:10.496Z]         	            	Diff:
[2021-08-31T10:12:10.496Z]         	            	--- Expected
[2021-08-31T10:12:10.496Z]         	            	+++ Actual
[2021-08-31T10:12:10.496Z]         	            	@@ -1,4 +1,4 @@
[2021-08-31T10:12:10.496Z]         	            	 [GRANT USAGE ON *.* TO 'joe'@'%']
[2021-08-31T10:12:10.496Z]         	            	+[GRANT SELECT ON test.* TO 'joe'@'%']
[2021-08-31T10:12:10.496Z]         	            	 [GRANT UPDATE ON role.* TO 'joe'@'%']
[2021-08-31T10:12:10.496Z]         	            	-[GRANT SELECT ON test.* TO 'joe'@'%']
[2021-08-31T10:12:10.496Z]         	            	 [GRANT DELETE ON mysql.user TO 'joe'@'%']
[2021-08-31T10:12:10.496Z]         	Test:       	TestShowGrantsForCurrentUserUsingRole
[2021-08-31T10:12:10.496Z]         	Messages:   	sql:SHOW GRANTS FOR current_user() USING otherrole;, args:[]

[unconsolable]

@tisonkun Could you please add this one to #25899 ?

[tisonkun]

@unconsolable added.

@karuppiah7890 thanks for reporting this. We regard unstable tests as bugs - and should be fixed in time seriously.

@morgo could you please take a look?

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'backport-x.y.z',
and then remove 'needs-more-info' label.

[tisonkun]

test unstable, affects master, fixed in master