You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "Y-sir (via GitHub)" <gi...@apache.org> on 2023/12/06 08:24:46 UTC
[I] shardingproxy 5.4.1 execute select * from xx where not = column = xx; There is a problem with the statement [shardingsphere]
Y-sir opened a new issue, #29306:
URL: https://github.com/apache/shardingsphere/issues/29306
## Bug Report
**For English only**, other languages will not accept.
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot reproduce it on current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Which version of ShardingSphere did you use?
5.4.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy and postgresql 14.2
### Expected behavior
It is hoped that the encrypted cno field query will have the same results as the unencrypted one.
### Actual behavior
After encryption, the cno field conditions become invalid and all queries are found.
### Reason analyze (If you can)
The guess is that the SQL parsing is abnormal and the cno field is not matched normally. Conditional encryption is performed before transmission.
The picture below shows the sql conditional field parsed normally by the != statement. If it is an encrypted field, it will be conditionally encrypted.
The picture below shows the not = statement. SQL is not parsed normally. The conditional fields are not encrypted and conditional encryption is not performed.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
--------------------------Create table statement------------------------------
DROP TABLE IF EXISTS "public"."sc";
CREATE TABLE "public"."sc" (
"cno" "pg_catalog"."text" COLLATE "pg_catalog"."default",
"grade" "pg_catalog"."text" COLLATE "pg_catalog"."default",
"sno" "pg_catalog"."text" COLLATE "pg_catalog"."default" NOT NULL
)
;
COMMENT ON COLUMN "public"."sc"."cno" IS '课程号';
COMMENT ON COLUMN "public"."sc"."grade" IS '成绩';
COMMENT ON COLUMN "public"."sc"."sno" IS '学号';
-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO "public"."sc" VALUES ('201215123', '94', '1');
INSERT INTO "public"."sc" VALUES ('201215121', '92', '1');
INSERT INTO "public"."sc" VALUES ('201215122', '90', '2');
INSERT INTO "public"."sc" VALUES ('201215121', '85', '2');
INSERT INTO "public"."sc" VALUES ('201215125', '95', '2');
INSERT INTO "public"."sc" VALUES ('201215121', '88', '3');
INSERT INTO "public"."sc" VALUES ('201215122', '80', '3');
---------------------------not = statement -------------------------------------
SELECT * FROM sc WHERE NOT cno = '201215121' ;
----------------------------!= statement ----------------------------------------
### 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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] shardingproxy 5.4.1 execute select * from xx where not = column = xx; There is a problem with the statement [shardingsphere]
Posted by "Y-sir (via GitHub)" <gi...@apache.org>.
Y-sir commented on issue #29306:
URL: https://github.com/apache/shardingsphere/issues/29306#issuecomment-1869219255
@TherChenYang Can you help me look at this? It also feels like a grammar parsing problem
--
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] shardingproxy 5.4.1 execute select * from xx where not = column = xx; There is a problem with the statement [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #29306:
URL: https://github.com/apache/shardingsphere/issues/29306#issuecomment-1913321391
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
Re: [I] shardingproxy 5.4.1 execute select * from xx where not = column = xx; There is a problem with the statement [shardingsphere]
Posted by "Y-sir (via GitHub)" <gi...@apache.org>.
Y-sir commented on issue #29306:
URL: https://github.com/apache/shardingsphere/issues/29306#issuecomment-1842423735
After testing, it was found that this problem is not only present in postgresql, nor is it supported by mysql. It should have nothing to do with the database version.
--
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] shardingproxy 5.4.1 execute select * from xx where not = column = xx; There is a problem with the statement [shardingsphere]
Posted by "TherChenYang (via GitHub)" <gi...@apache.org>.
TherChenYang commented on issue #29306:
URL: https://github.com/apache/shardingsphere/issues/29306#issuecomment-1869267571
@Y-sir Received, I will review and confirm the 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
Re: [I] shardingproxy 5.4.1 execute select * from xx where not = column = xx; There is a problem with the statement [shardingsphere]
Posted by "TherChenYang (via GitHub)" <gi...@apache.org>.
TherChenYang commented on issue #29306:
URL: https://github.com/apache/shardingsphere/issues/29306#issuecomment-1871199159
@Y-sir Can you display your ShardingSphere configuration including data encryption?
--
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] shardingproxy 5.4.1 execute select * from xx where not = column = xx; There is a problem with the statement [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #29306: shardingproxy 5.4.1 execute select * from xx where not = column = xx; There is a problem with the statement
URL: https://github.com/apache/shardingsphere/issues/29306
--
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