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