connection verification is diff from mysql

[mccxj]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

create user 'test1'@'%';
create user 'test1'@'127.0.0.1' identified by '123456';
flush privileges;

2. What did you expect to see?

mysql:

$ mysql -utest -p --host 127.0.0.1 --port 3306
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.19-debug Source distribution

3. What did you see instead?

$ mysql -utest -p --host 127.0.0.1 --port 4000
Enter password: ******
ERROR 1045 (28000): Access denied for user 'test'@'127.0.0.1' (using password: YES)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

F:\source\go\src\github.com\pingcap\tidb>git branch -v
* master             1562f2578 [ahead 571] add sum_decimal method for aggfunc (#7096)

[tiancaiamao]

Thanks for your feedback, I'll take a look. @mccxj

[tiancaiamao]

TiDB currently sort rows by 'host, user', that's the problem.
It should do it according to the following rules:

It is possible for the client host name and user name of an incoming connection to match more than one row in the user table. The preceding set of examples demonstrates this: Several of the entries shown match a connection from h1.example.net by fred.

When multiple matches are possible, the server must determine which of them to use. It resolves this issue as follows:

Whenever the server reads the user table into memory, it sorts the rows.

When a client attempts to connect, the server looks through the rows in sorted order.

The server uses the first row that matches the client host name and user name.

The server uses sorting rules that order rows with the most-specific Host values first. Literal host names and IP addresses are the most specific. (The specificity of a literal IP address is not affected by whether it has a netmask, so 198.51.100.13 and 198.51.100.0/255.255.255.0 are considered equally specific.) The pattern '%' means “any host” and is least specific. The empty string '' also means “any host” but sorts after '%'. Rows with the same Host value are ordered with the most-specific User values first (a blank User value means “any user” and is least specific). For rows with equally-specific Host and User values, the order is nondeterministic.

https://dev.mysql.com/doc/refman/8.0/en/connection-access.html
I'll fix it.