This is an automated email from the ASF dual-hosted git repository.
kxiao pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/branch-2.0 by this push:
new 470e00ee8e [Bug](storage )fix dead lock when create_tablet need lock
two tablet && update mv_p0… (#21969)
470e00ee8e is described below
commit 470e00ee8e3c9820ced5f74d8a3f585d46e72330
Author: Pxl <[email protected]>
AuthorDate: Sat Jul 22 15:27:05 2023 +0800
[Bug](storage )fix dead lock when create_tablet need lock two tablet &&
update mv_p0… (#21969)
fix dead lock when create_tablet need lock two tablet && update mv_p0/ssb
case
---
be/src/olap/tablet_manager.cpp | 25 +++--
be/src/olap/tablet_manager.h | 2 +
be/src/vec/sink/vtablet_sink.cpp | 34 +++---
.../ssb/multiple_no_where/multiple_no_where.out | 4 +-
.../multiple_ssb.out} | 4 +-
.../q_4_1_r1.out} | 9 --
.../doris/regression/action/CreateMVAction.groovy | 2 +-
.../ssb/multiple_no_where/multiple_no_where.groovy | 4 +-
.../multiple_ssb.groovy} | 42 +++++---
.../q_4_1_r1.groovy} | 119 ++-------------------
10 files changed, 81 insertions(+), 164 deletions(-)
diff --git a/be/src/olap/tablet_manager.cpp b/be/src/olap/tablet_manager.cpp
index 4f1c7ecf5e..8b68e62406 100644
--- a/be/src/olap/tablet_manager.cpp
+++ b/be/src/olap/tablet_manager.cpp
@@ -244,7 +244,18 @@ Status TabletManager::create_tablet(const
TCreateTabletReq& request, std::vector
int64_t tablet_id = request.tablet_id;
LOG(INFO) << "begin to create tablet. tablet_id=" << tablet_id;
- std::lock_guard<std::shared_mutex>
wrlock(_get_tablets_shard_lock(tablet_id));
+ // when we create rollup tablet A(assume on shard-1) from tablet B(assume
on shard-2)
+ // we need use write lock on shard-1 and then use read lock on shard-2
+ // if there have create rollup tablet C(assume on shard-2) from tablet
D(assume on shard-1) at the same time, we will meet deadlock
+ std::unique_lock two_tablet_lock(_two_tablet_mtx, std::defer_lock);
+ bool is_schema_change = request.__isset.base_tablet_id &&
request.base_tablet_id > 0;
+ bool need_two_lock = is_schema_change && ((_tablets_shards_mask &
request.base_tablet_id) !=
+ (_tablets_shards_mask &
tablet_id));
+ if (need_two_lock) {
+ two_tablet_lock.lock();
+ }
+
+ std::lock_guard wrlock(_get_tablets_shard_lock(tablet_id));
// Make create_tablet operation to be idempotent:
// 1. Return true if tablet with same tablet_id and schema_hash exist;
// false if tablet with same tablet_id but different schema_hash
exist.
@@ -258,13 +269,12 @@ Status TabletManager::create_tablet(const
TCreateTabletReq& request, std::vector
}
TabletSharedPtr base_tablet = nullptr;
- bool is_schema_change = false;
// If the CreateTabletReq has base_tablet_id then it is a alter-tablet
request
- if (request.__isset.base_tablet_id && request.base_tablet_id > 0) {
- is_schema_change = true;
+ if (is_schema_change) {
// if base_tablet_id's lock diffrent with new_tablet_id, we need lock
it.
- if ((_tablets_shards_mask & request.base_tablet_id) !=
(_tablets_shards_mask & tablet_id)) {
+ if (need_two_lock) {
base_tablet = get_tablet(request.base_tablet_id);
+ two_tablet_lock.unlock();
} else {
base_tablet = _get_tablet_unlocked(request.base_tablet_id);
}
@@ -550,9 +560,8 @@ std::pair<TabletSharedPtr, Status>
TabletManager::get_tablet_and_status(TTabletI
std::string err;
auto tablet = get_tablet(tablet_id, include_deleted, &err);
if (tablet == nullptr) {
- auto err_str = fmt::format("failed to get tablet: {}, reason: {}",
tablet_id, err);
- LOG(WARNING) << err_str;
- return {tablet, Status::InternalError(err_str)};
+ return {tablet,
+ Status::InternalError("failed to get tablet: {}, reason: {}",
tablet_id, err)};
}
return {tablet, Status::OK()};
diff --git a/be/src/olap/tablet_manager.h b/be/src/olap/tablet_manager.h
index 0ddeb4aaac..f7f41dcef1 100644
--- a/be/src/olap/tablet_manager.h
+++ b/be/src/olap/tablet_manager.h
@@ -250,6 +250,8 @@ private:
tablet_map_t& _get_tablet_map(TTabletId tablet_id);
tablets_shard& _get_tablets_shard(TTabletId tabletId);
+
+ std::mutex _two_tablet_mtx;
};
} // namespace doris
diff --git a/be/src/vec/sink/vtablet_sink.cpp b/be/src/vec/sink/vtablet_sink.cpp
index b0497a14a2..243504ff26 100644
--- a/be/src/vec/sink/vtablet_sink.cpp
+++ b/be/src/vec/sink/vtablet_sink.cpp
@@ -140,15 +140,14 @@ public:
std::atomic<bool> packet_in_flight {false};
};
-IndexChannel::~IndexChannel() {}
+IndexChannel::~IndexChannel() = default;
Status IndexChannel::init(RuntimeState* state, const
std::vector<TTabletWithPartition>& tablets) {
SCOPED_CONSUME_MEM_TRACKER(_index_channel_tracker.get());
for (auto& tablet : tablets) {
auto location = _parent->_location->find_tablet(tablet.tablet_id);
if (location == nullptr) {
- LOG(WARNING) << "unknown tablet, tablet_id=" << tablet.tablet_id;
- return Status::InternalError("unknown tablet");
+ return Status::InternalError("unknown tablet, tablet_id={}",
tablet.tablet_id);
}
std::vector<std::shared_ptr<VNodeChannel>> channels;
for (auto& node_id : location->node_ids) {
@@ -252,14 +251,12 @@ Status
IndexChannel::check_tablet_received_rows_consistency() {
continue;
}
if (tablet.second[i].second != tablet.second[0].second) {
- LOG(WARNING) << "rows num doest't match, load_id: " <<
_parent->_load_id
- << ", txn_id: " <<
std::to_string(_parent->_txn_id)
- << ", tablt_id: " << tablet.first
- << ", node_id: " << tablet.second[i].first
- << ", rows_num: " << tablet.second[i].second
- << ", node_id: " << tablet.second[0].first
- << ", rows_num: " << tablet.second[0].second;
- return Status::InternalError("rows num written by multi
replicas doest't match");
+ return Status::InternalError(
+ "rows num written by multi replicas doest't match,
load_id={}, txn_id={}, "
+ "tablt_id={}, node_id={}, rows_num={}, node_id={},
rows_num={}",
+ print_id(_parent->_load_id), _parent->_txn_id,
tablet.first,
+ tablet.second[i].first, tablet.second[i].second,
tablet.second[0].first,
+ tablet.second[0].second);
}
}
}
@@ -321,10 +318,9 @@ Status VNodeChannel::init(RuntimeState* state) {
_stub =
state->exec_env()->brpc_internal_client_cache()->get_client(_node_info.host,
_node_info.brpc_port);
if (_stub == nullptr) {
- LOG(WARNING) << "Get rpc stub failed, host=" << _node_info.host
- << ", port=" << _node_info.brpc_port << ", " <<
channel_info();
_cancelled = true;
- return Status::InternalError("get rpc stub failed");
+ return Status::InternalError("Get rpc stub failed, host={}, port={},
info={}",
+ _node_info.host, _node_info.brpc_port,
channel_info());
}
_rpc_timeout_ms = state->execution_timeout() * 1000;
@@ -394,19 +390,17 @@ Status VNodeChannel::open_wait() {
ExecEnv::GetInstance()->brpc_internal_client_cache()->erase(
_open_closure->cntl.remote_side());
}
- std::stringstream ss;
- ss << "failed to open tablet writer, error=" <<
berror(_open_closure->cntl.ErrorCode())
- << ", error_text=" << _open_closure->cntl.ErrorText();
+
_cancelled = true;
- LOG(WARNING) << ss.str() << " " << channel_info();
auto error_code = _open_closure->cntl.ErrorCode();
auto error_text = _open_closure->cntl.ErrorText();
if (_open_closure->unref()) {
delete _open_closure;
}
_open_closure = nullptr;
- return Status::InternalError("failed to open tablet writer, error={},
error_text={}",
- berror(error_code), error_text);
+ return Status::InternalError(
+ "failed to open tablet writer, error={}, error_text={},
info={}",
+ berror(error_code), error_text, channel_info());
}
Status status(Status::create(_open_closure->result.status()));
if (_open_closure->unref()) {
diff --git
a/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out
b/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out
index dd404cf8ed..00e05ab400 100644
--- a/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out
+++ b/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out
@@ -15,10 +15,10 @@
-- !select_q_2_1 --
1 1993 brand
--- !select_mv --
+-- !select_q_3_1 --
ASIA ASIA 1992 1
--- !select_mv --
+-- !select_q_4_1 --
0 nation 0
1993 nation 0
diff --git
a/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out
b/regression-test/data/mv_p0/ssb/multiple_ssb/multiple_ssb.out
similarity index 97%
copy from regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out
copy to regression-test/data/mv_p0/ssb/multiple_ssb/multiple_ssb.out
index dd404cf8ed..00e05ab400 100644
--- a/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out
+++ b/regression-test/data/mv_p0/ssb/multiple_ssb/multiple_ssb.out
@@ -15,10 +15,10 @@
-- !select_q_2_1 --
1 1993 brand
--- !select_mv --
+-- !select_q_3_1 --
ASIA ASIA 1992 1
--- !select_mv --
+-- !select_q_4_1 --
0 nation 0
1993 nation 0
diff --git
a/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out
b/regression-test/data/mv_p0/ssb/q_4_1_r1/q_4_1_r1.out
similarity index 94%
copy from regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out
copy to regression-test/data/mv_p0/ssb/q_4_1_r1/q_4_1_r1.out
index dd404cf8ed..497ac655b1 100644
--- a/regression-test/data/mv_p0/ssb/multiple_no_where/multiple_no_where.out
+++ b/regression-test/data/mv_p0/ssb/q_4_1_r1/q_4_1_r1.out
@@ -9,15 +9,6 @@
19930101 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2023-06-09 shipmode
name address city nation region phone mktsegment name address
city nation region phone name mfgr category brand color
type 4 container
19930101 2 2 2 2 2 2 2 2
2 2 2 2 2 2 2023-06-09 shipmode
name address city nation region phone mktsegment name address
city nation region phone name mfgr category brand color
type 4 container
--- !select_q_1_1 --
-12
-
--- !select_q_2_1 --
-1 1993 brand
-
--- !select_mv --
-ASIA ASIA 1992 1
-
-- !select_mv --
0 nation 0
1993 nation 0
diff --git
a/regression-test/framework/src/main/groovy/org/apache/doris/regression/action/CreateMVAction.groovy
b/regression-test/framework/src/main/groovy/org/apache/doris/regression/action/CreateMVAction.groovy
index 2b77a8815e..588bb2d01a 100644
---
a/regression-test/framework/src/main/groovy/org/apache/doris/regression/action/CreateMVAction.groovy
+++
b/regression-test/framework/src/main/groovy/org/apache/doris/regression/action/CreateMVAction.groovy
@@ -64,7 +64,7 @@ class CreateMVAction implements SuiteAction {
sqlResult = tmp.result[0]
log.info("result: ${sqlResult}".toString())
if (tryTimes == 60 || sqlResult.contains("CANCELLED")) {
- throw new IllegalStateException("MV create check times over
limit");
+ throw new IllegalStateException("MV create check times over
limit, result='${sqlResult}'");
}
Thread.sleep(1200)
tryTimes++
diff --git
a/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy
b/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy
index 36f6fc634a..b92b37c5e1 100644
---
a/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy
+++
b/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy
@@ -187,7 +187,7 @@ suite ("multiple_no_where") {
ORDER BY YEAR ASC, revenue DESC;""")
contains "(lineorder_q_3_1)"
}
- qt_select_mv """SELECT
+ qt_select_q_3_1 """SELECT
C_NATION,
S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR,
SUM(LO_REVENUE) AS revenue
@@ -213,7 +213,7 @@ suite ("multiple_no_where") {
ORDER BY YEAR ASC, C_NATION ASC;""")
contains "(lineorder_q_4_1)"
}
- qt_select_mv """SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
+ qt_select_q_4_1 """SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
C_NATION,
SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
diff --git
a/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy
b/regression-test/suites/mv_p0/ssb/multiple_ssb/multiple_ssb.groovy
similarity index 91%
copy from
regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy
copy to regression-test/suites/mv_p0/ssb/multiple_ssb/multiple_ssb.groovy
index 36f6fc634a..3a137a2a8f 100644
---
a/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy
+++ b/regression-test/suites/mv_p0/ssb/multiple_ssb/multiple_ssb.groovy
@@ -17,7 +17,7 @@
import org.codehaus.groovy.runtime.IOGroovyMethods
-suite ("multiple_no_where") {
+suite ("multiple_ssb") {
sql """ DROP TABLE IF EXISTS lineorder_flat; """
sql """set enable_nereids_planner=true"""
sql """SET enable_fallback_to_original_planner=false"""
@@ -93,32 +93,50 @@ suite ("multiple_no_where") {
}
createMV ("""create materialized view lineorder_q_1_1 as
- SELECT LO_ORDERKEY, LO_ORDERDATE, LO_DISCOUNT, LO_QUANTITY,
SUM(LO_EXTENDEDPRICE * LO_DISCOUNT)
- FROM lineorder_flat GROUP BY
- LO_ORDERKEY, LO_ORDERDATE, LO_DISCOUNT, LO_QUANTITY;""")
+ SELECT LO_ORDERKEY, SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS
revenue
+ FROM lineorder_flat
+ WHERE
+ LO_ORDERDATE >= 19930101
+ AND LO_ORDERDATE <= 19931231
+ AND LO_DISCOUNT >= 1 AND LO_DISCOUNT <= 3
+ AND LO_QUANTITY < 25
+ GROUP BY
+ LO_ORDERKEY;""")
createMV ("""create materialized view lineorder_q_2_1 as
SELECT
(LO_ORDERDATE DIV 10000) AS YEAR,
- P_BRAND, P_CATEGORY, S_REGION,
+ P_BRAND,
SUM(LO_REVENUE)
FROM lineorder_flat
- GROUP BY YEAR, P_BRAND, P_CATEGORY,S_REGION;""")
+ WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
+ GROUP BY YEAR, P_BRAND
+ ORDER BY YEAR, P_BRAND;""")
createMV ("""create materialized view lineorder_q_3_1 as
SELECT
C_NATION,
- S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, C_REGION,
S_REGION, LO_ORDERDATE,
+ S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR,
SUM(LO_REVENUE) AS revenue
FROM lineorder_flat
- GROUP BY C_NATION, S_NATION, YEAR, C_REGION, S_REGION,
LO_ORDERDATE;""")
+ WHERE
+ C_REGION = 'ASIA'
+ AND S_REGION = 'ASIA'
+ AND LO_ORDERDATE >= 19920101
+ AND LO_ORDERDATE <= 19971231
+ GROUP BY C_NATION, S_NATION, YEAR;""")
createMV ("""create materialized view lineorder_q_4_1 as
SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
- C_NATION,C_REGION,S_REGION,P_MFGR,
+ C_NATION,
SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
- GROUP BY YEAR, C_NATION,C_REGION,S_REGION,P_MFGR;""")
+ WHERE
+ C_REGION = 'AMERICA'
+ AND S_REGION = 'AMERICA'
+ AND P_MFGR IN ('MFGR#1', 'MFGR#2')
+ GROUP BY YEAR, C_NATION
+ ORDER BY YEAR ASC, C_NATION ASC;""")
sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY,
LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY,
LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT,
LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE,
LO_SHIPMODE,C_NAME,C_ADDRESS,C_CITY,C_NATION,C_REGION,C_PHONE,C_MKTSEGMENT,S_NAME,S_ADDRESS,S_CITY,S_NATION,S_REGION,S_PHONE,P_NAME,P_MFGR,P_CATEGORY,P_BRAND,P_COLOR,P_TYPE,P_SIZE,P_CONTAINER)
VALUES (19930101 , 2 , 2 , 2 , 2 , [...]
@@ -187,7 +205,7 @@ suite ("multiple_no_where") {
ORDER BY YEAR ASC, revenue DESC;""")
contains "(lineorder_q_3_1)"
}
- qt_select_mv """SELECT
+ qt_select_q_3_1 """SELECT
C_NATION,
S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR,
SUM(LO_REVENUE) AS revenue
@@ -213,7 +231,7 @@ suite ("multiple_no_where") {
ORDER BY YEAR ASC, C_NATION ASC;""")
contains "(lineorder_q_4_1)"
}
- qt_select_mv """SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
+ qt_select_q_4_1 """SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
C_NATION,
SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
diff --git
a/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy
b/regression-test/suites/mv_p0/ssb/q_4_1_r1/q_4_1_r1.groovy
similarity index 70%
copy from
regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy
copy to regression-test/suites/mv_p0/ssb/q_4_1_r1/q_4_1_r1.groovy
index 36f6fc634a..43fa7bc7bc 100644
---
a/regression-test/suites/mv_p0/ssb/multiple_no_where/multiple_no_where.groovy
+++ b/regression-test/suites/mv_p0/ssb/q_4_1_r1/q_4_1_r1.groovy
@@ -17,7 +17,7 @@
import org.codehaus.groovy.runtime.IOGroovyMethods
-suite ("multiple_no_where") {
+suite ("q_4_1_r1") {
sql """ DROP TABLE IF EXISTS lineorder_flat; """
sql """set enable_nereids_planner=true"""
sql """SET enable_fallback_to_original_planner=false"""
@@ -84,41 +84,7 @@ suite ("multiple_no_where") {
sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY,
LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY,
LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT,
LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE, LO_SHIPMODE, C_NAME,
C_ADDRESS, C_CITY, C_NATION, C_REGION, C_PHONE, C_MKTSEGMENT, S_NAME,
S_ADDRESS, S_CITY, S_NATION, S_REGION, S_PHONE, P_NAME, P_MFGR, P_CATEGORY,
P_BRAND, P_COLOR,P_TYPE,P_SIZE,P_CONTAINER) VALUES (19930101 [...]
- test {
- sql """create materialized view lineorder_q_1_1 as
- SELECT LO_ORDERKEY, SUM(LO_EXTENDEDPRICE * LO_DISCOUNT)
- FROM lineorder_flat GROUP BY
- LO_ORDERKEY, LO_ORDERDATE, LO_DISCOUNT, LO_QUANTITY;"""
- exception "not in select list"
- }
-
- createMV ("""create materialized view lineorder_q_1_1 as
- SELECT LO_ORDERKEY, LO_ORDERDATE, LO_DISCOUNT, LO_QUANTITY,
SUM(LO_EXTENDEDPRICE * LO_DISCOUNT)
- FROM lineorder_flat GROUP BY
- LO_ORDERKEY, LO_ORDERDATE, LO_DISCOUNT, LO_QUANTITY;""")
-
- createMV ("""create materialized view lineorder_q_2_1 as
- SELECT
- (LO_ORDERDATE DIV 10000) AS YEAR,
- P_BRAND, P_CATEGORY, S_REGION,
- SUM(LO_REVENUE)
- FROM lineorder_flat
- GROUP BY YEAR, P_BRAND, P_CATEGORY,S_REGION;""")
-
- createMV ("""create materialized view lineorder_q_3_1 as
- SELECT
- C_NATION,
- S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, C_REGION,
S_REGION, LO_ORDERDATE,
- SUM(LO_REVENUE) AS revenue
- FROM lineorder_flat
- GROUP BY C_NATION, S_NATION, YEAR, C_REGION, S_REGION,
LO_ORDERDATE;""")
-
- createMV ("""create materialized view lineorder_q_4_1 as
- SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
- C_NATION,C_REGION,S_REGION,P_MFGR,
- SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
- FROM lineorder_flat
- GROUP BY YEAR, C_NATION,C_REGION,S_REGION,P_MFGR;""")
+ createMV ("""create materialized view lineorder_mv as select LO_ORDERDATE
DIV 10000, C_NATION, C_REGION, S_REGION, P_MFGR, SUM(LO_REVENUE -
LO_SUPPLYCOST) from lineorder_flat group by LO_ORDERDATE DIV 10000, C_NATION,
C_REGION, S_REGION, P_MFGR;""")
sql """INSERT INTO lineorder_flat (LO_ORDERDATE, LO_ORDERKEY,
LO_LINENUMBER, LO_CUSTKEY, LO_PARTKEY, LO_SUPPKEY, LO_ORDERPRIORITY,
LO_SHIPPRIORITY, LO_QUANTITY, LO_EXTENDEDPRICE, LO_ORDTOTALPRICE, LO_DISCOUNT,
LO_REVENUE, LO_SUPPLYCOST, LO_TAX, LO_COMMITDATE,
LO_SHIPMODE,C_NAME,C_ADDRESS,C_CITY,C_NATION,C_REGION,C_PHONE,C_MKTSEGMENT,S_NAME,S_ADDRESS,S_CITY,S_NATION,S_REGION,S_PHONE,P_NAME,P_MFGR,P_CATEGORY,P_BRAND,P_COLOR,P_TYPE,P_SIZE,P_CONTAINER)
VALUES (19930101 , 2 , 2 , 2 , 2 , [...]
@@ -137,82 +103,19 @@ suite ("multiple_no_where") {
qt_select_star "select * from lineorder_flat order by 1,2;"
explain {
- sql("""SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
- FROM lineorder_flat
- WHERE
- LO_ORDERDATE >= 19930101
- AND LO_ORDERDATE <= 19931231
- AND LO_DISCOUNT >= 1 AND LO_DISCOUNT <= 3
- AND LO_QUANTITY < 25;""")
- contains "(lineorder_q_1_1)"
- }
- qt_select_q_1_1 """SELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue
- FROM lineorder_flat
- WHERE
- LO_ORDERDATE >= 19930101
- AND LO_ORDERDATE <= 19931231
- AND LO_DISCOUNT >= 1 AND LO_DISCOUNT <= 3
- AND LO_QUANTITY < 25;"""
-
- explain {
- sql("""SELECT
- SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR,
- P_BRAND
- FROM lineorder_flat
- WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
- GROUP BY (LO_ORDERDATE DIV 10000), P_BRAND
- ORDER BY YEAR, P_BRAND;""")
- contains "(lineorder_q_2_1)"
- }
- qt_select_q_2_1 """SELECT
- SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR,
- P_BRAND
- FROM lineorder_flat
- WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'
- GROUP BY YEAR, P_BRAND
- ORDER BY YEAR, P_BRAND;"""
-
- explain {
- sql("""SELECT
- C_NATION,
- S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR,
- SUM(LO_REVENUE) AS revenue
+ sql("""SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
+ C_NATION,
+ SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
FROM lineorder_flat
WHERE
- C_REGION = 'ASIA'
- AND S_REGION = 'ASIA'
- AND LO_ORDERDATE >= 19920101
- AND LO_ORDERDATE <= 19971231
- GROUP BY C_NATION, S_NATION, YEAR
- ORDER BY YEAR ASC, revenue DESC;""")
- contains "(lineorder_q_3_1)"
+ C_REGION = 'AMERICA'
+ AND S_REGION = 'AMERICA'
+ AND P_MFGR IN ('MFGR#1', 'MFGR#2')
+ GROUP BY YEAR, C_NATION
+ ORDER BY YEAR ASC, C_NATION ASC;""")
+ contains "(lineorder_mv)"
}
- qt_select_mv """SELECT
- C_NATION,
- S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR,
- SUM(LO_REVENUE) AS revenue
- FROM lineorder_flat
- WHERE
- C_REGION = 'ASIA'
- AND S_REGION = 'ASIA'
- AND LO_ORDERDATE >= 19920101
- AND LO_ORDERDATE <= 19971231
- GROUP BY C_NATION, S_NATION, YEAR
- ORDER BY YEAR ASC, revenue DESC;"""
- explain {
- sql("""SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
- C_NATION,
- SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
- FROM lineorder_flat
- WHERE
- C_REGION = 'AMERICA'
- AND S_REGION = 'AMERICA'
- AND P_MFGR IN ('MFGR#1', 'MFGR#2')
- GROUP BY YEAR, C_NATION
- ORDER BY YEAR ASC, C_NATION ASC;""")
- contains "(lineorder_q_4_1)"
- }
qt_select_mv """SELECT (LO_ORDERDATE DIV 10000) AS YEAR,
C_NATION,
SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
