[MDEV-9462] Out of memory using explain on 2 empty tables - Jira

Hung Tam Nguyen created issue - 2016-01-26 00:34

Elena Stepanova made changes - 2016-01-26 01:25

Field	Original Value	New Value
Status	Open [ 1 ]	Confirmed [ 10101 ]

Elena Stepanova added a comment - 2016-01-26 01:27 - edited

tnguyen,
thank you for the report and the test case.

Reproducible on current 5.5, 10.0, 10.1. The problem was introduced in 5.5.47 with the following revision:

commit 7e4da9b370d032db9015adb47ad2ff585aeaea5d

Author: Oleksandr Byelkin <sanja@mariadb.com>

Date:   Fri Nov 6 16:36:41 2015 +0100

    DEV-8632 Segmentation fault on INSERT

    View/derived fields should be taken into account when we build ref_pointer_array constructed.

    DBUG_ASSERTs added to avoid memory overrun.

MTR-like test case (same contents as in the attachment)
CREATE TABLE `UNPTEST` (
`REC_GROUP` char(2) DEFAULT NULL,
`CLIENT_INFO` text CHARACTER SET utf8,
`NAME` text,
`PHONE_NUMBER` text,
`ATTENTION_NAME` text,
`PAYMENT_TERM` text CHARACTER SET utf8,
`CREDIT_LIMIT` decimal(12,2) DEFAULT NULL,
`LAST_PAY_DATE` text CHARACTER SET utf8,
`TOTAL` double DEFAULT NULL,
`TOTAL_MCL` double DEFAULT NULL,
`TOTAL_MFS` double DEFAULT NULL,
`TOTAL_MIS` double DEFAULT NULL,
`BEFORE_DUE_7_MCL` double DEFAULT NULL,
`BEFORE_DUE_7_MFS` double DEFAULT NULL,
`BEFORE_DUE_7_MIS` double DEFAULT NULL,
`PER1_MCL` double DEFAULT NULL,
`PER1_MFS` double DEFAULT NULL,
`PER1_MIS` double DEFAULT NULL,
`PER2_MCL` double DEFAULT NULL,
`PER2_MFS` double DEFAULT NULL,
`PER2_MIS` double DEFAULT NULL,
`PER3_MCL` double DEFAULT NULL,
`PER3_MFS` double DEFAULT NULL,
`PER3_MIS` double DEFAULT NULL,
`PER4_MCL` double DEFAULT NULL,
`PER4_MFS` double DEFAULT NULL,
`PER4_MIS` double DEFAULT NULL,
`PER5_MCL` double DEFAULT NULL,
`PER5_MFS` double DEFAULT NULL,
`PER5_MIS` double DEFAULT NULL,
`PER6_MCL` double DEFAULT NULL,
`PER6_MFS` double DEFAULT NULL,
`PER6_MIS` double DEFAULT NULL,
`PER7_MCL` double DEFAULT NULL,
`PER7_MFS` double DEFAULT NULL,
`PER7_MIS` double DEFAULT NULL,
`BEFORE_DUE_7` double DEFAULT NULL,
`PER1` double DEFAULT NULL,
`PER2` double DEFAULT NULL,
`PER3` double DEFAULT NULL,
`PER4` double DEFAULT NULL,
`PER5` double DEFAULT NULL,
`PER6` double DEFAULT NULL,
`PER7` double DEFAULT NULL,
`REF` varchar(30) DEFAULT NULL,
`TYPE` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) DEFAULT CHARSET=latin1;

CREATE TABLE `UNPTEST1` (
`RECEIVABLE_GROUP` char(2) DEFAULT NULL,
`CLIENT_NUMBER` varchar(35) DEFAULT NULL,
`CLIENT_NAME` varchar(73) DEFAULT NULL,
`PHONE_NUMBER` char(12) DEFAULT NULL,
`ATTENTION_NAME` char(26) DEFAULT NULL,
`PAYMENT_TERM` varchar(26) CHARACTER SET utf8 DEFAULT NULL,
`CREDIT_LIMIT` decimal(12,2) DEFAULT NULL,
`LAST_PAY_DATE` varchar(42) CHARACTER SET utf8 DEFAULT NULL,
`TOTAL` decimal(12,2) DEFAULT NULL,
`BEFORE_DUE_7` decimal(12,2) DEFAULT NULL,
`PER1` decimal(12,2) DEFAULT NULL,
`PER2` decimal(12,2) DEFAULT NULL,
`PER3` decimal(12,2) DEFAULT NULL,
`PER4` decimal(12,2) DEFAULT NULL,
`PER5` decimal(12,2) DEFAULT NULL,
`PER6` decimal(12,2) DEFAULT NULL,
`PER7` decimal(12,2) DEFAULT NULL,
`DIVISION` varchar(3) CHARACTER SET utf8 NOT NULL,
`CLIENT_INFO` varchar(294) CHARACTER SET utf8 DEFAULT NULL,
`EXCHANGE_RATE` double NOT NULL,
`REF` varchar(30) DEFAULT NULL
) DEFAULT CHARSET=latin1;

