NOT EXISTS() predicate sometimes return NULL instead of True or False [CORE6120] #6369

firebird-automations · 2019-08-12T11:18:18Z

Submitted by: @livius2

In below query second column rerurn "NULL" instead of "TRUE". If you remove "NOT" then result is "FALSE".
Run it against database from CORE6106

SELECT
http://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 = http://SKW.ID
WHERE
S.ID_KONTRAHENT = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://K.ID
AND S.ID_URZADZENIE = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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

The text was updated successfully, but these errors were encountered:

firebird-automations · 2019-08-12T11:18:57Z

Modified by: @livius2

summary: NOT EXISTS() predicate sometimes return NULL instead True or False => NOT EXISTS() predicate sometimes return NULL instead of True or False

firebird-automations · 2019-08-12T11:32:55Z

Commented by: @livius2

You can simplify query by removing WHERE clause from main query.
But then you got True, False and NULL in the last column i suppose harder to test but maybe not.

firebird-automations · 2019-08-12T19:43:40Z

Commented by: Sean Leyne (seanleyne)

Karol,

You need to provide a simpler example!

It should not be necessary for someone to have to deal with **> 350 lines** of SQL to determine the source of the problem!!!

firebird-automations · 2019-08-12T19:44:14Z

Commented by: Sean Leyne (seanleyne)

When a smaller example is provided, the ticket/case will be re-opened.

firebird-automations · 2019-08-12T19:44:14Z

Modified by: Sean Leyne (seanleyne)

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Cannot Reproduce [ 5 ]

firebird-automations · 2019-08-12T23:20:29Z

Commented by: @livius2

Sean,
you have reproducible test case and the database.
It is something ideal for developer, repeatable test case.

If I knew what the problem is, I could slim down this sql. But i do not know the root of the problem.
And as i wrote previously, you can remove whole WHERE from the query and leave only "fields" but then you must deal with bigger resultset with mixed output values TRUE, FALSE, NULL (above return only this one with NULL).
Then sql will be not 357 lines only 138. I have spent hours to narrow down the problem and prepare test database with limited metadata.
The real query is much much bigger then above. You have only "FROM KONTRAHENT K" here without the joins and other features".

And also, you do not need to deal with whole query. Only debug 2 things.
1. Were Firebird return value from ", EXISTS" - when finded you go to point 2.
2. and then look what it do with result when you change it to ", NOT EXISTS".
I suppose simple to debug by core developer.

firebird-automations · 2019-08-13T00:37:07Z

Commented by: Sean Leyne (seanleyne)

"Reproduceable" also means "intelligible/understandable".

The SQL that you have provided (IMO) is a code generated DISASTER (I know from the linked case that you are using code to generate the SQL). It re-uses the same aliases for the same/identical CTE multiple times.

I don't know what you have tried in your debugging, but I would start by re-factoring the SQL as follows:
- each unique CTE should appears only once at the top of the first SQL
- all reference/use of a CTE should have a unique alias if you are JOINing to the CTE with different criteria

IMO, if you can't re-factor you code to a simple/basic example then it is in-appropriate to expect the engine or a developer to make sense of the problem! Otherwise, you are simply <i>"throwing a wad of cold spaghetti against a wall, letting it harden and expecting someone else to do untangle it"</i>.

Finally, my name is not "Shane".

firebird-automations · 2019-08-13T00:50:00Z

Commented by: @livius2

What i describe above, you do not need to deal with whole sql and analyze it
Try first to run this and analyze ita as:

1.
SELECT
EXISTS()
FROM
KONTRAHENT K
--without the where

and look when and how Exists here return the value "False"

2. then look what engine do with this result when you change it to not exists
SELECT
NOT EXISTS()
FROM
KONTRAHENT K
--without the where

when you do this and do not get the answer then you can ask me for something easier to reproduce.
But i suppose you do not tried this already?

PS. sorry for the name (fixed now)

firebird-automations · 2019-08-13T00:57:57Z

Commented by: Sean Leyne (seanleyne)

Edited SQL down to the minimum required to reproduce

firebird-automations · 2019-08-13T00:57:57Z

Modified by: Sean Leyne (seanleyne)

description: In below query second column rerurn "NULL" instead of "TRUE". If you remove "NOT" then result is "FALSE".
Run it against database from CORE6106

SELECT
http://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 = http://SKW.ID
WHERE
S.ID_KONTRAHENT = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://K.ID
AND S.ID_URZADZENIE = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://SKW.ID
WHERE
S.ID_KONTRAHENT = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://K.ID
AND S.ID_URZADZENIE = http://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 = http://SKW.ID
WHERE
S.ID_KONTRAHENT = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://K.ID
AND S.ID_URZADZENIE = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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

