\W
drop database test1;
create database test1;
use test1;

CREATE TABLE t1 (
hjmpTS bigint(20) DEFAULT NULL,
createdTS datetime(6) DEFAULT NULL,
modifiedTS datetime(6) DEFAULT NULL,
TypePkString bigint(20) DEFAULT NULL,
OwnerPkString bigint(20) DEFAULT NULL,
PK bigint(20) NOT NULL DEFAULT 0,
languagepk bigint(20) DEFAULT NULL,
Qualifier varchar(255) COLLATE utf8_bin DEFAULT NULL,
SourcePK bigint(20) DEFAULT NULL,
TargetPK bigint(20) DEFAULT NULL,
SequenceNumber int(11) DEFAULT 0,
RSequenceNumber int(11) DEFAULT 0,
aCLTS bigint(20) DEFAULT 0,
propTS bigint(20) DEFAULT 0,
PRIMARY KEY (PK),
KEY seqnr_201 (SequenceNumber),
KEY rseqnr_201 (RSequenceNumber),
KEY linksource_201 (SourcePK),
KEY linktarget_201 (TargetPK),
KEY qualifier_201 (Qualifier));

insert into t1 select seq,now(),now(),seq,seq,seq,5001,seq,seq,seq,seq,seq,seq,seq from seq_1_to_10000;

CREATE TABLE t2 (
ITEMPK bigint(20) NOT NULL DEFAULT 0,
ITEMTYPEPK bigint(20) DEFAULT NULL,
LANGPK bigint(20) NOT NULL DEFAULT 0,
p_locname varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (ITEMPK,LANGPK),
KEY ars_report_idx_t2_itempk_langpk (ITEMPK,LANGPK));

insert into t2 select seq,seq,5001,seq from seq_1_to_10000;
insert into t2 select seq,seq,seq,seq from seq_10001_to_20000;

CREATE TABLE t3 (
hjmpTS bigint(20) DEFAULT NULL,
createdTS datetime(6) DEFAULT NULL,
modifiedTS datetime(6) DEFAULT NULL,
TypePkString bigint(20) DEFAULT NULL,
OwnerPkString bigint(20) DEFAULT NULL,
PK bigint(20) NOT NULL DEFAULT 0,
p_timestamp datetime(6) DEFAULT NULL,
p_employee bigint(20) DEFAULT NULL,
p_description text COLLATE utf8_bin DEFAULT NULL,
p_previousorderversion bigint(20) DEFAULT NULL,
p_orderpos int(11) DEFAULT NULL,
p_order bigint(20) DEFAULT NULL,
p_code varchar(255) COLLATE utf8_bin DEFAULT NULL,
aCLTS bigint(20) DEFAULT 0,
propTS bigint(20) DEFAULT 0,
p_t6tatus bigint(20) DEFAULT NULL,
p_t6ubstatus bigint(20) DEFAULT NULL,
p_deliverymode bigint(20) DEFAULT NULL,
p_prevstatus bigint(20) DEFAULT NULL,
PRIMARY KEY (PK),
KEY orderRelIDX_2002 (p_order),
KEY orderposPosIDX_2002 (p_orderpos),
KEY ars_report_idx_t3_3 (p_t6tatus,p_prevstatus,p_order),
KEY ars_report_idx_t3_prevstatus (p_prevstatus),
KEY ars_report_idx_t3_createdTS (createdTS),
KEY ars_report_idx_t3_modifiedTS (modifiedTS));

insert into t3 select seq,now(),now(),seq,seq,seq,now(),seq,seq,seq,seq,3,seq,seq,seq,1,seq,seq,1 from seq_1_to_2000;
insert into t3 select seq,now(),now(),seq,seq,seq,now(),seq,seq,seq,seq,2,seq,seq,seq,2,seq,seq,2 from seq_2001_to_5000;
insert into t3 select seq,now(),now(),seq,seq,seq,now(),5001,seq,seq,seq,1,seq,seq,seq,3,seq,seq,3 from seq_5001_to_7000;
insert into t3 select seq,now(),now(),seq,seq,seq,now(),5001,seq,seq,seq,1,seq,seq,seq,NULL,seq,seq,3 from seq_7001_to_8000;
insert into t3 select seq,now(),now(),seq,seq,seq,now(),5001,seq,seq,seq,1,seq,seq,seq,3,seq,seq,NULL from seq_8001_to_10000;


