mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-29 21:36:45 +03:00
b6808c97f1
Added logical transformation of the execplan::ParseTrees with the taking out the common factor in expression of the form "(A and B) or (A and C)" for the purposes of passing a TPCH 19 query. Co-authored-by: Leonid Fedorov <leonid.fedorov@mariadb.com>
97 lines
3.6 KiB
Plaintext
97 lines
3.6 KiB
Plaintext
--source ../include/have_columnstore.inc
|
|
|
|
--disable_warnings
|
|
DROP DATABASE IF EXISTS mcol4530;
|
|
--enable_warnings
|
|
|
|
CREATE DATABASE mcol4530;
|
|
USE mcol4530;
|
|
|
|
|
|
create table part (
|
|
p_partkey int,
|
|
p_name varchar (55),
|
|
p_mfgr char (25),
|
|
p_brand char (10),
|
|
p_type varchar (25),
|
|
p_size int,
|
|
p_container char (10),
|
|
p_retailprice decimal(12,2),
|
|
p_comment varchar (23)
|
|
)engine=columnstore;
|
|
|
|
create table lineitem (
|
|
l_orderkey int,
|
|
l_partkey int,
|
|
l_suppkey int,
|
|
l_linenumber bigint,
|
|
l_quantity decimal(12,2),
|
|
l_extendedprice decimal(12,2),
|
|
l_discount decimal(12,2),
|
|
l_tax decimal(12,2),
|
|
l_returnflag char (1),
|
|
l_linestatus char (1),
|
|
l_shipdate date,
|
|
l_commitdate date,
|
|
l_receiptdate date,
|
|
l_shipinstruct char (25),
|
|
l_shipmode char (10),
|
|
l_comment varchar (44)
|
|
)engine=columnstore;
|
|
|
|
INSERT INTO part VALUES
|
|
(1, 'goldenrod lavender spring chocolate lace', 'Manufacturer#1', 'Brand#13', 'PROMO BURNISHED COPPER', 7, 'JUMBO PKG', 901.00, 'ly. slyly ironi'),
|
|
(2, 'blush thistle blue yellow saddle', 'Manufacturer#1', 'Brand#13', 'LARGE BRUSHED BRASS', 1, 'LG CASE', 902.00, 'lar accounts amo'),
|
|
(3, 'spring green yellow purple cornsilk', 'Manufacturer#4', 'Brand#42', 'STANDARD POLISHED BRASS', 21, 'WRAP CASE', 903.00, 'egular deposits hag'),
|
|
(4, 'cornflower chocolate smoke green pink', 'Manufacturer#3', 'Brand#34', 'SMALL PLATED BRASS', 14, 'MED DRUM', 904.00, 'p furiously r'),
|
|
(5, 'forest brown coral puff cream', 'Manufacturer#3', 'Brand#32', 'STANDARD POLISHED TIN', 15, 'SM PKG', 905.00, 'wake carefully');
|
|
|
|
INSERT INTO lineitem VALUES
|
|
(1, 156, 4, 1, 17, 17954.55, 0.04, 0.02, 'N', 'O', '1996-03-13', '1996-02-12', '1996-03-22', 'DELIVER IN PERSON', 'TRUCK', 'egular courts above the'),
|
|
(1, 68, 9, 2, 36, 34850.16, 0.09, 0.06, 'N', 'O', '1996-04-12', '1996-02-28', '1996-04-20', 'TAKE BACK RETURN', 'MAIL', 'ly final dependencies: slyly bold'),
|
|
(1, 64, 5, 3, 8, 7712.48, 0.10, 0.02, 'N', 'O', '1996-01-29', '1996-03-05', '1996-01-31', 'TAKE BACK RETURN', 'REG AIR', 'riously. regular, express dep'),
|
|
(1, 3, 6, 4, 28, 25284.00, 0.09, 0.06, 'N', 'O', '1996-04-21', '1996-03-30', '1996-05-16', 'NONE', 'AIR', 'lites. fluffily even de'),
|
|
(1, 25, 8, 5, 24, 22200.48, 0.10, 0.04, 'N', 'O', '1996-03-30', '1996-03-14', '1996-04-01', 'NONE', 'FOB', 'pending foxes. slyly re'),
|
|
(1, 16, 3, 6, 32, 29312.32, 0.07, 0.02, 'N', 'O', '1996-01-30', '1996-02-07', '1996-02-03', 'DELIVER IN PERSON', 'MAIL', 'arefully slyly ex'),
|
|
(2, 107, 2, 1, 38, 38269.80, 0.00, 0.05, 'N', 'O', '1997-01-28', '1997-01-14', '1997-02-02', 'TAKE BACK RETURN', 'RAIL', 'ven requests. deposits breach a');
|
|
|
|
|
|
select
|
|
sum(l_extendedprice* (1 - l_discount)) as revenue
|
|
from
|
|
lineitem,
|
|
part
|
|
where
|
|
(
|
|
p_partkey = l_partkey
|
|
and p_brand = 'Brand#23'
|
|
and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')
|
|
and l_quantity >= 2 and l_quantity <= 2 + 10
|
|
and p_size between 1 and 5
|
|
and l_shipmode in ('AIR', 'AIR REG')
|
|
and l_shipinstruct = 'DELIVER IN PERSON'
|
|
)
|
|
or
|
|
(
|
|
p_partkey = l_partkey
|
|
and p_brand = 'Brand#24'
|
|
and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')
|
|
and l_quantity >= 20 and l_quantity <= 20 + 10
|
|
and p_size between 1 and 10
|
|
and l_shipmode in ('AIR', 'AIR REG')
|
|
and l_shipinstruct = 'DELIVER IN PERSON'
|
|
)
|
|
or
|
|
(
|
|
p_partkey = l_partkey
|
|
and p_brand = 'Brand#44'
|
|
and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')
|
|
and l_quantity >= 26 and l_quantity <= 26 + 10
|
|
and p_size between 1 and 15
|
|
and l_shipmode in ('AIR', 'AIR REG')
|
|
and l_shipinstruct = 'DELIVER IN PERSON'
|
|
);
|
|
|
|
|
|
DROP DATABASE mcol4530;
|