SELECT A.RECEIVABLE_GROUP,A.CLIENT_INFO,A.CLIENT_NAME,A.PHONE_NUMBER,A.ATTENTION_NAME,A.PAYMENT_TERM,A.CREDIT_LIMIT,A.LAST_PAY_DATE,A.TOTAL,
COALESCE(B.TOTAL_MCL,0) AS TOTAL_MCL,
COALESCE(C.TOTAL_MFS,0) AS TOTAL_MFS,
COALESCE(D.TOTAL_MIS,0) AS TOTAL_MIS,
COALESCE(F.BEFORE_DUE_7_MCL,0) AS BEFORE_DUE_7_MCL,
COALESCE(G.BEFORE_DUE_7_MFS,0) AS BEFORE_DUE_7_MFS,
COALESCE(H.BEFORE_DUE_7_MIS,0) AS BEFORE_DUE_7_MIS,
COALESCE(I.PER1_MCL,0) AS PER1_MCL,
COALESCE(J.PER1_MFS,0) AS PER1_MFS,
COALESCE(K.PER1_MIS,0) AS PER1_MIS,
COALESCE(L.PER2_MCL,0) AS PER2_MCL,
COALESCE(M.PER2_MFS,0) AS PER2_MFS,
COALESCE(N.PER2_MIS,0) AS PER2_MIS,
COALESCE(O.PER3_MCL,0) AS PER3_MCL,
COALESCE(P.PER3_MFS,0) AS PER3_MFS,
COALESCE(R.PER3_MIS,0) AS PER3_MIS,
COALESCE(S.PER4_MCL,0) AS PER4_MCL,
COALESCE(T.PER4_MFS,0) AS PER4_MFS,
COALESCE(U.PER4_MIS,0) AS PER4_MIS,
COALESCE(V.PER5_MCL,0) AS PER5_MCL,
COALESCE(X.PER5_MFS,0) AS PER5_MFS,
COALESCE(Z.PER5_MIS,0) AS PER5_MIS,
COALESCE(Q.PER6_MCL,0) AS PER6_MCL,
COALESCE(Y.PER6_MFS,0) AS PER6_MFS,
COALESCE(W.PER6_MIS,0) AS PER6_MIS,
COALESCE(A1.PER7_MCL,0) AS PER7_MCL,
COALESCE(B1.PER7_MFS,0) AS PER7_MFS,
COALESCE(C1.PER7_MIS,0) AS PER7_MIS,
A.BEFORE_DUE_7,A.PER1,A.PER2,A.PER3,A.PER4,A.PER5,A.PER6,A.PER7,
CONCAT(A.DIVISION,'-',A.CLIENT_NUMBER) AS REF,"2" AS TYPE FROM
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,
GROUP_CONCAT(DISTINCT CLIENT_INFO SEPARATOR '<br>') AS CLIENT_INFO,
GROUP_CONCAT(DISTINCT CLIENT_NAME SEPARATOR '<br>') AS CLIENT_NAME,
GROUP_CONCAT( DISTINCT `PHONE_NUMBER` SEPARATOR '<br>' ) AS PHONE_NUMBER ,
GROUP_CONCAT( DISTINCT `ATTENTION_NAME` SEPARATOR '<br>' ) AS ATTENTION_NAME,
GROUP_CONCAT( DISTINCT `PAYMENT_TERM` SEPARATOR '<br>' ) AS PAYMENT_TERM,
CREDIT_LIMIT ,
GROUP_CONCAT( `LAST_PAY_DATE` SEPARATOR '<br>' ) AS LAST_PAY_DATE,
SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL,
SUM( `BEFORE_DUE_7`*EXCHANGE_RATE ) AS BEFORE_DUE_7,
SUM( `PER1`*EXCHANGE_RATE ) AS PER1,
SUM( `PER2`*EXCHANGE_RATE ) AS PER2,
SUM( `PER3`*EXCHANGE_RATE ) AS PER3,
SUM( `PER4`*EXCHANGE_RATE ) AS PER4,
SUM( `PER5`*EXCHANGE_RATE ) AS PER5,
SUM( `PER6`*EXCHANGE_RATE ) AS PER6,
SUM( `PER7`*EXCHANGE_RATE ) AS PER7
FROM `UNPTEST1`
WHERE REF IS NULL GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B ON A.CLIENT_NUMBER=B.CLIENT_NUMBER AND
A.DIVISION=B.DIVISION AND A.RECEIVABLE_GROUP=B.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C ON A.CLIENT_NUMBER=C.CLIENT_NUMBER
AND
A.DIVISION=C.DIVISION AND A.RECEIVABLE_GROUP=C.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS D ON A.CLIENT_NUMBER=D.CLIENT_NUMBER AND
A.DIVISION=D.DIVISION AND A.RECEIVABLE_GROUP=D.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=D.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS F ON A.CLIENT_NUMBER=F.CLIENT_NUMBER AND
A.DIVISION=F.DIVISION AND A.RECEIVABLE_GROUP=F.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=F.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS G ON A.CLIENT_NUMBER=G.CLIENT_NUMBER AND
A.DIVISION=G.DIVISION AND A.RECEIVABLE_GROUP=G.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=G.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS H ON A.CLIENT_NUMBER=H.CLIENT_NUMBER AND
A.DIVISION=H.DIVISION AND A.RECEIVABLE_GROUP=H.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=H.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS I ON A.CLIENT_NUMBER=I.CLIENT_NUMBER AND
A.DIVISION=I.DIVISION AND A.RECEIVABLE_GROUP=I.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=I.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS J ON A.CLIENT_NUMBER=J.CLIENT_NUMBER AND
A.DIVISION=J.DIVISION AND A.RECEIVABLE_GROUP=J.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=J.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS K ON A.CLIENT_NUMBER=K.CLIENT_NUMBER AND
A.DIVISION=K.DIVISION AND A.RECEIVABLE_GROUP=K.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=K.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS L ON A.CLIENT_NUMBER=L.CLIENT_NUMBER AND
A.DIVISION=L.DIVISION AND A.RECEIVABLE_GROUP=L.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=L.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS M ON A.CLIENT_NUMBER=M.CLIENT_NUMBER AND
A.DIVISION=M.DIVISION AND A.RECEIVABLE_GROUP=M.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=M.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS N ON A.CLIENT_NUMBER=N.CLIENT_NUMBER AND
A.DIVISION=N.DIVISION AND A.RECEIVABLE_GROUP=N.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=N.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS O ON A.CLIENT_NUMBER=O.CLIENT_NUMBER AND
A.DIVISION=O.DIVISION AND A.RECEIVABLE_GROUP=O.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=O.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS P ON A.CLIENT_NUMBER=P.CLIENT_NUMBER AND
A.DIVISION=P.DIVISION AND A.RECEIVABLE_GROUP=P.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=P.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS R ON A.CLIENT_NUMBER=R.CLIENT_NUMBER AND
A.DIVISION=R.DIVISION AND A.RECEIVABLE_GROUP=R.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=R.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS S ON A.CLIENT_NUMBER=S.CLIENT_NUMBER AND
A.DIVISION=S.DIVISION AND A.RECEIVABLE_GROUP=S.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=S.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS T ON A.CLIENT_NUMBER=T.CLIENT_NUMBER AND
A.DIVISION=T.DIVISION AND A.RECEIVABLE_GROUP=T.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=T.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS U ON A.CLIENT_NUMBER=U.CLIENT_NUMBER AND
A.DIVISION=U.DIVISION AND A.RECEIVABLE_GROUP=U.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=U.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS V ON A.CLIENT_NUMBER=V.CLIENT_NUMBER AND
A.DIVISION=V.DIVISION AND A.RECEIVABLE_GROUP=V.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=V.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS X ON A.CLIENT_NUMBER=X.CLIENT_NUMBER AND
A.DIVISION=X.DIVISION AND A.RECEIVABLE_GROUP=X.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=X.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Z ON A.CLIENT_NUMBER=Z.CLIENT_NUMBER AND
A.DIVISION=Z.DIVISION AND A.RECEIVABLE_GROUP=Z.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Z.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Q ON A.CLIENT_NUMBER=Q.CLIENT_NUMBER AND
A.DIVISION=Q.DIVISION AND A.RECEIVABLE_GROUP=Q.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Q.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Y ON A.CLIENT_NUMBER=Y.CLIENT_NUMBER AND
A.DIVISION=Y.DIVISION AND A.RECEIVABLE_GROUP=Y.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Y.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS W ON A.CLIENT_NUMBER=W.CLIENT_NUMBER AND
A.DIVISION=W.DIVISION AND A.RECEIVABLE_GROUP=W.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=W.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MCL
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A1 ON A.CLIENT_NUMBER=A1.CLIENT_NUMBER AND
A.DIVISION=A1.DIVISION AND A.RECEIVABLE_GROUP=A1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=A1.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MFS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B1 ON A.CLIENT_NUMBER=B1.CLIENT_NUMBER AND
A.DIVISION=B1.DIVISION AND A.RECEIVABLE_GROUP=B1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B1.CREDIT_LIMIT
LEFT JOIN
(SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MIS
FROM `UNPTEST1`
WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C1 ON A.CLIENT_NUMBER=C1.CLIENT_NUMBER AND
A.DIVISION=C1.DIVISION AND A.RECEIVABLE_GROUP=C1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C1.CREDIT_LIMIT
ORDER BY TOTAL DESC;

DROP TABLE UNPTEST, UNPTEST1;

Elena Stepanova added a comment - 2016-01-26 01:27 - edited tnguyen , thank you for the report and the test case. Reproducible on current 5.5, 10.0, 10.1. The problem was introduced in 5.5.47 with the following revision: commit 7e4da9b370d032db9015adb47ad2ff585aeaea5d Author: Oleksandr Byelkin <sanja@mariadb.com> Date: Fri Nov 6 16:36:41 2015 +0100 DEV-8632 Segmentation fault on INSERT View/derived fields should be taken into account when we build ref_pointer_array constructed. DBUG_ASSERTs added to avoid memory overrun. MTR-like test case (same contents as in the attachment) CREATE TABLE `UNPTEST` ( `REC_GROUP` char(2) DEFAULT NULL, `CLIENT_INFO` text CHARACTER SET utf8, `NAME` text, `PHONE_NUMBER` text, `ATTENTION_NAME` text, `PAYMENT_TERM` text CHARACTER SET utf8, `CREDIT_LIMIT` decimal(12,2) DEFAULT NULL, `LAST_PAY_DATE` text CHARACTER SET utf8, `TOTAL` double DEFAULT NULL, `TOTAL_MCL` double DEFAULT NULL, `TOTAL_MFS` double DEFAULT NULL, `TOTAL_MIS` double DEFAULT NULL, `BEFORE_DUE_7_MCL` double DEFAULT NULL, `BEFORE_DUE_7_MFS` double DEFAULT NULL, `BEFORE_DUE_7_MIS` double DEFAULT NULL, `PER1_MCL` double DEFAULT NULL, `PER1_MFS` double DEFAULT NULL, `PER1_MIS` double DEFAULT NULL, `PER2_MCL` double DEFAULT NULL, `PER2_MFS` double DEFAULT NULL, `PER2_MIS` double DEFAULT NULL, `PER3_MCL` double DEFAULT NULL, `PER3_MFS` double DEFAULT NULL, `PER3_MIS` double DEFAULT NULL, `PER4_MCL` double DEFAULT NULL, `PER4_MFS` double DEFAULT NULL, `PER4_MIS` double DEFAULT NULL, `PER5_MCL` double DEFAULT NULL, `PER5_MFS` double DEFAULT NULL, `PER5_MIS` double DEFAULT NULL, `PER6_MCL` double DEFAULT NULL, `PER6_MFS` double DEFAULT NULL, `PER6_MIS` double DEFAULT NULL, `PER7_MCL` double DEFAULT NULL, `PER7_MFS` double DEFAULT NULL, `PER7_MIS` double DEFAULT NULL, `BEFORE_DUE_7` double DEFAULT NULL, `PER1` double DEFAULT NULL, `PER2` double DEFAULT NULL, `PER3` double DEFAULT NULL, `PER4` double DEFAULT NULL, `PER5` double DEFAULT NULL, `PER6` double DEFAULT NULL, `PER7` double DEFAULT NULL, `REF` varchar(30) DEFAULT NULL, `TYPE` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL ) DEFAULT CHARSET=latin1; CREATE TABLE `UNPTEST1` ( `RECEIVABLE_GROUP` char(2) DEFAULT NULL, `CLIENT_NUMBER` varchar(35) DEFAULT NULL, `CLIENT_NAME` varchar(73) DEFAULT NULL, `PHONE_NUMBER` char(12) DEFAULT NULL, `ATTENTION_NAME` char(26) DEFAULT NULL, `PAYMENT_TERM` varchar(26) CHARACTER SET utf8 DEFAULT NULL, `CREDIT_LIMIT` decimal(12,2) DEFAULT NULL, `LAST_PAY_DATE` varchar(42) CHARACTER SET utf8 DEFAULT NULL, `TOTAL` decimal(12,2) DEFAULT NULL, `BEFORE_DUE_7` decimal(12,2) DEFAULT NULL, `PER1` decimal(12,2) DEFAULT NULL, `PER2` decimal(12,2) DEFAULT NULL, `PER3` decimal(12,2) DEFAULT NULL, `PER4` decimal(12,2) DEFAULT NULL, `PER5` decimal(12,2) DEFAULT NULL, `PER6` decimal(12,2) DEFAULT NULL, `PER7` decimal(12,2) DEFAULT NULL, `DIVISION` varchar(3) CHARACTER SET utf8 NOT NULL, `CLIENT_INFO` varchar(294) CHARACTER SET utf8 DEFAULT NULL, `EXCHANGE_RATE` double NOT NULL, `REF` varchar(30) DEFAULT NULL ) DEFAULT CHARSET=latin1; SELECT A.RECEIVABLE_GROUP,A.CLIENT_INFO,A.CLIENT_NAME,A.PHONE_NUMBER,A.ATTENTION_NAME,A.PAYMENT_TERM,A.CREDIT_LIMIT,A.LAST_PAY_DATE,A.TOTAL, COALESCE(B.TOTAL_MCL,0) AS TOTAL_MCL, COALESCE(C.TOTAL_MFS,0) AS TOTAL_MFS, COALESCE(D.TOTAL_MIS,0) AS TOTAL_MIS, COALESCE(F.BEFORE_DUE_7_MCL,0) AS BEFORE_DUE_7_MCL, COALESCE(G.BEFORE_DUE_7_MFS,0) AS BEFORE_DUE_7_MFS, COALESCE(H.BEFORE_DUE_7_MIS,0) AS BEFORE_DUE_7_MIS, COALESCE(I.PER1_MCL,0) AS PER1_MCL, COALESCE(J.PER1_MFS,0) AS PER1_MFS, COALESCE(K.PER1_MIS,0) AS PER1_MIS, COALESCE(L.PER2_MCL,0) AS PER2_MCL, COALESCE(M.PER2_MFS,0) AS PER2_MFS, COALESCE(N.PER2_MIS,0) AS PER2_MIS, COALESCE(O.PER3_MCL,0) AS PER3_MCL, COALESCE(P.PER3_MFS,0) AS PER3_MFS, COALESCE(R.PER3_MIS,0) AS PER3_MIS, COALESCE(S.PER4_MCL,0) AS PER4_MCL, COALESCE(T.PER4_MFS,0) AS PER4_MFS, COALESCE(U.PER4_MIS,0) AS PER4_MIS, COALESCE(V.PER5_MCL,0) AS PER5_MCL, COALESCE(X.PER5_MFS,0) AS PER5_MFS, COALESCE(Z.PER5_MIS,0) AS PER5_MIS, COALESCE(Q.PER6_MCL,0) AS PER6_MCL, COALESCE(Y.PER6_MFS,0) AS PER6_MFS, COALESCE(W.PER6_MIS,0) AS PER6_MIS, COALESCE(A1.PER7_MCL,0) AS PER7_MCL, COALESCE(B1.PER7_MFS,0) AS PER7_MFS, COALESCE(C1.PER7_MIS,0) AS PER7_MIS, A.BEFORE_DUE_7,A.PER1,A.PER2,A.PER3,A.PER4,A.PER5,A.PER6,A.PER7, CONCAT(A.DIVISION,'-',A.CLIENT_NUMBER) AS REF,"2" AS TYPE FROM (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER, GROUP_CONCAT(DISTINCT CLIENT_INFO SEPARATOR ' ') AS CLIENT_INFO, GROUP_CONCAT(DISTINCT CLIENT_NAME SEPARATOR ' ') AS CLIENT_NAME, GROUP_CONCAT( DISTINCT `PHONE_NUMBER` SEPARATOR ' ' ) AS PHONE_NUMBER , GROUP_CONCAT( DISTINCT `ATTENTION_NAME` SEPARATOR ' ' ) AS ATTENTION_NAME, GROUP_CONCAT( DISTINCT `PAYMENT_TERM` SEPARATOR ' ' ) AS PAYMENT_TERM, CREDIT_LIMIT , GROUP_CONCAT( `LAST_PAY_DATE` SEPARATOR ' ' ) AS LAST_PAY_DATE, SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL, SUM( `BEFORE_DUE_7`*EXCHANGE_RATE ) AS BEFORE_DUE_7, SUM( `PER1`*EXCHANGE_RATE ) AS PER1, SUM( `PER2`*EXCHANGE_RATE ) AS PER2, SUM( `PER3`*EXCHANGE_RATE ) AS PER3, SUM( `PER4`*EXCHANGE_RATE ) AS PER4, SUM( `PER5`*EXCHANGE_RATE ) AS PER5, SUM( `PER6`*EXCHANGE_RATE ) AS PER6, SUM( `PER7`*EXCHANGE_RATE ) AS PER7 FROM `UNPTEST1` WHERE REF IS NULL GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MCL FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B ON A.CLIENT_NUMBER=B.CLIENT_NUMBER AND A.DIVISION=B.DIVISION AND A.RECEIVABLE_GROUP=B.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MFS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C ON A.CLIENT_NUMBER=C.CLIENT_NUMBER AND A.DIVISION=C.DIVISION AND A.RECEIVABLE_GROUP=C.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( `TOTAL`*EXCHANGE_RATE ) AS TOTAL_MIS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS D ON A.CLIENT_NUMBER=D.CLIENT_NUMBER AND A.DIVISION=D.DIVISION AND A.RECEIVABLE_GROUP=D.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=D.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MCL FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS F ON A.CLIENT_NUMBER=F.CLIENT_NUMBER AND A.DIVISION=F.DIVISION AND A.RECEIVABLE_GROUP=F.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=F.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MFS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS G ON A.CLIENT_NUMBER=G.CLIENT_NUMBER AND A.DIVISION=G.DIVISION AND A.RECEIVABLE_GROUP=G.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=G.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( BEFORE_DUE_7*EXCHANGE_RATE ) AS BEFORE_DUE_7_MIS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS H ON A.CLIENT_NUMBER=H.CLIENT_NUMBER AND A.DIVISION=H.DIVISION AND A.RECEIVABLE_GROUP=H.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=H.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MCL FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS I ON A.CLIENT_NUMBER=I.CLIENT_NUMBER AND A.DIVISION=I.DIVISION AND A.RECEIVABLE_GROUP=I.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=I.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MFS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS J ON A.CLIENT_NUMBER=J.CLIENT_NUMBER AND A.DIVISION=J.DIVISION AND A.RECEIVABLE_GROUP=J.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=J.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER1*EXCHANGE_RATE ) AS PER1_MIS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS K ON A.CLIENT_NUMBER=K.CLIENT_NUMBER AND A.DIVISION=K.DIVISION AND A.RECEIVABLE_GROUP=K.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=K.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MCL FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS L ON A.CLIENT_NUMBER=L.CLIENT_NUMBER AND A.DIVISION=L.DIVISION AND A.RECEIVABLE_GROUP=L.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=L.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MFS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS M ON A.CLIENT_NUMBER=M.CLIENT_NUMBER AND A.DIVISION=M.DIVISION AND A.RECEIVABLE_GROUP=M.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=M.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER2*EXCHANGE_RATE ) AS PER2_MIS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS N ON A.CLIENT_NUMBER=N.CLIENT_NUMBER AND A.DIVISION=N.DIVISION AND A.RECEIVABLE_GROUP=N.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=N.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MCL FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS O ON A.CLIENT_NUMBER=O.CLIENT_NUMBER AND A.DIVISION=O.DIVISION AND A.RECEIVABLE_GROUP=O.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=O.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MFS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS P ON A.CLIENT_NUMBER=P.CLIENT_NUMBER AND A.DIVISION=P.DIVISION AND A.RECEIVABLE_GROUP=P.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=P.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER3*EXCHANGE_RATE ) AS PER3_MIS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS R ON A.CLIENT_NUMBER=R.CLIENT_NUMBER AND A.DIVISION=R.DIVISION AND A.RECEIVABLE_GROUP=R.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=R.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MCL FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS S ON A.CLIENT_NUMBER=S.CLIENT_NUMBER AND A.DIVISION=S.DIVISION AND A.RECEIVABLE_GROUP=S.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=S.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MFS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS T ON A.CLIENT_NUMBER=T.CLIENT_NUMBER AND A.DIVISION=T.DIVISION AND A.RECEIVABLE_GROUP=T.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=T.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER4*EXCHANGE_RATE ) AS PER4_MIS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS U ON A.CLIENT_NUMBER=U.CLIENT_NUMBER AND A.DIVISION=U.DIVISION AND A.RECEIVABLE_GROUP=U.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=U.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MCL FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS V ON A.CLIENT_NUMBER=V.CLIENT_NUMBER AND A.DIVISION=V.DIVISION AND A.RECEIVABLE_GROUP=V.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=V.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MFS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS X ON A.CLIENT_NUMBER=X.CLIENT_NUMBER AND A.DIVISION=X.DIVISION AND A.RECEIVABLE_GROUP=X.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=X.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER5*EXCHANGE_RATE ) AS PER5_MIS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Z ON A.CLIENT_NUMBER=Z.CLIENT_NUMBER AND A.DIVISION=Z.DIVISION AND A.RECEIVABLE_GROUP=Z.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Z.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MCL FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Q ON A.CLIENT_NUMBER=Q.CLIENT_NUMBER AND A.DIVISION=Q.DIVISION AND A.RECEIVABLE_GROUP=Q.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Q.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MFS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS Y ON A.CLIENT_NUMBER=Y.CLIENT_NUMBER AND A.DIVISION=Y.DIVISION AND A.RECEIVABLE_GROUP=Y.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=Y.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER6*EXCHANGE_RATE ) AS PER6_MIS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS W ON A.CLIENT_NUMBER=W.CLIENT_NUMBER AND A.DIVISION=W.DIVISION AND A.RECEIVABLE_GROUP=W.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=W.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MCL FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MCL" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS A1 ON A.CLIENT_NUMBER=A1.CLIENT_NUMBER AND A.DIVISION=A1.DIVISION AND A.RECEIVABLE_GROUP=A1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=A1.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MFS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MFS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS B1 ON A.CLIENT_NUMBER=B1.CLIENT_NUMBER AND A.DIVISION=B1.DIVISION AND A.RECEIVABLE_GROUP=B1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=B1.CREDIT_LIMIT LEFT JOIN (SELECT RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT,SUM( PER7*EXCHANGE_RATE ) AS PER7_MIS FROM `UNPTEST1` WHERE REF IS NULL AND DIVISION="MIS" GROUP BY RECEIVABLE_GROUP,DIVISION,CLIENT_NUMBER,CREDIT_LIMIT) AS C1 ON A.CLIENT_NUMBER=C1.CLIENT_NUMBER AND A.DIVISION=C1.DIVISION AND A.RECEIVABLE_GROUP=C1.RECEIVABLE_GROUP AND A.CREDIT_LIMIT=C1.CREDIT_LIMIT ORDER BY TOTAL DESC; DROP TABLE UNPTEST, UNPTEST1;

