You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "cicilzx (via GitHub)" <gi...@apache.org> on 2023/08/20 07:02:08 UTC
[GitHub] [shardingsphere] cicilzx opened a new issue, #28182: Error in query results for left join
cicilzx opened a new issue, #28182:
URL: https://github.com/apache/shardingsphere/issues/28182
### Which version of ShardingSphere did you use?
5.4.0
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
This is the query I am using:
```sql
CREATE TABLE website (web_id INT NOT NULL, user_id INT NOT NULL, id INT NOT NULL AUTO_INCREMENT, name VARCHAR (20) NOT NULL, url VARCHAR (30) DEFAULT '', age TINYINT UNSIGNED NOT NULL, alexa INT UNSIGNED NOT NULL, uv FLOAT DEFAULT 0, country CHAR (8) NOT NULL DEFAULT '', PRIMARY KEY (`id`)) ENGINE = InnoDB CHARSET = utf8;
INSERT INTO website (web_id, user_id, name, url, age, alexa, uv, country) VALUE (1, 1, 'a', 'https://www.a.com', 10, 50000, 1.0, 'USA');
INSERT INTO website (web_id, user_id, name, url, age, alexa, uv, country) VALUE (2, 1, 'b', 'https://www.b.com', 26, 40000, 0.5, 'UK');
INSERT INTO website (web_id, user_id, name, url, age, alexa, uv, country) VALUES (3, 1, 'c', 'https://www.c.com', 37, 70000, 2.4, 'China');
INSERT INTO website (web_id, user_id, name, url, age, alexa, uv, country) VALUES (7, 1, 'd', 'https://www.d.com', 28, 30000, 1.7, 'Italy');
INSERT INTO website (web_id, user_id, name, url, age, alexa, uv, country) VALUES (8, 1, 'e', 'https://www.e.com', 31, 5000, 2.9, 'China');
INSERT INTO website (web_id, user_id, name, url, age, alexa, uv, country) VALUES (4, 1, 'f', 'https://www.f.com', 10, 50000, 1.0, 'USA');
INSERT INTO website (web_id, user_id, name, url, age, alexa, uv, country) VALUE (5, 1, 'a', 'https://www.g.com', 50, 20000, 3.7, 'China');
INSERT INTO website SET web_id=6,user_id=1,name='f',url='https://www.h.com',age=29,alexa=12000,uv=2.9,country='Russia';
CREATE TABLE ntable (
web_id INT NOT NULL,
use_id INT NOT NULL,
`id` int NOT NULL,
PRIMARY KEY (`id`)
);
SELECT DISTINCT t1.id FROM website t1 LEFT JOIN ntable t2 ON t1.id = t2.id ORDER BY id;
```
### Expected behavior
This is the output of using Mysql directly:
```sh
Query OK, 0 rows affected (0.009 sec)
Query OK, 1 row affected (0.001 sec)
Query OK, 1 row affected (0.001 sec)
Query OK, 1 row affected (0.001 sec)
Query OK, 1 row affected (0.001 sec)
Query OK, 1 row affected (0.001 sec)
Query OK, 1 row affected (0.002 sec)
Query OK, 1 row affected (0.001 sec)
Query OK, 1 row affected (0.001 sec)
Query OK, 0 rows affected (0.007 sec)
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
+----+
8 rows in set (0.001 sec)
```
### Actual behavior
This is the output of using ShardingSphere-Proxy:
```sh
Query OK, 0 rows affected (0.042 sec)
Query OK, 1 row affected (0.006 sec)
Query OK, 1 row affected (0.006 sec)
Query OK, 1 row affected (0.005 sec)
Query OK, 1 row affected (0.006 sec)
Query OK, 1 row affected (0.007 sec)
Query OK, 1 row affected (0.007 sec)
Query OK, 1 row affected (0.006 sec)
Query OK, 1 row affected (0.006 sec)
Query OK, 0 rows affected (0.072 sec)
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.010 sec)
```
The table creating and data insertion parts are correct, but there are missing query results. It seems that something went wrong when using `left join`.
### My config information
config-sharding.yaml:
```yaml
databaseName: sharding_db
dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_0?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:3306/demo_ds_1?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
students:
actualDataNodes: ds_${0..1}.students_${0..1}
tableStrategy:
standard:
shardingColumn: order_id
shardingAlgorithmName: students_inline
keyGenerateStrategy:
column: order_id
keyGeneratorName: snowflake
student_skill:
actualDataNodes: ds_${0..1}.student_skill_${0..1}
tableStrategy:
standard:
shardingColumn: order_item_id
shardingAlgorithmName: student_skill_inline
keyGenerateStrategy:
column: order_item_id
keyGeneratorName: snowflake
website:
actualDataNodes: ds_${0..1}.website_${0..1}
tableStrategy:
standard:
shardingColumn: web_id
shardingAlgorithmName: website_inline
keyGenerateStrategy:
column: web_id
keyGeneratorName: snowflake
new_web:
actualDataNodes: ds_${0..1}.new_web_${0..1}
tableStrategy:
standard:
shardingColumn: web_id
shardingAlgorithmName: new_web_inline
keyGenerateStrategy:
column: web_id
keyGeneratorName: snowflake
ntable:
actualDataNodes: ds_${0..1}.ntable_${0..1}
tableStrategy:
standard:
shardingColumn: web_id
shardingAlgorithmName: ntable_inline
keyGenerateStrategy:
column: web_id
keyGeneratorName: snowflake
bindingTables:
- students,student_skill,website,new_web,ntable
defaultDatabaseStrategy:
standard:
shardingColumn: user_id
shardingAlgorithmName: database_inline
defaultTableStrategy:
none:
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression: ds_${user_id % 2}
students_inline:
type: INLINE
props:
algorithm-expression: students_${order_id % 2}
student_skill_inline:
type: INLINE
props:
algorithm-expression: student_skill_${order_item_id % 2}
website_inline:
type: INLINE
props:
algorithm-expression: website_${web_id % 2}
new_web_inline:
type: INLINE
props:
algorithm-expression: new_web_${web_id % 2}
ntable_inline:
type: INLINE
props:
algorithm-expression: ntable_${web_id % 2}
keyGenerators:
snowflake:
type: SNOWFLAKE
```
server.yaml:
```yaml
mode:
type: Standalone
repository:
type: JDBC
authority:
users:
- user: root
password: root
- user: sharding
password: sharding
privilege:
type: ALL_PERMITTED
```
--
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] github-actions[bot] commented on issue #28182: Error in query results for left join
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #28182:
URL: https://github.com/apache/shardingsphere/issues/28182#issuecomment-1728354058
There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.
--
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
Re: [I] Error in query results for left join [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #28182: Error in query results for left join
URL: https://github.com/apache/shardingsphere/issues/28182
--
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] strongduanmu commented on issue #28182: Error in query results for left join
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #28182:
URL: https://github.com/apache/shardingsphere/issues/28182#issuecomment-1685476731
Thank you for your feedback @cicilzx, we will check this problem.
--
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
Re: [I] Error in query results for left join [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #28182: Error in query results for left join
URL: https://github.com/apache/shardingsphere/issues/28182
--
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
Re: [I] Error in query results for left join [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #28182:
URL: https://github.com/apache/shardingsphere/issues/28182#issuecomment-3677691466
Issue Understanding
- Environment: ShardingSphere-Proxy 5.4.0. Database sharding by user_id (ds_${user_id % 2}); table sharding by web_id (website_${web_id % 2}, ntable_${web_id % 2}); website/ntable are bound tables.
- Symptom: After running the sample DDL/DML, SELECT DISTINCT t1.id FROM website t1 LEFT JOIN ntable t2 ON t1.id = t2.id ORDER BY id; returns 1..8 on native MySQL single table, but only 1..4 via Proxy.
Root Cause
- Column id uses MySQL local AUTO_INCREMENT per physical shard and is not globally unique.
- Sharding routes to ds_1 (all user_id = 1), and table shards split by web_id, producing two physical tables. Each table generates id = 1..4; after merge, DISTINCT collapses duplicates, leaving 1..4.
- The right table ntable is empty; the LEFT JOIN does not filter rows, so the discrepancy is solely due to duplicate id values across shards.
Problem Analysis
- The config does not assign a distributed key to id; the Snowflake generator is on web_id and inserts explicitly set it, so id remains physical auto-increment.
- At the logical layer, id repeats across shards; any de-duplication, ordering, or joins relying on id uniqueness diverge from single-database behavior.
- This stems from sharding design: ShardingSphere does not merge auto-increment sequences into a global unique stream; consistent results require an explicit globally unique key or using the sharding key in identifiers.
Conclusion
- This is expected behavior from the current sharding design, not a LEFT JOIN bug. To align with single-database results, use a globally distributed key for the primary identifier (e.g., apply Snowflake to the primary key and let the engine generate it) or join/query using an identifier that includes the sharding key, avoiding reliance on per-shard auto-increment id.
--
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