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/09/26 12:18:21 UTC

[GitHub] [shardingsphere] irwinai opened a new issue, #21203: cross schema query bug

irwinai opened a new issue, #21203:
URL: https://github.com/apache/shardingsphere/issues/21203

   ## Bug Report
   when I do cross schema query ,maybe leads to bug
   
   ### Which version of ShardingSphere did you use?
   5.1.1
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-JDBC 
   
   ### Expected behavior
   query success
   
   ### Actual behavior
   query failed with exception
   
   ### Reason analyze (If you can)
   TablesContext#getDatabaseName not right ,can return Optional.empty
   RemoveTokenGenerator#isGenerateSQLToken not right,`containsSchemaName` judge need to be modify
   
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   1. connect to a database with any schema,like `localhost:3306/testDb1`
   2. write query like `select id from testDb2.testTable` do cross schema query
   so the rewrite engine may get the actual sql like:`select id from testTable`,the schema has been removed.
   the 5.0.0 version is right.
   
   ### Example codes for reproduce this issue (such as a github link).
   ```java
   TablesContext
     public Optional<String> getDatabaseName() {
           // return empty so the query with any schema in actual query usage
           return Optional.empty();
       }
   ```
   
   ```java
   RemoveTokenGenerator
   @Override
       public boolean isGenerateSQLToken(final SQLStatementContext sqlStatementContext) {
           boolean containsRemoveSegment = false;
           if (sqlStatementContext instanceof RemoveAvailable) {
               containsRemoveSegment = !((RemoveAvailable) sqlStatementContext).getRemoveSegments().isEmpty();
           }
           boolean containsSchemaName = false;
           if (sqlStatementContext instanceof TableAvailable) {
               TablesContext tablesContext = ((TableAvailable) sqlStatementContext).getTablesContext();
               // ignore sharding multiple schema rewrite sql
               containsSchemaName = tablesContext.getDatabaseName().isPresent();
           }
           return containsRemoveSegment || containsSchemaName;
       }
   ```
   
   anyway,after I modify the code ,I got the right result.
   But,this may leads to another proble ,this is `select * from testDb2.testTable`.
   the query with `select * from x` cross schema query may leads to a new proble,I got code here.
   this query may got and NPE with schema is null.
   ```java
     private Collection<ColumnProjection> getShorthandColumnsFromSimpleTableSegment(final TableSegment table, final String owner) {
           if (!(table instanceof SimpleTableSegment)) {
               return Collections.emptyList();
           }
           String tableName = ((SimpleTableSegment) table).getTableName().getIdentifier().getValue();
           String tableAlias = table.getAlias().orElse(tableName);
           Collection<ColumnProjection> result = new LinkedList<>();
           if (null == owner) {
               schema.getAllColumnNames(tableName).stream().map(columnName -> new ColumnProjection(tableAlias, columnName, null)).forEach(result::add);
           } else if (owner.equalsIgnoreCase(tableAlias)) {
               schema.getAllColumnNames(tableName).stream().map(columnName -> new ColumnProjection(owner, columnName, null)).forEach(result::add);
           }
           return result;
       }
   ```
   which code leads to this new problem?
   the SelectStatementContext#getSchema
   ```java
    private ShardingSphereSchema getSchema(final Map<String, ShardingSphereMetaData> metaDataMap, final String defaultDatabaseName) {
           String databaseName = tablesContext.getDatabaseName().orElse(defaultDatabaseName);
           ShardingSphereMetaData metaData = metaDataMap.get(databaseName);
           if (null == metaData) {
               if (tablesContext.getTables().isEmpty()) {
                   return new ShardingSphereSchema();
               } else {
                   throw new SchemaNotExistedException(databaseName);
               }
           }
           String schemaName = tablesContext.getSchemaNames().stream().findFirst().orElse(databaseName);
           return metaData.getSchemaByName(schemaName);
       }
   ```
   when this code in 5.0.0 like this
   ```java
    private ShardingSphereSchema getSchema(final Map<String, ShardingSphereMetaData> metaDataMap, final String defaultDatabaseName) {
           String schemaName = tablesContext.getSchemaName().orElse(defaultSchemaName);
           ShardingSphereMetaData metaData = metaDataMap.get(schemaName);
           if (null == metaData) {
               throw new SchemaNotExistedException(schemaName);
           }
           return metaData.getSchema();
   ```
   So,at last,the new version has a lot of problems,I can't modify the code anymore....
   help me,pls


-- 
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


Re: [I] cross schema query bug [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #21203:
URL: https://github.com/apache/shardingsphere/issues/21203#issuecomment-2212470998

   How about try to SQL federation engine?


-- 
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] cross schema query bug [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #21203: cross schema query bug
URL: https://github.com/apache/shardingsphere/issues/21203


-- 
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] treezhu commented on issue #21203: cross schema query bug

Posted by GitBox <gi...@apache.org>.
treezhu commented on issue #21203:
URL: https://github.com/apache/shardingsphere/issues/21203#issuecomment-1343937468

   I have the same question,help


-- 
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] cross schema query bug [shardingsphere]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #21203:
URL: https://github.com/apache/shardingsphere/issues/21203#issuecomment-2259120610

   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