Elena Stepanova made changes - 2016-01-26 01:27

Fix Version/s		5.5 [ 15800 ]
Fix Version/s		10.0 [ 16000 ]
Fix Version/s		10.1 [ 16100 ]
Affects Version/s		5.5.47 [ 20300 ]
Affects Version/s		5.5 [ 15800 ]
Affects Version/s		10.0 [ 16000 ]
Affects Version/s		10.1 [ 16100 ]
Assignee		Oleksandr Byelkin [ sanja ]

Hung Tam Nguyen added a comment - 2016-01-26 18:36 - edited

Thanks for your prompt reply and bug confirmation. Note that I have a pretty basic installation of MariaDB 10.0.21 which is not affected by this bug for some reason:

Welcome to the MariaDB monitor.  Commands end with ; or \g.

Your MariaDB connection id is 219

Server version: 10.0.21-MariaDB-1~trusty-log mariadb.org binary distribution

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use sandbox2;

Database changed

MariaDB [sandbox2]> \. /home/tnguyen/UNPTEST.sql

Query OK, 0 rows affected (0.02 sec)

Query OK, 0 rows affected (0.03 sec)

+------+-------------+-------------+------+---------------+------+---------+--------------------------------------------------------------+------+----------------------------------------------+

| id   | select_type | table       | type | possible_keys | key  | key_len | ref                                                          | rows | Extra                                        |

+------+-------------+-------------+------+---------------+------+---------+--------------------------------------------------------------+------+----------------------------------------------+

|    1 | PRIMARY     | <derived2>  | ALL  | NULL          | NULL | NULL    | NULL                                                         |    2 | Using filesort                               |

|    1 | PRIMARY     | <derived3>  | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived4>  | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived5>  | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived6>  | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived7>  | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived8>  | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived9>  | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived10> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived11> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived12> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived13> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived14> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived15> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived16> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived17> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived18> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived19> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived20> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived21> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived22> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived23> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived24> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived25> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived26> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived27> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived28> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|    1 | PRIMARY     | <derived29> | ref  | key0          | key0 | 60      | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT |    2 | Using where                                  |

|   29 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   28 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   27 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   26 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   25 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   24 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   23 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   22 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   21 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   20 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   19 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   18 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   17 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   16 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   15 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   14 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   13 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   12 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   11 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|   10 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|    9 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|    8 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|    7 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|    6 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|    5 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|    4 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|    3 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using temporary; Using filesort |

|    2 | DERIVED     | UNPTEST1    | ALL  | NULL          | NULL | NULL    | NULL                                                         |    1 | Using where; Using filesort                  |

+------+-------------+-------------+------+---------------+------+---------+--------------------------------------------------------------+------+----------------------------------------------+

