# -------------------------------------------------------------- # # Test case migrated from regression test suite: bug6065.sql # # Author: Daniel Lee, daniel.lee@mariadb.com # -------------------------------------------------------------- # # --source ../include/have_columnstore.inc # USE tpch1; # --disable_warnings --disable_warnings DROP TABLE IF EXISTS `pv_facts`; --enable_warnings # CREATE TABLE `pv_facts` ( `page_view_id` varchar(35) NOT NULL, `visit_date_key` date NOT NULL, `visit_time_key` varchar(6) DEFAULT '000000', `visitor_key` int unsigned NOT NULL, `user_key` int unsigned NOT NULL, `referring_url_key` int unsigned NOT NULL, `url_key` int unsigned NOT NULL, `publication_key` int unsigned NOT NULL, `eref_key` int unsigned NOT NULL, `iref_key` int unsigned NOT NULL, `content_key` int unsigned NOT NULL, `device_key` int unsigned NOT NULL, `visit_time` decimal(18,5) NOT NULL, `visit_id` varchar(100) NOT NULL, `ip_address` bigint NOT NULL, `visit_type` varchar(20) NOT NULL, `page_type` varchar(20) NOT NULL, `page_id` varchar(255) DEFAULT NULL, `first_byte` int unsigned DEFAULT NULL, `dom_interactive` int unsigned DEFAULT NULL, `dns_lookup` int unsigned DEFAULT NULL, `dom_done` int unsigned DEFAULT NULL, `small_screen` int unsigned DEFAULT '0', `view_width` int unsigned DEFAULT '0', `view_height` int unsigned DEFAULT '0' ) ENGINE=Columnstore DEFAULT CHARSET=utf8; insert into pv_facts values ('U1mQdAoBCjUAAHbgZcsAAABS','2014-04-24',183012,47544380,0,0,1,3,1,1,0,18,1398378612.15000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','INDEX','existing_user',0,0,0,0,0,1280,652); insert into pv_facts values ('U1mQgwoBCjUAAHbcVCMAAAAm','2014-04-24',183027,47544380,0,1,6,3,1,588,0,18,1398378627.16000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','MISC','Editors\' Choice',0,0,0,0,0,1280,652); insert into pv_facts values ('U1mRDQoBCjcAACltU4MAAAAO','2014-04-24',183245,47544380,0,0,1,3,1,588,0,18,1398378765.38000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','INDEX','existing_user',0,0,0,0,0,1280,652); insert into pv_facts values ('U1mRHAoBCjUAAHfsPr4AAAAI','2014-04-24',183301,47544380,0,1,338,3,1,12967,0,18,1398378780.92000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',747,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mRuQoBCn0AAD1LRyAAAAA7','2014-04-24',183538,47544380,0,338,1065,3,1,7490,0,18,1398378937.80000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',646,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mS1goBCjgAAFPpujQAAAAq','2014-04-24',184022,47544380,0,5530,31,3,1,17208,0,18,1398379222.31000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',49,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mSoQoBCjQAAGlPc44AAABa','2014-04-24',183930,47544380,0,1065,5530,3,1,7490,0,18,1398379169.63000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',646,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mT8woBCjQAAGzYKq4AAABH','2014-04-24',184508,47544380,0,1743587,3734,3,1,15557,0,18,1398379507.53000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',280,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mTAQoBCn0AADG@5EoAAAAq','2014-04-24',184105,47544380,0,31,96,3,1,17208,2211498,18,1398379265.39000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','FEATURE_ARTICLE',1035489,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mTUAoBCjcAABpjRKEAAABE','2014-04-24',184225,47544380,0,96,6,3,1,16465,0,18,1398379344.69000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','MISC','Editors\' Choice',0,0,0,0,0,1280,652); insert into pv_facts values ('U1mTdwoBCjcAAB30SZMAAAAp','2014-04-24',184303,47544380,0,6,254,3,1,17332,0,18,1398379383.19000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',489,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mTwQoBCjYAAB9mJv0AAAAd','2014-04-24',184418,47544380,0,254,1743587,3,1,17332,2211589,18,1398379457.75000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','ARTICLE',1035618,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mU5AoBCjgAAFgaWngAAAA1','2014-04-24',184909,47544380,0,189,1793585,3,1,4553,2212159,18,1398379748.68000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','ARTICLE',1036245,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mU8goBCjUAAHKCH3QAAABJ','2014-04-24',184922,47544380,0,1793585,1808578,3,1,2702,2212384,18,1398379762.04000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','ARTICLE',1036401,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mUZwoBCjYAAB73G9UAAABg','2014-04-24',184704,47544380,0,0,1,3,1,15557,0,18,1398379623.69000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','INDEX','existing_user',0,0,0,0,0,1280,652); insert into pv_facts values ('U1mUcQoBCjQAAG1Uc9YAAAAK','2014-04-24',184714,47544380,0,1,189,3,1,4553,0,18,1398379633.67000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','CATEGORY',280,0,0,0,0,0,1280,652); insert into pv_facts values ('U1mVWgoBCjMAAEh2BRcAAABP','2014-04-24',185106,47544380,0,1808578,6,3,1,2137,0,18,1398379866.03000,'U1mQdAoBCjUAAHbgZcsAAABS',1136145639,'DIRECT','MISC','Editors\' Choice',0,0,0,0,0,1280,652); # select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; select distinct * from ( select visit_id, first_value(visit_time) over (partition by visit_id order by visit_time asc) as start_time, first_value(visit_time) over (partition by visit_id order by visit_time desc) as end_time, first_value(eref_key) over (partition by visit_id order by visit_time asc) as landing_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time asc) as landing_iref_key, first_value(page_id) over (partition by visit_id order by visit_time asc) as landing_page_id, first_value(page_type) over (partition by visit_id order by visit_time asc) as landing_page_type, first_value(eref_key) over (partition by visit_id order by visit_time desc) as exit_eref_key, first_value(iref_key) over (partition by visit_id order by visit_time desc) as exit_iref_key, first_value(page_id) over (partition by visit_id order by visit_time desc) as exit_page_id, first_value(page_type) over (partition by visit_id order by visit_time desc) as exit_page_type, count(*) over (partition by visit_id ) as pvs, 0 as clicks from pv_facts where visit_id='U1mQdAoBCjUAAHbgZcsAAABS') a; # --disable_warnings DROP TABLE `pv_facts`; #