|
CREATE TABLE `orders` (
`hjmpTS` bigint(20) DEFAULT NULL,
`createdTS` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
`modifiedTS` datetime(6) DEFAULT NULL,
`TypePkString` bigint(20) DEFAULT NULL,
`OwnerPkString` bigint(20) DEFAULT NULL,
`PK` bigint(20) NOT NULL DEFAULT 0,
`calculatedflag` tinyint(1) DEFAULT NULL,
`code` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`currencypk` bigint(20) DEFAULT NULL,
`deliveryaddresspk` bigint(20) DEFAULT NULL,
`deliverycost` decimal(30,8) DEFAULT NULL,
`deliverymodepk` bigint(20) DEFAULT NULL,
`deliverystatuspk` bigint(20) DEFAULT NULL,
`globaldiscountvalues` text COLLATE utf8_bin DEFAULT NULL,
`netflag` tinyint(1) DEFAULT NULL,
`paymentaddresspk` bigint(20) DEFAULT NULL,
`paymentcost` decimal(30,8) DEFAULT NULL,
`paymentinfopk` bigint(20) DEFAULT NULL,
`paymentmodepk` bigint(20) DEFAULT NULL,
`paymentstatuspk` bigint(20) DEFAULT NULL,
`statuspk` bigint(20) DEFAULT NULL,
`p_exportstatus` bigint(20) DEFAULT NULL,
`statusinfo` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`totalprice` decimal(30,8) DEFAULT NULL,
`totaldiscounts` decimal(30,8) DEFAULT NULL,
`totaltax` decimal(30,8) DEFAULT NULL,
`totaltaxvalues` text COLLATE utf8_bin DEFAULT NULL,
`userpk` bigint(20) DEFAULT NULL,
`subtotal` decimal(30,8) DEFAULT NULL,
`discountondelivery` tinyint(1) DEFAULT NULL,
`discountonpayment` tinyint(1) DEFAULT NULL,
`p_previousdeliverymode` bigint(20) DEFAULT NULL,
`p_site` bigint(20) DEFAULT NULL,
`p_store` bigint(20) DEFAULT NULL,
`p_guid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_yoyosum` bigint(20) DEFAULT NULL,
`p_transportcompany` bigint(20) DEFAULT NULL,
`p_freevouchers` longblob DEFAULT NULL,
`p_pointofservice` bigint(20) DEFAULT NULL,
`p_deliveryzonelist` text COLLATE utf8_bin DEFAULT NULL,
`p_tariffzonelist` text COLLATE utf8_bin DEFAULT NULL,
`p_ordersubstatus` bigint(20) DEFAULT NULL,
`p_deletelines` longblob DEFAULT NULL,
`p_weight` decimal(30,8) DEFAULT NULL,
`p_volume` decimal(30,8) DEFAULT NULL,
`p_weightbulky` decimal(30,8) DEFAULT NULL,
`p_volumebulky` decimal(30,8) DEFAULT NULL,
`p_paywithgift` tinyint(1) DEFAULT NULL,
`p_paywithyoyo` tinyint(1) DEFAULT NULL,
`p_deletedfreegoodsmap` longblob DEFAULT NULL,
`p_versionid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_originalversion` bigint(20) DEFAULT NULL,
`p_fraudulent` tinyint(1) DEFAULT NULL,
`p_potentiallyfraudulent` tinyint(1) DEFAULT NULL,
`p_salesapplication` bigint(20) DEFAULT NULL,
`p_language` bigint(20) DEFAULT NULL,
`p_deliverydate` datetime(6) DEFAULT NULL,
`p_deliverytime` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_shippingdate` datetime(6) DEFAULT NULL,
`p_informingmethod` bigint(20) DEFAULT NULL,
`p_requesteddeliverydate` datetime(6) DEFAULT NULL,
`p_sendsmswhenstatuschanged` tinyint(1) DEFAULT NULL,
`p_sapapprovedate` datetime(6) DEFAULT NULL,
`p_sapcalculatedate` datetime(6) DEFAULT NULL,
`p_sapreservedate` datetime(6) DEFAULT NULL,
`p_ordertype` bigint(20) DEFAULT NULL,
`p_promotionsdate` datetime(6) DEFAULT NULL,
`p_operator` bigint(20) DEFAULT NULL,
`p_buyerpaymentresulterror` tinyint(1) DEFAULT NULL,
`p_parentorder` bigint(20) DEFAULT NULL,
`p_cslockdate` datetime(6) DEFAULT NULL,
`p_cslockoperator` bigint(20) DEFAULT NULL,
`p_ismigrated` tinyint(1) DEFAULT NULL,
`p_partlypaidsum` decimal(30,8) DEFAULT NULL,
`p_deliveryinterval` bigint(20) DEFAULT NULL,
`p_refunddate` datetime(6) DEFAULT NULL,
`p_yoyoawardedpoints` decimal(30,8) DEFAULT NULL,
`p_yoyodebitedpoints` decimal(30,8) DEFAULT NULL,
`p_needrefundyoyo` tinyint(1) DEFAULT NULL,
`p_giftusedforyoyoaddpoints` decimal(30,8) DEFAULT NULL,
`p_processedtime` datetime(6) DEFAULT NULL,
`aCLTS` bigint(20) DEFAULT 0,
`propTS` bigint(20) DEFAULT 0,
`p_fulfilmentstatus` bigint(20) DEFAULT NULL,
`p_notes` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_computerid` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`p_ordercreator` bigint(20) DEFAULT NULL,
`p_fiascode` varchar(255) COLLATE utf8_bin DEFAULT NULL,
`originaldeliverycost` decimal(30,8) DEFAULT NULL,
`p_ordernotificationstatus` bigint(20) DEFAULT NULL,
`p_chequeassigned` tinyint(1) DEFAULT NULL,
`refundeddeliverycost` decimal(30,8) DEFAULT NULL,
`p_remindsmscount` int(11) DEFAULT NULL,
`p_checkoutsuccess` tinyint(1) DEFAULT NULL,
`p_modified` tinyint(1) DEFAULT NULL,
`p_dateofstatusupdate` datetime(6) DEFAULT NULL,
`p_instorecompleted` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`PK`,`createdTS`),
KEY `OrderCode_45` (`code`),
KEY `OrderUser_45` (`userpk`),
KEY `guidIndex_45` (`p_guid`),
KEY `currencyPK_idx_45` (`currencypk`),
KEY `paymentAddressPK_idx_45` (`paymentaddresspk`),
KEY `ars_report_idx_orders_create` (`createdTS`) USING BTREE,
KEY `ars_report_idx_orders_modify` (`modifiedTS`) USING BTREE,
KEY `ars_report_idx_orders_deliveryaddresspk` (`deliveryaddresspk`) USING BTREE,
KEY `ars_report_idx_orders_delivery_dates` (`p_deliverydate`) USING BTREE,
KEY `ars_report_idx_orders_req_delivery_dates` (`p_requesteddeliverydate`) USING BTREE,
KEY `ars_report_idx_orders_deliverymodepk` (`deliverymodepk`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
PARTITION BY RANGE COLUMNS(`createdTS`)
(PARTITION `p0` VALUES LESS THAN ('2017-03-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p1` VALUES LESS THAN ('2017-04-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p2` VALUES LESS THAN ('2017-05-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p3` VALUES LESS THAN ('2017-06-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p4` VALUES LESS THAN ('2017-07-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p5` VALUES LESS THAN ('2017-08-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p6` VALUES LESS THAN ('2017-09-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p7` VALUES LESS THAN ('2017-10-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p8` VALUES LESS THAN ('2017-11-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p9` VALUES LESS THAN ('2017-12-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p10` VALUES LESS THAN ('2018-01-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p11` VALUES LESS THAN ('2018-02-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p12` VALUES LESS THAN ('2018-03-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p13` VALUES LESS THAN ('2018-04-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p14` VALUES LESS THAN ('2018-05-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p15` VALUES LESS THAN ('2018-06-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p16` VALUES LESS THAN ('2018-07-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p17` VALUES LESS THAN ('2018-08-01 00:00:00.000000') ENGINE = InnoDB,
PARTITION `p18` VALUES LESS THAN ('2018-09-01 00:00:00.000000') ENGINE = InnoDB)
|
|
Strange, maybe that's happened because your select more simple that my view?
My full view looks like this:
CREATE OR REPLACE VIEW third_view AS
SELECT
o.PK AS order_pk,
MAX(IF(omc.ostatus = 'SHIPPED', omc.p_timestamp, null)) AS shipped_date,
MAX(IF(omc.ostatus = 'REALIZATION', omc.p_timestamp, null)) AS realization_date,
MAX(IF(omc.ostatus = 'RETURN', omc.p_timestamp, null)) AS return_date,
MAX(IF(omc.ostatus = 'RETURN_AND_REALIZATION', omc.p_timestamp, null)) AS return_and_realization_date,
MAX(IF(omc.ostatus = 'APPROVED', omc.p_timestamp, null)) AS approved_date,
MAX(IF(omc.ostatus = 'READY_TO_PACKING', omc.p_timestamp, null)) AS ready_to_packing_date,
MAX(IF(omc.ostatus = 'PACKED', omc.p_timestamp, null)) AS packed_date,
MAX(IF(omc.ostatus = 'READY_FOR_ISSUANCE', omc.p_timestamp, null)) AS ready_for_issurance_date,
MAX(IF(omc.ostatus = 'IN_STORE_PARTIALLY_COMPLETED', omc.p_timestamp, null)) AS in_store_partially_completed_date,
MAX(IF(omc.ostatus = 'DELIVERED', omc.p_timestamp, null)) AS delivered_date,
MAX(IF(omc.ostatus = 'CANCELLED', omc.p_timestamp, null)) AS cancelled_date,
MAX(IF(omc.ostatus = 'RETURNED_FOR_REVISION', omc.p_timestamp, null)) AS returned_for_revision_date,
MAX(IF(omc.ostatus = 'MOVED_TO_RETAIL', omc.p_timestamp, null)) AS moved_to_retail_date,
MAX(IF(omc.ostatus = 'PROCESSED', omc.p_timestamp, null)) AS processed_date,
MAX(IF(omc.ostatus = 'WAITING', omc.p_timestamp, null)) AS waiting_date,
MAX(IF(omc.ostatus = 'UNCALL', omc.p_timestamp, null)) AS uncall_date,
MAX(IF(omc.ostatus = 'PARTIALLY_READY_FOR_ISSUANCE', omc.p_timestamp, null)) AS partially_ready_for_issuance_date,
MAX(IF(omc.ostatus = 'PARTIAL_REALIZATION', omc.p_timestamp, null)) AS partial_realization_date,
MAX(IF(omc.ostatus = 'REALIZATION_AND_RETURN', omc.p_timestamp, null)) AS realization_and_return_date
FROM main_view AS o
JOIN ordermodifirecords AS omr
ON o.PK = omr.p_order
JOIN ordermodifirecentrs AS omc
ON omc.p_modificationrecord = omr.PK
WHERE omc.p_status IS NOT NULL
GROUP BY o.PK
ORDER BY NULL;
If i test your example, disable optimization option works good.
|