# -------------------------------------------------------------- #
# Test case migrated from regression test suite: MCOL-3434.sql
#
# Author: Daniel Lee, daniel.lee@mariadb.com
# -------------------------------------------------------------- #
#
--source ../include/have_columnstore.inc
#
USE tpch1;
#
--disable_warnings
DROP TABLE IF EXISTS MCOL3434CS;
DROP TABLE IF EXISTS MCOL3434INNO;
--enable_warnings

CREATE TABLE `MCOL3434INNO` (`id` int(11) NOT NULL AUTO_INCREMENT, 
                             `cid` int(11) DEFAULT NULL, 
                             `d` datetime DEFAULT NULL, 
                             `c` decimal(12,4) DEFAULT NULL, 
                             PRIMARY KEY (`id`) ) ENGINE=InnoDB;

CREATE TABLE `MCOL3434CS` (  `id` int(11) NOT NULL, 
                             `cid` int(11) DEFAULT NULL, 
                             `d` datetime DEFAULT NULL, 
                             `c` decimal(12,4) DEFAULT NULL)engine=columnstore;

insert into MCOL3434INNO (cid,d,c) values
(1 , '2018-01-01 00:00:00' ,20000.0500),
(59, '2018-12-10 15:58:02' ,9411.9900),
(35, '2018-09-17 15:58:02' ,36870.7000),
(39, '2018-01-29 15:58:02' ,95532.2400),
(31, '2018-05-07 15:58:02' ,47070.5300),
(31, '2019-05-06 15:58:02' ,86272.1300),
(83, '2018-07-30 15:58:02' ,30866.2900),
(86, '2018-11-19 15:58:02' ,39956.1100),
(80, '2018-02-05 15:58:02' ,63099.1800),
(73, '2018-02-26 15:58:02' ,69119.2500),
(12, '2018-08-27 15:58:02' ,25862.1200),
(72, '2018-01-15 15:58:02' ,2750.4100),
(62, '2019-08-12 15:58:02' ,21755.6300),
(4 , '2018-07-30 15:58:02' ,66189.2800),
(64, '2019-04-08 15:58:02' ,7740.9000),
(79, '2018-03-26 15:58:02' ,32161.7800),
(38, '2017-10-23 15:58:02' ,63938.2300),
(32, '2018-04-30 15:58:02' ,46690.8100),
(28, '2017-10-09 15:58:02' ,8932.5700),
(49, '2019-04-08 15:58:02' ,53954.2800),
(8 , '2018-02-12 15:58:02' ,75162.7700),
(36, '2018-08-20 15:58:02' ,56255.9700),
(23, '2018-09-24 15:58:02' ,71082.9200),
(11, '2018-11-19 15:58:02' ,67723.0100),
(19, '2017-11-27 15:58:02' ,98601.9300),
(20, '2019-07-29 15:58:02' ,59633.1800),
(86, '2018-08-13 15:58:02' ,8402.3700),
(81, '2018-02-05 15:58:02' ,62094.3200),
(67, '2018-09-24 15:58:02' ,40681.7600),
(59, '2018-04-16 15:58:02' ,77994.5700),
(78, '2018-08-06 15:58:02' ,41158.3900),
(41, '2018-01-29 15:58:02' ,86817.7300),
(88, '2018-02-12 15:58:02' ,34142.0000),
(32, '2018-07-23 15:58:02' ,87281.6800),
(67, '2018-04-09 15:58:02' ,60700.2200),
(87, '2018-08-27 15:58:02' ,99412.2500),
(41, '2019-07-01 15:58:02' ,14074.1800),
(48, '2017-10-16 15:58:02' ,41803.8400),
(18, '2018-06-04 15:58:02' ,64304.2600),
(31, '2018-06-18 15:58:02' ,15637.4400),
(93, '2019-04-15 15:58:02' ,14780.2800),
(17, '2018-11-05 15:58:02' ,60211.8000),
(74, '2017-12-18 15:58:02' ,17876.8100),
(26, '2018-03-05 15:58:02' ,4426.1100),
(93, '2018-09-10 15:58:02' ,71415.0300),
(7 , '2019-03-18 15:58:02' ,93697.3000),
(99, '2019-05-27 15:58:02' ,67578.6700),
(100,'2017-10-30 15:58:02' ,76466.4500),
(97, '2018-07-23 15:58:02' ,92691.8400),
(93, '2018-01-01 15:58:02' ,50753.3700),
(96, '2019-02-04 15:58:02' ,57590.3400),
(0 , '2019-02-18 15:58:02' ,36886.0500),
(2 , '2019-08-19 15:58:02' ,95945.4100),
(78, '2019-08-05 15:58:02' ,79709.8100),
(90, '2019-06-10 15:58:02' ,86259.0300),
(97, '2019-02-11 15:58:02' ,48557.2900),
(58, '2018-10-08 15:58:02' ,55589.8400),
(40, '2019-01-21 15:58:02' ,36140.1500),
(88, '2019-01-21 15:58:02' ,90286.2400),
(59, '2019-03-04 15:58:02' ,47115.0700),
(61, '2018-06-11 15:58:02' ,31477.3500),
(69, '2018-09-03 15:58:02' ,46080.9000),
(79, '2018-08-13 15:58:02' ,36584.1100),
(20, '2017-12-18 15:58:02' ,82544.5900);

insert into MCOL3434CS select * from MCOL3434INNO;

select distinct cid, week(d) as week, 
                max(c) over (partition by cid, week(d)) as c_mx, 
                sum(c) over (partition by cid, week(d)) as c_sum, 
                count(c) over (partition by cid, week(d)) as c_cnt, 
                max(week(d)) over (partition by year(d)) as w_max 
    from MCOL3434INNO where year(d) = '2018' order by id;

select distinct cid, week(d) as week, 
                max(c) over (partition by cid, week(d)) as c_mx, 
                sum(c) over (partition by cid, week(d)) as c_sum, 
                count(c) over (partition by cid, week(d)) as c_cnt, 
                max(week(d)) over (partition by year(d)) as w_max 
    from MCOL3434CS where year(d) = '2018' order by id;

--disable_warnings
DROP TABLE IF EXISTS MCOL3434CS;
DROP TABLE IF EXISTS MCOL3434INNO;
--enable_warnings
#