CREATE TABLE t4 (
hjmpTS bigint(20) DEFAULT NULL,
createdTS datetime(6) DEFAULT NULL,
modifiedTS datetime(6) DEFAULT NULL,
TypePkString bigint(20) DEFAULT NULL,
OwnerPkString bigint(20) DEFAULT NULL,
PK bigint(20) NOT NULL DEFAULT 0,
description varchar(255) COLLATE utf8_bin DEFAULT NULL,
name varchar(255) COLLATE utf8_bin DEFAULT NULL,
uniqueid varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_profilepicture bigint(20) DEFAULT NULL,
p_ldapsearchbase varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_dn text COLLATE utf8_bin DEFAULT NULL,
p_cn varchar(255) COLLATE utf8_bin DEFAULT NULL,
defaultpaymentaddress bigint(20) DEFAULT NULL,
defaultshippingaddress bigint(20) DEFAULT NULL,
passwd varchar(255) COLLATE utf8_bin DEFAULT NULL,
encode varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_passwordanswer varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_passwordquestion varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_sessionlanguage bigint(20) DEFAULT NULL,
p_sessioncurrency bigint(20) DEFAULT NULL,
p_logindisabled tinyint(1) DEFAULT NULL,
p_lastlogin datetime(6) DEFAULT NULL,
p_hmclogindisabled tinyint(1) DEFAULT NULL,
p_userprofile bigint(20) DEFAULT NULL,
p_europe1pricefactory_udg bigint(20) DEFAULT NULL,
p_europe1pricefactory_upg bigint(20) DEFAULT NULL,
p_europe1pricefactory_utg bigint(20) DEFAULT NULL,
p_ldapaccount tinyint(1) DEFAULT NULL,
p_domain varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_ldaplogin varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_authorizedtounlockpages tinyint(1) DEFAULT NULL,
p_yoyocardinfo bigint(20) DEFAULT NULL,
aCLTS bigint(20) DEFAULT 0,
propTS bigint(20) DEFAULT 0,
p_pointofservice bigint(20) DEFAULT NULL,
p_contactemail varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_employeeid varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_emails longblob DEFAULT NULL,
p_regionalmanager bigint(20) DEFAULT NULL,
p_innerregion varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_sectortdm bigint(20) DEFAULT NULL,
p_customerid varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_previewcatalogversions text COLLATE utf8_bin DEFAULT NULL,
p_title bigint(20) DEFAULT NULL,
p_defaultpaymentinfo bigint(20) DEFAULT NULL,
p_token text COLLATE utf8_bin DEFAULT NULL,
p_originaluid varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_type bigint(20) DEFAULT NULL,
p_temppassword varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_region varchar(255) COLLATE utf8_bin DEFAULT NULL,
p_stores text COLLATE utf8_bin DEFAULT NULL,
p_middlename varchar(255) COLLATE utf8_bin DEFAULT NULL,
PRIMARY KEY (PK),
UNIQUE KEY UID_4 (uniqueid),
KEY regionalmanagerRelIDX_4 (p_regionalmanager),
KEY sectortdmRelIDX_4 (p_sectortdm),
KEY ars_cc_t4_name (name),
KEY ars_cc_t4_name_uniqueid (name,uniqueid),
KEY ars_rest_v1_i_order_defaultshippingaddress (defaultshippingaddress));

insert into t4 select  seq,now(),now(),seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,
	'1',now(),'1',seq,seq,seq,seq,'1',seq,seq,'1',seq,0,0,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq from seq_1_to_10000;

CREATE TABLE t5 (
hjmpTS bigint(20) DEFAULT NULL,
createdTS datetime(6) DEFAULT NULL,
modifiedTS datetime(6) DEFAULT NULL,
TypePkString bigint(20) DEFAULT NULL,
OwnerPkString bigint(20) DEFAULT NULL,
PK bigint(20) NOT NULL DEFAULT 0,
activeflag tinyint(1) DEFAULT NULL,
isocode varchar(255) COLLATE utf8_bin DEFAULT NULL,
aCLTS bigint(20) DEFAULT 0,
propTS bigint(20) DEFAULT 0,
PRIMARY KEY (PK),
KEY ISOCode_32 (isocode));

insert into t5 select seq,now(),now(),seq,seq,seq,'1','seq',seq,seq from seq_1_to_5000;
insert into t5 select seq,now(),now(),seq,seq,seq,'1','ru',seq,seq from seq_5001_to_5001;
insert into t5 select seq,now(),now(),seq,seq,seq,'1','seq',seq,seq from seq_5002_to_10000;

