You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-07-30 19:23:07 +03:00
a1e64d4cb0
This fixes discrepance with the server, which assigns DATE type to last_day()'s result. Now we also assigns DATE result type and, also, use proper dataconvert::Day data structure to return date. Tests agree with InnoDB. Also, this patch includes test for MCOL-5669, to show we fixed it.
113 lines
12 KiB
Plaintext
113 lines
12 KiB
Plaintext
--source include/have_innodb.inc
|
|
--source ../include/have_columnstore.inc
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcol_5669;
|
|
--enable_warnings
|
|
CREATE DATABASE mcol_5669;
|
|
USE mcol_5669;
|
|
|
|
if (!$MASTER_MYPORT)
|
|
{
|
|
# Running with --extern
|
|
let $MASTER_MYPORT=`SELECT @@port`;
|
|
}
|
|
|
|
#
|
|
# Enable cross engine join
|
|
# Configure user and password in Columnstore.xml file
|
|
#
|
|
--exec $MCS_MCSSETCONFIG CrossEngineSupport User 'cejuser'
|
|
--exec $MCS_MCSSETCONFIG CrossEngineSupport Password 'Vagrant1|0000001'
|
|
--exec $MCS_MCSSETCONFIG CrossEngineSupport Port $MASTER_MYPORT
|
|
|
|
#
|
|
# Create corresponding in the server
|
|
#
|
|
--disable_warnings
|
|
CREATE USER IF NOT EXISTS'cejuser'@'localhost' IDENTIFIED BY 'Vagrant1|0000001';
|
|
--enable_warnings
|
|
GRANT ALL PRIVILEGES ON *.* TO 'cejuser'@'localhost';
|
|
FLUSH PRIVILEGES;
|
|
|
|
# Oirginal script by Edward Stoever for Mariadb Support
|
|
# REF: CS0715568
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS `colstore_zzzzzz_zzz_999999`;
|
|
--enable_warnings
|
|
|
|
CREATE TABLE `colstore_zzzzzz_zzz_999999` (
|
|
`yyyymmdd` date DEFAULT NULL,
|
|
`di_source_id` smallint(5) unsigned DEFAULT 0,
|
|
`customer_code` varchar(85) DEFAULT NULL,
|
|
`item_code` varchar(85) DEFAULT NULL,
|
|
`location_id` int(10) unsigned NOT NULL DEFAULT 0,
|
|
`brand_sku_id` int(10) unsigned NOT NULL DEFAULT 0,
|
|
`item_gift` tinyint(3) unsigned NOT NULL DEFAULT 0,
|
|
`units` int(11) NOT NULL DEFAULT 0,
|
|
`volume` bigint(20) NOT NULL DEFAULT 0,
|
|
`eu` decimal(14,4) NOT NULL DEFAULT 0.0000,
|
|
`value` decimal(14,2) NOT NULL DEFAULT 0.00,
|
|
`value_incl` decimal(14,2) NOT NULL DEFAULT 0.00,
|
|
`units_per_item` smallint(5) unsigned DEFAULT 0,
|
|
`units_per_case` decimal(18,6) NOT NULL DEFAULT 0.000000,
|
|
`base_curreny_id` tinyint(3) unsigned NOT NULL DEFAULT 0
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
|
|
|
|
INSERT INTO `colstore_zzzzzz_zzz_999999` VALUES ('2023-12-18',5298,'QEXCTLAUVUKTOLOLONVOJESCIKYNUJLBAWJF','XXUEQNUHFBSIOTAYROTAXNULVULYHROSXHVF',684963,1004,1,1,2166,1.0000,2.00,10.00,10,1.000000,2),('2023-12-17',5129,'LDLUQWHWODYJYROSWGSSNKLZNTFUJJTRCKVV','QVDFPJBFXVJLDJLBZUZNQOXWOIVGRQGHQJZS',693859,1004,0,1,2006,6.0000,4.00,2.00,7,8.000000,2),('2023-12-09',5297,'MKNKOMSCIIQFEGSUWAKBZUZPZBJSJUTMBVAQ','BMFQOWPMNFLRYRNPKFVMXBREWVNBUUMCZQGK',323051,1004,0,1,1463,6.0000,8.00,4.00,2,10.000000,1),('2023-12-20',5129,'BIMIEYDXEBUSEUKRFAMKLDGUHCNIDRZYRNOF','NVQQHMNHTXGQIUWBRGDZKFUHCRBFVQPBNLOP',361466,1004,1,1,1483,8.0000,8.00,5.00,3,7.000000,2),('2023-12-24',5280,'GUCDLURZYQLHCQUDHAEVQUXEEICLAVZLFVND','ASNMUNGSVZNNANQOYYYUFSVZNRTSKVXCUQVH',884018,1004,1,1,2182,3.0000,5.00,6.00,3,6.000000,1),('2023-12-07',5293,'EFNWVMYJXNWTEQRMJJRGIUBVZMJKYLMBUSIM','KPUEKMHAFYAKYMNFLRXPHPDVUNDFQNRVEIDS',218839,1004,1,1,1385,7.0000,5.00,6.00,2,3.000000,2),('2023-12-04',5389,'OWPKHDXDXBQAETGALAUXDAQCRZWLRYTYKGCR','BHGLJNNANMXAMIDQWKMCAXKHIRLFRRLEOEIB',374575,1004,0,1,2067,3.0000,6.00,3.00,7,3.000000,1),('2023-12-26',5245,'WNYEZKDLWYFDAUWYBMGWOEDHXUDHBKWCZNTD','KSHGMQUYKDNEIBJRFAMJLCEPKGYCRZWJGFGP',375655,1004,1,1,1806,1.0000,2.00,7.00,10,6.000000,2),('2023-12-31',5156,'SGAKXJERWJDSACHGKFXXUHCNKJPYWPHROUFS','YOXWPJBGBNKLCCFVNBRFBRFBPUFRRJTREUMY',418571,1004,1,1,1702,10.0000,10.00,9.00,6,3.000000,1),('2023-12-13',5307,'BSHKBERXMQUARKYLKREUMASNMSGBMHBJPZDT','IIQHLLXDYIWHXPIVFMVRXNVOGRQFBREWWQQI',640569,1004,0,1,1767,8.0000,9.00,9.00,10,2.000000,2),('2023-12-19',5294,'EDFODAQGGMTFWRSQZYUELTMCZOZFCVUNEKMG','RRKXJCJPYVGVLSHHOZFAPWOGNUKRCMDGWMVT',351550,1004,0,1,1972,6.0000,3.00,6.00,1,6.000000,2),('2023-12-21',5277,'CDJKYLKSKXHUBVBVXZGHRLGXSVCASOQLJLCF','UGWQOXWOHSQCOPFHTWDARHMNDDIDTKSJSJTR',186083,1004,0,1,1135,8.0000,6.00,7.00,5,6.000000,1),('2023-12-13',5281,'KJPZZCKVXBMIDRBDPPFEHWMUMZPXWMWWTDJL','BZVEKLAVASJUVTHEZLFUGXSVARGITWCXEGQN',665425,1004,1,1,1210,7.0000,1.00,3.00,2,1.000000,2),('2023-12-11',5129,'YSTPUDHAHJAYTVWXWSXIZYRRGGLPNVOKIMIE','YECWDBVBSMFRSOQLIJXHUATQYUDIHJBBGAIQ',238314,1004,1,1,2110,9.0000,7.00,9.00,1,9.000000,1),('2023-12-21',5231,'EBWDATREUMZMKPVGTBAWHWNXYZBKVZKDLUPO','ANLSFYBKXHUCDIGFKIKDKQYVHAGEDDGYWPJY',769856,1004,1,1,2193,4.0000,9.00,10.00,5,5.000000,2),('2023-12-04',5129,'QBJQDTHFHSRGFHUBYNTFVMYFGRNRTREXAJUW','YEYECASOSVYFGQLJMIGDAPYYVLPUAUVUKRDQ',840406,1004,0,1,1675,10.0000,3.00,4.00,1,3.000000,1),('2023-12-13',5389,'SPXTYLMDCBAZTYJZWHYTZSPUENAPZAFALDLS','GCSGEEGTATVUOIXROUHAHKEPKIKCGYWQOVNB',764531,1004,1,1,1600,7.0000,8.00,8.00,7,1.000000,1),('2023-12-28',5226,'DGWNZHOWUFTEPLMZLIJTQXSTSEWSYRLFUGXT','ZSQCNHXQMMBSLZSMJJRHKFTDLTLAVZMMZJWJ',187212,1004,0,1,2108,2.0000,2.00,4.00,1,4.000000,1),('2023-12-01',5156,'JOQNRUWAOQPCQYUCDIHKFUIJSMIGEFLNJGFF','MSFVPLLWYDWVRVEJGEBVXDXCWYFDCBZTVWVN',196576,1004,0,1,1871,5.0000,1.00,10.00,6,9.000000,2),('2023-12-06',5279,'SDNDELTJQBJSKYMNGTWBUUQRNQNRUXFHXRRG','IVELOPFJFZGIWKJRHKDMAQDTHECXEEIFBQAE',856704,1004,1,1,2000,1.0000,7.00,2.00,7,1.000000,1),('2023-12-03',5300,'KTPSTPTWFMRACJORTQZAFXYBJRFDYKCKPWOE','EHBHJWGTYJDOKILGVLUPPFGPECBYPEZLGYXT',165891,1004,0,1,1310,7.0000,8.00,9.00,8,6.000000,1),('2023-12-08',5319,'TAVBTRCJRFDXDZMLUONYEAQAGCVVRUARJSOP','HQJVCBYOZGGOALBBBDNFKNHYVIEVQSREVRYP',201168,1004,1,1,1978,5.0000,9.00,8.00,3,1.000000,1),('2023-12-17',5300,'CGXUFSWEJFYBLBAZSQDSCJMKONYCRBIJZSQC','MCCEPLKUPROTBBERXKKUTKRFCUQWKKTPTZRK',942528,1004,1,1,1922,2.0000,5.00,8.00,7,1.000000,1),('2023-12-08',5279,'OKIJUZMOGODXCTLZPZFBQADSEQRMKQYVFQNT','FSYNWTDLWZIQDVTGZEWVQPCRADRYQKAYRMMY',357537,1004,1,1,1150,5.0000,1.00,2.00,5,8.000000,2),('2023-12-16',5304,'POBMIEYDURWKILFVJLDIFDYKFVMZKCIJTSGF','GROSAWFOECAUWAOQNQSOTBZSSJTOQMNEICOL',620301,1004,1,1,2191,3.0000,3.00,6.00,1,8.000000,2),('2023-12-11',5228,'BNODAQCRZZWLQTYMPMPPDYHSORQFBURZWLPS','RJRHNRVCBYMRVEICOLQUZQEBRHIUZMMXBRGD',879631,1004,1,1,1460,7.0000,2.00,1.00,6,8.000000,1),('2023-12-06',5301,'ENCZPZDTHGLKREUNEHXSWEIAEWTEQOXXUGVK','PUENZKDKQACKTNJFZHOVLVUMZOSAXKJQCNJE',845683,1004,1,1,1773,2.0000,9.00,10.00,2,9.000000,2),('2023-12-30',5389,'BKUUPQHOAHLKQACOJFXWPLMYKDKSHIVATQYT','AWEHZBFAMHAHJXNVNDFOFJDPOZFDCCENEIAB',416626,1004,1,1,1867,8.0000,8.00,5.00,2,5.000000,2),('2023-12-19',5269,'YQKBCGYZEVPNVMAQCPQMKPWMTHFGRMLTJQZD','SCKSJSMGVHCNIDSDPNVQRJWFKMDENDCBCESY',687817,1004,0,1,1172,1.0000,2.00,7.00,9,4.000000,2),('2023-12-21',5296,'EXZGHTTQUELSHHPEZLFSYPEDCDKPVFSVYJYO','ZGHRPWODZNRTTMGSUUOKLXEEIFAMJHKESAXM',795156,1004,0,1,1683,5.0000,9.00,8.00,3,2.000000,2),('2023-12-13',5227,'SWHXNZGIWIADQVGUDGXUGUGUIFCTONYFFIAF','XXWPLJLFRSNODYLJMIEYDVSEQPFHRPAEXBLD',418800,1004,0,1,1819,2.0000,10.00,1.00,7,9.000000,1),('2023-12-18',5296,'KDKRFZFEHVJINNEHYWMUNFNYEYISQZACMDEM','XBPVHYVJHLHEZJWEJGDZLIGIVFPGOBPWLSCL',928217,1004,0,1,1711,10.0000,10.00,2.00,8,4.000000,1),('2023-12-05',5294,'NRWJESDMBVXAJUVVPNVOHTXHUCZTTOLNGTBY','MRYQISPVIGFJFYCQYUCEPMQRKCFTBDMARIOX',748307,1004,1,1,1711,9.0000,8.00,3.00,9,5.000000,2),('2023-12-08',5277,'JNNAPWQLKRDSCKTLZQFEFNYGIVHWOFJGDWAL','FRTQBHGKHFGQMJKZSRDQTYMLXDZMKOPHPEDE',437348,1004,0,1,2010,9.0000,10.00,3.00,6,7.000000,2),('2023-12-25',5301,'JPVIEXYZBLBYOZFCWBRDSBFYBLAUYIVFNYFG','PFJBFWSZUYIWIZACKSKVXBORSMICMEMVSBDM',101926,1004,1,1,2135,9.0000,5.00,9.00,10,10.000000,1),('2023-12-21',5133,'ERVCBYPDZJXNTGBMHYVHZCPOAMICPSTOPJXN','WVNEGRRGIWKLYJZUCDHDUMDFOGNWTEPOXWRS',665704,1004,1,1,2101,6.0000,3.00,6.00,2,10.000000,1),('2023-12-27',5228,'OECYNRUXFIYTZQEYHNTDMBUVTHGHTWCYJZTW','ZLENEHWNZKZUYKFUJKYKILDKOPGLNIYWLOOC',838538,1004,1,1,1692,3.0000,6.00,2.00,9,2.000000,2),('2023-12-27',5301,'FVLTMEIBLATQYVGTZQFGPDAQEWULURADNEHX','RQGHRNNDBWCYJAYRPXRSPTBAZSSKVYHPCTJO',789632,1004,0,1,1538,6.0000,5.00,6.00,4,5.000000,2),('2023-12-30',5245,'NUJKXLJNLSGDXDYKBDNDFQNQSSHIVEIBJSLC','CEPNUHEYFHSUVUMAPAHKFSZVDGWMYEZMMXBQ',939131,1004,1,1,1277,4.0000,5.00,2.00,4,4.000000,2),('2023-12-07',5226,'EJHIVEHZZYSWBVYDYFGPEEKJSMHXSVZMKQXP','IXLMDDIHKESZVDEOGMSDOKIMKQXOEEGWMUNE',368610,1004,0,1,1297,9.0000,6.00,2.00,1,8.000000,2),('2023-12-15',5226,'IGEBWFJEUNCBWFNXZFEEHXRTPVKLZNTERTVU','LYJBEPMQTWZJWHWIEWWRTRFCSJPXRSMFTCDM',929491,1004,0,1,1371,6.0000,2.00,1.00,8,7.000000,2),('2023-12-05',5273,'OMWTGYBLZPZFDZLIIOVOHTWCXHPFFNXZFANN','ZJWIBFYCOPFIXROVLTKWZISOSVCXFJHGJCKU',674379,1004,1,1,1530,4.0000,5.00,2.00,7,9.000000,1),('2023-12-26',5228,'QPZFAOTAXLNEJINQQFFLMFNALFTBDLWXYYXT','ATUTHHPAKZQGHTVWYCPSTPUELQYUDJIPAJQE',873066,1004,1,1,1726,3.0000,8.00,1.00,9,1.000000,2);
|
|
|
|
--disable_warnings
|
|
DROP TABLE IF EXISTS `zzz_999999`;
|
|
--enable_warnings
|
|
|
|
CREATE TABLE `zzz_999999` (
|
|
`yyyymmdd` date NOT NULL,
|
|
`di_source_id` int(10) unsigned NOT NULL DEFAULT 0,
|
|
`location_id` int(10) unsigned NOT NULL DEFAULT 0,
|
|
`brand_sku_id` int(10) unsigned NOT NULL DEFAULT 0,
|
|
`item_gift` tinyint(3) unsigned NOT NULL DEFAULT 0,
|
|
`units` int(11) NOT NULL DEFAULT 0,
|
|
`volume` int(11) NOT NULL DEFAULT 0,
|
|
`eu` decimal(12,3) NOT NULL DEFAULT 0.000,
|
|
KEY `yyyymmdd` (`yyyymmdd`),
|
|
KEY `source_id` (`di_source_id`),
|
|
KEY `location_id` (`location_id`),
|
|
KEY `brand_sku_id` (`brand_sku_id`),
|
|
KEY `source_sku` (`di_source_id`,`brand_sku_id`,`yyyymmdd`),
|
|
KEY `combi` (`di_source_id`,`location_id`,`brand_sku_id`),
|
|
KEY `combo2` (`yyyymmdd`,`di_source_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
|
|
|
|
INSERT INTO `zzz_999999` VALUES ('2023-12-18',5298,684963,1004,1,1,2166,1.000),('2023-12-17',5129,693859,1004,0,1,2006,6.000),('2023-12-09',5297,323051,1004,0,1,1463,6.000),('2023-12-20',5129,361466,1004,1,1,1483,8.000),('2023-12-24',5280,884018,1004,1,1,2182,3.000),('2023-12-07',5293,218839,1004,1,1,1385,7.000),('2023-12-04',5389,374575,1004,0,1,2067,3.000),('2023-12-26',5245,375655,1004,1,1,1806,1.000),('2023-12-31',5156,418571,1004,1,1,1702,10.000),('2023-12-13',5307,640569,1004,0,1,1767,8.000),('2023-12-19',5294,351550,1004,0,1,1972,6.000),('2023-12-21',5277,186083,1004,0,1,1135,8.000),('2023-12-13',5281,665425,1004,1,1,1210,7.000),('2023-12-11',5129,238314,1004,1,1,2110,9.000),('2023-12-21',5231,769856,1004,1,1,2193,4.000),('2023-12-04',5129,840406,1004,0,1,1675,10.000),('2023-12-13',5389,764531,1004,1,1,1600,7.000),('2023-12-28',5226,187212,1004,0,1,2108,2.000),('2023-12-01',5156,196576,1004,0,1,1871,5.000),('2023-12-06',5279,856704,1004,1,1,2000,1.000),('2023-12-03',5300,165891,1004,0,1,1310,7.000),('2023-12-08',5319,201168,1004,1,1,1978,5.000),('2023-12-17',5300,942528,1004,1,1,1922,2.000),('2023-12-08',5279,357537,1004,1,1,1150,5.000),('2023-12-16',5304,620301,1004,1,1,2191,3.000),('2023-12-11',5228,879631,1004,1,1,1460,7.000),('2023-12-06',5301,845683,1004,1,1,1773,2.000),('2023-12-30',5389,416626,1004,1,1,1867,8.000),('2023-12-19',5269,687817,1004,0,1,1172,1.000),('2023-12-21',5296,795156,1004,0,1,1683,5.000),('2023-12-13',5227,418800,1004,0,1,1819,2.000),('2023-12-18',5296,928217,1004,0,1,1711,10.000),('2023-12-05',5294,748307,1004,1,1,1711,9.000),('2023-12-08',5277,437348,1004,0,1,2010,9.000),('2023-12-25',5301,101926,1004,1,1,2135,9.000),('2023-12-21',5133,665704,1004,1,1,2101,6.000),('2023-12-27',5228,838538,1004,1,1,1692,3.000),('2023-12-27',5301,789632,1004,0,1,1538,6.000),('2023-12-30',5245,939131,1004,1,1,1277,4.000),('2023-12-07',5226,368610,1004,0,1,1297,9.000),('2023-12-15',5226,929491,1004,0,1,1371,6.000),('2023-12-05',5273,674379,1004,1,1,1530,4.000),('2023-12-26',5228,873066,1004,1,1,1726,3.000);
|
|
|
|
|
|
select
|
|
*
|
|
from (
|
|
select
|
|
s.di_source_id, s.brand_sku_id,
|
|
date_format('2024-01-01','%Y-%m-%d') as adjusted_dtm,
|
|
0 as is_sit,
|
|
0 as sit_uom,
|
|
sum(s.volume / 9000) / ( datediff('2023-12-31', '2023-12-01') +1) as sales_uom_daily_average
|
|
from colstore_zzzzzz_zzz_999999 s
|
|
where s.di_source_id in (5129,5133,5136,5137,5156,5225,5226,5227,5228,5231,5245,5249,5251,5253,5269,5273,5277,5278,5279,5280,5281,5285,5293,5294,5296,5297,5298,5299,5300,5301,5302,5303,5304,5307,5312,5319,5379,5380,5389)
|
|
and s.brand_sku_id = 1004
|
|
and (s.yyyymmdd >= '2023-12-01' and s.yyyymmdd <= '2023-12-31')
|
|
group by 1,2,3
|
|
union
|
|
select
|
|
s.di_source_id, s.brand_sku_id,
|
|
date_add(s.yyyymmdd, interval (case when day(yyyymmdd ) < 15 then (day(yyyymmdd ) - 1)*-1 else (day(last_day(yyyymmdd )) - day(yyyymmdd )) +1 end) day) as adjusted_dtm,
|
|
1 as is_sit,
|
|
sum(s.volume / 9000) as sit_uom, 0 as sales_uom_daily_average
|
|
from zzz_999999 s
|
|
where date_add(yyyymmdd, interval (case when day(yyyymmdd ) < 15 then (day(yyyymmdd ) - 1)*-1 else (day(last_day(yyyymmdd )) - day(yyyymmdd )) +1 end) day) = '2024-01-01'
|
|
and s.brand_sku_id = 1004
|
|
group by 1,2,3,4
|
|
|
|
|
|
) as z;
|
|
|
|
DROP DATABASE mcol_5669;
|