56 rows in set (0.01 sec)

Hung Tam Nguyen added a comment - 2016-01-26 18:36 - edited Thanks for your prompt reply and bug confirmation. Note that I have a pretty basic installation of MariaDB 10.0.21 which is not affected by this bug for some reason: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 219 Server version: 10.0.21-MariaDB-1~trusty-log mariadb.org binary distribution Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> use sandbox2; Database changed MariaDB [sandbox2]> \. /home/tnguyen/UNPTEST.sql Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.03 sec) +------+-------------+-------------+------+---------------+------+---------+--------------------------------------------------------------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------------+------+---------------+------+---------+--------------------------------------------------------------+------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using filesort | | 1 | PRIMARY | <derived3> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived4> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived5> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived6> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived7> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived8> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived9> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived10> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived11> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived12> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived13> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived14> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived15> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived16> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived17> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived18> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived19> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived20> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived21> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived22> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived23> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived24> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived25> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived26> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived27> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived28> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 1 | PRIMARY | <derived29> | ref | key0 | key0 | 60 | A.RECEIVABLE_GROUP,A.DIVISION,A.CLIENT_NUMBER,A.CREDIT_LIMIT | 2 | Using where | | 29 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 28 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 27 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 26 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 25 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 24 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 23 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 22 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 21 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 20 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 19 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 18 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 17 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 16 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 15 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 14 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 13 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 12 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 11 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 10 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 9 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 8 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 7 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 6 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 5 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 4 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 3 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using temporary; Using filesort | | 2 | DERIVED | UNPTEST1 | ALL | NULL | NULL | NULL | NULL | 1 | Using where; Using filesort | +------+-------------+-------------+------+---------------+------+---------+--------------------------------------------------------------+------+----------------------------------------------+ 56 rows in set (0.01 sec)

