Issue Details (XML | Word | Printable)

Key: CORE-6106
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Karol Bieniaszewski
Votes: 0
Watchers: 4
Operations

If you were logged in you would be able to see more operations.
Firebird Core

no current record for fetch operation

Created: 22/Jul/19 01:59 PM   Updated: 23/Jul/19 01:36 PM
Component/s: Engine
Affects Version/s: 3.0.5
Fix Version/s: None

File Attachments: 1. File COREXXX.FDB (3.81 MB)


QA Status: No test


 Description  « Hide
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
  K.ID, K.KONT_NR
FROM
  KONTRAHENT K
WHERE
  NOT(
    EXISTS(
      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 K.KONT_NR > 0
  )


----------------------------------------

SQL Message : -508
The cursor identified in the UPDATE or DELETE statement is not positioned on a row.

Engine Code : 335544348
Engine Message :
no current record for fetch operation



----------------------------------------

if you change last
  AND K.KONT_NR > 0)
to
  AND K.KONT_NR+0 > 0)

it run without an error

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 22/Jul/19 02:27 PM
Reformatted SQL for readibility

Sean Leyne added a comment - 22/Jul/19 04:13 PM
Karol,

There are some aspects of the SQL which I find "unexpected":

1- The use of UNION when AND EXISTS could be used

2- The use of an EXISTS as a JOIN criteria, again this could be better expressed as a EXISTS within the WHERE clause

3- You use the alias "KW" 4 separate times. IMO, a very bad practice

Karol Bieniaszewski added a comment - 22/Jul/19 10:02 PM
This is good catch, but as you probably can guess, this is not hand written query.
It is generated and mixed together from few separated modules.

I can change query (some generation paths) to hide the problem. But the issue somewhere exist, when the plan ues one additional index.