Issue 1:
I want to know if there is a problem with my transaction manager or is the SQL statement incorrectly written, or is it something else?I hope anyone can give me some hints.

The development environment：hibernate+spring

Exception:

com.google.common.eventbus.EventBus$LoggingSubscriberExceptionHandler handleException

Could not dispatch event: io.shardingsphere.core.transaction.listener.TransactionListener@5c0a4d7f to public void io.shardingsphere.core.transaction.listener.TransactionListener.listen(io.shardingsphere.core.transaction.event.TransactionEvent) throws java.sql.SQLException

My HibernateTransactionManager design:

@bean

@scope("singleton")

public HibernateTransactionManager transactionManager() {

return new AppTransactionManager();

}

@SuppressWarnings("serial")

public final class AppTransactionManager extends HibernateTransactionManager {

public boolean isExistingTransaction() {
Object transaction = doGetTransaction();
return isExistingTransaction(transaction);
}

@Autowired
@Override
public void setSessionFactory(SessionFactory sessionFactory) {
super.setSessionFactory(sessionFactory);
}

}

sql action log:

com.google.common.eventbus.EventBus$LoggingSubscriberExceptionHandler handleException

Could not dispatch event: io.shardingsphere.core.transaction.listener.TransactionListener@5c0a4d7f to public void io.shardingsphere.core.transaction.listener.TransactionListener.listen(io.shardingsphere.core.transaction.event.TransactionEvent) throws java.sql.SQLException

Hibernate: select playerenti_.id as id1_11_ from player playerenti_ where playerenti_.account=?

2018-08-13 15:30:26,752 [INFO ] [game-BalancingPool-/loginActor-14] Sharding-Sphere-SQL - Rule Type: sharding

2018-08-13 15:30:26,752 [INFO ] [game-BalancingPool-/loginActor-14] Sharding-Sphere-SQL - Logic SQL: select playerenti_.id as id1_11_ from player playerenti_ where playerenti_.account=?

2018-08-13 15:30:26,752 [INFO ] [game-BalancingPool-/loginActor-14] Sharding-Sphere-SQL - SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=player, alias=Optional.of(playerenti_))]), conditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(beginPosition=38, skippedSchemaNameLength=0, originalLiterals=player)], parametersIndex=1)), containStar=false, selectListLastPosition=33, groupByLastPosition=0, items=[CommonSelectItem(expression=playerenti_.id, alias=Optional.of(id1_11_))], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null)

2018-08-13 15:30:26,753 [INFO ] [game-BalancingPool-/loginActor-14] Sharding-Sphere-SQL - Actual SQL: sharding0 ::: select playerenti_.id as id1_11_ from player playerenti_ where playerenti_.account=? ::: [[123]]

2018-08-13 15:30:26,753 [INFO ] [game-BalancingPool-/loginActor-14] Sharding-Sphere-SQL - Actual SQL: sharding1 ::: select playerenti_.id as id1_11_ from player playerenti_ where playerenti_.account=? ::: [[123]]

2018-08-13 15:30:26,753 [INFO ] [game-BalancingPool-/loginActor-14] Sharding-Sphere-SQL - Actual SQL: sharding2 ::: select playerenti_.id as id1_11_ from player playerenti_ where playerenti_.account=? ::: [[123]]

com.google.common.eventbus.EventBus$LoggingSubscriberExceptionHandler handleException

Could not dispatch event: io.shardingsphere.core.transaction.listener.TransactionListener@5c0a4d7f to public void io.shardingsphere.core.transaction.listener.TransactionListener.listen(io.shardingsphere.core.transaction.event.TransactionEvent) throws java.sql.SQLException

com.google.common.eventbus.EventBus$LoggingSubscriberExceptionHandler handleException

Could not dispatch event: io.shardingsphere.core.transaction.listener.TransactionListener@5c0a4d7f to public void io.shardingsphere.core.transaction.listener.TransactionListener.listen(io.shardingsphere.core.transaction.event.TransactionEvent) throws java.sql.SQLException

com.google.common.eventbus.EventBus$LoggingSubscriberExceptionHandler handleException

Could not dispatch event: io.shardingsphere.core.transaction.listener.TransactionListener@5c0a4d7f to public void io.shardingsphere.core.transaction.listener.TransactionListener.listen(io.shardingsphere.core.transaction.event.TransactionEvent) throws java.sql.SQLException

2018-08-13 15:30:26,767 [INFO ] [game-BalancingPool-/loginActor-14] IdWorkerBaseImpl - timestamp:1534145426767,timestampLeftShift:19,nextId:128613326315848704,workerId:1,sequence:0

Hibernate: insert into player (account, active_pass, active_time, ban_end_time, ban_talk_end_time, channel, create_server_id, create_step, device, introduce, mark, nickname, pic_id, pwd, register_time, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

2018-08-13 15:30:26,865 [INFO ] [game-BalancingPool-/loginActor-14] Sharding-Sphere-SQL - Rule Type: sharding

2018-08-13 15:30:26,865 [INFO ] [game-BalancingPool-/loginActor-14] Sharding-Sphere-SQL - Logic SQL: insert into player (account, active_pass, active_time, ban_end_time, ban_talk_end_time, channel, create_server_id, create_step, device, introduce, mark, nickname, pic_id, pwd, register_time, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

2018-08-13 15:30:26,865 [INFO ] [game-BalancingPool-/loginActor-14] Sharding-Sphere-SQL - SQLStatement: InsertStatement(super=DMLStatement(super=AbstractSQLStatement(type=DML, tables=Tables(tables=[Table(name=player, alias=Optional.absent())]), conditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=id, tableName=player), operator=EQUAL, positionValueMap={}, positionIndexMap={0=15})])])), sqlTokens=[TableToken(beginPosition=12, skippedSchemaNameLength=0, originalLiterals=player), io.shardingsphere.core.parsing.parser.token.InsertValuesToken@2618291e], parametersIndex=16)), columns=[Column(name=account, tableName=player), Column(name=active_pass, tableName=player), Column(name=active_time, tableName=player), Column(name=ban_end_time, tableName=player), Column(name=ban_talk_end_time, tableName=player), Column(name=channel, tableName=player), Column(name=create_server_id, tableName=player), Column(name=create_step, tableName=player), Column(name=device, tableName=player), Column(name=introduce, tableName=player), Column(name=mark, tableName=player), Column(name=nickname, tableName=player), Column(name=pic_id, tableName=player), Column(name=pwd, tableName=player), Column(name=register_time, tableName=player), Column(name=id, tableName=player)], generatedKeyConditions=[], insertValues=InsertValues(insertValues=[InsertValue(type=VALUES, expression=(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), parametersCount=16)]), columnsListLastPosition=193, generateKeyColumnIndex=-1, insertValuesListLastPosition=250)

2018-08-13 15:30:26,866 [INFO ] [game-BalancingPool-/loginActor-14] Sharding-Sphere-SQL - Actual SQL: sharding0 ::: insert into player (account, active_pass, active_time, ban_end_time, ban_talk_end_time, channel, create_server_id, create_step, device, introduce, mark, nickname, pic_id, pwd, register_time, id) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ::: [[123, 0, null, null, null, 0, 1, 4, ZDRkMmQwNWVkNDVmMzA4ZDk1ZDI2MmFhZjQyZjU4NTY=, null, 1, null, null, password, 2018-08-13 15:30:26.768, 128613326315848704]]

Issue 2:
Supporting DISTINCT SQL syntax is on the way.

We plan to support DISTINCT SQL syntax. The following is our thought about DISTINCT, welcome to discuss this solution.

SQL Usage

None GROUP BY
GROUP BY

Simple SELECT
SELECT DISTINCT price FROM item;
SELECT DISTINCT price FROM item GROUP BY price;