=>

In below query second column rerurn "NULL" instead of "TRUE". If you remove "NOT" then result is "FALSE".
Run it against database from CORE6106

SELECT
http://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 = http://SKW.ID
WHERE
S.ID_KONTRAHENT = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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 = http://K.ID
AND S.ID_URZADZENIE = http://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 = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://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

firebird-automations · 2019-08-13T01:44:54Z

Commented by: Sean Leyne (seanleyne)

Does this SQL have the same problem?

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
http://K.ID,
(
NOT EXISTS(
SELECT 1
FROM KWARTALY_DATY KW1
WHERE
/* jest jakakolwiek deklaracja w zadanym przedziale to znaczy, ze jest platnikiem */
EXISTS(
SELECT 1
FROM SPR S
INNER JOIN KWARTAL SKW ON S.ID_KWARTAL = http://SKW.ID
WHERE
S.ID_KONTRAHENT = http://K.ID
AND SKW.CKWARTAL BETWEEN KW1.MIN_KWARTAL AND KW1.MAX_KWARTAL
)
OR
/* jesli ma wpis o statusie dzialnosci za zadany okres (z data pozyaskania nie z przyszlosci) to tez jest platnikiem */
EXISTS(
SELECT 1
FROM STATUS_PLATNIKA SP
INNER JOIN STATUS_PLATNIKA_TYP SPT ON SP.ID_STATUS_PLATNIKA_TYP = http://SPT.ID
WHERE
SP.ID_KONTRAHENT = http://K.ID
AND SP.DATA_POZYSKANIA <= KW1.MAX_ZALICZAJ_DO
AND (
SPT.FLAGA_SYS IN(1)
AND (
(SP.DATA_OD <= KW1.MAX_D_DO)
AND (
SP.DATA_DO IS NULL
OR (SP.DATA_DO >= KW1.MIN_D_OD)
)
)
)
)
UNION ALL

  SELECT 1
  FROM KWARTALY\_DATY KD2
    INNER JOIN URZADZENIE U ON 1 = 1
    /\* robimy liste kwartalow w ktorych obowiazuje status dzialalnosci kontrahenta i szukamy brakow deklaracji \*/ 
    INNER JOIN KWARTALY KW2 ON
      EXISTS\(
        SELECT 1
        FROM STATUS\_PLATNIKA SP
          INNER JOIN STATUS\_PLATNIKA\_TYP SPT ON SP\.ID\_STATUS\_PLATNIKA\_TYP = <http://SPT.ID>
        WHERE 
            SP\.ID\_KONTRAHENT = <http://K.ID> 
            AND SP\.DATA\_POZYSKANIA <= KD2\.MAX\_ZALICZAJ\_DO
            AND \(
              SPT\.FLAGA\_SYS IN\(1\)
              AND \(
               \(SP\.DATA\_OD <= KW2\.D\_DO\)
                AND \(
                    SP\.DATA\_DO IS NULL
                    OR \(SP\.DATA\_DO \>= KW2\.D\_OD\)
                  \)
               \)
            \)
      \)
  WHERE 
    NOT EXISTS\(
      SELECT 1
      FROM SPR S
      WHERE 
          S\.ID\_KONTRAHENT = <http://K.ID> 
          AND S\.ID\_URZADZENIE = <http://U.ID> 
          AND \(
            \(EXTRACT\(YEAR FROM S\.DATA\_OD\)\*12\+EXTRACT\(MONTH FROM S\.DATA\_OD\)\) BETWEEN\(KW2\.ROK\*12\+KW2\.MIESIAC\_OD\) AND \(KW2\.ROK\*12\+KW2\.MIESIAC\_DO\)
            OR
            \(EXTRACT\(YEAR FROM S\.DATA\_DO\)\*12\+EXTRACT\(MONTH FROM S\.DATA\_DO\)\) BETWEEN\(KW2\.ROK\*12\+KW2\.MIESIAC\_OD\) AND \(KW2\.ROK\*12\+KW2\.MIESIAC\_DO\)
          \)
    \)
\)  \-\- Main NOT EXISTS
AND \(
    SELECT
        DATEDIFF\(DAY, KD2\.MIN\_D\_OD, KD2\.MAX\_D\_DO\)
    FROM KWARTALY\_DATY KD2
  \) = \(
    SELECT
        SUM\(
          DATEDIFF\(DAY, CASE WHEN SP\.DATA\_OD < KD3\.MIN\_D\_OD THEN KD3\.MIN\_D\_OD ELSE SP\.DATA\_OD END, CASE WHEN COALESCE\(SP\.DATA\_DO, KD3\.MAX\_D\_DO\) \> KD3\.MAX\_D\_DO THEN KD3\.MAX\_ZALICZAJ\_DO ELSE COALESCE\(SP\.DATA\_DO, KD3\.MAX\_D\_DO\) END\)
        \)
    FROM 
        KWARTALY\_DATY KD3
        INNER JOIN STATUS\_PLATNIKA SP ON 1 = 1 
        INNER JOIN STATUS\_PLATNIKA\_TYP SPT ON SP\.ID\_STATUS\_PLATNIKA\_TYP = <http://SPT.ID> 
    WHERE 
        SP\.ID\_KONTRAHENT = <http://K.ID> 
        AND SP\.DATA\_POZYSKANIA <= KD3\.MAX\_ZALICZAJ\_DO
        AND \(SPT\.FLAGA\_SYS IN\(2\) AND SP\.DATA\_OD <= KD3\.MAX\_D\_DO AND \(SP\.DATA\_DO IS NULL OR SP\.DATA\_DO \>= KD3\.MIN\_D\_OD\)\)
  \)

)
FROM
KONTRAHENT K

firebird-automations · 2019-08-13T01:56:22Z

Commented by: Sean Leyne (seanleyne)

FYI, not all the criteria for the column is contained in the NOT EXISTS(), so the problem may not be there...

What happens if you drop:

AND \(
    SELECT
        DATEDIFF\(DAY, KD2\.MIN\_D\_OD, KD2\.MAX\_D\_DO\)
    FROM KWARTALY\_DATY KD2
  \) = \(
    SELECT
        SUM\(
          DATEDIFF\(DAY, CASE WHEN SP\.DATA\_OD < KD3\.MIN\_D\_OD THEN KD3\.MIN\_D\_OD ELSE SP\.DATA\_OD END, CASE WHEN COALESCE\(SP\.DATA\_DO, KD3\.MAX\_D\_DO\) \> KD3\.MAX\_D\_DO THEN KD3\.MAX\_ZALICZAJ\_DO ELSE COALESCE\(SP\.DATA\_DO, KD3\.MAX\_D\_DO\) END\)
        \)
    FROM 
        KWARTALY\_DATY KD3
        INNER JOIN STATUS\_PLATNIKA SP ON 1 = 1 
        INNER JOIN STATUS\_PLATNIKA\_TYP SPT ON SP\.ID\_STATUS\_PLATNIKA\_TYP = <http://SPT.ID> 
    WHERE 
        SP\.ID\_KONTRAHENT = <http://K.ID> 
        AND SP\.DATA\_POZYSKANIA <= KD3\.MAX\_ZALICZAJ\_DO
        AND \(
          SPT\.FLAGA\_SYS IN\(2\)
          AND SP\.DATA\_OD <= KD3\.MAX\_D\_DO
          AND \(
            SP\.DATA\_DO IS NULL
            OR SP\.DATA\_DO \>= KD3\.MIN\_D\_OD
          \)
        \)
  \)

firebird-automations · 2019-08-13T10:19:41Z

Commented by: @livius2

Sean, you have absolutely right here
this is outside of Exists/not exists, then result is understandable now as logic table is:

false and NULL = false
but
true and NULL = NULL

then the issue doesn't exists at all.

firebird-automations · 2019-08-14T19:48:47Z

Modified by: Sean Leyne (seanleyne)

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Cannot Reproduce [ 5 ] =>

firebird-automations · 2019-08-14T19:50:11Z

Commented by: Sean Leyne (seanleyne)

Re-opened the case to provide the correct resolution details.

Karol has agreed that there is no issue to be addressed by this ticket.

firebird-automations · 2019-08-14T19:50:11Z

Modified by: Sean Leyne (seanleyne)

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

firebird-automations · 2019-08-16T18:13:23Z

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

firebird-automations closed this as completed Aug 14, 2019

firebird-automations added affect-version: 3.0.4 affect-version: 3.0.5 resolution: wontfix priority: major component: engine type: bug labels Apr 25, 2021

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

NOT EXISTS() predicate sometimes return NULL instead of True or False [CORE6120] #6369

NOT EXISTS() predicate sometimes return NULL instead of True or False [CORE6120] #6369

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 14, 2019

firebird-automations commented Aug 14, 2019

firebird-automations commented Aug 14, 2019

firebird-automations commented Aug 16, 2019

Navigation Menu

NOT EXISTS() predicate sometimes return NULL instead of True or False [CORE6120] #6369

NOT EXISTS() predicate sometimes return NULL instead of True or False [CORE6120] #6369

Comments

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 12, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 13, 2019

firebird-automations commented Aug 14, 2019

firebird-automations commented Aug 14, 2019

firebird-automations commented Aug 14, 2019

firebird-automations commented Aug 16, 2019