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/04/28 02:23:35 UTC
[GitHub] [shardingsphere] xiaoma20082008 opened a new issue #5357: execute agg function whthout any other columns occurred error
xiaoma20082008 opened a new issue #5357:
URL: https://github.com/apache/shardingsphere/issues/5357
## Bug Report
not support min,max without any other columns, like `select max(guid),min(guid) from t`
### Which version of ShardingSphere did you use?
4.0.1
### Which project did you use? Sharding-JDBC or Sharding-Proxy?
Sharding-JDBC
### Expected behavior
can execute
### Actual behavior
throws exceptions
```java
java.lang.IllegalStateException: Can't find index: AggregationProjection(type=MIN, innerExpression=(guid), alias=Optional.absent(), derivedAggregationProjections=[], index=-1), please add alias for aggregate selections
at com.google.common.base.Preconditions.checkState(Preconditions.java:469)
at org.apache.shardingsphere.sql.parser.relation.statement.impl.SelectSQLStatementContext.setIndexForAggregationProjection(SelectSQLStatementContext.java:110)
at org.apache.shardingsphere.sql.parser.relation.statement.impl.SelectSQLStatementContext.setIndexes(SelectSQLStatementContext.java:103)
at org.apache.shardingsphere.sharding.merge.dql.DQLMergeEngine.merge(DQLMergeEngine.java:66)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.getCurrentResultSet(ShardingPreparedStatement.java:165)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.getResultSet(ShardingPreparedStatement.java:155)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.executeQuery(ShardingPreparedStatement.java:124)
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
```java
ShardingDataSource dataSouce = xxx ;
String sql = " select max(guid),min(guid) from t";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepare(sql);
ResultSet rs = ps.executeQuery();
) {
// print rs
}
```
### Example codes for reproduce this issue (such as a github link).
```java
// just execute sql: select max(guid),min(guid) from t, and t is a sharding table
```
----------------------------------------------------------------
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
[GitHub] [shardingsphere] tristaZero commented on issue #5357: agg function must include an alias stmt instead of exeucte it directly
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #5357:
URL: https://github.com/apache/shardingsphere/issues/5357#issuecomment-639222597
@xiaoma20082008 Hi I am sorry to ping you again, If you have something new, could you give your update here? Since we have to close the stale issue past 7 days.
----------------------------------------------------------------
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
[GitHub] [shardingsphere] tristaZero commented on issue #5357: agg function must include an alias stmt instead of exeucte it directly
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #5357:
URL: https://github.com/apache/shardingsphere/issues/5357#issuecomment-631867252
Hi @xiaoma20082008
Thanks for your feedback. I tried the SQL `select max(order_id),min(order_id) from t_order` in the `master branch` and there is no exception, see this,
```
[INFO ] 12:02:50.003 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: show tables
[INFO ] 12:02:50.003 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dal.dialect.mysql.ShowTablesStatement@1977d221, tablesContext=TablesContext(tables=[]))
[INFO ] 12:02:50.003 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: show tables
[INFO ] 12:02:50.118 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: SHOW COLUMNS FROM t_order_item FROM sharding_db
[INFO ] 12:02:50.118 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dal.dialect.mysql.ShowColumnsStatement@6b8dfa7c, tablesContext=TablesContext(tables=[SimpleTableSegment(tableName=TableNameSegment(startIndex=18, stopIndex=29, identifier=IdentifierValue(value=t_order_item, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)]))
[INFO ] 12:02:50.118 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_0 ::: SHOW COLUMNS FROM t_order_item_0
[INFO ] 12:02:50.358 [ShardingSphere-Command-3] ShardingSphere-SQL - Logic SQL: select max(order_id),min(order_id) from t_order
[INFO ] 12:02:50.358 [ShardingSphere-Command-3] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@18ddab5a, tablesContext=TablesContext(tables=[SimpleTableSegment(tableName=TableNameSegment(startIndex=40, stopIndex=46, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)])), tablesContext=TablesContext(tables=[SimpleTableSegment(tableName=TableNameSegment(startIndex=40, stopIndex=46, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)]), projectionsContext=ProjectionsContext(startIndex=7, stopIndex=33, distinctRow=false, projections=[AggregationProjection(type=MAX, innerExpression=(order_id), alias=Optional.empty, derivedAggregationProjections=[], index=-1), AggregationProjection(type=MIN, innerExpression=(order_id), alias=Optional.empty, derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@6b5f856b, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@1dd18185, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@6532edb3, containsSubquery=false)
[INFO ] 12:02:50.359 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: select max(order_id),min(order_id) from t_order_0
[INFO ] 12:02:50.359 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: select max(order_id),min(order_id) from t_order_1
[INFO ] 12:02:50.359 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_1 ::: select max(order_id),min(order_id) from t_order_0
[INFO ] 12:02:50.359 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_1 ::: select max(order_id),min(order_id) from t_order_1
```
My configuration is,
```yaml
schemaName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithm:
type: INLINE
props:
algorithm.expression: t_order_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithm:
type: INLINE
props:
algorithm.expression: t_order_item_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_item_id
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithm:
type: INLINE
props:
algorithm.expression: ds_${user_id % 2}
defaultTableStrategy:
none:
```
Could you try your SQL in the `master branch` or the latest release, i.e., `4.1.0`?
----------------------------------------------------------------
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
[GitHub] [shardingsphere] tristaZero edited a comment on issue #5357: agg function must include an alias stmt instead of exeucte it directly
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #5357:
URL: https://github.com/apache/shardingsphere/issues/5357#issuecomment-631867252
Hi @xiaoma20082008
Thanks for your feedback. I tried the SQL `select max(order_id),min(order_id) from t_order` in the `master branch` and there is no exception, see this,
```
[INFO ] 12:02:50.003 [ShardingSphere-Command-1] ShardingSphere-SQL - Logic SQL: show tables
[INFO ] 12:02:50.003 [ShardingSphere-Command-1] ShardingSphere-SQL - SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dal.dialect.mysql.ShowTablesStatement@1977d221, tablesContext=TablesContext(tables=[]))
[INFO ] 12:02:50.003 [ShardingSphere-Command-1] ShardingSphere-SQL - Actual SQL: ds_0 ::: show tables
[INFO ] 12:02:50.118 [ShardingSphere-Command-2] ShardingSphere-SQL - Logic SQL: SHOW COLUMNS FROM t_order_item FROM sharding_db
[INFO ] 12:02:50.118 [ShardingSphere-Command-2] ShardingSphere-SQL - SQLStatement: CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dal.dialect.mysql.ShowColumnsStatement@6b8dfa7c, tablesContext=TablesContext(tables=[SimpleTableSegment(tableName=TableNameSegment(startIndex=18, stopIndex=29, identifier=IdentifierValue(value=t_order_item, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)]))
[INFO ] 12:02:50.118 [ShardingSphere-Command-2] ShardingSphere-SQL - Actual SQL: ds_0 ::: SHOW COLUMNS FROM t_order_item_0
[INFO ] 12:02:50.358 [ShardingSphere-Command-3] ShardingSphere-SQL - Logic SQL: select max(order_id),min(order_id) from t_order
[INFO ] 12:02:50.358 [ShardingSphere-Command-3] ShardingSphere-SQL - SQLStatement: SelectStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.SelectStatement@18ddab5a, tablesContext=TablesContext(tables=[SimpleTableSegment(tableName=TableNameSegment(startIndex=40, stopIndex=46, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)])), tablesContext=TablesContext(tables=[SimpleTableSegment(tableName=TableNameSegment(startIndex=40, stopIndex=46, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)]), projectionsContext=ProjectionsContext(startIndex=7, stopIndex=33, distinctRow=false, projections=[AggregationProjection(type=MAX, innerExpression=(order_id), alias=Optional.empty, derivedAggregationProjections=[], index=-1), AggregationProjection(type=MIN, innerExpression=(order_id), alias=Optional.empty, derivedAggregationProjections=[], index=-1)]), groupByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.groupby.GroupByContext@6b5f856b, orderByContext=org.apache.shardingsphere.sql.parser.binder.segment.select.orderby.OrderByContext@1dd18185, paginationContext=org.apache.shardingsphere.sql.parser.binder.segment.select.pagination.PaginationContext@6532edb3, containsSubquery=false)
[INFO ] 12:02:50.359 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: select max(order_id),min(order_id) from t_order_0
[INFO ] 12:02:50.359 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_0 ::: select max(order_id),min(order_id) from t_order_1
[INFO ] 12:02:50.359 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_1 ::: select max(order_id),min(order_id) from t_order_0
[INFO ] 12:02:50.359 [ShardingSphere-Command-3] ShardingSphere-SQL - Actual SQL: ds_1 ::: select max(order_id),min(order_id) from t_order_1
```
My configuration is,
```yaml
schemaName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
rules:
- !SHARDING
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithm:
type: INLINE
props:
algorithm.expression: t_order_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithm:
type: INLINE
props:
algorithm.expression: t_order_item_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_item_id
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithm:
type: INLINE
props:
algorithm.expression: ds_${user_id % 2}
defaultTableStrategy:
none:
```
**Could you try your SQL in the `master branch` or the latest release, i.e., `4.1.0`?**
----------------------------------------------------------------
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
[GitHub] [shardingsphere] tristaZero commented on issue #5357: agg function must include an alias stmt instead of exeucte it directly
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #5357:
URL: https://github.com/apache/shardingsphere/issues/5357#issuecomment-643756531
@xiaoma20082008 Hi I am sorry not to see your response beyond 9 days, which took me to close this issue. As you know, any issue without a reply will be closed in 7 days.
----------------------------------------------------------------
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
[GitHub] [shardingsphere] tristaZero commented on issue #5357: agg function must include an alias stmt instead of exeucte it directly
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #5357:
URL: https://github.com/apache/shardingsphere/issues/5357#issuecomment-631868584
BTW, `./incubator-shardingsphere/shardingsphere-jdbc/shardingsphere-jdbc-core/src/test/java/org/apache/shardingsphere/driver/jdbc/core/statement/ShardingSphereStatementTest.java` is recommended as a test entry for the `master branch`.
----------------------------------------------------------------
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
[GitHub] [shardingsphere] xiaoma20082008 commented on issue #5357: agg function must include an alias stmt instead of exeucte it directly
Posted by GitBox <gi...@apache.org>.
xiaoma20082008 commented on issue #5357:
URL: https://github.com/apache/shardingsphere/issues/5357#issuecomment-631895092
ok,I'll try it later.
----------------------------------------------------------------
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
[GitHub] [shardingsphere] tristaZero closed issue #5357: agg function must include an alias stmt instead of exeucte it directly
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #5357:
URL: https://github.com/apache/shardingsphere/issues/5357
----------------------------------------------------------------
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