diff options
Diffstat (limited to 'mysql-test/r/derived.result')
| -rw-r--r-- | mysql-test/r/derived.result | 345 |
1 files changed, 345 insertions, 0 deletions
diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result index a59f8a6dfee..1d643333424 100644 --- a/mysql-test/r/derived.result +++ b/mysql-test/r/derived.result @@ -603,6 +603,351 @@ select x.id, message from (select id from t1) x left join where coalesce(message,0) <> 0; id message drop table t1,t2; +# +# MDEV-7827: Assertion `!table || (!table->read_set || +# bitmap_is_set(table->read_set, field_index))' failed +# in Field_long::val_str on EXPLAIN EXTENDED +# +CREATE TABLE t1 (f1 INT, f2 INT, KEY(f2)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (6,9); +CREATE TABLE t2 (f3 INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (2),(0); +EXPLAIN EXTENDED +SELECT f1 FROM ( SELECT * FROM t1 ) AS sq +WHERE f1 IN ( +SELECT f3 FROM t2 WHERE f2 IN ( +SELECT f3 FROM t2 HAVING f3 >= 8 +) +); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 1 100.00 +1 PRIMARY <subquery4> eq_ref distinct_key distinct_key 4 sq.f2 1 100.00 +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(<subquery4>); Using join buffer (flat, BNL join) +4 MATERIALIZED t2 ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED t1 system NULL NULL NULL NULL 1 100.00 +Warnings: +Note 1276 Field or reference 'sq.f2' of SELECT #3 was resolved in SELECT #1 +Note 1003 select 6 AS `f1` from <materialize> (select `test`.`t2`.`f3` from `test`.`t2` having (`test`.`t2`.`f3` >= 8)) semi join (`test`.`t2`) where ((`test`.`t2`.`f3` = 6) and (9 = `<subquery4>`.`f3`)) +DROP TABLE t2,t1; +# +# MDEV-9462: Out of memory using explain on 2 empty tables +# +CREATE TABLE `t1` ( +`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 +); +CREATE TABLE `t2` ( +`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 +); +explain +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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 `t2` +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; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived3> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived4> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived5> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived6> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived7> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived8> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived9> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived10> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived11> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived12> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived13> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived14> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived15> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived16> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived17> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived18> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived19> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived20> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived21> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived22> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived23> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived24> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived25> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived26> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived27> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived28> system NULL NULL NULL NULL 0 const row not found +1 PRIMARY <derived29> system NULL NULL NULL NULL 0 const row not found +29 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +28 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +27 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +26 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +25 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +24 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +23 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +22 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +21 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +20 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +19 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +18 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +17 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +16 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +15 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +14 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +13 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +12 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +11 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +10 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +9 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +8 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +7 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +5 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +4 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +3 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +2 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table +DROP TABLES t1,t2; set optimizer_switch=@save_derived_optimizer_switch; # # Start of 10.1 tests |