\W

CREATE or replace 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);

insert into orders SELECT
seq,now(),now(),seq,seq,seq,1,"varchar",seq,seq,seq,seq,seq,"text",1,seq,seq,seq,seq,seq,seq,seq,"varchar",seq,seq,seq,"text",seq,seq,1,1,seq,seq,seq,"varchar",seq,seq,"longblob",seq,"text","text",seq,"longblob",seq,seq,seq,seq,1,1,"longblob","varchar",seq,1,1,seq,seq,now(),"varchar",now(),seq,now(),1,now(),now(),now(),seq,now(),seq,1,seq,now(),seq,1,seq,seq,now(),seq,seq,1,seq,now(),seq,seq,seq,"varchar","varchar",seq,"varchar",seq,seq,1,seq,seq,1,1,now(),1  
from seq_1_to_300;

insert into orders SELECT
seq,'2018-08-20 00:00:00.000000',now(),seq,seq,seq,1,"varchar",seq,seq,seq,seq,seq,"text",1,seq,seq,seq,seq,seq,seq,seq,"varchar",seq,seq,seq,"text",seq,seq,1,1,seq,seq,seq,"varchar",seq,seq,"longblob",seq,"text","text",seq,"longblob",seq,seq,seq,seq,1,1,"longblob","varchar",seq,1,1,seq,seq,now(),"varchar",now(),seq,now(),1,now(),now(),now(),seq,now(),seq,1,seq,now(),seq,1,seq,seq,now(),seq,seq,1,seq,now(),seq,seq,seq,"varchar","varchar",seq,"varchar",seq,seq,1,seq,seq,1,1,now(),1  
from seq_301_to_500;

CREATE OR REPLACE FUNCTION report_from_order_creation_date()
RETURNS DATETIME DETERMINISTIC
RETURN @from_order_creation_date;

CREATE OR REPLACE FUNCTION report_until_order_creation_date()
RETURNS DATETIME DETERMINISTIC
RETURN @until_order_creation_date;

CREATE OR REPLACE VIEW main_view AS
SELECT *
FROM orders AS o
WHERE o.createdTS BETWEEN report_from_order_creation_date() AND report_until_order_creation_date();

SET @from_order_creation_date = DATE_SUB(NOW(),INTERVAL 1 HOUR);
SET @until_order_creation_date = NOW();

CREATE OR REPLACE VIEW second_view AS
SELECT o.PK AS order_pk
FROM main_view AS o ;

CREATE OR REPLACE VIEW third_view AS
SELECT o.PK AS order_pk
FROM main_view AS o;

CREATE OR REPLACE VIEW first_view AS
SELECT o.order_pk
FROM second_view AS o
LEFT JOIN third_view AS d ON o.order_pk=d.order_pk;

explain extended select * from first_view;

select * from first_view limit 3;

