In the federation scenario, the result of select view is incomplete； #22553

peilinqian · 2022-12-01T04:12:24Z

Which version of ShardingSphere did you use?

we find java version: java8, full_version=1.8.0_342, full_path=/home/peilq_sharding/bisheng-jdk1.8.0_342//bin/java
ShardingSphere-5.2.2-SNAPSHOT
Commit ID: dirty-753c0cee8ee6fd3db00536da55b64bc5198a3758
Commit Message: Optimize sqlFederationExecutor init logic when sqlFederationType modify dynamically (#22209)
Branch: 753c0ce
Build time: 2022-11-19T10:18:41+0800

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

In the federation scenario, the result of select view is complete；

Actual behavior

In the federation scenario, the result of select view is incomplete；

create view select_view as select distinct(user_id) from t_order order by user_id;
SQL SUCCESS
select * from select_view;
+---------+
| user_id |
+---------+
| 10      |
| 12      |
| 14      |
| 16      |
| 18      |
| 20      |
| 22      |
| 24      |
| 26      |
| 28      |
+---------+
select distinct(user_id) from t_order order by user_id;
+---------+
| user_id |
+---------+
| 10      |
| 11      |
| 12      |
| 13      |
| 14      |
| 15      |
| 16      |
| 17      |
| 18      |
| 19      |
| 20      |
| 21      |
| 22      |
| 23      |
| 24      |
| 25      |
| 26      |
| 27      |
| 28      |
| 29      |
+---------+
drop view select_view;

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

create view select_view as select distinct(user_id) from t_order order by user_id;
select * from select_view;
drop view select_view;
select distinct(user_id) from t_order order by user_id;

Example codes for reproduce this issue (such as a github link).

drop table if exists t_order; 
create table t_order (order_id int primary key, user_id int not null, status varchar(50) not null, merchant_id int not null, remark varchar(50), creation_date date);
insert into t_order values(1000, 10, 'init', 1, 'test', '2017-07-08');
insert into t_order values(1001, 10, 'init', 2, 'test', '2017-07-08');
insert into t_order values(2000, 20, 'init', 3, 'test', '2017-08-08');
insert into t_order values(2001, 20, 'init', 4, 'test', '2017-08-08');
insert into t_order values(1100, 11,  'init', 5, 'test', '2017-08-08');
insert into t_order values(1101, 11, 'init', 6, 'test', '2017-08-08');
insert into t_order values(2100, 21, 'finish', 7, 'test', '2017-08-08');
insert into t_order values(2101, 21, 'finish', 8, 'test', '2017-08-08');
insert into t_order values(1200, 12, 'finish', 9, 'finish', '2017-08-08');
insert into t_order values(1201, 12, 'finish', 10, 'test22', '2017-08-18');
insert into t_order values(2200, 22, 'finish', 11, 'test', '2017-08-18');
insert into t_order values(2201, 22, 'finish', 12, 'test', '2017-08-18');
insert into t_order values(1300, 13, 'finish', 13, '', '2017-08-18');
insert into t_order values(1301, 13, 'finish', 14, 'test', '2017-08-18');
insert into t_order values(2300, 23, 'finish ', 15, 'test', '2017-08-18');
insert into t_order values(2301, 23, 'finish', 16, 'test', '2017-08-18');
insert into t_order values(1400, 14, 'init', 17, '', '2017-08-18');
insert into t_order values(1401, 14, 'init', 18, 'test', '2017-08-18');
insert into t_order values(2400, 24, 'init', 19, 'test', '2017-08-18');
insert into t_order values(2401, 24, 'init', 20, 'test', '2017-08-18');
insert into t_order values(1500, 15, 'init', 1, '', '2017-08-28');
insert into t_order values(1501, 15, 'init', 2, 'test', '2017-08-28');
insert into t_order values(2500, 25, 'init', 3, 'test', '2017-08-28');
insert into t_order values(2501, 25, 'init', 4, 'test', '2017-08-28');
insert into t_order values(1600, 16, 'init', 5, 'test', '2017-08-28');
insert into t_order values(1601, 16, 'init', 6, '', '2017-08-28');
insert into t_order values(2600, 26, 'init', 7, 'test', '2017-08-28');
insert into t_order values(2601, 26, 'init', 8);
insert into t_order values(1700, 17, 'init', 9, 'test', '2017-08-28');
insert into t_order values(1701, 17, 'finish', 10, 'test', '2017-08-18');
insert into t_order values(2700, 27, 'finish', 11, 'test', '2017-08-18');
insert into t_order values(2701, 27, 'finish', 12, 'test', '2017-08-18');
insert into t_order values(1800, 18, 'finish', 13, 'test', '2017-08-18');
insert into t_order values(1801, 18, 'finish', 14);
insert into t_order values(2800, 28, 'finish', 15, 'test', '2017-08-18');
insert into t_order values(2801, 28, 'finish', 16, 'test', '2017-08-18');
insert into t_order values(1900, 19, 'init', 17, 'test', '2017-08-18');
insert into t_order values(1901, 19, 'init', 18, 'test', '2017-08-18');
insert into t_order values(2900, 29, 'init', 19, 'test', '2017-08-18');
insert into t_order values(2901, 29, 'init', 20, 'test', '2017-08-18');
insert into t_order values(1902, 19, 'init', 17, 'test11', '2017-08-18');
insert into t_order values(1903, 19, 'init', 18, 'test12', '2017-08-18');
insert into t_order values(2902, 29, 'init', 19, 'test', '2017-08-18');
insert into t_order values(2903, 29, 'init', 20, 'test', '2017-08-18');

schemaName: test_db
dataSources:
  ds_0:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 260
    minPoolSize: 10
    password: Test@123
    url: jdbc:opengauss://90.90.44.171:14000/test_db?batchMode=on
    username: tpccuser
  ds_1:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 260
    minPoolSize: 10
    password: Test@123
    url: jdbc:opengauss://90.90.44.171:15000/test_db?batchMode=on
    username: tpccuser
rules:
- !SHARDING
  tables:
    t_user:
      actualDataNodes: ds_0.t_user
    t_product:
      actualDataNodes: ds_0.t_product
    t_merchant:
      actualDataNodes: ds_1.t_merchant
    t_product_detail:
      actualDataNodes: ds_1.t_product_detail
    t_order:
      actualDataNodes: ds_${0..1}.t_order
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: database_inline
    t_order_item:
      actualDataNodes: ds_${0..1}.t_order_item
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: database_inline
    t_order_item1:
      actualDataNodes: ds_${0..1}.t_order_item1
      databaseStrategy:
        standard:
          shardingColumn: user_id
          shardingAlgorithmName: database_inline
    t_new_order:
      actualDataNodes: ds_${0..1}.t_new_order_${0..1}
      databaseStrategy:
        standard:
          shardingAlgorithmName: database_inline
          shardingColumn: user_id
      tableStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: table_inline
  bindingTables:
    - t_order,t_order_item
  broadcastTables:
    - t_product_category
    - t_country
  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_${user_id % 2}
        allow-range-query-with-inline-sharding: true
    table_inline:
      type: INLINE
      props:
        algorithm-expression: t_new_order_${order_id % 2}
        allow-range-query-with-inline-sharding: true
mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: 7.212.123.28:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
authority:
  users:
    - user: root@%
      password: root
    - user: sharding
      password: sharding
  privilege:
    type: ALL_PERMITTED
rules:
- !TRANSACTION
  defaultType: XA
  providerType: Atomikos
props:
  sql-show: true
  sql-federation-type: ADVANCED

The text was updated successfully, but these errors were encountered:

peilinqian · 2022-12-01T07:23:56Z

the log of select view is incomplete

[INFO ] 2022-12-01 12:08:22.681 [Connection-233-ThreadExecutor] ShardingSphere-SQL - Logic SQL: create view select_view as select distinct(user_id) from t_order order by user_id
[INFO ] 2022-12-01 12:08:22.681 [Connection-233-ThreadExecutor] ShardingSphere-SQL - SQLStatement: OpenGaussCreateViewStatement(super=CreateViewStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), view=SimpleTableSegment(tableName=TableNameSegment(startIndex=12, stopIndex=22, identifier=IdentifierValue(value=select_view, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), viewDefinition=select distinct(user_id) from t_order order by user_id, select=OpenGaussSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=42, stopIndex=50, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=43, stopIndex=49, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=true), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=57, stopIndex=63, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional.empty, groupBy=Optional.empty, having=Optional.empty, orderBy=Optional[OrderBySegment(startIndex=65, stopIndex=80, orderByItems=[ColumnOrderByItemSegment(super=TextOrderByItemSegment(), column=ColumnSegment(startIndex=74, stopIndex=80, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional.empty))])], combine=Optional.empty), limit=Optional.empty, lock=Optional.empty, window=Optional.empty)))
[INFO ] 2022-12-01 12:08:22.681 [Connection-233-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: create view select_view as select distinct(user_id) from t_order order by user_id
[INFO ] 2022-12-01 12:08:22.710 [Connection-233-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select * from select_view
[INFO ] 2022-12-01 12:08:22.710 [Connection-233-ThreadExecutor] ShardingSphere-SQL - SQLStatement: OpenGaussSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=7, projections=[ShorthandProjectionSegment(startIndex=7, stopIndex=7, owner=Optional.empty, alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=14, stopIndex=24, identifier=IdentifierValue(value=select_view, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional.empty, groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combine=Optional.empty), limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-12-01 12:08:22.710 [Connection-233-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select * from select_view
[INFO ] 2022-12-01 12:08:22.726 [Connection-233-ThreadExecutor] ShardingSphere-SQL - Logic SQL: select distinct(user_id) from t_order order by user_id
[INFO ] 2022-12-01 12:08:22.726 [Connection-233-ThreadExecutor] ShardingSphere-SQL - SQLStatement: OpenGaussSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=15, stopIndex=23, projections=[ColumnProjectionSegment(column=ColumnSegment(startIndex=16, stopIndex=22, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional.empty), alias=Optional.empty)], distinctRow=true), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=30, stopIndex=36, identifier=IdentifierValue(value=t_order, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional.empty, groupBy=Optional.empty, having=Optional.empty, orderBy=Optional[OrderBySegment(startIndex=38, stopIndex=53, orderByItems=[ColumnOrderByItemSegment(super=TextOrderByItemSegment(), column=ColumnSegment(startIndex=47, stopIndex=53, identifier=IdentifierValue(value=user_id, quoteCharacter=NONE), owner=Optional.empty))])], combine=Optional.empty), limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2022-12-01 12:08:22.726 [Connection-233-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: select distinct(user_id) from t_order order by user_id
[INFO ] 2022-12-01 12:08:22.726 [Connection-233-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: select distinct(user_id) from t_order order by user_id
[INFO ] 2022-12-01 12:08:22.729 [Connection-233-ThreadExecutor] ShardingSphere-SQL - Logic SQL: drop view select_view
[INFO ] 2022-12-01 12:08:22.729 [Connection-233-ThreadExecutor] ShardingSphere-SQL - SQLStatement: OpenGaussDropViewStatement(super=DropViewStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), views=[SimpleTableSegment(tableName=TableNameSegment(startIndex=10, stopIndex=20, identifier=IdentifierValue(value=select_view, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty)]))
[INFO ] 2022-12-01 12:08:22.729 [Connection-233-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: drop view select_view
[INFO ] 2022-12-01 12:08:22.740 [Connection-233-ThreadExecutor] ShardingSphere-SQL - Logic SQL: create view select_view as select distinct on (user_id) * from t_order order by user_id,order_id
@

strongduanmu · 2022-12-02T11:11:20Z

This phenomenon is caused by the concurrent operation of metadata. Since there is currently no lock and consistency guarantee, please avoid concurrent execution of DDL statements.

zhaojinchao95 · 2022-12-06T15:03:59Z

public class JDBCDriver {
    static final String DB_URL = "jdbc:mysql://127.0.0.1:3307/sharding_db";
    static final String USER = "root";
    static final String PASS = "root";
    
    public static void main(String[] args) throws Exception {
        Statement statement = getConnection().createStatement();
        List<String> sqlList = Arrays.asList("drop table if exists t_order;",
                "create table t_order (order_id int primary key, user_id int not null, status varchar(50) not null, merchant_id int not null, remark varchar(50), creation_date date);");
        String records = "insert into t_order values(1000, 10, 'init', 1, 'test', '2017-07-08')," +
                "(1001, 10, 'init', 2, 'test', '2017-07-08')," +
                "(2000, 20, 'init', 3, 'test', '2017-08-08')," +
                "(2001, 20, 'init', 4, 'test', '2017-08-08')," +
                "(1100, 11,  'init', 5, 'test', '2017-08-08')," +
                "(1101, 11, 'init', 6, 'test', '2017-08-08')," +
                "(2100, 21, 'finish', 7, 'test', '2017-08-08')," +
                "(2101, 21, 'finish', 8, 'test', '2017-08-08')," +
                "(1200, 12, 'finish', 9, 'finish', '2017-08-08')," +
                "(1201, 12, 'finish', 10, 'test22', '2017-08-18')," +
                "(2200, 22, 'finish', 11, 'test', '2017-08-18')," +
                "(2201, 22, 'finish', 12, 'test', '2017-08-18')," +
                "(1300, 13, 'finish', 13, '', '2017-08-18')," +
                "(1301, 13, 'finish', 14, 'test', '2017-08-18')," +
                "(2300, 23, 'finish ', 15, 'test', '2017-08-18')," +
                "(2301, 23, 'finish', 16, 'test', '2017-08-18')," +
                "(1400, 14, 'init', 17, '', '2017-08-18')," +
                "(1401, 14, 'init', 18, 'test', '2017-08-18')," +
                "(2400, 24, 'init', 19, 'test', '2017-08-18')," +
                "(2401, 24, 'init', 20, 'test', '2017-08-18')," +
                "(1500, 15, 'init', 1, '', '2017-08-28')," +
                "(1501, 15, 'init', 2, 'test', '2017-08-28')," +
                "(2500, 25, 'init', 3, 'test', '2017-08-28')," +
                "(2501, 25, 'init', 4, 'test', '2017-08-28')," +
                "(1600, 16, 'init', 5, 'test', '2017-08-28')," +
                "(1601, 16, 'init', 6, '', '2017-08-28')," +
                "(2600, 26, 'init', 7, 'test', '2017-08-28')," +
                "(1700, 17, 'init', 9, 'test', '2017-08-28')," +
                "(1701, 17, 'finish', 10, 'test', '2017-08-18')," +
                "(2700, 27, 'finish', 11, 'test', '2017-08-18')," +
                "(2701, 27, 'finish', 12, 'test', '2017-08-18')," +
                "(1800, 18, 'finish', 13, 'test', '2017-08-18')," +
                "(2800, 28, 'finish', 15, 'test', '2017-08-18')," +
                "(2801, 28, 'finish', 16, 'test', '2017-08-18')," +
                "(1900, 19, 'init', 17, 'test', '2017-08-18')," +
                "(1901, 19, 'init', 18, 'test', '2017-08-18')," +
                "(2900, 29, 'init', 19, 'test', '2017-08-18')," +
                "(2901, 29, 'init', 20, 'test', '2017-08-18')," +
                "(1902, 19, 'init', 17, 'test11', '2017-08-18')," +
                "(1903, 19, 'init', 18, 'test12', '2017-08-18')," +
                "(2902, 29, 'init', 19, 'test', '2017-08-18')," +
                "(2903, 29, 'init', 20, 'test', '2017-08-18');";
        for (String each : sqlList) {
            statement.execute(each);
        }
        statement.execute(records);
        statement.execute("create view select_view as select distinct(user_id) from t_order order by user_id;");
        ResultSet rs = statement.executeQuery("select * from select_view;");
        while(rs.next()){
            System.out.println("userId is : " + rs.getInt("user_id"));
        }
    }
    
    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        System.out.println("Connecting to database...");
        return DriverManager.getConnection(DB_URL, USER, PASS);
    }
}

@peilinqian Hi, I can't reproduce it when i use JDBC to test it.

strongduanmu added status: volunteer wanted in: metadata labels Dec 2, 2022

peilinqian mentioned this issue Dec 17, 2022

In the federation scenario，"select order by null first" or "create view order by null first",the result is incorrect; #22826

Closed

github-actions bot added the stale label Jul 12, 2023

taojintianxia removed the stale label Jul 11, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

In the federation scenario, the result of select view is incomplete； #22553

In the federation scenario, the result of select view is incomplete； #22553

peilinqian commented Dec 1, 2022 •

edited

Loading

peilinqian commented Dec 1, 2022

strongduanmu commented Dec 2, 2022

zhaojinchao95 commented Dec 6, 2022 •

edited

Loading

In the federation scenario, the result of select view is incomplete； #22553

In the federation scenario, the result of select view is incomplete； #22553

Comments

peilinqian commented Dec 1, 2022 • edited Loading

Which version of ShardingSphere did you use?

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

Expected behavior

Actual behavior

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

Example codes for reproduce this issue (such as a github link).

peilinqian commented Dec 1, 2022

strongduanmu commented Dec 2, 2022

zhaojinchao95 commented Dec 6, 2022 • edited Loading

peilinqian commented Dec 1, 2022 •

edited

Loading

zhaojinchao95 commented Dec 6, 2022 •

edited

Loading