CREATE TABLE t6 (
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_t6ubstatus 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_t6_create (createdTS) USING BTREE,
KEY ars_report_idx_t6_modify (modifiedTS) USING BTREE,
KEY ars_report_idx_t6_deliveryaddresspk (deliveryaddresspk) USING BTREE,
KEY ars_report_idx_t6_delivery_dates (p_deliverydate) USING BTREE,
KEY ars_report_idx_t6_req_delivery_dates (p_requesteddeliverydate) USING BTREE,
KEY ars_report_idx_t6_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 t6 select  seq,'0000-00-00 00:00:00.000000',now(),seq,seq,seq,'1',seq,seq,seq,seq,seq,seq,seq,'1',
	seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,'1','1',seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,
	'1','1',seq,seq,seq,'1','1',seq,seq,now(),seq,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,seq,seq,seq,seq,seq,seq,'1',seq,seq,'1','1',now(),'1' from seq_1_to_1000;

insert into t6 select  seq,'2018-04-02 00:00:00.000000',now(),seq,seq,seq,'1',seq,seq,seq,seq,seq,seq,seq,'1',
	seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,'1','1',seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,
	'1','1',seq,seq,seq,'1','1',seq,seq,now(),seq,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,seq,seq,seq,seq,seq,seq,'1',seq,seq,'1','1',now(),'1' from seq_1001_to_2000;

insert into t6 select  seq,now(),now(),seq,seq,seq,'1',seq,seq,seq,seq,seq,seq,seq,'1',
	seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,'1','1',seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,seq,
	'1','1',seq,seq,seq,'1','1',seq,seq,now(),seq,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,seq,seq,seq,seq,seq,seq,'1',seq,seq,'1','1',now(),'1' from seq_2001_to_10000;


CREATE ALGORITHM=UNDEFINED VIEW v1 AS
SELECT o.hjmpTS AS hjmpTS,
       o.createdTS AS createdTS,
       o.modifiedTS AS modifiedTS,
       o.TypePkString AS TypePkString,
       o.OwnerPkString AS OwnerPkString,
       o.PK AS PK,
       o.calculatedflag AS calculatedflag,
       o.code AS code,
       o.currencypk AS currencypk,
       o.deliveryaddresspk AS deliveryaddresspk,
       o.deliverycost AS deliverycost,
       o.deliverymodepk AS deliverymodepk,
       o.deliverystatuspk AS deliverystatuspk,
       o.globaldiscountvalues AS globaldiscountvalues,
       o.netflag AS netflag,
       o.paymentaddresspk AS paymentaddresspk,
       o.paymentcost AS paymentcost,
       o.paymentinfopk AS paymentinfopk,
       o.paymentmodepk AS paymentmodepk,
       o.paymentstatuspk AS paymentstatuspk,
       o.statuspk AS statuspk,
       o.p_exportstatus AS p_exportstatus,
       o.statusinfo AS statusinfo,
       o.totalprice AS totalprice,
       o.totaldiscounts AS totaldiscounts,
       o.totaltax AS totaltax,
       o.totaltaxvalues AS totaltaxvalues,
       o.userpk AS userpk,
       o.subtotal AS subtotal,
       o.discountondelivery AS discountondelivery,
       o.discountonpayment AS discountonpayment,
       o.p_previousdeliverymode AS p_previousdeliverymode,
       o.p_site AS p_site,
       o.p_store AS p_store,
       o.p_guid AS p_guid,
       o.p_yoyosum AS p_yoyosum,
       o.p_transportcompany AS p_transportcompany,
       o.p_freevouchers AS p_freevouchers,
       o.p_pointofservice AS p_pointofservice,
       o.p_deliveryzonelist AS p_deliveryzonelist,
       o.p_tariffzonelist AS p_tariffzonelist,
       o.p_t6ubstatus AS p_t6ubstatus,
       o.p_deletelines AS p_deletelines,
       o.p_weight AS p_weight,
       o.p_volume AS p_volume,
       o.p_weightbulky AS p_weightbulky,
       o.p_volumebulky AS p_volumebulky,
       o.p_paywithgift AS p_paywithgift,
       o.p_paywithyoyo AS p_paywithyoyo,
       o.p_deletedfreegoodsmap AS p_deletedfreegoodsmap,
       o.p_versionid AS p_versionid,
       o.p_originalversion AS p_originalversion,
       o.p_fraudulent AS p_fraudulent,
       o.p_potentiallyfraudulent AS p_potentiallyfraudulent,
       o.p_salesapplication AS p_salesapplication,
       o.p_language AS p_language,
       o.p_deliverydate AS p_deliverydate,
       o.p_deliverytime AS p_deliverytime,
       o.p_shippingdate AS p_shippingdate,
       o.p_informingmethod AS p_informingmethod,
       o.p_requesteddeliverydate AS p_requesteddeliverydate,
       o.p_sendsmswhenstatuschanged AS p_sendsmswhenstatuschanged,
       o.p_sapapprovedate AS p_sapapprovedate,
       o.p_sapcalculatedate AS p_sapcalculatedate,
       o.p_sapreservedate AS p_sapreservedate,
       o.p_ordertype AS p_ordertype,
       o.p_promotionsdate AS p_promotionsdate,
       o.p_operator AS p_operator,
       o.p_buyerpaymentresulterror AS p_buyerpaymentresulterror,
       o.p_parentorder AS p_parentorder,
       o.p_cslockdate AS p_cslockdate,
       o.p_cslockoperator AS p_cslockoperator,
       o.p_ismigrated AS p_ismigrated,
       o.p_partlypaidsum AS p_partlypaidsum,
       o.p_deliveryinterval AS p_deliveryinterval,
       o.p_refunddate AS p_refunddate,
       o.p_yoyoawardedpoints AS p_yoyoawardedpoints,
       o.p_yoyodebitedpoints AS p_yoyodebitedpoints,
       o.p_needrefundyoyo AS p_needrefundyoyo,
       o.p_giftusedforyoyoaddpoints AS p_giftusedforyoyoaddpoints,
       o.p_processedtime AS p_processedtime,
       o.aCLTS AS aCLTS,
       o.propTS AS propTS,
       o.p_fulfilmentstatus AS p_fulfilmentstatus,
       o.p_notes AS p_notes,
       o.p_computerid AS p_computerid,
       o.p_ordercreator AS p_ordercreator,
       o.p_fiascode AS p_fiascode,
       o.originaldeliverycost AS originaldeliverycost,
       o.p_ordernotificationstatus AS p_ordernotificationstatus,
       o.p_chequeassigned AS p_chequeassigned,
       o.refundeddeliverycost AS refundeddeliverycost,
       o.p_remindsmscount AS p_remindsmscount,
       o.p_checkoutsuccess AS p_checkoutsuccess,
       o.p_modified AS p_modified,
       o.p_dateofstatusupdate AS p_dateofstatusupdate,
       o.p_instorecompleted AS p_instorecompleted
FROM t6 o;

CREATE VIEW v2 AS 
select `l`.`PK` AS `pk` from t5 `l` where `l`.`isocode` = 'ru';

CREATE VIEW v3 AS 
SELECT u.PK AS user_id,
       max(u.name) AS user_name,
       group_concat(ugl.p_locname separator '; ') AS user_roles
FROM ((t4 u
JOIN t1 pgr on(u.PK = pgr.SourcePK))
JOIN t2 ugl on(ugl.ITEMPK = pgr.TargetPK AND ugl.LANGPK = (SELECT v2.pk FROM v2)))
GROUP BY u.PK
ORDER BY NULL;

CREATE VIEW v4 AS
SELECT max(oh.p_order) AS order_id,
       max(oh.createdTS) AS creation_date,
       max(oh.modifiedTS) AS modification_date,
       group_concat(oh.p_description,'' separator ',') AS description,
       max(oh.p_prevstatus) AS previous_status_id,
       max(oh.p_t6tatus) AS current_status_id,
       max(oh.p_employee) AS employee_id,
       max(u.user_id) AS user_id,
       max(u.user_name) AS user_name,
       max(u.user_roles) AS user_roles
FROM ((v1 o
      JOIN t3 oh on (o.PK = oh.p_order))
      JOIN v3 u on (oh.p_employee = u.user_id))
WHERE (oh.p_t6tatus IS NOT NULL OR oh.p_prevstatus IS NOT NULL) AND oh.p_order IS NOT NULL
GROUP BY oh.p_order,
         oh.p_prevstatus,
         oh.p_t6tatus
ORDER BY NULL;

analyze  select * from v4;

set optimizer_switch='derived_with_keys=off';
analyze  select * from v4;

set optimizer_switch='derived_with_keys=on';

