Fujinzhong commented 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:
# shardingjdbc config
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:
[email protected]
