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 2021/12/07 06:34:18 UTC

[GitHub] [shardingsphere] userkdg opened a new issue #13972: subquery table alias bug

userkdg opened a new issue #13972:
URL: https://github.com/apache/shardingsphere/issues/13972


   ## Bug Report
    subquery table set alias , proxy rewrite sql error .  such as :
   
   ``` sql
   MySQL [ec_order_db]> preview select count(1) as cnt from (select a.address from ec_oms_order a) as tmp;
   +------------------+-----------------------------------------------------------------------------------------------+
   | data_source_name | sql                                                                                           |
   +------------------+-----------------------------------------------------------------------------------------------+
   | ec_order_ds      | select count(1) as cnt from (select a.a.address_cipher AS address from ec_oms_order a) as tmp |
   +------------------+-----------------------------------------------------------------------------------------------+
   
   ```
   
   ### Which version of ShardingSphere did you use?
   the lastest master
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   shardingsphere-proxy
   
   ### Expected behavior
   
   ``` sql 
    select count(1) as cnt from (select a.address_cipher AS address from ec_oms_order a) as tmp
   ```
   
   ### Actual behavior
   ``` sql 
   select count(1) as cnt from (select a.a.address_cipher AS address from ec_oms_order a) as tmp
   ```
   ### Reason analyze (If you can)
   EncryptPredicateColumnTokenGenerator
   
   ### 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).
   ```


-- 
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] userkdg commented on issue #13972: subquery table alias bug

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


   > ## Bug Report
   > subquery table set alias , proxy rewrite sql error . such as :
   > 
   > ```sql
   > MySQL [ec_order_db]> preview select count(1) as cnt from (select a.address from ec_oms_order a) as tmp;
   > +------------------+-----------------------------------------------------------------------------------------------+
   > | data_source_name | sql                                                                                           |
   > +------------------+-----------------------------------------------------------------------------------------------+
   > | ec_order_ds      | select count(1) as cnt from (select a.a.address_cipher AS address from ec_oms_order a) as tmp |
   > +------------------+-----------------------------------------------------------------------------------------------+
   > ```
   > 
   > ### Which version of ShardingSphere did you use?
   > the lastest master
   > 
   > ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   > shardingsphere-proxy
   > 
   > ### Expected behavior
   > ```sql
   >  select count(1) as cnt from (select a.address_cipher AS address from ec_oms_order a) as tmp
   > ```
   > 
   > ### Actual behavior
   > ```sql
   > select count(1) as cnt from (select a.a.address_cipher AS address from ec_oms_order a) as tmp
   > ```
   > 
   > ### Reason analyze (If you can)
   > ![image](https://user-images.githubusercontent.com/21057449/144983499-052fbddf-dde8-44ef-ae40-a36da4a7bc55.png)
   > 
   > ![image](https://user-images.githubusercontent.com/21057449/144983685-f47dfe97-6837-4d04-8371-93584170bfed.png)
   > 
   > ### 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).
   
   I try find bug
   
   position bug:
   
   the lastest master: 
   ``` java 
    private SubstitutableColumnNameToken generateSQLTokens(final ColumnProjectionSegment segment, final EncryptTable encryptTable,
                                                              final String tableName, final SubqueryType subqueryType) {
           String columnName = segment.getColumn().getIdentifier().getValue();
           String alias = segment.getAlias().orElseGet(() -> segment.getColumn().getIdentifier().getValue());
           Collection<ColumnProjection> projections = generateProjections(tableName, columnName, alias, null, encryptTable, subqueryType);
           int startIndex = segment.getColumn().getOwner().isPresent() ? segment.getColumn().getOwner().get().getStopIndex() + 2 : segment.getColumn().getStartIndex();
           int stopIndex = segment.getStopIndex();
           return new SubstitutableColumnNameToken(startIndex, stopIndex, projections);
       }
   ```
   
   local fix: 
   ``` java
   private SubstitutableColumnNameToken generateSQLTokens(final String tableName, final ColumnProjectionSegment columnSegment, 
                                                              final ColumnProjection columnProjection, final SubqueryType subqueryType) {
           Collection<ColumnProjection> projections = generateProjections(tableName, columnProjection, subqueryType, columnSegment.getColumn().getOwner().isPresent());
           int startIndex = columnSegment.getColumn().getOwner().isPresent() ? columnSegment.getColumn().getOwner().get().getStopIndex() : columnSegment.getColumn().getStartIndex();
           int stopIndex = columnSegment.getStopIndex();
           return new SubstitutableColumnNameToken(startIndex, stopIndex, projections);
       }
   ```
   
   explain:  for source ,
   the method org.apache.shardingsphere.encrypt.rewrite.token.generator.impl.EncryptProjectionTokenGenerator#generateSQLTokens 
   1 line set shothand=false, but 2 line shorthand = columnSegment.getColumn().getOwner().isPresent() ,one method variable had tow behavior, Behavior inconsistency
   
   test:
   fixed , check normal query / subquery is ok 
   


-- 
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] userkdg commented on issue #13972: subquery table alias bug

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


   > I will check on this soon.
   
   ``` java
   
    private SubstitutableColumnNameToken generateSQLTokens(final String tableName, final ColumnProjectionSegment columnSegment, 
                                                              final ColumnProjection columnProjection, final SubqueryType subqueryType) {
           Collection<ColumnProjection> projections = generateProjections(tableName, columnProjection, subqueryType, false);
           int startIndex = columnSegment.getColumn().getOwner().isPresent() ? columnSegment.getColumn().getOwner().get().getStopIndex() + 2 : columnSegment.getColumn().getStartIndex();
           int stopIndex = columnSegment.getStopIndex();
           return new SubstitutableColumnNameToken(startIndex, stopIndex, projections);
       }
       
   ```
   
   i dont understand ** columnSegment.getColumn().getOwner().get().getStopIndex() + 2 **, startIndex  need add 2 
   
   


-- 
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] userkdg commented on issue #13972: subquery table alias bug

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


   > > I will check on this soon.
   > 
   > ```java
   >  private SubstitutableColumnNameToken generateSQLTokens(final String tableName, final ColumnProjectionSegment columnSegment, 
   >                                                            final ColumnProjection columnProjection, final SubqueryType subqueryType) {
   >         Collection<ColumnProjection> projections = generateProjections(tableName, columnProjection, subqueryType, false);
   >         int startIndex = columnSegment.getColumn().getOwner().isPresent() ? columnSegment.getColumn().getOwner().get().getStopIndex() + 2 : columnSegment.getColumn().getStartIndex();
   >         int stopIndex = columnSegment.getStopIndex();
   >         return new SubstitutableColumnNameToken(startIndex, stopIndex, projections);
   >     }
   >     
   > ```
   > 
   > i dont understand ** columnSegment.getColumn().getOwner().get().getStopIndex() + 2 **, startIndex need add 2
   
   i found ``` +2 `` reason
   
   such as : 
   ``` sql
   preview select ab.address from ec_oms_order ab where ab.address ='1';
   select aab.address_cipher AS address from ec_oms_order ab where aab.address_cipher ='9ht7b2lvIAZEi99P2eIg3w=='
   ``` 
   when we set table alias multi letters , will produce bugs due to ```+2```  only support one length letter .
   .... 
   


-- 
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] userkdg edited a comment on issue #13972: subquery table alias bug

Posted by GitBox <gi...@apache.org>.
userkdg edited a comment on issue #13972:
URL: https://github.com/apache/shardingsphere/issues/13972#issuecomment-989485381


   > ## Bug Report
   > subquery table set alias , proxy rewrite sql error . such as :
   > 
   > ```sql
   > MySQL [ec_order_db]> preview select count(1) as cnt from (select a.address from ec_oms_order a) as tmp;
   > +------------------+-----------------------------------------------------------------------------------------------+
   > | data_source_name | sql                                                                                           |
   > +------------------+-----------------------------------------------------------------------------------------------+
   > | ec_order_ds      | select count(1) as cnt from (select a.a.address_cipher AS address from ec_oms_order a) as tmp |
   > +------------------+-----------------------------------------------------------------------------------------------+
   > ```
   > 
   > ### Which version of ShardingSphere did you use?
   > the lastest master
   > 
   > ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   > shardingsphere-proxy
   > 
   > ### Expected behavior
   > ```sql
   >  select count(1) as cnt from (select a.address_cipher AS address from ec_oms_order a) as tmp
   > ```
   > 
   > ### Actual behavior
   > ```sql
   > select count(1) as cnt from (select a.a.address_cipher AS address from ec_oms_order a) as tmp
   > ```
   > 
   > ### Reason analyze (If you can)
   > ![image](https://user-images.githubusercontent.com/21057449/144983499-052fbddf-dde8-44ef-ae40-a36da4a7bc55.png)
   > 
   > ![image](https://user-images.githubusercontent.com/21057449/144983685-f47dfe97-6837-4d04-8371-93584170bfed.png)
   > 
   > ### 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).
   
   I try find bug
   
   position bug:
   
   the lastest master: 
   ``` java 
   private SubstitutableColumnNameToken generateSQLTokens(final String tableName, final ColumnProjectionSegment columnSegment, 
                                                              final ColumnProjection columnProjection, final SubqueryType subqueryType) {
           Collection<ColumnProjection> projections = generateProjections(tableName, columnProjection, subqueryType, false);
           int startIndex = columnSegment.getColumn().getOwner().isPresent() ? columnSegment.getColumn().getOwner().get().getStopIndex() + 2 : columnSegment.getColumn().getStartIndex();
           int stopIndex = columnSegment.getStopIndex();
           return new SubstitutableColumnNameToken(startIndex, stopIndex, projections);
       }
   ```
   
   local fix: 
   ``` java
   private SubstitutableColumnNameToken generateSQLTokens(final String tableName, final ColumnProjectionSegment columnSegment, 
                                                              final ColumnProjection columnProjection, final SubqueryType subqueryType) {
           Collection<ColumnProjection> projections = generateProjections(tableName, columnProjection, subqueryType, columnSegment.getColumn().getOwner().isPresent());
           int startIndex = columnSegment.getColumn().getOwner().isPresent() ? columnSegment.getColumn().getOwner().get().getStopIndex() : columnSegment.getColumn().getStartIndex();
           int stopIndex = columnSegment.getStopIndex();
           return new SubstitutableColumnNameToken(startIndex, stopIndex, projections);
       }
   ```
   
   explain:  for source ,
   the method org.apache.shardingsphere.encrypt.rewrite.token.generator.impl.EncryptProjectionTokenGenerator#generateSQLTokens 
   1 line set shothand=false, but 2 line shorthand = columnSegment.getColumn().getOwner().isPresent() ,one method variable had tow behavior, Behavior inconsistency
   
   test:
   fixed , check normal query / subquery is ok 
   


-- 
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] cheese8 commented on issue #13972: subquery table alias bug

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


   I will check on this soon.


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