You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "humingzhang (via GitHub)" <gi...@apache.org> on 2023/08/14 01:27:09 UTC
[GitHub] [shardingsphere] humingzhang opened a new issue, #28068: Parsing SQL errors when using Oracle
humingzhang opened a new issue, #28068:
URL: https://github.com/apache/shardingsphere/issues/28068
## Bug Report
### Which version of ShardingSphere did you use?
5.2.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
Success to execute query
### Actual behavior
Invalid identification error
### Reason analyze (If you can)
column alias parsing error
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
1、Use mybaties plus
2、Enable automatic pagination
3、execute sql
SELECT
c.employee_code,
c.employee_position,
c.employee_email,
c.create_time,
c.join_state,
a.nick_name,
a.uuid AS userUuid,
a.account_type,
a.username,
a.avatar,
a.display_name,
a.phone,
a.email,
a.state AS status,
a.auth_type,
a.auth_result,
a.id
FROM
uc_account a
JOIN uc_dept_user_relation d ON
(a.uuid = d.user_uuid)
LEFT JOIN uc_company_user c ON
(a.uuid = c.user_uuid
AND d.company_uuid = c.company_uuid)
WHERE
a.deleted = 0
AND (c.deleted = 0
OR c.deleted IS NULL)
AND d.deleted = 0
AND c.join_state IN (?)
AND d.company_uuid = ?
GROUP BY
c.employee_code,
c.employee_position,
c.employee_email,
c.create_time,
c.join_state,
a.nick_name,
a.uuid,
a.account_type,
a.username,
a.avatar,
a.display_name,
a.phone,
a.email,
a.state,
a.auth_type,
a.auth_result,
a.id
ORDER BY
a.id DESC
4、mybatis-plus sql
SELECT
*
FROM
(
SELECT
TMP.*,
ROWNUM ROW_ID
FROM
(
SELECT
c.employee_code,
c.employee_position,
c.employee_email,
c.create_time,
c.join_state,
a.nick_name,
a.uuid AS userUuid,
a.account_type,
a.username,
a.avatar,
a.display_name,
a.phone,
a.email,
a.state AS status,
a.auth_type,
a.auth_result,
a.id
FROM
uc_account a
JOIN uc_dept_user_relation d ON
(a.uuid = d.user_uuid)
LEFT JOIN uc_company_user c ON
(a.uuid = c.user_uuid
AND d.company_uuid = c.company_uuid)
WHERE
a.deleted = 0
AND (c.deleted = 0
OR c.deleted IS NULL)
AND d.deleted = 0
AND c.join_state IN (?)
AND d.company_uuid = ?
GROUP BY
c.employee_code,
c.employee_position,
c.employee_email,
c.create_time,
c.join_state,
a.nick_name,
a.uuid,
a.account_type,
a.username,
a.avatar,
a.display_name,
a.phone,
a.email,
a.state,
a.auth_type,
a.auth_result,
a.id
ORDER BY
a.id DESC ) TMP
WHERE
ROWNUM <=?)
WHERE
ROW_ID > ?
5、sharding sql
SELECT
"c"."employee_code",
"c"."employee_position",
"c"."EMPLOYEE_EMAIL" AS "employee_email",
"c"."create_time",
"c"."join_state",
"a"."nick_name",
"a"."uuid" AS "userUuid",
"a"."account_type",
"a"."username",
"a"."avatar",
"a"."DISPLAY_NAME" AS "display_name",
"a"."PHONE" AS "phone",
"a"."EMAIL" AS "email",
"a"."state" AS "status",
"a"."auth_type",
"a"."auth_result",
"a"."id",
"ROWNUM" AS "ROW_ID"
FROM
(
SELECT
"c"."employee_code",
"c"."employee_position",
"c"."EMPLOYEE_EMAIL",
"c"."create_time",
"c"."join_state",
"a"."nick_name",
"a"."uuid" AS "userUuid",
"a"."account_type",
"a"."username",
"a"."avatar",
"a"."DISPLAY_NAME",
"a"."PHONE",
"a"."EMAIL",
"a"."state" AS "status",
"a"."auth_type",
"a"."auth_result",
"a"."id",
ROWNUM ROW_ID
FROM
(
SELECT
c.employee_code,
c.employee_position,
c.EMPLOYEE_EMAIL,
c.create_time,
c.join_state,
a.nick_name,
a.uuid AS userUuid,
a.account_type,
a.username,
a.avatar,
a.DISPLAY_NAME,
a.PHONE,
a.EMAIL,
a.state AS status,
a.auth_type,
a.auth_result,
a.id
FROM
uc_account a
JOIN uc_dept_user_relation d ON
(a.uuid = d.user_uuid)
LEFT JOIN uc_company_user c ON
(a.uuid = c.user_uuid
AND d.company_uuid = c.company_uuid)
WHERE
a.deleted = 0
AND (c.deleted = 0
OR c.deleted IS NULL)
AND d.deleted = 0
AND c.join_state IN
(?)
AND d.company_uuid = ?
GROUP BY
c.employee_code,
c.employee_position,
c.EMPLOYEE_EMAIL,
c.create_time,
c.join_state,
a.nick_name,
a.uuid,
a.account_type,
a.username,
a.avatar,
a.DISPLAY_NAME,
a.PHONE,
a.EMAIL,
a.state,
a.auth_type,
a.auth_result,
a.id
ORDER BY
a.id DESC ) TMP
WHERE
ROWNUM <=?)
WHERE
ROW_ID > ?
--
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
[GitHub] [shardingsphere] humingzhang commented on issue #28068: Parsing SQL errors when using Oracle
Posted by "humingzhang (via GitHub)" <gi...@apache.org>.
humingzhang commented on issue #28068:
URL: https://github.com/apache/shardingsphere/issues/28068#issuecomment-1681563211
@RaigorJiang HI, I have tested the following versions and there are related issues
5.0
5.1
5.2
5.3
5.4
There was a previous version that did not have this issue: 4.0.0-RC4
--
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] zhangfengcdt commented on issue #28068: Parsing SQL errors when using Oracle
Posted by "zhangfengcdt (via GitHub)" <gi...@apache.org>.
zhangfengcdt commented on issue #28068:
URL: https://github.com/apache/shardingsphere/issues/28068#issuecomment-1726274216
I tested these three SQLs using the Oracle SQL dialect on the master branch:
https://github.com/apache/shardingsphere/commit/b11bbfb12859f425f6afce14bd2e5ab58b82f565
None of them failed to be parsed.
--
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 #28068: Parsing SQL errors when using Oracle
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #28068:
URL: https://github.com/apache/shardingsphere/issues/28068#issuecomment-1726717544
@zhangfengcdt Thank you for your feedback. @humingzhang Can you test with master branch?
--
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] humingzhang closed issue #28068: Parsing SQL errors when using Oracle
Posted by "humingzhang (via GitHub)" <gi...@apache.org>.
humingzhang closed issue #28068: Parsing SQL errors when using Oracle
URL: https://github.com/apache/shardingsphere/issues/28068
--
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] Parsing SQL errors when using Oracle [shardingsphere]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #28068:
URL: https://github.com/apache/shardingsphere/issues/28068#issuecomment-1773329462
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
[GitHub] [shardingsphere] humingzhang commented on issue #28068: Parsing SQL errors when using Oracle
Posted by "humingzhang (via GitHub)" <gi...@apache.org>.
humingzhang commented on issue #28068:
URL: https://github.com/apache/shardingsphere/issues/28068#issuecomment-1681561479
> Hi @humingzhang The latest version has improved compatibility with `Oracle`, can you try 5.4.0? In addition, the community has submitted many issues to improve `Oracle` parsing, you can also participate, thank you!
> Hi @humingzhang The latest version has improved compatibility with `Oracle`, can you try 5.4.0? In addition, the community has submitted many issues to improve `Oracle` parsing, you can also participate, thank you!
--
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] RaigorJiang commented on issue #28068: Parsing SQL errors when using Oracle
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #28068:
URL: https://github.com/apache/shardingsphere/issues/28068#issuecomment-1679191351
Hi @humingzhang
The latest version has improved compatibility with `Oracle`, can you try 5.4.0?
In addition, the community has submitted many issues to improve `Oracle` parsing, you can also participate, thank you!
--
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] humingzhang closed issue #28068: Parsing SQL errors when using Oracle
Posted by "humingzhang (via GitHub)" <gi...@apache.org>.
humingzhang closed issue #28068: Parsing SQL errors when using Oracle
URL: https://github.com/apache/shardingsphere/issues/28068
--
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] humingzhang commented on issue #28068: Parsing SQL errors when using Oracle
Posted by "humingzhang (via GitHub)" <gi...@apache.org>.
humingzhang commented on issue #28068:
URL: https://github.com/apache/shardingsphere/issues/28068#issuecomment-1676686295
version 5.1.1 does not have 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] github-actions[bot] commented on issue #28068: Parsing SQL errors when using Oracle
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #28068:
URL: https://github.com/apache/shardingsphere/issues/28068#issuecomment-1722305793
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] Parsing SQL errors when using Oracle [shardingsphere]
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang closed issue #28068: Parsing SQL errors when using Oracle
URL: https://github.com/apache/shardingsphere/issues/28068
--
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