You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/09/18 03:56:33 UTC
[GitHub] [shardingsphere] Fujinzhong edited a comment on issue #6497: Can subqueries be supported in select？
Fujinzhong edited a comment on issue #6497:
URL: https://github.com/apache/shardingsphere/issues/6497#issuecomment-694634942
> @Fujinzhong There is still some routing and parsing info in your log, which will show you how ShardingSphere handles your SQL. Could you give them a look?
09:49:55.788 [main] INFO ShardingSphere-SQL - Logic SQL: SELECT `date`, customer_name `name`, customer_account `account`, customer_phone `phone`, count( 1 ) total, sum( CASE WHEN CODE IN ( 30,- 30 ) THEN 1 ELSE 0 END ) AS succ, sum( CASE WHEN CODE IN ( - 30,- 20 ) THEN 1 ELSE 0 END ) AS fail, sum( CASE WHEN CODE IN ( 20 ) THEN 1 ELSE 0 END ) AS excep, sum( CASE WHEN CODE IN ( 30 ) THEN 1 ELSE 0 END ) AS report_succ, sum( CASE WHEN CODE IN ( - 30 ) THEN 1 ELSE 0 END ) AS report_fail, sum( CASE WHEN CODE IN ( - 11 ) THEN 1 ELSE 0 END ) AS `reject` FROM ( SELECT customer_account,customer_phone,phone phone_s, customer_name, date_format( create_time, '%Y-%m-%d' ) AS `date`,max( CODE ) AS CODE FROM mms_order WHERE customer_account IS NOT NULL and create_time between ? and ? GROUP BY customer_account,send_id,customer_phone,phone,customer_name,`date` ) a GROUP BY `date`,customer_name,customer_account,customer_phone
09:49:55.789 [main] INFO ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@1f1a57e8, tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5a056d39), tablesContext=org.apache.shardingsphere.sql.parser.binder.segment.table.TablesContext@5a056d39, projectionsContext=ProjectionsContext(startIndex=8, stopIndex=494, distinctRow=false, projections=[ColumnProjection(owner=null, name=date, alias=Optional.empty), ColumnProjection(owner=null, name=customer_name, alias=Optional[name]), ColumnProjection(owner=null, name=customer_account, alias=Optional[account]), ColumnProjection(owner=null, name=customer_phone, alias=Optional[phone]), AggregationProjection(type=COUNT, innerExpression=( 1 ), alias=Optional[total], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( 30,- 30 ) T
HEN 1 ELSE 0 END ), alias=Optional[succ], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( - 30,- 20 ) THEN 1 ELSE 0 END ), alias=Optional[fail], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( 20 ) THEN 1 ELSE 0 END ), alias=Optional[excep], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( 30 ) THEN 1 ELSE 0 END ), alias=Optional[report_succ], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( - 30 ) THEN 1 ELSE 0 END ), alias=Optional[report_fail], derivedAggregationProjections=[], index=-1), AggregationProjection(type=SUM, innerExpression=( CASE WHEN CODE IN ( - 11 ) THEN 1 ELSE 0 END ), alias=Optional[reject], derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segmen
t.select.groupby.GroupByContext@4926f6d6, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@351a39e7, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@78f28dbb, containsSubquery=false)
09:49:55.791 [main] INFO ShardingSphere-SQL - Actual SQL: master ::: SELECT `date`, customer_name `name`, customer_account `account`, customer_phone `phone`, count( 1 ) total, sum( CASE WHEN CODE IN ( 30,- 30 ) THEN 1 ELSE 0 END ) AS succ, sum( CASE WHEN CODE IN ( - 30,- 20 ) THEN 1 ELSE 0 END ) AS fail, sum( CASE WHEN CODE IN ( 20 ) THEN 1 ELSE 0 END ) AS excep, sum( CASE WHEN CODE IN ( 30 ) THEN 1 ELSE 0 END ) AS report_succ, sum( CASE WHEN CODE IN ( - 30 ) THEN 1 ELSE 0 END ) AS report_fail, sum( CASE WHEN CODE IN ( - 11 ) THEN 1 ELSE 0 END ) AS `reject` FROM ( SELECT customer_account,customer_phone,phone phone_s, customer_name, date_format( create_time, '%Y-%m-%d' ) AS `date`,max( CODE ) AS CODE FROM mms_order WHERE customer_account IS NOT NULL and create_time between ? and ? GROUP BY customer_account,send_id,customer_phone,phone,customer_name,`date` ) a GROUP BY `date`,customer_name,customer_account,customer_phone ::: [2020-09-18 00:00:00, 2020-09-18 23:59:59]
This is full log and in the following text is shardingsphere-jdbc configuration.
spring:
shardingsphere:
datasource:
names: master,record
master:
jdbc-url: 'jdbc:mysql://172.16.40.217:3306/XXXXXXXX?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai'
username: xxxx
password: xxxxx
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
hikari:
minimum-idle: 10
maximum-pool-size: 25
auto-commit: true
record:
jdbc-url: 'jdbc:mysql://xxxxxx:3306/XXXXXXXX?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai'
username: xxxx
password: xxxx
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
hikari:
minimum-idle: 10
maximum-pool-size: 25
auto-commit: true
sharding:
default-data-source-name: master
binding-tables: mms_order
tables:
mms_order:
actual-data-nodes: record.mms_order_$->{0..10}
key-generator:
column: id
type: SNOWFLAKE
table-strategy:
standard:
precise-algorithm-class-name: com.demo.common.config.database.DateShardingTableAlgorithm
range-algorithm-class-name: com.demo.common.config.database.TableShardingRangeAlgorithm
sharding-column: create_time
props:
sql:
show: true
----------------------------------------------------------------
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