Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13552

Bug: Server Shutsdown when using LEAD Window Function within a CASE Function

    XMLWordPrintable

    Details

      Description

      When executing the following query, the Server shutdown:

      CREATE OR REPLACE TABLE rel595.t3_scada_alarms_oper_mod_ots_states_cycles_2017_Events
      ENGINE = Aria
        WITH t3_scada_alarms_oper_mod_ots_states_cycles_2017_aux
        AS
        (
          SELECT
            A.id_cycles,
            A.MachineID,
            A.MachineUT,
            A.ENVELOPE,
            A.Cycle_DownTime_State,
            A.Cycle_DownTime_State_Description,
            A.Cycle_DateTimeON,
            A.Cycle_DateTimeOFF,
            A.`Cycle_Time[sec]`,
            A.`Working Hours`,
            A.Cycle_Alarm_Code_OrderByAlarm,
            A.Cycle_Alarm_Description_OrderByAlarm,
            A.Cycle_First_Alarm_Code,
            A.Cycle_First_Alarm_Description,
            A.Cycle_Alarm_OrderByDateTimeON,
            A.Cycle_Alarm_Functional_Group,
            A.Cycle_Alarm_Turbine_State_Request,
            A.Cycle_Operation_Mode_Code,
            A.Cycle_Operation_Mode_Description,
            A.Cycle_Operation_Mode_Turbine_State_Request,
            A.Cycle_Final_Reset_Type,
            A.Cycle_Final_Reset_Criteria,
            A.Cycle_Final_Availability,
            A.Cycle_OT_NumOT,
            A.Cycle_OT_Description,
            A.Cycle_OT_AlarmCode,
            A.Cycle_OT_AlarmDescription,
            A.Cycle_OT_TypeCode,
            A.Cycle_OT_TypeDescription,
            A.Cycle_OT_Subtipo,
            A.Cycle_OT_LCNId,
            A.Cycle_OT_Planned,
            A.Cycle_OT_With_Consumption,
            A.Cycle_OT_Availability_Stop,
            B.Event_Code,
            B.Event_Description,
            B.DateTimeON
              AS Event_DateTimeON,
            B.DateTimeOFF
              AS Event_DateTimeOFF,
            MAX(B.DateTimeOFF) 
              OVER (PARTITION BY A.id_cycles)
              AS Event_Max_DateTimeOFF
          FROM
            rel595.t3_scada_alarms_oper_mod_ots_states_cycles_2017 AS A
            LEFT JOIN rel595.t1_scada_events_joined_dbvar_17543 AS B
              ON
                B.DateTimeON BETWEEN A.Cycle_DateTimeON AND A.Cycle_DateTimeOFF
                AND A.MachineUT = B.MachineUT
        )
          SELECT
            MachineID,
            MachineUT,
            ENVELOPE,
            Cycle_DownTime_State,
            Cycle_DownTime_State_Description,
            Cycle_DateTimeON,
            Cycle_DateTimeOFF,
            `Cycle_Time[sec]`,
            `Working Hours`,
            Cycle_Alarm_Code_OrderByAlarm,
            Cycle_Alarm_Description_OrderByAlarm,
            Cycle_First_Alarm_Code,
            Cycle_First_Alarm_Description,
            Cycle_Alarm_OrderByDateTimeON,
            Cycle_Alarm_Functional_Group,
            Cycle_Alarm_Turbine_State_Request,
            Cycle_Operation_Mode_Code,
            Cycle_Operation_Mode_Description,
            Cycle_Operation_Mode_Turbine_State_Request,
            Cycle_Final_Reset_Type,
            Cycle_Final_Reset_Criteria,
            Cycle_Final_Availability,
            Cycle_OT_NumOT,
            Cycle_OT_Description,
            Cycle_OT_AlarmCode,
            Cycle_OT_AlarmDescription,
            Cycle_OT_TypeCode,
            Cycle_OT_TypeDescription,
            Cycle_OT_Subtipo,
            Cycle_OT_LCNId,
            Cycle_OT_Planned,
            Cycle_OT_With_Consumption,
            Cycle_OT_Availability_Stop,
            CASE
              WHEN
                Event_Code IS NOT NULL
                AND NOT Cycle_Final_Reset_Criteria = 'LOCAL-WO'
                AND NOT Cycle_Alarm_Code_OrderByAlarm NOT REGEXP '903|904'
                -- Not Alarm Codes "903: Authoriz. person in turbine"
                -- nor "904:Presence of unauth. person in turbine".
              THEN
                1
              ELSE
                0
            END AS `WTG_Reset_by_SCADA_Command`,
            CASE
              WHEN
                Event_DateTimeOFF BETWEEN SUBTIME(Cycle_DateTimeOFF, '00:10:00') AND ADDTIME(Cycle_DateTimeOFF, '00:10:00')
                AND 
                (
                  Event_Code IS NOT NULL
                  AND NOT Cycle_Final_Reset_Criteria = 'LOCAL-WO'
                  AND NOT Cycle_Alarm_Code_OrderByAlarm NOT REGEXP '903|904'
                  -- Not Alarm Codes "903: Authoriz. person in turbine"
                  -- nor "904:Presence of unauth. person in turbine".
                )
                AND 
                (
                  ADDTIME(Cycle_DateTimeOFF, '00:30:00') < LEAD(Cycle_DateTimeON) OVER (PARTITION BY MachineID ORDER BY MachineID, Cycle_DateTimeON)
                  OR (LEAD(Cycle_DateTimeON) OVER (PARTITION BY MachineID ORDER BY MachineID, Cycle_DateTimeON) IS NULL)
                )
              THEN
                1
              ELSE
                0
            END AS `WTG_SCADA_Command_Success_Offset10`,
            TIMESTAMPDIFF(MINUTE, Event_DateTimeOFF, Cycle_DateTimeOFF)
              AS DateTimeDiff_StateOFF_EventOFF,
            Event_Code,
            Event_Description,
            Event_DateTimeON,
            Event_DateTimeOFF
          FROM
            t3_scada_alarms_oper_mod_ots_states_cycles_2017_aux
          WHERE
            (Event_DateTimeOFF = Event_Max_DateTimeOFF)
            OR Event_DateTimeOFF IS NULL
          GROUP BY
            id_cycles
          ORDER BY
            MachineID,
            Cycle_DateTimeON
      

      Thank you

        Attachments

        1. mdev13552.test
          4.25 MB
        2. MDEV-13552.xlsx
          10 kB
        3. my.ini
          31 kB
        4. NGALARRETA-P700-error.log
          51 kB

          Issue Links

            Activity

              People

              Assignee:
              cvicentiu Vicențiu Ciorbaru
              Reporter:
              Juan Juan Telleria
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration