Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\prabhu.e\AppData\Roaming\MySQL\Workbench\log\wb.log]
We found that the query is incorrect but it doesn't Throws error.
How to repeat:
WITH UNIT_ACCESS AS (SELECT DISTINCT UNIT_NAME FROM UNIT WHERE UNIT_NUMBER IN
(SELECT UNIT_NUMBER FROM PERSON_ROLES T1 INNER JOIN ROLE_RIGHTS T2 ON T1.ROLE_ID = T2.ROLE_ID
INNER JOIN RIGHTS T3 ON T2.RIGHT_ID = T3.RIGHT_ID WHERE T1.DESCEND_FLAG = 'N'
AND T1.PERSON_ID = '100031776' AND RIGHT_NAME IN ('AGREEMENT_ADMINISTRATOR')
UNION SELECT CHILD_UNIT_NUMBER FROM UNIT_WITH_CHILDREN WHERE UNIT_NUMBER IN
(SELECT UNIT_NUMBER FROM PERSON_ROLES T1 INNER JOIN ROLE_RIGHTS T2 ON T1.ROLE_ID = T2.ROLE_ID
INNER JOIN RIGHTS T3 ON T2.RIGHT_ID = T3.RIGHT_ID WHERE T1.DESCEND_FLAG = 'Y' AND T1.PERSON_ID = '100031776'
AND RIGHT_NAME IN ('AGREEMENT_ADMINISTRATOR'))))
SELECT T2.CATEGORY_CODE AS CATEGORY_CODE, T2.DESCRIPTION AS CATEGORYS,
SUM(CASE WHEN T1.AGREEMENT_STATUS_CODE in (1,6) THEN 1 ELSE 0 END )AS "DRAFT",
SUM(CASE WHEN T1.AGREEMENT_STATUS_CODE in (2,5,7) THEN 1 ELSE 0 END )AS "IN WORKFLOW"
FROM AGREEMENT_HEADER T1
INNER JOIN AGREEMENT_CATEGORY T2 ON T1.CATEGORY_CODE = T2.CATEGORY_CODE
WHERE T1.AGREEMENT_REQUEST_ID IN (
SELECT DISTINCT T4. AGREEMENT_REQUEST_ID FROM AGREEMENT_HEADER T4
LEFT JOIN AGREEMENT_PEOPLE T3 on T4.AGREEMENT_REQUEST_ID = T3.AGREEMENT_REQUEST_ID
WHERE T1.AGREEMENT_SEQUENCE_STATUS NOT IN ('TEMPLATE') AND
((T1.UNIT_NUMBER IN
(SELECT DISTINCT UNIT_NUMBER FROM UNIT_ACCESS) OR T3.PERSON_ID = '100031776'
OR T1.REQUESTOR_PERSON_ID = '100031776')))
GROUP BY T2.CATEGORY_CODE,T2.DESCRIPTION;
-- fetching unit name in UNIT_ACCESS
-- Comparing unit number by using UNIT_ACCESS in filter condition but UNIT_ACCESS has unit name is a varchar datatype, unit number has int datatype
main issue is there is no unit number column in UNIT_ACCESS but it works smoothly. how is possible whithout showing column not found error
Description: ----[For better reports, please attach the log file after submitting. You can find it in C:\Users\prabhu.e\AppData\Roaming\MySQL\Workbench\log\wb.log] We found that the query is incorrect but it doesn't Throws error. How to repeat: WITH UNIT_ACCESS AS (SELECT DISTINCT UNIT_NAME FROM UNIT WHERE UNIT_NUMBER IN (SELECT UNIT_NUMBER FROM PERSON_ROLES T1 INNER JOIN ROLE_RIGHTS T2 ON T1.ROLE_ID = T2.ROLE_ID INNER JOIN RIGHTS T3 ON T2.RIGHT_ID = T3.RIGHT_ID WHERE T1.DESCEND_FLAG = 'N' AND T1.PERSON_ID = '100031776' AND RIGHT_NAME IN ('AGREEMENT_ADMINISTRATOR') UNION SELECT CHILD_UNIT_NUMBER FROM UNIT_WITH_CHILDREN WHERE UNIT_NUMBER IN (SELECT UNIT_NUMBER FROM PERSON_ROLES T1 INNER JOIN ROLE_RIGHTS T2 ON T1.ROLE_ID = T2.ROLE_ID INNER JOIN RIGHTS T3 ON T2.RIGHT_ID = T3.RIGHT_ID WHERE T1.DESCEND_FLAG = 'Y' AND T1.PERSON_ID = '100031776' AND RIGHT_NAME IN ('AGREEMENT_ADMINISTRATOR')))) SELECT T2.CATEGORY_CODE AS CATEGORY_CODE, T2.DESCRIPTION AS CATEGORYS, SUM(CASE WHEN T1.AGREEMENT_STATUS_CODE in (1,6) THEN 1 ELSE 0 END )AS "DRAFT", SUM(CASE WHEN T1.AGREEMENT_STATUS_CODE in (2,5,7) THEN 1 ELSE 0 END )AS "IN WORKFLOW" FROM AGREEMENT_HEADER T1 INNER JOIN AGREEMENT_CATEGORY T2 ON T1.CATEGORY_CODE = T2.CATEGORY_CODE WHERE T1.AGREEMENT_REQUEST_ID IN ( SELECT DISTINCT T4. AGREEMENT_REQUEST_ID FROM AGREEMENT_HEADER T4 LEFT JOIN AGREEMENT_PEOPLE T3 on T4.AGREEMENT_REQUEST_ID = T3.AGREEMENT_REQUEST_ID WHERE T1.AGREEMENT_SEQUENCE_STATUS NOT IN ('TEMPLATE') AND ((T1.UNIT_NUMBER IN (SELECT DISTINCT UNIT_NUMBER FROM UNIT_ACCESS) OR T3.PERSON_ID = '100031776' OR T1.REQUESTOR_PERSON_ID = '100031776'))) GROUP BY T2.CATEGORY_CODE,T2.DESCRIPTION; -- fetching unit name in UNIT_ACCESS -- Comparing unit number by using UNIT_ACCESS in filter condition but UNIT_ACCESS has unit name is a varchar datatype, unit number has int datatype main issue is there is no unit number column in UNIT_ACCESS but it works smoothly. how is possible whithout showing column not found error