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 2022/06/20 04:03:34 UTC
[GitHub] [shardingsphere] wsm12138 opened a new issue, #18434: When use DistSQL excute 'preview select * from e;' ,Showing unexpected results
wsm12138 opened a new issue, #18434:
URL: https://github.com/apache/shardingsphere/issues/18434
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
```
we find java version: java8, full_version=1.8.0_312
ShardingSphere-5.1.3-SNAPSHOT
Commit ID: dirty-7bfa655d42754ec1144d26af7089962703d840e6
Commit Message: Optimize sharding tables route logic when execute cursor statement (#18413)
Branch: 7bfa655d42754ec1144d26af7089962703d840e6
Build time: 2022-06-20T10:04:48+0800
```
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
```
sharding_db=> preview select * from e;
preview select * from f;
preview select * from g; data_source_name | actual_sql
------------------+------------------
ds_1 | select * from e
(1 row)
sharding_db=> data_source_name | actual_sql
------------------+------------------
ds_2 | select * from f
(1 row)
sharding_db=>
data_source_name | actual_sql
------------------+------------------
ds_3 | select * from g
(1 row)
```
### Actual behavior
```
sharding_db=> preview select * from e;
preview select * from f;
preview select * from g; data_source_name | actual_sql
------------------+------------------
ds_1 | select * from e
(1 row)
sharding_db=> data_source_name | actual_sql
------------------+------------------
ds_1 | select * from f
(1 row)
sharding_db=>
data_source_name | actual_sql
------------------+------------------
ds_2 | select * from g
(1 row)
```
### Reason analyze (If you can)
use yaml Show the normal
but use distsql 'ALTER SQL_PARSER RULE SQL_COMMENT_PARSE_ENABLE=true;' ,Showing unexpected results
maybe distsql have bug?
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
#### config-sharding.yaml
```
databaseName: sharding_db
dataSources:
ds_1:
url: jdbc:opengauss://ip:prot/demo_ds_0
username: gaussdb
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_2:
url: jdbc:opengauss://ip:prot/demo_ds_1
username: gaussdb
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_3:
url: jdbc:opengauss://ip:prot/dzq
username: gaussdb
password:
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
d:
actualDataNodes: ds_1.d_1,ds_2.d_2,ds_3.d_3
databaseStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: d_database_inline
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: d_table_inline
broadcastTables:
- a
- b
- c
defaultDatabaseStrategy:
none:
defaultTableStrategy:
none:
shardingAlgorithms:
d_database_inline:
type: INLINE
props:
algorithm-expression: ds_${id % 3 + 1}
d_table_inline:
type: INLINE
props:
algorithm-expression: d_${id % 3 + 1}
```
#### server.yaml
```
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: 630_dzq
server-lists: ip:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: true
rules:
- !AUTHORITY
users:
- root@%:root
provider:
type: ALL_PERMITTED
props:
sql-show: true
```
####
sql
```
/* ShardingSphere hint: dataSourceName=ds_1 */ CREATE TABLE e(id int, name char);
INSERT INTO e(id, name) VALUES(1, '1'), (2, '2'), (3, '3');
/* ShardingSphere hint: dataSourceName=ds_2 */ CREATE TABLE f(id int, name char);
INSERT INTO f(id, name) VALUES(1, '1'), (2, '2'), (3, '3');
/* ShardingSphere hint: dataSourceName=ds_3 */ CREATE TABLE g(id int, name char);
INSERT INTO g(id, name) VALUES(1, '1'), (2, '2'), (3, '3');
```
### Example codes for reproduce this issue (such as a github link).
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuichenchuxin closed issue #18434: When use DistSQL excute 'preview select * from e;' ,Showing unexpected results
Posted by GitBox <gi...@apache.org>.
tuichenchuxin closed issue #18434: When use DistSQL excute 'preview select * from e;' ,Showing unexpected results
URL: https://github.com/apache/shardingsphere/issues/18434
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wsm12138 commented on issue #18434: When use DistSQL excute 'preview select * from e;' ,Showing unexpected results
Posted by GitBox <gi...@apache.org>.
wsm12138 commented on issue #18434:
URL: https://github.com/apache/shardingsphere/issues/18434#issuecomment-1161278600
@lanchengx @RaigorJiang
please take a look
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #18434: When use DistSQL excute 'preview select * from e;' ,Showing unexpected results
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #18434:
URL: https://github.com/apache/shardingsphere/issues/18434#issuecomment-1189845077
@wsm12138
After verification, I found that the reason was that the routing results did not meet expectations when creating table, so the preview query got that result.
#### sql_parser rule
```sql
postgres=> SHOW SQL_PARSER RULE;
sql_comment_parse_enable | parse_tree_cache | sql_statement_cache
--------------------------+--------------------------------------------+----------------------------------------------
true | {"initialCapacity":128,"maximumSize":1024} | {"initialCapacity":2000,"maximumSize":65535}
(1 row)
```
#### routing of create table
```
[INFO ] 2022-07-20 13:22:14.462 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Logic SQL: /* ShardingSphere hint: dataSourceName=ds_0 */ CREATE TABLE g(id int, name char);
[INFO ] 2022-07-20 13:22:14.462 [Connection-2-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLCreateTableStatement(super=CreateTableStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[CommentSegment(text=/* ShardingSphere hint: dataSourceName=ds_0 */, startIndex=0, stopIndex=45)]), table=SimpleTableSegment(tableName=TableNameSegment(startIndex=60, stopIndex=60, identifier=IdentifierValue(value=g, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), columnDefinitions=[ColumnDefinitionSegment(startIndex=62, stopIndex=67, columnName=ColumnSegment(startIndex=62, stopIndex=63, identifier=IdentifierValue(value=id, quoteCharacter=NONE), owner=Optional.empty), dataType=DataTypeSegment(startIndex=65, stopIndex=67, dataTypeName=int, dataLength=null), primaryKey=false, notNull=false, referencedTables=[]), ColumnDefinitionSegment(startIndex=70, stopIndex=78, columnName=ColumnSegment(startIndex=70, stopIndex=73, identi
fier=IdentifierValue(value=name, quoteCharacter=NONE), owner=Optional.empty), dataType=DataTypeSegment(startIndex=75, stopIndex=78, dataTypeName=char, dataLength=null), primaryKey=false, notNull=false, referencedTables=[])], constraintDefinitions=[]), ifNotExists=false)
[INFO ] 2022-07-20 13:22:14.462 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: /* ShardingSphere hint: dataSourceName=ds_0 */ CREATE TABLE g(id int, name char);
```
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] wsm12138 commented on issue #18434: When use DistSQL excute 'preview select * from e;' ,Showing unexpected results
Posted by GitBox <gi...@apache.org>.
wsm12138 commented on issue #18434:
URL: https://github.com/apache/shardingsphere/issues/18434#issuecomment-1159984785
# normal
#### server.yaml
```
rules:
- !AUTHORITY
users:
- root@%:root
# - sharding@:sharding
provider:
type: ALL_PERMITTED
- !SQL_PARSER
sqlCommentParseEnabled: true
sqlStatementCache:
initialCapacity: 2000
maximumSize: 65535
parseTreeCache:
initialCapacity: 128
maximumSize: 1024
props:
sql-show: true
```
#### sql
```
/* ShardingSphere hint: dataSourceName=ds_1 */ CREATE TABLE e(id int, name char);
INSERT INTO e(id, name) VALUES(1, '1'), (2, '2'), (3, '3');
/* ShardingSphere hint: dataSourceName=ds_2 */ CREATE TABLE f(id int, name char);
INSERT INTO f(id, name) VALUES(1, '1'), (2, '2'), (3, '3');
/* ShardingSphere hint: dataSourceName=ds_3 */ CREATE TABLE g(id int, name char);
INSERT INTO g(id, name) VALUES(1, '1'), (2, '2'), (3, '3');
preview select * from e;
preview select * from f;
preview select * from g;
```
#### behavior
<img width="670" alt="image" src="https://user-images.githubusercontent.com/86462784/174529972-9c3da9f4-a49f-4e68-ad25-8e71c7302cbc.png">
# unnormal
#### server.yaml
```
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: 630_dzq
server-lists: ip:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: true
rules:
- !AUTHORITY
users:
- root@%:root
provider:
type: ALL_PERMITTED
props:
sql-show: true
```
#### sql
```
ALTER SQL_PARSER RULE SQL_COMMENT_PARSE_ENABLE=true;
/* ShardingSphere hint: dataSourceName=ds_1 */ CREATE TABLE e(id int, name char);
INSERT INTO e(id, name) VALUES(1, '1'), (2, '2'), (3, '3');
/* ShardingSphere hint: dataSourceName=ds_2 */ CREATE TABLE f(id int, name char);
INSERT INTO f(id, name) VALUES(1, '1'), (2, '2'), (3, '3');
/* ShardingSphere hint: dataSourceName=ds_3 */ CREATE TABLE g(id int, name char);
INSERT INTO g(id, name) VALUES(1, '1'), (2, '2'), (3, '3');
preview select * from e;
preview select * from f;
preview select * from g;
```
#### behavior
<img width="538" alt="image" src="https://user-images.githubusercontent.com/86462784/174530281-ddb7dd03-01e8-46cb-9352-8abb0e9f103b.png">
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #18434: When use DistSQL excute 'preview select * from e;' ,Showing unexpected results
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #18434:
URL: https://github.com/apache/shardingsphere/issues/18434#issuecomment-1189854039
OK, thank you @tuichenchuxin
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tuichenchuxin commented on issue #18434: When use DistSQL excute 'preview select * from e;' ,Showing unexpected results
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #18434:
URL: https://github.com/apache/shardingsphere/issues/18434#issuecomment-1189852735
https://github.com/apache/shardingsphere/issues/18620
hint with datasource has been deleted
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org