COUNT()
SELECT COUNT(DISTINCT price) FROM item;
SELECT COUNT(DISTINCT price) FROM item GROUP BY price;

SUM()
SELECT SUM(DISTINCT price) FROM item;
SELECT SUM(DISTINCT price) FROM item GROUP BY price;

AVG()
SELECT AVG(DISTINCT price) FROM item;
SELECT AVG(DISTINCT price) FROM item GROUP BY price;

MIN()
SELECT MIN(DISTINCT price) FROM item;
SELECT MIN(DISTINCT price) FROM item GROUP BY price;

MAX()
SELECT MAX(DISTINCT price) FROM item;
SELECT MAX(DISTINCT price) FROM item GROUP BY price;

Processing

The basic processing procedure is as follow:

SQL Parse --> SQL Route --> SQL Rewrite --> SQL Execute --> SQL Merge

Considering those DISTINCT SQL above, we can group those SQL to handle.

Simple SELECT

SQL Parse

It is necessary to analyze the DISTINCT token.

SQL Route

To make sure where SQL should be sent to execute.

SQL Rewrite

Rewrite logic SQL to actual SQL. Let us suppose we rewrite logic SQL to 2 actual SQLs:

SELECT DISTINCT price FROM item_0;

SELECT DISTINCT price FROM item_1;

SQL Execute

Send actual SQLs to some of the shardings to execute. Let us suppose that we get two resultsets, r1 and r2 from two shardings.

SQL Merge

To get distinct results from r1 and r2 by DISTINCT(r1, r2).

Therefore, we can get fianl distinct results from all of the shardings.

COUNT() & SUM()

The processing of COUNT() and SUM() is same, so we take COUNT() for example.

SQL Parse

It is necessary to analyze the DISTINCT token.

SQL Route

To make sure where SQL should be sent to execute.

SQL Rewrite

Rewrite logic SQL to actual SQL. Let us suppose we rewrite logic SQL to 2 actual SQLs:

SELECT DISTINCT price FROM item_0;

SELECT DISTINCT price FROM item_1;

SQL Execute

Send actual SQLs to some of the shardings to execute. Let us suppose that we get two resultsets, r1 and r2 from two shardings.

SQL Merge

To get distinct results r3 from r1 and r2 by DISTINCT(r1, r2), then it is possiable to get final correct COUNT(DISTINCT price) by counting r3.

You can find instead of getting COUNT(DISTINCT price) from shardings and counting those resultsets, we get DISTINCT price from each sharding and distinguish those results again in our procedure. At last, we calculate the COUNT() of TWICE-DISTINCT results. The processing of SUM() is same with this.

AVG

Handling AVG is more complex. We need to get SUM(DISTINCT price) and COUNT(DISTINCT price) by using the processing above. After that, we can get AVG(DISTINCT price) by calculating SUM(DISTINCT price)/COUNT(DISTINCT price).

MIN() & MAX()

It is easier to process two of those functions. Here, we take MIN() for example.

SQL Parse

It is necessary to analyze the DISTINCT token.

SQL Route

To make sure where SQL should be sent to execute.

SQL Rewrite

Rewrite logic SQL to actual SQL. Let us suppose we rewrite logic SQL to 2 actual SQLs:

SELECT MIN(DISTINCT price) FROM item_0;

SELECT MIN(DISTINCT price) FROM item_1;

SQL Execute

Send actual SQLs to some of the shardings to execute. Let us suppose that we get two resultsets, r1 and r2 from two shardings.

SQL Merge

To get final MIN(DISTINCT price) result from r1 and r2 by MIN(r1, r2).

DISTINCT + GROUP BY

Welcome to join us to discuss this processing.

Base on the result of the above analysis, we can get the following tasks:

Parding the SQL syntax.

Rewriting the logic SQL containing DISTINCT to the relative actual SQLs correctly.

Executing those actual SQLs

Merging those resultsets by using corresponding algorithms.