[GitHub] [shardingsphere] lwtdev commented on issue #7918: When the table is not sharding , in 5.0.0, SQL parsing is wrong, but it is correct in 4.1.1

[GitBox <gi...@apache.org>]

lwtdev commented on issue #7918:
URL: https://github.com/apache/shardingsphere/issues/7918#issuecomment-718344884


   @strongduanmu Glad to see your detailed reply.
   
   ## DCL Statements
   >I refer to the official MySQL documentation, the SET PASSWORD statement should not support = PASSWORD('auth_string').
   
   I found a demo in [MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/show-index.html)
   
   ```sql
   SET PASSWORD FOR 'jeffrey'@'localhost' = 'auth_string';
   ```
   And I try `password('auth_string')` in MySQL, it works well:
   ```sql
   mysql> show variables like '%innodb_version%';
   +----------------+--------+
   | Variable_name  | Value  |
   +----------------+--------+
   | innodb_version | 5.7.23 |
   +----------------+--------+
   1 row in set, 1 warning (0.00 sec)
   
   mysql> select user,host from mysql.user where user = 'test1';
   +-------+------+
   | user  | host |
   +-------+------+
   | test1 | %    |
   +-------+------+
   1 row in set (0.00 sec)
   
   mysql> SET PASSWORD FOR 'test1'@'%' = PASSWORD('auth_string');
   Query OK, 0 rows affected, 1 warning (0.00 sec)
   ```
   ## DAL Satements
   
   >The SHOW CREATE EVENT, SHOW CREATE USER and SHOW CREATE VIEW statements are not supported in 4.X and 5.X versions.
   
   I have test `create view` and `show create view` statements in 4.1.1. `create view` is not supported but 'show create view` is works well.
   ```sql
   mysql> show create view v2;
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   | View | Create View                                                                                                                            | character_set_client | collation_connection |
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   | v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `customer`.`id` AS `id` from `customer` | utf8                 | utf8_general_ci      |
   +------+----------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
   1 row in set (0.00 sec)
   ```
   Maybe we can support `show create ?`  when we support `create ?` . I found that there are already some problems related to this.
   
   
   
   >The syntax of the SHOW INDEXES and SHOW KEYS statements are as follows, the first parameter of the "from in" clause is "tbl_name", which is the ai table in the case​​.
   
   I adjusted this SQL, and the test results are as follows:
   ```sql
   ShardingProxy(4.1.1)No Sharding
   showKeysWithLike[hasShardingKey:none];	 Support:true;	SQL: SHOW KEYS in customer in db1 where column_name like '%id%';
   ds_0|SHOW KEYS in customer in db1 where column_name like '%id%'|
   
   
   ShardingProxy(5.0.0.RC1)No Sharding
   showKeysWithLike[hasShardingKey:none];	 Support:false;	SQL: SHOW KEYS in customer in db1 where column_name like '%id%';
   java.sql.SQLException: 2Unknown exception: [Conversion = 'i']
   ```
   ## RL Statements
   
   >According to the definition of MySQL documents, the start slave io_thread until RELAY_LOG_FILE = 'log_name', MASTER_LOG_POS = 4; statement seems to be illegal.
   
   Yes, this SQL is illegal, the grammer check is ok, but parameter for `io_thread` is illegal.I adjusted this SQL, and the test results are as follows:
   
   ```sql
   ShardingProxy(4.1.1)No Sharding
   startSlaveIoThread[hasShardingKey:none];	 Support:true;	SQL: start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_LOG_POS = 4;
   ds_0|start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_LOG_POS = 4|
   
   ShardingProxy(5.0.0.RC1)No Sharding
   startSlaveIoThread[hasShardingKey:none];	 Support:false;	SQL: start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_LOG_POS = 4;
   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `start slave io_thread until RELAY_LOG_FILE  = 'log_name', RELAY_LOG_POS = 4`'
   ```


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org