Elena Stepanova added a comment - 2016-01-26 18:42

Note that I have a pretty basic installation of MariaDB 10.0.21 which is not affected by this bug for some reason

This is because the mentioned revision, from which the problem started, was a part of 10.0.23. Previous releases are not affected.

Elena Stepanova added a comment - 2016-01-26 18:42 Note that I have a pretty basic installation of MariaDB 10.0.21 which is not affected by this bug for some reason This is because the mentioned revision, from which the problem started, was a part of 10.0.23. Previous releases are not affected.

Elena Stepanova made changes - 2016-01-26 18:42

Labels

optimizer

optimizer regression

Hung Tam Nguyen added a comment - 2016-01-26 18:45

Thank you!

Hung Tam Nguyen added a comment - 2016-01-26 18:45 Thank you!

Rasmus Johansson (Inactive) made changes - 2016-02-03 13:58

Sprint

5.5.48-0 [ 33 ]

Rasmus Johansson (Inactive) made changes - 2016-02-03 13:58

Rank

Ranked higher

Oleksandr Byelkin made changes - 2016-02-03 16:18

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Oleksandr Byelkin added a comment - 2016-02-03 17:25 - edited

in sql_yacc.yy:

              if (!sel->next_select())

                $2->select_n_where_fields+=

                  sel->select_n_where_fields;

