NOT EXISTS() predicate sometimes return NULL instead True or False
------------------------------------------------------------------
Key: CORE-6120
URL: http://tracker.firebirdsql.org/browse/CORE-6120
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.4, 3.0.5
Reporter: Karol Bieniaszewski
In below query second column rerurn "NULL" instead of "TRUE". If you remove
"NOT" then result is "FALSE".
Run it against database from CORE-6106
SELECT
K.ID
,
(
NOT EXISTS
(WITH KWARTALY AS
(SELECT
*
FROM
KWARTAL KW
WHERE
KW.CKWARTAL BETWEEN '2019.I'
AND '2019.I'), KWARTALY_DATY AS
(SELECT
MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD
, MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO
, MIN(KW.D_OD) AS MIN_D_OD
, MAX(KW.D_DO) AS MAX_D_DO
, MIN(KW.CKWARTAL) AS MIN_KWARTAL
, MAX(KW.CKWARTAL) AS MAX_KWARTAL
FROM
KWARTALY KW)
SELECT
1
FROM
KWARTALY_DATY KW
WHERE
/* jest jakakolwiek deklaracja w zadanym przedziale to znaczy, ze jest
platnikiem */
EXISTS
(SELECT
*
FROM
SPR S
INNER JOIN KWARTAL SKW ON S.ID_KWARTAL = SKW.ID
WHERE
S.ID_KONTRAHENT = K.ID
AND SKW.CKWARTAL BETWEEN KW.MIN_KWARTAL
AND KW.MAX_KWARTAL
)
OR
/* jesli ma wpis o statusie dzialnosci za zadany okres (z data pozyaskania nie
z przyszlosci) to tez jest platnikiem */
EXISTS
(SELECT
*
FROM
STATUS_PLATNIKA SP
INNER JOIN STATUS_PLATNIKA_TYP SPT ON
SP.ID_STATUS_PLATNIKA_TYP = SPT.ID
WHERE
SP.ID_KONTRAHENT = K.ID
AND SP.DATA_POZYSKANIA <= KW.MAX_ZALICZAJ_DO
AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.MAX_D_DO)
AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.MIN_D_OD))))
)
UNION ALL
SELECT
1
FROM
KWARTALY_DATY KD
INNER JOIN URZADZENIE U ON 1 = 1
/* robimy liste kwartalow w ktorych obowiazuje status dzialalnosci
kontrahenta i szukamy brakow deklaracji */
INNER JOIN KWARTALY KW ON
EXISTS
(SELECT
*
FROM
STATUS_PLATNIKA SP
INNER JOIN STATUS_PLATNIKA_TYP SPT ON
SP.ID_STATUS_PLATNIKA_TYP = SPT.ID
WHERE
SP.ID_KONTRAHENT = K.ID
AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO
AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.D_DO) AND
(SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.D_OD))))
)
WHERE
NOT EXISTS
(SELECT
*
FROM
SPR S
WHERE
S.ID_KONTRAHENT = K.ID
AND S.ID_URZADZENIE = U.ID
AND ((EXTRACT(YEAR FROM S.DATA_OD)*12+EXTRACT(MONTH FROM
S.DATA_OD)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO) OR
(EXTRACT(YEAR FROM S.DATA_DO)*12+EXTRACT(MONTH FROM S.DATA_DO))
BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO))
)
) AND (WITH KWARTALY AS
(SELECT
*
FROM
KWARTAL KW
WHERE
KW.CKWARTAL BETWEEN '2019.I'
AND '2019.I'), KWARTALY_DATY AS
(SELECT
MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD
, MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO
, MIN(KW.D_OD) AS MIN_D_OD
, MAX(KW.D_DO) AS MAX_D_DO
, MIN(KW.CKWARTAL) AS MIN_KWARTAL
, MAX(KW.CKWARTAL) AS MAX_KWARTAL
FROM
KWARTALY KW)
SELECT
DATEDIFF(DAY, KD.MIN_D_OD, KD.MAX_D_DO)
FROM
KWARTALY_DATY KD) =(WITH KWARTALY AS
(SELECT
*
FROM
KWARTAL KW
WHERE
KW.CKWARTAL BETWEEN '2019.I'
AND '2019.I'), KWARTALY_DATY AS
(SELECT
MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD
, MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO
, MIN(KW.D_OD) AS MIN_D_OD
, MAX(KW.D_DO) AS MAX_D_DO
, MIN(KW.CKWARTAL) AS MIN_KWARTAL
, MAX(KW.CKWARTAL) AS MAX_KWARTAL
FROM
KWARTALY KW)
SELECT
SUM(DATEDIFF(DAY, CASE WHEN SP.DATA_OD < KD.MIN_D_OD THEN KD.MIN_D_OD
ELSE SP.DATA_OD END, CASE WHEN COALESCE(SP.DATA_DO, KD.MAX_D_DO) > KD.MAX_D_DO
THEN KD.MAX_ZALICZAJ_DO ELSE COALESCE(SP.DATA_DO, KD.MAX_D_DO) END))
FROM
KWARTALY_DATY KD
INNER JOIN STATUS_PLATNIKA SP ON 1 = 1
INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = SPT.ID
WHERE
SP.ID_KONTRAHENT = K.ID
AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO
AND (SPT.FLAGA_SYS IN(2) AND SP.DATA_OD <= KD.MAX_D_DO AND (SP.DATA_DO
IS NULL OR SP.DATA_DO >= KD.MIN_D_OD))))
FROM
KONTRAHENT K
WHERE
((
/* PP platnik */
CASE WHEN (
EXISTS
(WITH KWARTALY AS
(SELECT
*
FROM
KWARTAL KW
WHERE
KW.CKWARTAL BETWEEN '2019.I'
AND '2019.I'), KWARTALY_DATY AS
(SELECT
MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD
, MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO
, MIN(KW.D_OD) AS MIN_D_OD
, MAX(KW.D_DO) AS MAX_D_DO
, MIN(KW.CKWARTAL) AS MIN_KWARTAL
, MAX(KW.CKWARTAL) AS MAX_KWARTAL
FROM
KWARTALY KW)
SELECT
1
FROM
KWARTALY_DATY KW
WHERE
/* jest jakakolwiek deklaracja w zadanym przedziale to znaczy, ze jest
platnikiem */
EXISTS
(SELECT
*
FROM
SPR S
INNER JOIN KWARTAL SKW ON S.ID_KWARTAL = SKW.ID
WHERE
S.ID_KONTRAHENT = K.ID
AND SKW.CKWARTAL BETWEEN KW.MIN_KWARTAL
AND KW.MAX_KWARTAL
)
OR
/* jesli ma wpis o statusie dzialnosci za zadany okres (z data pozyaskania nie
z przyszlosci) to tez jest platnikiem */
EXISTS
(SELECT
*
FROM
STATUS_PLATNIKA SP
INNER JOIN STATUS_PLATNIKA_TYP SPT ON
SP.ID_STATUS_PLATNIKA_TYP = SPT.ID
WHERE
SP.ID_KONTRAHENT = K.ID
AND SP.DATA_POZYSKANIA <= KW.MAX_ZALICZAJ_DO
AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.MAX_D_DO)
AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.MIN_D_OD))))
)
UNION ALL
SELECT
1
FROM
KWARTALY_DATY KD
INNER JOIN URZADZENIE U ON 1 = 1
/* robimy liste kwartalow w ktorych obowiazuje status dzialalnosci
kontrahenta i szukamy brakow deklaracji */
INNER JOIN KWARTALY KW ON
EXISTS
(SELECT
*
FROM
STATUS_PLATNIKA SP
INNER JOIN STATUS_PLATNIKA_TYP SPT ON
SP.ID_STATUS_PLATNIKA_TYP = SPT.ID
WHERE
SP.ID_KONTRAHENT = K.ID
AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO
AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.D_DO) AND
(SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.D_OD))))
)
WHERE
NOT EXISTS
(SELECT
*
FROM
SPR S
WHERE
S.ID_KONTRAHENT = K.ID
AND S.ID_URZADZENIE = U.ID
AND ((EXTRACT(YEAR FROM S.DATA_OD)*12+EXTRACT(MONTH FROM
S.DATA_OD)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO) OR
(EXTRACT(YEAR FROM S.DATA_DO)*12+EXTRACT(MONTH FROM S.DATA_DO))
BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO))
)
)) IS NULL THEN -1 ELSE 0 END
/* PP platnik koniec */
+
/* PP nieplatnik */
CASE WHEN (
NOT EXISTS
(WITH KWARTALY AS
(SELECT
*
FROM
KWARTAL KW
WHERE
KW.CKWARTAL BETWEEN '2019.I'
AND '2019.I'), KWARTALY_DATY AS
(SELECT
MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD
, MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO
, MIN(KW.D_OD) AS MIN_D_OD
, MAX(KW.D_DO) AS MAX_D_DO
, MIN(KW.CKWARTAL) AS MIN_KWARTAL
, MAX(KW.CKWARTAL) AS MAX_KWARTAL
FROM
KWARTALY KW)
SELECT
1
FROM
KWARTALY_DATY KW
WHERE
/* jest jakakolwiek deklaracja w zadanym przedziale to znaczy, ze jest
platnikiem */
EXISTS
(SELECT
*
FROM
SPR S
INNER JOIN KWARTAL SKW ON S.ID_KWARTAL = SKW.ID
WHERE
S.ID_KONTRAHENT = K.ID
AND SKW.CKWARTAL BETWEEN KW.MIN_KWARTAL
AND KW.MAX_KWARTAL
)
OR
/* jesli ma wpis o statusie dzialnosci za zadany okres (z data pozyaskania nie
z przyszlosci) to tez jest platnikiem */
EXISTS
(SELECT
*
FROM
STATUS_PLATNIKA SP
INNER JOIN STATUS_PLATNIKA_TYP SPT ON
SP.ID_STATUS_PLATNIKA_TYP = SPT.ID
WHERE
SP.ID_KONTRAHENT = K.ID
AND SP.DATA_POZYSKANIA <= KW.MAX_ZALICZAJ_DO
AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.MAX_D_DO)
AND (SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.MIN_D_OD))))
)
UNION ALL
SELECT
1
FROM
KWARTALY_DATY KD
INNER JOIN URZADZENIE U ON 1 = 1
/* robimy liste kwartalow w ktorych obowiazuje status dzialalnosci
kontrahenta i szukamy brakow deklaracji */
INNER JOIN KWARTALY KW ON
EXISTS
(SELECT
*
FROM
STATUS_PLATNIKA SP
INNER JOIN STATUS_PLATNIKA_TYP SPT ON
SP.ID_STATUS_PLATNIKA_TYP = SPT.ID
WHERE
SP.ID_KONTRAHENT = K.ID
AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO
AND (SPT.FLAGA_SYS IN(1) AND ((SP.DATA_OD <= KW.D_DO) AND
(SP.DATA_DO IS NULL OR (SP.DATA_DO >= KW.D_OD))))
)
WHERE
NOT EXISTS
(SELECT
*
FROM
SPR S
WHERE
S.ID_KONTRAHENT = K.ID
AND S.ID_URZADZENIE = U.ID
AND ((EXTRACT(YEAR FROM S.DATA_OD)*12+EXTRACT(MONTH FROM
S.DATA_OD)) BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO) OR
(EXTRACT(YEAR FROM S.DATA_DO)*12+EXTRACT(MONTH FROM S.DATA_DO))
BETWEEN(KW.ROK*12+KW.MIESIAC_OD) AND (KW.ROK*12+KW.MIESIAC_DO))
)
) AND (WITH KWARTALY AS
(SELECT
*
FROM
KWARTAL KW
WHERE
KW.CKWARTAL BETWEEN '2019.I'
AND '2019.I'), KWARTALY_DATY AS
(SELECT
MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD
, MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO
, MIN(KW.D_OD) AS MIN_D_OD
, MAX(KW.D_DO) AS MAX_D_DO
, MIN(KW.CKWARTAL) AS MIN_KWARTAL
, MAX(KW.CKWARTAL) AS MAX_KWARTAL
FROM
KWARTALY KW)
SELECT
DATEDIFF(DAY, KD.MIN_D_OD, KD.MAX_D_DO)
FROM
KWARTALY_DATY KD) =(WITH KWARTALY AS
(SELECT
*
FROM
KWARTAL KW
WHERE
KW.CKWARTAL BETWEEN '2019.I'
AND '2019.I'), KWARTALY_DATY AS
(SELECT
MIN(KW.ZALICZAJ_OD) AS MIN_ZALICZAJ_OD
, MAX(KW.ZALICZAJ_DO) AS MAX_ZALICZAJ_DO
, MIN(KW.D_OD) AS MIN_D_OD
, MAX(KW.D_DO) AS MAX_D_DO
, MIN(KW.CKWARTAL) AS MIN_KWARTAL
, MAX(KW.CKWARTAL) AS MAX_KWARTAL
FROM
KWARTALY KW)
SELECT
SUM(DATEDIFF(DAY, CASE WHEN SP.DATA_OD < KD.MIN_D_OD THEN KD.MIN_D_OD
ELSE SP.DATA_OD END, CASE WHEN COALESCE(SP.DATA_DO, KD.MAX_D_DO) > KD.MAX_D_DO
THEN KD.MAX_ZALICZAJ_DO ELSE COALESCE(SP.DATA_DO, KD.MAX_D_DO) END))
FROM
KWARTALY_DATY KD
INNER JOIN STATUS_PLATNIKA SP ON 1 = 1
INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = SPT.ID
WHERE
SP.ID_KONTRAHENT = K.ID
AND SP.DATA_POZYSKANIA <= KD.MAX_ZALICZAJ_DO
AND (SPT.FLAGA_SYS IN(2) AND SP.DATA_OD <= KD.MAX_D_DO AND (SP.DATA_DO
IS NULL OR SP.DATA_DO >= KD.MIN_D_OD)))) IS NULL THEN -1 ELSE 0 END
/* PP nieplatnik koniec */
) < 0
AND K.KONT_NR+0 > 0)
ORDER BY
1
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
