WITH + UNION: Server Crashes

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;

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