actually add items of the same SELECT, that is an error.

Oleksandr Byelkin added a comment - 2016-02-03 17:25 - edited in sql_yacc.yy: if (!sel->next_select()) $2->select_n_where_fields+= sel->select_n_where_fields; actually add items of the same SELECT, that is an error.

Oleksandr Byelkin added a comment - 2016-02-03 18:17

revision-id: d8c50ced2bd14fcec3378fa5a6c3032d3b099ce7 (mariadb-5.5.47-26-gd8c50ce)
parent(s): f66d01610f713a76f38de650464bfaea476b9f32
committer: Oleksandr Byelkin
timestamp: 2016-02-03 17:15:22 +0100
message:

~~MDEV-9462~~: Out of memory using explain on 2 empty tables

Fixed adding derived tables items to outer one.

—

Oleksandr Byelkin added a comment - 2016-02-03 18:17 revision-id: d8c50ced2bd14fcec3378fa5a6c3032d3b099ce7 (mariadb-5.5.47-26-gd8c50ce) parent(s): f66d01610f713a76f38de650464bfaea476b9f32 committer: Oleksandr Byelkin timestamp: 2016-02-03 17:15:22 +0100 message: MDEV-9462 : Out of memory using explain on 2 empty tables Fixed adding derived tables items to outer one. —

