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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5.47, 10.1.9, 10.1.10, 5.5(EOL), 10.0(EOL), 10.1(EOL)
Fix Version/s: 5.5.48, 10.0.24, 10.1.12
Component/s: Optimizer
Labels:
- optimizer
- regression
Environment:
Ubuntu 14.04.3 LTS, VM with 10GB RAM

Sprint:
5.5.48-0

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.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

explain_bug.sql
15 kB
2016-01-26 00:22
my.cnf
5 kB
2016-01-26 00:32

Activity

Ascending order - Click to sort in descending order

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;

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.

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!

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 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

People

Assignee:: Oleksandr Byelkin

Reporter:: Hung Tam Nguyen

Votes:: 0 Vote for this issue

Watchers:: 4 Start watching this issue

Dates

Created:: 2016-01-26 00:34

Updated:: 2016-02-06 14:38

Resolved:: 2016-02-06 14:38

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration