[
http://tracker.firebirdsql.org/browse/CORE-6120?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Sean Leyne reopened CORE-6120:
------------------------------
> NOT EXISTS() predicate sometimes return NULL instead of 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
--
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