Oleksandr Byelkin made changes - 2016-02-03 18:35

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Sergei Golubchik made changes - 2016-02-04 17:06

Description

Tested on MySQL 5.5.29 as well as MariaDB 10.0.21, bug not present. The explain returns the desired result (56 rows).

The bug is easily reproducible on MariaDB 10.1 by running the attached explain_bug.sql script on any test database. All it does is create 2 empty tables and running an explain.

The result is
ERROR 5 (HY000) at line 79 in file: '/home/tnguyen/explailn_bug.sql': Out of memory (Needed 5784 bytes)

MySQL error log:
2016-01-25 16:47:45 139975536690944 [ERROR] mysqld: Out of memory (Needed 5784 bytes)
2016-01-25 16:47:45 139975536690944 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

ulimit -a:
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 47584
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 47584
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

syslog:
nothing

This bug is causing previously working ETL code to fail after a database upgrade, even though we don't have any real memory issue as it was working fine on older db versions.

Also attached the same my.cnf that I used to test on 10.0.21 and 10.1.9 & 10.1.10.

Tested on MySQL 5.5.29 as well as MariaDB 10.0.21, bug not present. The explain returns the desired result (56 rows).

The bug is easily reproducible on MariaDB 10.1 by running the attached explain_bug.sql script on any test database. All it does is create 2 empty tables and running an explain.

The result is
{noformat}
ERROR 5 (HY000) at line 79 in file: '/home/tnguyen/explailn_bug.sql': Out of memory (Needed 5784 bytes)
{noformat}
MySQL error log:
{noformat}
2016-01-25 16:47:45 139975536690944 [ERROR] mysqld: Out of memory (Needed 5784 bytes)
2016-01-25 16:47:45 139975536690944 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
{noformat}
ulimit -a:
{noformat}
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 47584
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 8192
cpu time (seconds, -t) unlimited
max user processes (-u) 47584
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited
{noformat}

syslog:
nothing

This bug is causing previously working ETL code to fail after a database upgrade, even though we don't have any real memory issue as it was working fine on older db versions.

Also attached the same my.cnf that I used to test on 10.0.21 and 10.1.9 & 10.1.10.

Sergei Golubchik made changes - 2016-02-05 18:01

Assignee

Oleksandr Byelkin [ sanja ]

Sergei Golubchik [ serg ]

Sergei Golubchik made changes - 2016-02-05 18:01

Status

Stalled [ 10000 ]

In Review [ 10002 ]

Oleksandr Byelkin added a comment - 2016-02-06 12:13

OOM happened because instead of adding number of items from derived table it doubled number of items in main SELECT for each derived table (wrong SELECT_LEX was taken).

Oleksandr Byelkin added a comment - 2016-02-06 12:13 OOM happened because instead of adding number of items from derived table it doubled number of items in main SELECT for each derived table (wrong SELECT_LEX was taken).

Sergei Golubchik added a comment - 2016-02-06 13:45

ok to push

Sergei Golubchik added a comment - 2016-02-06 13:45 ok to push

Sergei Golubchik made changes - 2016-02-06 13:45

Assignee	Sergei Golubchik [ serg ]	Oleksandr Byelkin [ sanja ]
Status	In Review [ 10002 ]	Stalled [ 10000 ]

Oleksandr Byelkin made changes - 2016-02-06 14:38

Fix Version/s		5.5.48 [ 21000 ]
Fix Version/s		10.0.24 [ 21101 ]
Fix Version/s		10.1.12 [ 21502 ]
Fix Version/s	10.0 [ 16000 ]
Fix Version/s	5.5 [ 15800 ]
Fix Version/s	10.1 [ 16100 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2021-12-06 21:42

Workflow

MariaDB v3 [ 73801 ]

MariaDB v4 [ 150032 ]

MariaDB Server

Out of memory using explain on 2 empty tables

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration