mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-21 19:45:56 +03:00
73 lines
2.4 KiB
Plaintext
73 lines
2.4 KiB
Plaintext
# -------------------------------------------------------------- #
|
|
# Test case migrated from regression test suite: bug2977.sql
|
|
#
|
|
# Author: Daniel Lee, daniel.lee@mariadb.com
|
|
# -------------------------------------------------------------- #
|
|
#
|
|
--source ../include/have_columnstore.inc
|
|
#
|
|
USE tpch1;
|
|
#
|
|
--disable_warnings
|
|
drop table if exists `shipamounts`;
|
|
drop table if exists `ship1`;
|
|
drop table if exists `ship2`;
|
|
--enable_warnings
|
|
|
|
CREATE TABLE `shipamounts` (
|
|
`OrderNum` varchar(50) DEFAULT NULL,
|
|
`OrderLine` int(11) DEFAULT NULL,
|
|
`ShippingAmount` decimal(10,2) DEFAULT NULL,
|
|
`ShippingDiscountAmount` decimal(10,2) DEFAULT NULL,
|
|
`ShippingOrderAmount` decimal(10,2) DEFAULT NULL,
|
|
`ShippingDiscountOrderAmount` decimal(10,2) DEFAULT NULL
|
|
) engine=columnstore DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE `ship1` (
|
|
`OrderNum` varchar(50) DEFAULT NULL,
|
|
`OrderLine` int(11) DEFAULT NULL,
|
|
`ShippingAmount` decimal(10,2) DEFAULT NULL,
|
|
`ShippingOrderAmount` decimal(10,2) DEFAULT NULL,
|
|
`ShippingDiscountAmount` decimal(10,2) DEFAULT NULL,
|
|
`ShippingDiscountOrderAmount` decimal(10,2) DEFAULT NULL
|
|
) engine=columnstore DEFAULT CHARSET=latin1;
|
|
|
|
CREATE TABLE `ship2` (
|
|
`OrderNum` varchar(50) DEFAULT NULL,
|
|
`OrderLine` int(11) DEFAULT NULL,
|
|
`ShippingAmount` decimal(10,2) DEFAULT NULL,
|
|
`ShippingOrderAmount` decimal(10,2) DEFAULT NULL,
|
|
`ShippingDiscountAmount` decimal(10,2) DEFAULT NULL,
|
|
`ShippingDiscountOrderAmount` decimal(10,2) DEFAULT NULL
|
|
) engine=columnstore DEFAULT CHARSET=latin1;
|
|
|
|
insert into shipamounts values (2357203684, 352338, 0, 0, 0, 0);
|
|
insert into ship1 values (2357203684, 352338, 0, 0, 0, 0);
|
|
insert into ship2 values (2357203684, 352338, 0, 0, 0, 0);
|
|
|
|
SELECT s.OrderNum,
|
|
s.OrderLine,
|
|
s.ShippingAmount,
|
|
s.ShippingOrderAmount,
|
|
total.ShippingTotalAmount,
|
|
s.ShippingDiscountAmount,
|
|
s.ShippingDiscountOrderAmount,
|
|
total.ShippingDiscountTotalAmount,
|
|
IF( s.OrderLine = MIN(t.OrderLine), 1, 0 )
|
|
FROM shipAmounts AS s
|
|
LEFT OUTER JOIN ship1 AS t ON s.OrderNum = t.OrderNum
|
|
LEFT OUTER JOIN
|
|
(SELECT OrderNum, SUM(ShippingAmount) AS ShippingTotalAmount,
|
|
SUM(ShippingDiscountAmount) AS ShippingDiscountTotalAmount
|
|
FROM ship2
|
|
GROUP BY OrderNum) AS total ON s.OrderNum = total.OrderNum
|
|
GROUP BY s.OrderNum,s.OrderLine,3,4,5,6,7,8;
|
|
|
|
--disable_warnings
|
|
drop table if exists shipamounts;
|
|
drop table if exists ship1;
|
|
drop table if exists ship2;
|
|
--enable_warnings
|
|
#
|
|
|