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/01/04 08:03:39 UTC
[GitHub] [shardingsphere] userkdg opened a new issue #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
userkdg opened a new issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506
### Which version of ShardingSphere did you use?
5.1.0-GA
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
Proxy
### Expected behavior
``` sql
SELECT b.address, b.normal_field
FROM (SELECT a.address_cipher as address, a.normal_field FROM ec_oms_order a WHERE a.order_code = 1373) b;
```
Unknown column 'b.address_cipher' in 'field list', due to suquery without address_cipher.
### Actual behavior
``` sql
SELECT b.address_cipher as address, b.normal_field
FROM (SELECT a.address_cipher as address, a.normal_field FROM ec_oms_order a WHERE a.order_code = 1373) b;
```
### Reason analyze (If you can)
subquery & resultset projections has sql rewrite bug.
In resultset . ``` b.address_cipher as address ``` projection, name must to ```address_cipher``` , can ``` findEncryptor()`` to decrypt column data , but subquery , ``` a.address_cipher as address ``` result unkown ``` b.address_cipher``` .
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
rule:
tables:
ec_oms_order:
address:
columnPlain: address
columnCipher: address_cipher
encrypter: encrypt-aes
queryWithCipherColumn: true
--
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] strongduanmu commented on issue #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1004735445
> I will check on it laster. please assign it to me.
@cheese8 Welcome.
--
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 #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
cheese8 commented on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1005305832
ok, get it, @userkdg there is a problem in shorthand case. i will fix it 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
[GitHub] [shardingsphere] TeslaCN commented on issue #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1081348122
Hi @strongduanmu @cheese8
Any update here?
--
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 #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
cheese8 commented on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1004834507
Hi @userkdg What is your logicSQL? I checked this as below on master，not very good but at it's ok:
LogicSQL:
SELECT b.certificate_number, b.amount FROM (SELECT a.certificate_number as certificate_number, a.amount FROM t_account a WHERE a.amount = 1373) b
ActualSQL:
SELECT b.cipher_certificate_number AS certificate_number, b.cipher_amount AS amount FROM (SELECT a.cipher_certificate_number, a.assisted_query_certificate_number, a.cipher_amount FROM t_account a WHERE a.cipher_amount = 1373) b
--
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 edited a comment on issue #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
cheese8 edited a comment on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1004834507
Hi @userkdg What is your logicSQL? I checked this as below on master，not very good but at it's ok:
LogicSQL:
`SELECT b.certificate_number, b.amount FROM (SELECT a.certificate_number as certificate_number, a.amount FROM t_account a WHERE a.amount = 1373) b`
ActualSQL:
```SELECT b.cipher_certificate_number AS certificate_number, b.cipher_amount AS amount FROM (SELECT a.cipher_certificate_number, a.assisted_query_certificate_number, a.cipher_amount FROM t_account a WHERE a.cipher_amount = 1373) b```
--
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 edited a comment on issue #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
cheese8 edited a comment on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1004834507
Hi @userkdg What is your logicSQL? I checked this as below on master，not very good but at it's ok:
LogicSQL:
```SELECT b.certificate_number, b.amount FROM (SELECT a.certificate_number as certificate_number, a.amount FROM t_account a WHERE a.amount = 1373) b```
ActualSQL:
```SELECT b.cipher_certificate_number AS certificate_number, b.cipher_amount AS amount FROM (SELECT a.cipher_certificate_number, a.assisted_query_certificate_number, a.cipher_amount FROM t_account a WHERE a.cipher_amount = 1373) b```
--
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] tuichenchuxin closed issue #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
tuichenchuxin closed issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506
--
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 edited a comment on issue #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
cheese8 edited a comment on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1004834507
Hi @userkdg What is your logicSQL? I checked this as below on master，not very good but it's ok:
LogicSQL:
`SELECT b.certificate_number, b.amount FROM (SELECT a.certificate_number as certificate_number, a.amount FROM t_account a WHERE a.amount = 1373) b`
ActualSQL:
```SELECT b.cipher_certificate_number AS certificate_number, b.cipher_amount AS amount FROM (SELECT a.cipher_certificate_number, a.assisted_query_certificate_number, a.cipher_amount FROM t_account a WHERE a.cipher_amount = 1373) b```
--
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] tuichenchuxin commented on issue #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
tuichenchuxin commented on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1081348279
Fixed.
--
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 #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
userkdg edited a comment on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1004892130
> Hi @userkdg What is your logicSQL? I checked this as below on master，not very good but it's ok:
>
> LogicSQL:
>
> `SELECT b.certificate_number, b.amount FROM (SELECT a.certificate_number as certificate_number, a.amount FROM t_account a WHERE a.amount = 1373) b`
>
> ActualSQL:
>
> `SELECT b.cipher_certificate_number AS certificate_number, b.cipher_amount AS amount FROM (SELECT a.cipher_certificate_number, a.assisted_query_certificate_number, a.cipher_amount FROM t_account a WHERE a.cipher_amount = 1373) b`
very thinks.
yes. this is ok. it must to analyze out projections & (mutil ) subquery projections
i met two case
- column projections
like your example
- shorthand projections case
```
select b.* from (select a.address, a.address as addr2, … from ec_oms_order a) b
```
--
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 #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
userkdg commented on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1004892130
> Hi @userkdg What is your logicSQL? I checked this as below on master，not very good but it's ok:
>
> LogicSQL:
>
> `SELECT b.certificate_number, b.amount FROM (SELECT a.certificate_number as certificate_number, a.amount FROM t_account a WHERE a.amount = 1373) b`
>
> ActualSQL:
>
> `SELECT b.cipher_certificate_number AS certificate_number, b.cipher_amount AS amount FROM (SELECT a.cipher_certificate_number, a.assisted_query_certificate_number, a.cipher_amount FROM t_account a WHERE a.cipher_amount = 1373) b`
very thinks.
yes. this is ok. it must to analyze out projections & (mutil ) subquery projections, and then
i met two case
- column projections
like your example
- shorthand projections case
```
select b.* from (select a.address, a.address as addr2, … from ec_oms_order a) b
```
--
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 #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
cheese8 commented on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1004660563
I will check on it laster. please assign it to me.
--
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] strongduanmu commented on issue #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1004606578
@userkdg Thank you for your feedback, I will investigate this issue.
--
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 edited a comment on issue #14506: on encrypt, subquery projections token error, cause sql executor throw sqlexception
Posted by GitBox <gi...@apache.org>.
cheese8 edited a comment on issue #14506:
URL: https://github.com/apache/shardingsphere/issues/14506#issuecomment-1004834507
Hi @userkdg What is your logicSQL? I checked this as below on master，not very good but at it's ok:
LogicSQL:
`SELECT b.certificate_number, b.amount FROM (SELECT a.certificate_number as certificate_number, a.amount FROM t_account a WHERE a.amount = 1373) b`
ActualSQL:
`SELECT b.cipher_certificate_number AS certificate_number, b.cipher_amount AS amount FROM (SELECT a.cipher_certificate_number, a.assisted_query_certificate_number, a.cipher_amount FROM t_account a WHERE a.cipher_amount = 1373) b`
--
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