mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-18 21:44:02 +03:00
311 lines
10 KiB
Plaintext
311 lines
10 KiB
Plaintext
# -------------------------------------------------------------- #
|
|
# Test case migrated from regression test suite: bug3719.sql
|
|
#
|
|
# Author: Daniel Lee, daniel.lee@mariadb.com
|
|
# -------------------------------------------------------------- #
|
|
#
|
|
--source ../include/have_columnstore.inc
|
|
#
|
|
USE tpch1;
|
|
#
|
|
--disable_warnings
|
|
create database if not exists geo;
|
|
--enable_warnings
|
|
use geo;
|
|
|
|
--disable_warnings
|
|
drop table if exists `geo_tag_values`;
|
|
drop table if exists `geo_tags`;
|
|
--enable_warnings
|
|
|
|
CREATE TABLE `geo_tag_values` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`value` varchar(255) DEFAULT NULL,
|
|
`geo_tag_id` int(11) DEFAULT NULL
|
|
) engine=columnstore;
|
|
|
|
CREATE TABLE `geo_tags` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`name` varchar(255) DEFAULT NULL,
|
|
`deleted_at` datetime DEFAULT NULL,
|
|
`created_at` datetime DEFAULT NULL,
|
|
`updated_at` datetime DEFAULT NULL,
|
|
`lock_version` int(11) DEFAULT NULL
|
|
) engine=columnstore;
|
|
|
|
--disable_warnings
|
|
create database if not exists pbkt;
|
|
--enable_warnings
|
|
|
|
use pbkt;
|
|
--disable_warnings
|
|
drop table if exists `areas`;
|
|
drop table if exists `bigsumplus`;
|
|
drop table if exists `dim_date`;
|
|
drop table if exists `sizes`;
|
|
drop table if exists `tag_values`;
|
|
drop view if exists `tag_values_geo_country`;
|
|
drop view if exists `tag_values_geo_region`;
|
|
drop view if exists `tag_values_ptype`;
|
|
--enable_warnings
|
|
|
|
CREATE TABLE `areas` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`name` varchar(255) DEFAULT NULL,
|
|
`deleted_at` datetime DEFAULT NULL,
|
|
`created_at` datetime DEFAULT NULL,
|
|
`updated_at` datetime DEFAULT NULL,
|
|
`lock_version` int(11) DEFAULT NULL
|
|
) engine=columnstore;
|
|
|
|
CREATE TABLE `bigsumplus` (
|
|
`served` int(20) DEFAULT NULL,
|
|
`clicked` int(11) DEFAULT NULL,
|
|
`acted` int(11) DEFAULT NULL,
|
|
`cdate` date DEFAULT NULL,
|
|
`flight_id` int(11) DEFAULT NULL,
|
|
`flight_creative_id` int(11) DEFAULT NULL,
|
|
`geo_country` int(11) DEFAULT NULL,
|
|
`geo_region` int(11) DEFAULT NULL,
|
|
`geo_city` int(11) DEFAULT NULL,
|
|
`geo_zip_code_text` int(11) DEFAULT NULL,
|
|
`age` int(11) DEFAULT NULL,
|
|
`area` int(11) DEFAULT NULL,
|
|
`site` int(11) DEFAULT NULL,
|
|
`gender` int(11) DEFAULT NULL,
|
|
`reg_zip` int(11) DEFAULT NULL,
|
|
`geo_metro_code` int(11) DEFAULT NULL,
|
|
`sic_code` int(11) DEFAULT NULL,
|
|
`track` int(11) DEFAULT NULL,
|
|
`ptype` int(11) DEFAULT NULL,
|
|
`search_kw` int(11) DEFAULT NULL,
|
|
`user_kw` int(11) DEFAULT NULL,
|
|
`cam_make` int(11) DEFAULT NULL,
|
|
`cam_model` int(11) DEFAULT NULL,
|
|
`cam_type` int(11) DEFAULT NULL,
|
|
`m_manuf` int(11) DEFAULT NULL,
|
|
`m_carrier` int(11) DEFAULT NULL,
|
|
`login` int(11) DEFAULT NULL,
|
|
`ref_domain` int(11) DEFAULT NULL,
|
|
`linked_domains` int(11) DEFAULT NULL,
|
|
`sp` int(11) DEFAULT NULL,
|
|
`spon` int(11) DEFAULT NULL,
|
|
`agency_id` int(11) DEFAULT NULL,
|
|
`advertiser_id` int(11) DEFAULT NULL,
|
|
`campaign_id` int(11) DEFAULT NULL,
|
|
`product_id` int(11) DEFAULT NULL,
|
|
`creative_id` int(11) DEFAULT NULL,
|
|
`month_id` int(2) DEFAULT NULL,
|
|
`day_of_week_id` int(1) DEFAULT NULL,
|
|
`quarter_id` int(1) DEFAULT NULL,
|
|
`size_id` int(11) DEFAULT NULL,
|
|
`tier_id` int(11) DEFAULT NULL,
|
|
`cost_impressions` decimal(7,2) DEFAULT NULL,
|
|
`cost_actions` decimal(7,2) DEFAULT NULL,
|
|
`cost_clicks` decimal(7,2) DEFAULT NULL,
|
|
`bonus_impressions` int(11) DEFAULT NULL,
|
|
`flight_type_id` int(10) DEFAULT NULL,
|
|
`flight_status_id` int(10) DEFAULT NULL,
|
|
`bonus_actions` int(11) DEFAULT NULL,
|
|
`bonus_clicks` int(11) DEFAULT NULL,
|
|
`imp_goal` int(11) DEFAULT NULL
|
|
) engine=columnstore;
|
|
|
|
CREATE TABLE `dim_date` (
|
|
`cdate` date DEFAULT NULL,
|
|
`quarter_num` int(10) DEFAULT NULL,
|
|
`quarter_name` varchar(20) DEFAULT NULL,
|
|
`month_number` int(10) DEFAULT NULL,
|
|
`month_name` varchar(20) DEFAULT NULL,
|
|
`week_of_year` int(10) DEFAULT NULL,
|
|
`day_of_week` varchar(20) DEFAULT NULL,
|
|
`day_of_year` int(10) DEFAULT NULL
|
|
) engine=columnstore;
|
|
|
|
CREATE TABLE `sizes` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`name` varchar(32) DEFAULT NULL,
|
|
`width` int(11) DEFAULT NULL,
|
|
`height` int(11) DEFAULT NULL,
|
|
`deleted_at` datetime DEFAULT NULL,
|
|
`created_at` datetime DEFAULT NULL,
|
|
`updated_at` datetime DEFAULT NULL,
|
|
`lock_version` int(11) DEFAULT NULL
|
|
) engine=columnstore;
|
|
|
|
CREATE TABLE `tag_values` (
|
|
`id` int(11) DEFAULT NULL,
|
|
`value` varchar(255) DEFAULT NULL,
|
|
`tag_id` int(11) DEFAULT NULL,
|
|
`deleted_at` datetime DEFAULT NULL,
|
|
`created_at` datetime DEFAULT NULL,
|
|
`updated_at` datetime DEFAULT NULL
|
|
) engine=columnstore;
|
|
|
|
create view `tag_values_geo_country` AS
|
|
select `t`.`id` AS `id`,`t`.`value` AS `value`,`t`.`geo_tag_id` AS `tag_id`
|
|
from ( `geo`.`geo_tag_values` `t` join
|
|
`geo`.`geo_tags` `tag`)
|
|
where ((`t`.`geo_tag_id` = `tag`.`id`) and (`tag`.`name` = 'geo_country'));
|
|
|
|
create view `tag_values_geo_region` AS
|
|
select `t`.`id` AS `id`,`t`.`value` AS `value`,`t`.`geo_tag_id` AS `tag_id`
|
|
from (`geo`.`geo_tag_values` `t` join `geo`.`geo_tags` `tag`)
|
|
where ((`t`.`geo_tag_id` = `tag`.`id`) and (`tag`.`name` = 'geo_region'));
|
|
|
|
create view `tag_values_ptype` AS
|
|
select `t`.`id` AS `id`,`t`.`value` AS `value`,`t`.`tag_id` AS `tag_id`,`t`.`deleted_at` AS
|
|
`deleted_at`,`t`.`created_at` AS `created_at`,`t`.`updated_at` AS `updated_at`
|
|
from `tag_values` `t`
|
|
where (`t`.`tag_id` = 5);
|
|
|
|
use pbkt;
|
|
|
|
select
|
|
`areas`.`id` as `c0`,
|
|
`areas`.`name` as `c1`,
|
|
`tag_values_ptype`.`id` as `c2`,
|
|
`tag_values_ptype`.`value` as `c3`,
|
|
`sizes`.`id` as `c4`,
|
|
`sizes`.`name` as `c5`,
|
|
`tag_values_geo_region`.`id` as `c6`,
|
|
`tag_values_geo_region`.`value` as `c7`,
|
|
`tag_values_geo_country`.`id` as `c8`,
|
|
`tag_values_geo_country`.`value` as `c9`,
|
|
`dim_date`.`cdate` as `c10`
|
|
from
|
|
`areas` as `areas`,
|
|
`bigsumplus` as `bigsumplus`,
|
|
`tag_values_ptype` as `tag_values_ptype`,
|
|
`sizes` as `sizes`,
|
|
`tag_values_geo_region` as `tag_values_geo_region`,
|
|
`tag_values_geo_country` as `tag_values_geo_country`,
|
|
`dim_date` as `dim_date`
|
|
where `bigsumplus`.`area` = `areas`.`id` and
|
|
`bigsumplus`.`ptype` = `tag_values_ptype`.`id` and
|
|
`bigsumplus`.`size_id` = `sizes`.`id` and
|
|
`bigsumplus`.`geo_region` = `tag_values_geo_region`.`id` and
|
|
`bigsumplus`.`geo_country` = `tag_values_geo_country`.`id` and
|
|
`bigsumplus`.`cdate` = `dim_date`.`cdate` and
|
|
(`tag_values_geo_country`.`id` = '26088') and (`dim_date`.`cdate` in
|
|
(DATE '2011-04-01', DATE '2011-04-02', DATE '2011-04-03', DATE '2011-04-04', DATE '2011-04-05', DATE
|
|
'2011-04-06',
|
|
DATE '2011-04-07', DATE '2011-04-08', DATE '2011-04-09', DATE '2011-04-10', DATE '2011-04-11', DATE
|
|
'2011-04-12', DATE '2011-04-13',
|
|
DATE '2011-04-14', DATE '2011-04-15', DATE '2011-04-16', DATE '2011-04-17', DATE '2011-04-18', DATE
|
|
'2011-04-19', DATE '2011-04-20',
|
|
DATE '2011-04-21', DATE '2011-04-22', DATE '2011-04-23', DATE '2011-04-24', DATE '2011-04-25', DATE
|
|
'2011-04-26', DATE '2011-04-27',
|
|
DATE '2011-04-28', DATE '2011-04-29', DATE '2011-04-30'))
|
|
group by `areas`.`id`, `areas`.`name`, `tag_values_ptype`.`id`, `tag_values_ptype`.`value`, `sizes`.`id`,
|
|
`sizes`.`name`, `tag_values_geo_region`.`id`, `tag_values_geo_region`.`value`, `tag_values_geo_country`.`id`,
|
|
`tag_values_geo_country`.`value`, `dim_date`.`cdate` order by ISNULL(`areas`.`id`), `areas`.`id` ASC,
|
|
ISNULL(`tag_values_ptype`.`id`), `tag_values_ptype`.`id` ASC, ISNULL(`sizes`.`id`), `sizes`.`id` ASC,
|
|
ISNULL(`tag_values_geo_region`.`id`), `tag_values_geo_region`.`id` ASC, ISNULL(`tag_values_geo_country`.`id`),
|
|
`tag_values_geo_country`.`id` ASC, ISNULL(`dim_date`.`cdate`), `dim_date`.`cdate` ASC;
|
|
|
|
select
|
|
`areas`.`id` as `c0`,
|
|
`areas`.`name` as `c1`,
|
|
`tag_values_ptype`.`id` as `c2`,
|
|
`tag_values_ptype`.`value` as `c3`,
|
|
`sizes`.`id` as `c4`,
|
|
`sizes`.`name` as `c5`,
|
|
`tag_values_geo_region`.`id` as `c6`,
|
|
`tag_values_geo_region`.`value` as `c7`,
|
|
`tag_values_geo_country`.`id` as `c8`,
|
|
`tag_values_geo_country`.`value` as `c9`,
|
|
`dim_date`.`cdate` as `c10`
|
|
from
|
|
`areas` as `areas`,
|
|
`bigsumplus` as `bigsumplus`,
|
|
`tag_values_ptype` as `tag_values_ptype`,
|
|
`sizes` as `sizes`,
|
|
`tag_values_geo_region` as `tag_values_geo_region`,
|
|
`tag_values_geo_country` as `tag_values_geo_country`,
|
|
`dim_date` as `dim_date`
|
|
where
|
|
`bigsumplus`.`area` = `areas`.`id` and
|
|
`bigsumplus`.`ptype` = `tag_values_ptype`.`id` and
|
|
`bigsumplus`.`size_id` = `sizes`.`id` and
|
|
`bigsumplus`.`geo_region` = `tag_values_geo_region`.`id` and
|
|
`bigsumplus`.`geo_country` = `tag_values_geo_country`.`id` and
|
|
`bigsumplus`.`cdate` = `dim_date`.`cdate` and
|
|
(`tag_values_geo_country`.`id` = '26088') and (`dim_date`.`cdate` in
|
|
(DATE '2011-04-01', DATE '2011-04-02', DATE '2011-04-03', DATE '2011-04-04', DATE '2011-04-05', DATE
|
|
'2011-04-06',
|
|
DATE '2011-04-07', DATE '2011-04-08', DATE '2011-04-09', DATE '2011-04-10', DATE '2011-04-11', DATE
|
|
'2011-04-12', DATE '2011-04-13',
|
|
DATE '2011-04-14', DATE '2011-04-15', DATE '2011-04-16', DATE '2011-04-17', DATE '2011-04-18', DATE
|
|
'2011-04-19', DATE '2011-04-20',
|
|
DATE '2011-04-21', DATE '2011-04-22', DATE '2011-04-23', DATE '2011-04-24', DATE '2011-04-25', DATE
|
|
'2011-04-26', DATE '2011-04-27',
|
|
DATE '2011-04-28', DATE '2011-04-29', DATE '2011-04-30'))
|
|
/*
|
|
group by
|
|
`areas`.`id`,
|
|
`areas`.`name`,
|
|
`tag_values_ptype`.`id`,
|
|
`tag_values_ptype`.`value`,
|
|
`sizes`.`id`,
|
|
`sizes`.`name`,
|
|
`tag_values_geo_region`.`id`,
|
|
`tag_values_geo_region`.`value`,
|
|
`tag_values_geo_country`.`id`,
|
|
`tag_values_geo_country`.`value`,
|
|
`dim_date`.`cdate`
|
|
*/
|
|
order by
|
|
ISNULL(`areas`.`id`)
|
|
/*
|
|
,
|
|
`areas`.`id` ASC,
|
|
ISNULL(`tag_values_ptype`.`id`),
|
|
`tag_values_ptype`.`id` ASC,
|
|
ISNULL(`sizes`.`id`), `sizes`.`id` ASC,
|
|
ISNULL(`tag_values_geo_region`.`id`),
|
|
`tag_values_geo_region`.`id` ASC,
|
|
ISNULL(`tag_values_geo_country`.`id`),
|
|
`tag_values_geo_country`.`id` ASC,
|
|
ISNULL(`dim_date`.`cdate`),
|
|
`dim_date`.`cdate` ASC
|
|
*/
|
|
;
|
|
|
|
select
|
|
t.value
|
|
from
|
|
`tag_values_ptype` t,
|
|
`tag_values_ptype` t1,
|
|
`tag_values_geo_country`
|
|
where
|
|
t.`id` = `tag_values_geo_country`.`id` and
|
|
t.`id` = t1.`id`
|
|
order by
|
|
ISNULL(t1.`value`);
|
|
|
|
select `tag_values_ptype`.value from (select * from tag_values_ptype) t1, tag_values_ptype
|
|
where tag_values_ptype.id = t1.id;
|
|
|
|
use geo;
|
|
--disable_warnings
|
|
drop table `geo_tag_values`;
|
|
drop table `geo_tags`;
|
|
--enable_warnings
|
|
|
|
use pbkt;
|
|
--disable_warnings
|
|
drop table `areas`;
|
|
drop table `bigsumplus`;
|
|
drop table `dim_date`;
|
|
drop table `sizes`;
|
|
drop table `tag_values`;
|
|
drop view `tag_values_geo_country`;
|
|
drop view `tag_values_geo_region`;
|
|
drop view `tag_values_ptype`;
|
|
drop database geo;
|
|
drop database pbkt;
|
|
--enable_warnings
|
|
#
|
|
|