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/10/27 12:05:36 UTC
[GitHub] [shardingsphere] strongduanmu opened a new issue, #21804: SQL parse error occurs when execute complex select statement with oracle database
strongduanmu opened a new issue, #21804:
URL: https://github.com/apache/shardingsphere/issues/21804
## Bug Report
### Which version of ShardingSphere did you use?
[e3f6fbb](https://github.com/apache/shardingsphere/commit/e3f6fbb0b57d6b788b93b1196cdab355d13da085)
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
Execute following sql successfully.
```sql
SELECT
INSTANCE_NAME,
TEXT_FIELD,
RECEIVE_USER_CODE,
RECEIVE_USER_NAME,
SUBMIT_USER_NAME,
CREATE_TIME,
WORKITEM_TYPE,
ROOT_INSTANCE_ID,
STATE,
SUBMIT_TIME,
DISPLAY_NAME,
WORKFLOW_CODE,
ACTIVITY_CODE,
WORKITEM_ID,
IS_READ,
rm
FROM
(
SELECT
INSTANCE_NAME,
TEXT_FIELD,
RECEIVE_USER_CODE,
RECEIVE_USER_NAME,
SUBMIT_USER_NAME,
CREATE_TIME,
WORKITEM_TYPE,
ROOT_INSTANCE_ID,
STATE,
SUBMIT_TIME,
DISPLAY_NAME,
WORKFLOW_CODE,
ACTIVITY_CODE,
WORKITEM_ID,
IS_READ,
ROW_NUMBER () OVER ( PARTITION BY ROOT_INSTANCE_ID ORDER BY SUBMIT_TIME DESC ) rm
FROM
OA_WORKITEMS
WHERE
1 = 1
) OW
WHERE
rm = '1'
AND OW.ROOT_INSTANCE_ID IN ( 'b9dfc132-af9e-4f1a-a3ec-a65b0ab60929', '9c0128d5-0f72-43e1-ae93-0f07a9c89857', '927ad465-dda8-4263-a9bb-5e2eb7a9b63f', '8757e454-c8f2-49e4-8bfe-abbd85b966b4', '52769d52-fffb-4521-b98b-8e962afdf978', '9a4ceca5-7f5d-4ec7-a02e-0e4ee3532dce', '5359ab2c-dbc0-4f09-beab-e60c639d0f5a', '77ed4f27-215f-4ea9-8893-424e2c72eb1f', '5ed2afbf-6555-45b8-81c7-708b3cde3918', '79889fcb-4826-4b5f-b4e3-a5c32ba5d389' )
ORDER BY
DECODE(
OW.ROOT_INSTANCE_ID,
'b9dfc132-af9e-4f1a-a3ec-a65b0ab60929',
'9c0128d5-0f72-43e1-ae93-0f07a9c89857',
'927ad465-dda8-4263-a9bb-5e2eb7a9b63f',
'8757e454-c8f2-49e4-8bfe-abbd85b966b4',
'52769d52-fffb-4521-b98b-8e962afdf978',
'9a4ceca5-7f5d-4ec7-a02e-0e4ee3532dce',
'5359ab2c-dbc0-4f09-beab-e60c639d0f5a',
'77ed4f27-215f-4ea9-8893-424e2c72eb1f',
'5ed2afbf-6555-45b8-81c7-708b3cde3918',
'79889fcb-4826-4b5f-b4e3-a5c32ba5d389'
)
```
### Actual behavior
### 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).
--
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] strongduanmu commented on issue #21804: SQL parse error occurs when execute complex select statement with oracle database
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #21804:
URL: https://github.com/apache/shardingsphere/issues/21804#issuecomment-1293429783
Following sql also throw exceptions.
```sql
SELECT
U .object_id,
U .login_name,
U .user_name,
U .duty_name,
U .office_phone,
U .office_location,
U.SORT_NUMBER,
U.SEX,
U.FAX_NUMBER,
U.INTRANET_EMAIL,
U.EMALI,
U.IS_PHONE_BOOK,
U.DEPT_ID,
U.INVENTED_ID,
U.SYS_FLAG,
U.AREA_CODE,
U.LUDIAN_CODE,
U.AREA_CODE||U.LUDIAN_CODE||substr(U.OFFICE_PHONE,-5,5) AS zhuanOfficePhone,
(SELECT listagg (de.DEPT_NAME, '/')
WITHIN GROUP (ORDER BY LENGTH(de.DEPT_LEVEL) DESC) AS dept_name
FROM SYS_ADDRESSBOOK_DEPT de where de.object_id != '1'
START WITH de.OBJECT_ID = #{deptId}
CONNECT BY PRIOR de.PARENT_ID = de.object_id)
AS dept_name,
CASE WHEN u.INVENTED_ID is NULL THEN P .file_id ELSE P1 .file_id end as file_id,
U .MOBILE_PHONE,
sup.post_id,
r.role_id,
r.role_name,
r.role_key,
(SELECT listagg (de.OBJECT_ID, '/') WITHIN GROUP (
ORDER BY LENGTH (de.DEPT_LEVEL) DESC) AS deptIds
FROM
SYS_ADDRESSBOOK_DEPT de START WITH de.OBJECT_ID = #{deptId} CONNECT BY PRIOR de.PARENT_ID = de.OBJECT_ID
) AS deptIds
FROM
SYS_ADDRESSBOOK_USER U
LEFT JOIN sys_user_picture P ON U .object_id = P .user_id AND P . TYPE = '1'
LEFT JOIN sys_user_picture P1 ON U.INVENTED_ID = P1 .user_id AND P1 . TYPE = '1'
LEFT JOIN sys_user_post sup ON U .object_id = sup.user_id
LEFT JOIN sys_user_role sur ON U .object_id = sur.user_id
LEFT JOIN sys_role r ON r.role_id = sur.role_id
WHERE u.dept_id = #{deptId} and nvl(u.LOGIN_NAME,'1') != 'OaAdmin'
SELECT sw.SYS_ID AS ID, sw.INSTANCE_ID AS instance_id, SW.DOCUMENT_IDENTIFIER AS document_identifier, sw.ISSUED_NUMBER_OF_DOCUMENT AS ISSUED_NUMBER_OF_DOCUMENT, sw.DOCUMENT_TITLE AS DOCUMENT_TITLE, sw.DOCUMENT_TITLE AS FAWEN_DOCUMENT_TITLE, NVL ( SW.EMERGENCY_DEGREE, 'EMERGENCY_DEGREE_01' ) AS EMERGENCY_DEGREE, sw.REGISTER_NAME AS REGISTER_NAME, sw.REGISTER_ID AS REGISTER_ID, sw.CREATE_TIME AS CREATE_TIME, sw.MODIFY_DATE AS MODIFY_DATE, '收文' AS gongwen_type, SW.DOCUMENT_TYPE AS document_type, SW.PER_FORM_URL AS PER_FORM_URL, sw.DOCUMENT_WORKFLOW_CODE AS DOCUMENT_WORKFLOW_CODE, sw.IS_ARCHIVED AS is_Archived, sw.IS_FINISH AS is_Finish, SW.FROM_DEPARTMENT AS DEPARTMENT, --来文单位FROM_ DEPARTMENT '' AS mainToDoDepartment, -- 主办单位 SW.DOCUMENT_TIME AS DOCUMENT_TIME, SW.SERIAL
_NUMBER AS SERIAL_NUMBER, SW.CATEGORY_ID AS CATEGORY_ID, '' AS aiganture_id, SW.REMARK AS REMARK, SW.DEPART_TITLE_TYPE_ID AS DEPART_TITLE_TYPE_ID, SW.DEPART_TITLE_TYPE AS DEPART_TITLE_TYPE, '' AS MAIN_RECEIVER_DEPARTMENT, NVL (NULL, '') AS ISSUING_DATE, SW.IS_DIFFERENTIATE AS IS_DIFFERENTIATE, SW.ARCHIVED_DATE AS ARCHIVED_DATE, '' AS FILE_TYPE, '' AS RECOMMENDATIONFROM OA_INCOMING_DISPATCHES swwhere (sw.RECYCLE_FLAG=0 or sw.RECYCLE_FLAG is null)UNION SELECT fw.SYS_ID AS ID, fw.INSTANCE_ID AS instance_id, fw.DOCUMENT_IDENTIFIER AS document_identifier, fw.ISSUED_NUMBER_OF_DOCUMENT AS ISSUED_NUMBER_OF_DOCUMENT, (case when os.SIGN_DOCUMENT_TITLE is not null then os.SIGN_DOCUMENT_TITLE else fw.DOCUMENT_TITLE end) as DOCUMENT_TITLE, fw.DOCUMENT_TITLE AS FAWEN_DOCUMENT_TITLE, NVL ( FW.EMERGENC
Y_DEGREE, 'EMERGENCY_DEGREE_01' ) AS EMERGENCY_DEGREE, fw.DRAFTER_NAME AS REGISTER_NAME, fw.DRAFTER_ID AS REGISTER_ID, fw.DRAFTER_CREATE_TIME AS CREATE_TIME, fw.MODIFY_DATE AS MODIFY_DATE, '发文' AS gongwen_type, fw.DOCUMENT_TYPE AS document_type, fw.PER_FORM_URL AS PER_FORM_URL, fw.DOCUMENT_WORKFLOW_CODE AS DOCUMENT_WORKFLOW_CODE, fw.IS_ARCHIVED AS is_Archived, fw.IS_FINISH AS is_Finish, DRAFTER_DEPT AS DEPARTMENT, -- 主办单位（发文拟稿单位) DRAFTER_DEPT AS mainToDoDepartment, FW.DOCUMENT_TIME AS DOCUMENT_TIME, FW.SERIAL_NUMBER AS SERIAL_NUMBER, NVL (NULL, ''), FW.AIGANTURE_ID AS aiganture_id, '' AS REMARK, FW.DEPART_TITLE_TYPE_ID AS DEPART_TITLE_TYPE_
ID, FW.DEPART_TITLE_TYPE AS DEPART_TITLE_TYPE, to_char(FW.MAIN_RECEIVER_DEPARTMENT) AS MAIN_RECEIVER_DEPARTMENT, FW.ISSUING_DATE AS ISSUING_DATE, FW.IS_DIFFERENTIATE AS IS_DIFFERENTIATE, FW.ARCHIVED_DATE AS ARCHIVED_DATE, FW.FILE_TYPE AS FILE_TYPE, FW.RECOMMENDATION AS RECOMMENDATION FROM OA_OUTGOING_MESSAGE fw LEFT JOIN OA_SIGN os ON fw.AIGANTURE_ID = os.SYS_ID where (fw.RECYCLE_FLAG=0 or fw.RECYCLE_FLAG is null)
```
--
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] tristaZero closed issue #21804: SQL parse error occurs when execute complex select statement with oracle database
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #21804: SQL parse error occurs when execute complex select statement with oracle database
URL: https://github.com/apache/shardingsphere/issues/21804
--
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