mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-21 19:45:56 +03:00
147 lines
7.7 KiB
Plaintext
147 lines
7.7 KiB
Plaintext
# -------------------------------------------------------------- #
|
||
# Test case migrated from regression test suite: bug4457.sql
|
||
#
|
||
# Author: Daniel Lee, daniel.lee@mariadb.com
|
||
# -------------------------------------------------------------- #
|
||
#
|
||
--source ../include/have_columnstore.inc
|
||
#
|
||
USE tpch1;
|
||
#
|
||
#-- connector parsing test. no data needed
|
||
CREATE TABLE if not exists `poc_enveloppe_fact` (
|
||
`pef_env_id_env` bigint(20) DEFAULT NULL COMMENT 'Id Tech',
|
||
`pef_env_env_id` varchar(45) DEFAULT NULL COMMENT 'CAB',
|
||
`pef_env_id_article` int(11) DEFAULT NULL COMMENT 'Id dim article',
|
||
`pef_int_date_integration_datetime` datetime DEFAULT NULL COMMENT 'Datetime integration',
|
||
`pef_int_date_integration_date` date DEFAULT NULL COMMENT 'Date integration',
|
||
`pef_dep_numdepot` varchar(8) DEFAULT NULL COMMENT 'Numero de depot',
|
||
`pef_dep_codelieudepot` varchar(45) DEFAULT NULL COMMENT 'Code regate lieu depot',
|
||
`pef_dep_coclico_deposant_restitue` varchar(45) DEFAULT NULL COMMENT 'Client deposant',
|
||
`pef_dep_coclico_contractant` varchar(45) DEFAULT NULL COMMENT 'Client contractant',
|
||
`pef_dep_date_depot_restitue_datetime` datetime DEFAULT NULL,
|
||
`pef_dep_date_depot_restitue_date` date DEFAULT NULL,
|
||
`pef_ind_nb_traces` int(11) DEFAULT NULL COMMENT 'Nombre de traces de enveloppe',
|
||
`pef_id_nb_traces` tinyint(4) DEFAULT NULL COMMENT 'Id dim libelle nb traces',
|
||
`pef_pnd_id_pnd` tinyint(4) DEFAULT NULL COMMENT 'Id dim pnd et motif',
|
||
`pef_date_restit_limite_pli` datetime DEFAULT NULL,
|
||
`pef_ind_ecart_delai_restit` int(11) DEFAULT NULL COMMENT 'Ecart entre restitution r<>elle et limite',
|
||
`pef_id_ecart` tinyint(4) DEFAULT NULL COMMENT 'Id dim libell<6C> <20>cart',
|
||
`pef_trc_date_cpt_sens_tdistrib` date DEFAULT NULL,
|
||
`pef_trc_id_regate_sens_tdistrib` int(11) DEFAULT NULL COMMENT 'Id Code regate trace tdistrib',
|
||
`pef_trc_type_bordereau` varchar(5) DEFAULT NULL COMMENT 'Type de bordereau',
|
||
`pef_car_image_url` tinyint(4) DEFAULT NULL COMMENT 'Booleen uri stock<63>e',
|
||
`pef_ind_trc_ace_sens_code` varchar(45) DEFAULT NULL,
|
||
`pef_ind_trc_ace_date_flashage` date DEFAULT NULL,
|
||
`pef_ind_trc_re_sens_code` varchar(45) DEFAULT NULL,
|
||
`pef_ind_trc_re_date_flashage` date DEFAULT NULL,
|
||
`pef_ind_has_trc_re` tinyint(4) DEFAULT NULL,
|
||
`pef_ind_has_trc_ace` tinyint(4) DEFAULT NULL,
|
||
`pef_trc_id_re_coderegate` int(11) DEFAULT NULL COMMENT 'Id Code regate trace Reception Enseigne',
|
||
`pef_trc_id_ace_coderegate` int(11) DEFAULT NULL COMMENT 'Id Code regate trace Annonce Courrier Enseigne',
|
||
`pef_ind_is_site_egal_ace_re` tinyint(4) DEFAULT NULL,
|
||
`pef_id_arrivee_site` tinyint(4) DEFAULT NULL,
|
||
`pef_id_annoncee_site` tinyint(4) DEFAULT NULL,
|
||
`pef_ind_has_trc_li_rdddmo` tinyint(4) DEFAULT NULL,
|
||
`pef_ind_has_trc_li_rdrenr` tinyint(4) DEFAULT NULL,
|
||
`pef_id_li` tinyint(4) DEFAULT NULL,
|
||
`pef_car_id_ar` varchar(105) DEFAULT NULL COMMENT 'Identifiant AR',
|
||
`pef_trc_type_cab` varchar(30) DEFAULT NULL COMMENT 'Type de CAB',
|
||
`pef_dep_code_prodcom` varchar(30) DEFAULT NULL COMMENT 'Code produit commercial',
|
||
`pef_derniere_date_DMC` datetime DEFAULT NULL,
|
||
`pef_dernier_dmc` varchar(6) DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_date_depart` date DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_id_site_depart` int(11) DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_date_arrivee` date DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_id_site_arrivee` int(11) DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_delai` bigint(20) DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_id_delai` tinyint(4) DEFAULT NULL,
|
||
`pef_levee_reclamation` tinyint(4) DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_date_fin_vie` bigint(20) DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_date_PARTID` bigint(20) DEFAULT NULL,
|
||
`pef_id_fonctionnel_tournee` bigint(20) DEFAULT NULL
|
||
) engine=columnstore;
|
||
|
||
CREATE TABLE if not exists `poc_enveloppe_partid_fact` (
|
||
`pepf_id_fonctionnel_tournee` int(11) DEFAULT NULL,
|
||
`pepf_int_date_trc_partid` date DEFAULT NULL,
|
||
`pepf_int_date_trc_partid_date` bigint(20) DEFAULT NULL,
|
||
`pepf_env_id_env` bigint(20) DEFAULT NULL
|
||
) engine=columnstore;
|
||
|
||
CREATE TABLE if not exists `poc_tmp_calc_indic` (
|
||
`pef_env_id_env` bigint(20) DEFAULT NULL COMMENT 'Id Tech',
|
||
`pef_ind_nb_traces` int(11) DEFAULT NULL COMMENT 'Nombre de traces de enveloppe',
|
||
`pef_id_nb_traces` tinyint(4) DEFAULT NULL COMMENT 'Id dim libelle nb traces',
|
||
`pef_trc_date_cpt_sens_tdistrib` date DEFAULT NULL,
|
||
`pef_trc_id_regate_sens_tdistrib` int(11) DEFAULT NULL COMMENT 'Id Code regate trace tdistrib',
|
||
`pef_trc_type_bordereau` varchar(5) DEFAULT NULL COMMENT 'Type de bordereau',
|
||
`pef_ind_trc_ace_sens_code` varchar(45) DEFAULT NULL,
|
||
`pef_ind_trc_ace_date_flashage` date DEFAULT NULL,
|
||
`pef_ind_trc_re_sens_code` varchar(45) DEFAULT NULL,
|
||
`pef_ind_trc_re_date_flashage` date DEFAULT NULL,
|
||
`pef_ind_has_trc_re` tinyint(4) DEFAULT NULL,
|
||
`pef_ind_has_trc_ace` tinyint(4) DEFAULT NULL,
|
||
`pef_trc_id_re_coderegate` int(11) DEFAULT NULL COMMENT 'Id Code regate trace Reception Enseigne',
|
||
`pef_trc_id_ace_coderegate` int(11) DEFAULT NULL COMMENT 'Id Code regate trace Annonce Courrier Enseigne',
|
||
`pef_ind_is_site_egal_ace_re` tinyint(4) DEFAULT NULL,
|
||
`pef_id_arrivee_site` tinyint(4) DEFAULT NULL,
|
||
`pef_id_annoncee_site` tinyint(4) DEFAULT NULL,
|
||
`pef_ind_has_trc_li_rdddmo` tinyint(4) DEFAULT NULL,
|
||
`pef_ind_has_trc_li_rdrenr` tinyint(4) DEFAULT NULL,
|
||
`pef_id_li` tinyint(4) DEFAULT NULL,
|
||
`pef_derniere_date_DMC` datetime DEFAULT NULL,
|
||
`pef_dernier_dmc` varchar(6) DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_date_depart` date DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_id_site_depart` int(11) DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_date_arrivee` date DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_id_site_arrivee` int(11) DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_delai` bigint(20) DEFAULT NULL,
|
||
`pef_ind_trc_CEN06_id_delai` tinyint(4) DEFAULT NULL,
|
||
`pef_id_fonctionnel_tournee` bigint(20) DEFAULT NULL
|
||
) engine=columnstore;
|
||
|
||
UPDATE poc_enveloppe_fact C,
|
||
(SELECT A.pef_env_id_env, A.pef_ind_trc_CEN06_date_depart,
|
||
A.pef_ind_trc_CEN06_date_fin_vie, MAX(B.pepf_int_date_trc_partid_date) as
|
||
pepf_int_date_trc_partid_date, B.pepf_id_fonctionnel_tournee
|
||
FROM poc_enveloppe_fact A, poc_enveloppe_partid_fact B, poc_tmp_calc_indic C
|
||
WHERE A.pef_env_id_env=C.pef_env_id_env
|
||
and A.pef_ind_trc_CEN06_date_depart is not null
|
||
and A.pef_ind_trc_CEN06_date_fin_vie is not null
|
||
AND A.pef_ind_trc_CEN06_date_PARTID is null
|
||
and A.pef_env_id_env=B.pepf_env_id_env
|
||
GROUP BY A.pef_env_id_env, A.pef_ind_trc_CEN06_date_depart,
|
||
A.pef_ind_trc_CEN06_date_fin_vie, B.pepf_id_fonctionnel_tournee
|
||
Having MAX(B.pepf_int_date_trc_partid_date) between
|
||
A.pef_ind_trc_CEN06_date_depart and A.pef_ind_trc_CEN06_date_fin_vie) D
|
||
SET C.pef_ind_trc_CEN06_date_partid = D.pepf_int_date_trc_partid_date,
|
||
C.pef_id_fonctionnel_tournee = D.pepf_id_fonctionnel_tournee
|
||
WHERE C.pef_env_id_env= D.pef_env_id_env;
|
||
|
||
UPDATE poc_enveloppe_fact C,
|
||
(SELECT A.pef_env_id_env, A.pef_ind_trc_CEN06_date_depart,
|
||
A.pef_ind_trc_CEN06_date_fin_vie, MAX(B.pepf_int_date_trc_partid_date) as
|
||
pepf_int_date_trc_partid_date, B.pepf_id_fonctionnel_tournee
|
||
FROM poc_enveloppe_fact A, poc_enveloppe_partid_fact B, poc_tmp_calc_indic C
|
||
WHERE A.pef_env_id_env=C.pef_env_id_env
|
||
and A.pef_ind_trc_CEN06_date_depart is not null
|
||
and A.pef_ind_trc_CEN06_date_fin_vie is not null
|
||
AND A.pef_ind_trc_CEN06_date_PARTID is null
|
||
and A.pef_env_id_env=B.pepf_env_id_env
|
||
GROUP BY A.pef_env_id_env, A.pef_ind_trc_CEN06_date_depart,
|
||
A.pef_ind_trc_CEN06_date_fin_vie, B.pepf_id_fonctionnel_tournee
|
||
Having pepf_int_date_trc_partid_date between
|
||
A.pef_ind_trc_CEN06_date_depart and A.pef_ind_trc_CEN06_date_fin_vie) D
|
||
SET C.pef_ind_trc_CEN06_date_partid = D.pepf_int_date_trc_partid_date,
|
||
C.pef_id_fonctionnel_tournee = D.pepf_id_fonctionnel_tournee
|
||
WHERE C.pef_env_id_env= D.pef_env_id_env;
|
||
|
||
--disable_warnings
|
||
drop table poc_enveloppe_fact;
|
||
drop table poc_enveloppe_partid_fact;
|
||
drop table poc_tmp_calc_indic;
|
||
--enable_warnings
|
||
#
|
||
|