Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.2.8
-
Windows 7 x64
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
- is duplicated by
-
MDEV-13796 CTE mysqld got signal 11
-
- Closed
-