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/03/02 08:10:40 UTC
[GitHub] [incubator-shardingsphere] nevereverever opened a new issue #4565:
distinct returns wrong result
nevereverever opened a new issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565
sharding-proxy version:4.0.0
I insert some rows to sharding-proxy by my custom hash algorithm.
person_id|name|last_update_time|account|which data node
-|-|-|-|-|
174fd27ef0f34fd5b9296f102ffdbd47| liuqiang| 2020-02-12 20:47:10 |3|ds_0
4295029899a64d1bbfc5b4d37b628f0c| wangwu| 2020-02-12 20:28:22| 2|ds_0
a672ba34d2f14f4d8e3ee9e8ccfd6ca5| liyi| 2020-02-12 20:47:16| 1|ds_0
bf840dc0eaf64f7a8a7493c3ffea8799| huangyao| 2020-02-12 20:28:38| 3|ds_0
c63b749e981a4dc6b4942b18978e0d08| lisi| 2020-02-12 20:28:17| 2|ds_0
e28da7bc9ddc45bdb2974041ed564fc3| zhangsan| 2020-02-12 20:27:48| 1|ds_0
f741d4fb69f14b869dac11e2889fe8c1| penggang| 2020-02-12 20:28:34| 3|ds_0
0596342115c14328b2b0458d0860a90c| lingang| 2020-02-12 20:28:47| 3|ds_1
2f8b95104c114cc5a8ab6bc86ef9ab62| luyang| 2020-02-12 20:28:50| 2|ds_1
4397392fb6274bf2bfcfc41ae1da55a5| tangliang |2020-02-12 20:28:42| 1|ds_1
c57c358c49cc4da6b98477ad8a55db3f| help| 2020-02-13 16:01:28| 3|ds_1
d9edd130ab8241f6be4b96a12cdb0404| zhaoliu| 2020-02-12 20:28:26| 2|ds_1
f97ea21d03124aa8b59a23bad5585e81| wanger| 2020-02-12 20:28:29| 1|ds_1
then,I execute this SQL:
```SQL
select distinct account from tbperson;
```
result is :
account
-|
3|
2|
1|
3|
2|
1|
right result gonna be:
account
-|
3|
2|
1|
look forward to your reply.
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking edited a comment on issue
#4565: distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-594426703
If table user ~ 2 databases and 3 tables of each.
```
1.select distinct age from user => wrong
2.select distinct age from user group by age
3.select distinct age from user order by age
4.select distinct age from user group by age order by age
5.select distinct age from user group by name
6.select distinct age from user order by id
7.select distinct age from user group by name order by id
8.select distinct age from user group by 1
```
All of above sqls will executed to right result except the first one.
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin edited a comment on issue
#4565: distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
geomonlin edited a comment on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-593724603
I test it with mysql, eg: table: t_order
`CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));`
sharding rule:
```
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
shardingRule:
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_item_id
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds_${user_id % 2}
defaultTableStrategy:
none:
```
The inserted user_id contains
**6
2
2
2
6
4
2
2
3
3
1
3
3**
When exec "SELECT distinct user_id from t_order", the output resutl is Duplicate data，
**3
1
3
4
6
4
2
6
2**
When exec `SELECT distinct user_id from t_order GROUP BY user_id`, the resutl is Correct.
**1
2
3
4
6**
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4565:
distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-593303962
Could you make a simple demo for reproduce your issue?
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking edited a comment on issue
#4565: distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-594426703
If table user ~ 2 databases and 3 tables of each.
```
**1.select distinct age from user => wrong**
2.select distinct age from user group by age
3.select distinct age from user order by age
4.select distinct age from user group by age order by age
5.select distinct age from user group by name
6.select distinct age from user order by id
7.select distinct age from user group by name order by id
8.select distinct age from user group by 1
```
All of above sqls will executed to right result except the first one.
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking edited a comment on issue
#4565: distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-594426703
If table user ~ 2 databases and 3 tables of each.
```
1.select distinct age from user => wrong
2.select distinct age from user group by age
3.select distinct age from user order by age
4.select distinct age from user group by age order by age
5.select distinct age from user group by name
6.select distinct age from user order by id
7.select distinct age from user group by name order by id
8.select distinct age from user group by 1
```
All of above sqls will executed to right result except the first one.
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4565:
distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-593876735
@nevereverever You can use
```
select distinct account from tbperson order by account;
```
instead of your sql.
It will be executed to correct result.
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin edited a comment on issue
#4565: distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
geomonlin edited a comment on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-593724603
I test it, eg: table: t_order `CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));`
The inserted user_id contains
**6
2
2
2
6
4
2
2
3
3
1
3
3**
When exec "SELECT distinct user_id from t_order", the output resutl is Duplicate data，
**3
1
3
4
6
4
2
6
2**
When exec `SELECT distinct user_id from t_order GROUP BY user_id`, the resutl is Correct.
**1
2
3
4
6**
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin edited a comment on issue
#4565: distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
geomonlin edited a comment on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-593724603
I test it with mysql, This seems to be a bug. eg: table: t_order
`CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));`
sharding rule:
```
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
shardingRule:
tables:
t_order:
actualDataNodes: ds_${0..1}.t_order_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_id
t_order_item:
actualDataNodes: ds_${0..1}.t_order_item_${0..1}
tableStrategy:
inline:
shardingColumn: order_id
algorithmExpression: t_order_item_${order_id % 2}
keyGenerator:
type: SNOWFLAKE
column: order_item_id
bindingTables:
- t_order,t_order_item
defaultDatabaseStrategy:
inline:
shardingColumn: user_id
algorithmExpression: ds_${user_id % 2}
defaultTableStrategy:
none:
```
The inserted user_id contains
**6
2
2
2
6
4
2
2
3
3
1
3
3**
When exec "SELECT distinct user_id from t_order", the output resutl is Duplicate data，
**3
1
3
4
6
4
2
6
2**
When exec `SELECT distinct user_id from t_order GROUP BY user_id`, the resutl is Correct.
**1
2
3
4
6**
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu closed issue #4565: distinct
returns wrong result
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] nevereverever commented on issue #4565:
distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
nevereverever commented on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-593707113
> Could you make a simple demo for reproduce your issue?
@kimmking
This example is simple enough.
I mean,SQL is sent to two physical nodes for execution, instead of merging the execution results.
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin commented on issue #4565:
distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
geomonlin commented on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-593724603
I test it, eg: table: t_order `CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));`
Insert user_id include
**6
2
2
2
6
4
2
2
3
3
1
3
3**
When exec "SELECT distinct user_id from t_order", the output resutl is Duplicate data，
**3
1
3
4
6
4
2
6
2**
When exec `SELECT distinct user_id from t_order GROUP BY user_id`, the resutl is Correct.
**1
2
3
4
6**
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] nevereverever commented on issue #4565:
distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
nevereverever commented on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-594973561
Oh, I see what you're saying.
This bug will be fixed in 5.0.0?
@kimmking
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] geomonlin edited a comment on issue
#4565: distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
geomonlin edited a comment on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-593724603
I test it with mysql, eg: table: t_order `CREATE TABLE IF NOT EXISTS t_order (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));`
The inserted user_id contains
**6
2
2
2
6
4
2
2
3
3
1
3
3**
When exec "SELECT distinct user_id from t_order", the output resutl is Duplicate data，
**3
1
3
4
6
4
2
6
2**
When exec `SELECT distinct user_id from t_order GROUP BY user_id`, the resutl is Correct.
**1
2
3
4
6**
----------------------------------------------------------------
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
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4565:
distinct returns wrong result
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4565: distinct returns wrong result
URL: https://github.com/apache/incubator-shardingsphere/issues/4565#issuecomment-594426703
If table user ~ 2 databases and 3 tables of each.
```
1. select distinct age from user => wrong
2.select distinct age from user group by age
3.select distinct age from user order by age
4.select distinct age from user group by age order by age
5.select distinct age from user group by name
6.select distinct age from user order by id
7.select distinct age from user group by name order by id
8.select distinct age from user group by 1
```
All of above sqls will executed to right result except the first one.
----------------------------------------------------------------
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
With regards,
Apache Git Services