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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
