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

WITH + UNION: Server Crashes

    XMLWordPrintable

    Details

      Description

      When using WITH subquery together with UNION, the Server Crashed.

      The executed query is the following:

       
      CREATE OR REPLACE TABLE t3_mtbv
      AS
        WITH t_GROUP_BY_MachineUT_Stop_WorkReport_StartDate
        AS
        (
          SELECT
            MachineUT,
            Study_Start_Date,
            Study_End_Date,
            CASE
              WHEN
                -- There is a Stop:
                C.NumOT IS NOT NULL
              THEN
                DATE(StopStartDate)
              WHEN
                -- There is a WorkReport (And not a Stop)
                D.NumOT IS NOT NULL
              THEN
                DATE(WorkReportStartDate)
              ELSE
                NULL
            END AS Stop_WorkReport_StartDate,
            CASE
              -- Priority 1: ZPM1 - PREVENTIVE
              WHEN
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ') LIKE '%ZPM1%'
              THEN
                'ZPM1'
                
              -- Priority 2: ZPM3 - LARGE CORRECTIVE
              WHEN
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ') LIKE '%ZPM3%'
              THEN
                'ZPM3'
                
              -- Priority 3: ZPM4 - DESIGN MODIFICATION
              WHEN
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ') LIKE '%ZPM4%'
              THEN
                'ZPM4'
                
              -- Priority 4: ZPM9 - OUTAGE
              WHEN
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ') LIKE '%ZPM9%'
              THEN
                'ZPM9'
                
              -- Priority 5: ZPM2 - SMALL CORRECTIVE
              WHEN
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ') LIKE '%ZPM2%'
              THEN
                'ZPM2'
              ELSE
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ')
            END AS Priority_OTTypeCode  
          FROM
            mtbv.t1_population AS A
            INNER JOIN eo_mariadb.t_ots AS B
            ON
              A.MachineUT = B.UT
              -- EXCLUDED WORK ORDERS: Commisioning, Extra Commisioning, Availability Test, Wind Farm Preservation, Paralizations
              AND B.OTTypeCode IN ('ZPM1', 'ZPM2', 'ZPM3', 'ZPM4', 'ZPM9') 
              AND (B.Enabled = 1 OR B.Enabled IS NULL)
              AND (B.TipoUbicacion IN ('A', 'E') OR B.TipoUbicacion IS NULL)
              AND (B.OTDate BETWEEN A.Study_Start_Date AND A.Study_End_Date)
              -- AND (B.StatusUsuario <> 'SOLI' OR B.StatusUsuario IS NULL)
            LEFT JOIN eo_mariadb.t_stops AS C
            ON
              B.NumOT = C.NumOT
              AND (C.Enabled = 1 OR C.Enabled IS NULL)
              -- Stoppages shorter than 2 minutes are excluded:
              AND (C.StopTotalTime > 0.03333333)
              -- Stoppages longer than 30 minutes:
              AND (C.StopTotalTime <= 720)
            LEFT JOIN eo_mariadb.t_workreports AS D
            ON
              B.NumOT = D.NumOT
              AND C.NumOT IS NULL
              AND (D.Enabled = 1 OR D.Enabled IS NULL)
              AND (D.WorkReportTotalTime > 0.03333333)
              AND (D.WorkReportTotalTime < 720)
            GROUP BY
              MachineUT,
              Stop_WorkReport_StartDate    
            HAVING
              Stop_WorkReport_StartDate IS NOT NULL
              AND (DATE(Stop_WorkReport_StartDate) BETWEEN DATE(Study_Start_Date) AND DATE(Study_End_Date))
            ORDER BY
              MachineUT,
              Stop_WorkReport_StartDate
        ), t_mtbv_YEARMONTH
        AS
        (
          SELECT
            SUBSTRING(DATE_FORMAT(STR_TO_DATE(CONCAT(CAST(A.YearMonth AS CHAR), '01'), '%Y%m%d'), '%Y %M'), 1, 8)
              AS `Year_Month`,
            `LifeDays_UT_YearMonth`,
            count(*)
              AS `Visit_Count`,
            ROUND((`LifeDays_UT_YearMonth`/count(*)), 3)
              AS `MTBV [Days]`    
          FROM
            mtbv.t1_population_yearmonth AS A
            INNER JOIN t_GROUP_BY_MachineUT_Stop_WorkReport_StartDate AS B
            ON
              A.MachineUT = B.MachineUT
              AND A.YearMonth = DATE_FORMAT(B.Stop_WorkReport_StartDate, '%Y%m')
            INNER JOIN t2_wtg_lifedays AS C
            ON
              A.YearMonth = C.YearMonth
          GROUP BY
            `Year_Month`
          ORDER BY
            A.YearMonth
        ), t_mtbv_TOTAL
        AS
        (
          SELECT
            'TOTAL',
            `LifeDays_UT_YearMonth`,
            count(*)
              AS `Visit_Count`,
            ROUND((`LifeDays_UT_YearMonth`/count(*)), 3)
              AS `MTBV [Days]`    
          FROM
            t_GROUP_BY_MachineUT_Stop_WorkReport_StartDate,
            mtbv.t2_WTG_LifeDays
          WHERE
            YearMonth = 'TOTAL'
        )
        SELECT
          *
        FROM
          t_mtbv_YEARMONTH
        UNION
        SELECT
          *
        FROM
          t_mtbv_TOTAL;
      

      And the MariaDB error log was as follows:

       
      171122 15:35:58 [ERROR] mysqld got exception 0xc0000005 ;
      This could be because you hit a bug. It is also possible that this binary
      or one of the libraries it was linked against is corrupt, improperly built,
      or misconfigured. This error can also be caused by malfunctioning hardware.
       
      To report this bug, see https://mariadb.com/kb/en/reporting-bugs
       
      We will try our best to scrape up some info that will hopefully help
      diagnose the problem, but since we have already crashed, 
      something is definitely wrong and this may fail.
       
      Server version: 10.2.8-MariaDB
      key_buffer_size=65536
      read_buffer_size=262144
      max_used_connections=20
      max_threads=65537
      thread_count=18
      It is possible that mysqld could use up to 
      key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1512 K  bytes of memory
      Hope that's ok; if not, decrease some variables in the equation.
       
      Thread pointer: 0x2c7f1a8f8
      Attempting backtrace. You can use the following information to find out
      where mysqld died. If you see no messages after this, something went
      terribly wrong...
      mysqld.exe!sub_select_cache()[sql_select.cc:18338]
      mysqld.exe!evaluate_join_record()[sql_select.cc:18784]
      mysqld.exe!sub_select()[sql_select.cc:18602]
      mysqld.exe!do_select()[sql_select.cc:18107]
      mysqld.exe!JOIN::exec_inner()[sql_select.cc:3485]
      mysqld.exe!JOIN::exec()[sql_select.cc:3279]
      mysqld.exe!mysql_select()[sql_select.cc:3680]
      mysqld.exe!mysql_derived_fill()[sql_derived.cc:1073]
      mysqld.exe!mysql_handle_single_derived()[sql_derived.cc:197]
      mysqld.exe!st_join_table::preread_init()[sql_select.cc:11856]
      mysqld.exe!sub_select()[sql_select.cc:18531]
      mysqld.exe!do_select()[sql_select.cc:18107]
      mysqld.exe!JOIN::exec_inner()[sql_select.cc:3485]
      mysqld.exe!JOIN::exec()[sql_select.cc:3279]
      mysqld.exe!st_select_lex_unit::exec()[sql_union.cc:1006]
      mysqld.exe!mysql_union()[sql_union.cc:41]
      mysqld.exe!handle_select()[sql_select.cc:351]
      mysqld.exe!execute_sqlcom_select()[sql_parse.cc:6446]
      mysqld.exe!mysql_execute_command()[sql_parse.cc:3460]
      mysqld.exe!mysql_parse()[sql_parse.cc:7891]
      mysqld.exe!dispatch_command()[sql_parse.cc:1814]
      mysqld.exe!do_command()[sql_parse.cc:1359]
      mysqld.exe!threadpool_process_request()[threadpool_common.cc:346]
      mysqld.exe!tp_callback()[threadpool_common.cc:192]
      ntdll.dll!TpPostWork()
      ntdll.dll!RtlRealSuccessor()
      kernel32.dll!BaseThreadInitThunk()
      ntdll.dll!RtlUserThreadStart()
       
      Trying to get some variables.
      Some pointers may be invalid and cause the dump to abort.
      Query (0x2cb62a510): WITH t_GROUP_BY_MachineUT_Stop_WorkReport_StartDate
       
        AS
       
        (
       
          SELECT
       
            MachineUT,
       
            Study_Start_Date,
       
            Study_End_Date,
       
            CASE
       
              WHEN
       
                -- There is a Stop:
       
                C.NumOT IS NOT NULL
       
              THEN
       
                DATE(StopStartDate)
       
              WHEN
       
                -- There is a WorkReport (And not a Stop)
       
                D.NumOT IS NOT NULL
       
              THEN
       
                DATE(WorkReportStartDate)
       
              ELSE
       
                NULL
       
            END AS Stop_WorkReport_StartDate,
       
            CASE
       
              -- Priority 1: ZPM1 - PREVENTIVE
       
              WHEN
       
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ') LIKE '%ZPM1%'
       
              THEN
       
                'ZPM1'
       
                
       
              -- Priority 2: ZPM3 - LARGE CORRECTIVE
       
              WHEN
       
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ') LIKE '%ZPM3%'
       
              THEN
       
                'ZPM3'
       
                
       
              -- Priority 3: ZPM4 - DESIGN MODIFICATION
       
              WHEN
       
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ') LIKE '%ZPM4%'
       
              THEN
       
                'ZPM4'
       
                
       
              -- Priority 4: ZPM9 - OUTAGE
       
              WHEN
       
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ') LIKE '%ZPM9%'
       
              THEN
       
                'ZPM9'
       
                
       
              -- Priority 5: ZPM2 - SMALL CORRECTIVE
       
              WHEN
       
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ') LIKE '%ZPM2%'
       
              THEN
       
                'ZPM2'
       
              ELSE
       
                GROUP_CONCAT(DISTINCT OTTypeCode SEPARATOR ' / ')
       
            END AS Priority_OTTypeCode  
       
          FROM
       
            mtbv.t1_population AS A
       
            INNER JOIN eo_mariadb.t_ots AS B
       
            ON
       
              A.MachineUT = B.UT
       
              -- EXCLUDED WORK ORDERS: Commisioning, Extra Commisioning, Availability Test, Wind Farm Preservation, Paralizations
       
              AND B.OTTypeCode IN ('ZPM1', 'ZPM2', 'ZPM3', 'ZPM4', 'ZPM9') 
       
              AND (B.Enabled = 1 OR B.Enabled IS NULL)
       
              AND (B.TipoUbicacion IN ('A', 'E') OR B.TipoUbicacion IS NULL)
       
              AND (B.OTDate BETWEEN A.Study_Start_Date AND A.Study_End_Date)
       
              -- AND (B.StatusUsuario <> 'SOLI' OR B.StatusUsuario IS NULL)
       
            LEFT JOIN eo_mariadb.t_stops AS C
       
            ON
       
              B.NumOT = C.NumOT
       
              AND (C.Enabled = 1 OR C.Enabled IS NULL)
       
              -- Stoppages shorter than 2 minutes are excluded:
       
              AND (C.StopTotalTime > 0.03333333)
       
              -- Stoppages longer than 30 minutes:
       
              AND (C.StopTotalTime <= 720)
       
            LEFT JOIN eo_mariadb.t_workreports AS D
       
            ON
       
              B.NumOT = D.NumOT
       
              AND C.NumOT IS NULL
       
              AND (D.Enabled = 1 OR D.Enabled IS NULL)
       
              AND (D.WorkReportTotalTime > 0.03333333)
       
              AND (D.WorkReportTotalTime < 720)
       
            GROUP BY
       
              MachineUT,
       
              Stop_WorkReport_StartDate    
       
            HAVING
       
              Stop_WorkReport_StartDate IS NOT NULL
       
              AND (DATE(Stop_WorkReport_StartDate) BETWEEN DATE(Study_Start_Date) AND DATE(Study_End_Date))
       
            ORDER BY
       
              MachineUT,
       
              Stop_WorkReport_StartDate
       
        ), t_mtbv_YEARMONTH
       
        AS
       
        (
       
          SELECT
       
            SUBSTRING(DATE_FORMAT(STR_TO_DATE(CONCAT(CAST(A.YearMonth AS CHAR), '01'), '%Y%m%d'), '%Y %M'), 1, 8)
       
              AS `Year_Month`,
       
            `LifeDays_UT_YearMonth`,
       
            count(*)
       
              AS `Visit_Count`,
       
            ROUND((`LifeDays_UT_YearMonth`/count(*)), 3)
       
              AS `MTBV [Days]`    
       
          FROM
       
            mtbv.t1_population_yearmonth AS A
       
            INNER JOIN t_GROUP_BY_MachineUT_Stop_WorkReport_StartDate AS B
       
            ON
       
              A.MachineUT = B.MachineUT
       
              AND A.YearMonth = DATE_FORMAT(B.Stop_WorkReport_StartDate, '%Y%m')
       
            INNER JOIN t2_wtg_lifedays AS C
       
            ON
       
              A.YearMonth = C.YearMonth
       
          GROUP BY
       
            `Year_Month`
       
          ORDER BY
       
            A.YearMonth
       
        ), t_mtbv_TOTAL
       
        AS
       
        (
       
          SELECT
       
            'TOTAL',
       
            `LifeDays_UT_YearMonth`,
       
            count(*)
       
              AS `Visit_Count`,
       
            ROUND((`LifeDays_UT_YearMonth`/count(*)), 3)
       
              AS `MTBV [Days]`    
       
          FROM
       
            t_GROUP_BY_MachineUT_Stop_WorkReport_StartDate,
       
            mtbv.t2_WTG_LifeDays
       
          WHERE
       
            YearMonth = 'TOTAL'
       
        )
       
        SELECT
       
          *
       
        FROM
       
          t_mtbv_YEARMONTH
       
        UNION
       
        SELECT
       
          *
       
        FROM
       
          t_mtbv_TOTAL
      Connection ID (thread ID): 656
      Status: NOT_KILLED
       
      Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
      
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              alice Alice Sherepa
              Reporter:
              Juan Juan Telleria
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: