FlyingZC opened a new issue, #29156: URL: https://github.com/apache/shardingsphere/issues/29156
# Background Hi community. This issue is for #29149. The ShardingSphere SQL parser engine helps users to parse SQL to create the AST (Abstract Syntax Tree) and visit the AST to get SQLStatement (Java Object). Currently, we are planning to enhance the support for `SQL Server` SQL parsing in ShardingSphere. More details: https://shardingsphere.apache.org/document/current/en/reference/sharding/parse/ # Task This issue is to support more `SQL Server` sql parsing, as follows: ```sql SELECT DISTINCT user.FirstName, user.LastName INTO ms_user FROM user INNER JOIN ( SELECT * FROM ClickStream WHERE cs.url = 'www.microsoft.com' ) AS ms ON user.user_ip = ms.user_ip ``` [link](https://learn.microsoft.com/zh-cn/sql/t-sql/statements/create-external-table-transact-sql?view=sql-server-ver16) ```sql SELECT req.session_id , req.total_elapsed_time AS duration_ms , req.cpu_time AS cpu_time_ms , req.total_elapsed_time - req.cpu_time AS wait_time , req.logical_reads , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text FROM sys.dm_exec_requests AS req CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST ORDER BY total_elapsed_time DESC ``` [link](https://learn.microsoft.com/zh-cn/troubleshoot/sql/database-engine/performance/troubleshoot-slow-running-queries) ```sql SELECT t.text, (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time, (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time, ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / qs.execution_count) AS avg_wait_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.total_logical_writes / qs.execution_count AS avg_writes, (qs.total_elapsed_time/1000) AS cumulative_elapsed_time_all_executions FROM sys.dm_exec_query_stats qs CROSS apply sys.Dm_exec_sql_text (sql_handle) t WHERE t.text like '<Your Query>%' -- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped. ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC ``` [link](https://learn.microsoft.com/zh-cn/troubleshoot/sql/database-engine/performance/troubleshoot-slow-running-queries) ```sql SELECT t.text, qs.total_elapsed_time / qs.execution_count AS avg_elapsed_time, qs.total_worker_time / qs.execution_count AS avg_cpu_time, (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count AS avg_wait_time, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, qs.total_logical_writes / qs.execution_count AS avg_writes, qs.total_elapsed_time AS cumulative_elapsed_time FROM sys.dm_exec_query_stats qs CROSS apply sys.Dm_exec_sql_text (sql_handle) t WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time > 0.2 ORDER BY qs.total_elapsed_time / qs.execution_count DESC ``` [link](https://learn.microsoft.com/zh-cn/troubleshoot/sql/database-engine/performance/troubleshoot-slow-running-queries) ```sql SELECT 'DECLARE @serverName NVARCHAR(512) = N''' + value + '''' FROM sys.dm_hadr_fabric_config_parameters WHERE parameter_name = 'DnsRecordName' UNION SELECT 'DECLARE @node NVARCHAR(512) = N''' + NodeName + '.' + Cluster + '''' FROM ( SELECT SUBSTRING(replica_address, 0, CHARINDEX('\', replica_address)) AS NodeName, RIGHT(service_name, CHARINDEX('/', REVERSE(service_name)) - 1) AppName, JoinCol = 1 FROM sys.dm_hadr_fabric_partitions fp INNER JOIN sys.dm_hadr_fabric_replicas fr ON fp.partition_id = fr.partition_id INNER JOIN sys.dm_hadr_fabric_nodes fn ON fr.node_name = fn.node_name WHERE service_name LIKE '%ManagedServer%' AND replica_role = 2 ) t1 LEFT JOIN ( SELECT value AS Cluster, JoinCol = 1 FROM sys.dm_hadr_fabric_config_parameters WHERE parameter_name = 'ClusterName' ) t2 ON (t1.JoinCol = t2.JoinCol) INNER JOIN ( SELECT [value] AS AppName FROM sys.dm_hadr_fabric_config_parameters WHERE section_name = 'SQL' AND parameter_name = 'InstanceName' ) t3 ON (t1.AppName = t3.AppName) UNION SELECT 'DECLARE @port NVARCHAR(512) = N''' + value + '''' FROM sys.dm_hadr_fabric_config_parameters WHERE parameter_name = 'HadrPort' ``` [link](https://learn.microsoft.com/zh-cn/azure/azure-sql/managed-instance/managed-instance-link-preparation?view=azuresql) # Process 1. First confirm that this is a correct `SQL Server` sql syntax, if not please leave a message under the issue and ignore it; 2. Compare SQL definitions in Official SQL Doc and ShardingSphere SQL Doc; 3. If there is any difference in ShardingSphere SQL Doc, please correct them by referring to the Official SQL Doc; 4. Run mvn install the current_file_module; 5. Check whether there are any exceptions. If indeed, please fix them. (Especially xxxVisitor.class); 6. Add new corresponding SQL case in SQL Cases and expected parsed result in Expected Statement XML; 7. Run SQLParserParameterizedTest to make sure no exceptions. # Relevant Skills 1. Master JAVA language 2. Have a basic understanding of Antlr `g4` file 3. Be familiar with `SQL Server` SQLs -- 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. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
