You've already forked mariadb-columnstore-engine
mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-10-31 18:30:33 +03:00
115 lines
4.4 KiB
Plaintext
115 lines
4.4 KiB
Plaintext
# -------------------------------------------------------------- #
|
|
# 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
|
|
#
|
|
|