KunlunBase is a distributed relational database management system(RDBMS) with complete NewSQL capabilities and robust transaction ACID guarantees and is compatible with standard SQL. Applications which used PostgreSQL or MySQL can work with KunlunBase as-is without any code change or rebuild because KunlunBase supports both PostgreSQL and MySQL connection protocols and DML SQL grammars. MySQL DBAs can quickly work on a KunlunBase cluster because we use MySQL as storage nodes of KunlunBase. KunlunBase can elastically scale out as needed, and guarantees transaction ACID under error conditions, and KunlunBase fully passes TPC-C, TPC-H and TPC-DS test suites, so it not only support OLTP workloads but also OLAP workloads. Application developers can use KunlunBase to build IT systems that handles terabytes of data, without any effort on their part to implement data sharding, distributed transaction processing, distributed query processing, crash safety, high availability, strong consistency, horizontal scalability. All these powerful features are provided by KunlunBase. KunlunBase supports powerful and user friendly cluster management, monitor and provision features, can be readily used as DBaaS.
2022-01-25 16:29:18: charles@zettadb.com created the issue
sql:
select
w_warehouse_name
,w_warehouse_sq_ft
,w_city
,w_county
,w_state
,w_country
,ship_carriers
,year
,sum(jan_sales) as jan_sales
,sum(feb_sales) as feb_sales
,sum(mar_sales) as mar_sales
,sum(apr_sales) as apr_sales
,sum(may_sales) as may_sales
,sum(jun_sales) as jun_sales
,sum(jul_sales) as jul_sales
,sum(aug_sales) as aug_sales
,sum(sep_sales) as sep_sales
,sum(oct_sales) as oct_sales
,sum(nov_sales) as nov_sales
,sum(dec_sales) as dec_sales
,sum(jan_sales/w_warehouse_sq_ft) as jan_sales_per_sq_foot
,sum(feb_sales/w_warehouse_sq_ft) as feb_sales_per_sq_foot
,sum(mar_sales/w_warehouse_sq_ft) as mar_sales_per_sq_foot
,sum(apr_sales/w_warehouse_sq_ft) as apr_sales_per_sq_foot
,sum(may_sales/w_warehouse_sq_ft) as may_sales_per_sq_foot
,sum(jun_sales/w_warehouse_sq_ft) as jun_sales_per_sq_foot
,sum(jul_sales/w_warehouse_sq_ft) as jul_sales_per_sq_foot
,sum(aug_sales/w_warehouse_sq_ft) as aug_sales_per_sq_foot
,sum(sep_sales/w_warehouse_sq_ft) as sep_sales_per_sq_foot
,sum(oct_sales/w_warehouse_sq_ft) as oct_sales_per_sq_foot
,sum(nov_sales/w_warehouse_sq_ft) as nov_sales_per_sq_foot
,sum(dec_sales/w_warehouse_sq_ft) as dec_sales_per_sq_foot
,sum(jan_net) as jan_net
,sum(feb_net) as feb_net
,sum(mar_net) as mar_net
,sum(apr_net) as apr_net
,sum(may_net) as may_net
,sum(jun_net) as jun_net
,sum(jul_net) as jul_net
,sum(aug_net) as aug_net
,sum(sep_net) as sep_net
,sum(oct_net) as oct_net
,sum(nov_net) as nov_net
,sum(dec_net) as dec_net
from (
select
w_warehouse_name
,w_warehouse_sq_ft
,w_city
,w_county
,w_state
,w_country
,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers
,d_year as year
,sum(case when d_moy = 1
then ws_sales_price* ws_quantity else 0 end) as jan_sales
,sum(case when d_moy = 2
then ws_sales_price* ws_quantity else 0 end) as feb_sales
,sum(case when d_moy = 3
then ws_sales_price* ws_quantity else 0 end) as mar_sales
,sum(case when d_moy = 4
then ws_sales_price* ws_quantity else 0 end) as apr_sales
,sum(case when d_moy = 5
then ws_sales_price* ws_quantity else 0 end) as may_sales
,sum(case when d_moy = 6
then ws_sales_price* ws_quantity else 0 end) as jun_sales
,sum(case when d_moy = 7
then ws_sales_price* ws_quantity else 0 end) as jul_sales
,sum(case when d_moy = 8
then ws_sales_price* ws_quantity else 0 end) as aug_sales
,sum(case when d_moy = 9
then ws_sales_price* ws_quantity else 0 end) as sep_sales
,sum(case when d_moy = 10
then ws_sales_price* ws_quantity else 0 end) as oct_sales
,sum(case when d_moy = 11
then ws_sales_price* ws_quantity else 0 end) as nov_sales
,sum(case when d_moy = 12
then ws_sales_price* ws_quantity else 0 end) as dec_sales
,sum(case when d_moy = 1
then ws_net_paid_inc_tax * ws_quantity else 0 end) as jan_net
,sum(case when d_moy = 2
then ws_net_paid_inc_tax * ws_quantity else 0 end) as feb_net
,sum(case when d_moy = 3
then ws_net_paid_inc_tax * ws_quantity else 0 end) as mar_net
,sum(case when d_moy = 4
then ws_net_paid_inc_tax * ws_quantity else 0 end) as apr_net
,sum(case when d_moy = 5
then ws_net_paid_inc_tax * ws_quantity else 0 end) as may_net
,sum(case when d_moy = 6
then ws_net_paid_inc_tax * ws_quantity else 0 end) as jun_net
,sum(case when d_moy = 7
then ws_net_paid_inc_tax * ws_quantity else 0 end) as jul_net
,sum(case when d_moy = 8
then ws_net_paid_inc_tax * ws_quantity else 0 end) as aug_net
,sum(case when d_moy = 9
then ws_net_paid_inc_tax * ws_quantity else 0 end) as sep_net
,sum(case when d_moy = 10
then ws_net_paid_inc_tax * ws_quantity else 0 end) as oct_net
,sum(case when d_moy = 11
then ws_net_paid_inc_tax * ws_quantity else 0 end) as nov_net
,sum(case when d_moy = 12
then ws_net_paid_inc_tax * ws_quantity else 0 end) as dec_net
from
web_sales
,warehouse
,date_dim
,time_dim
,ship_mode
where
ws_warehouse_sk = w_warehouse_sk
and ws_sold_date_sk = d_date_sk
and ws_sold_time_sk = t_time_sk
and ws_ship_mode_sk = sm_ship_mode_sk
and d_year = 2002
and t_time between 49530 and 49530+28800
and sm_carrier in ('DIAMOND','AIRBORNE')
group by
w_warehouse_name
,w_warehouse_sq_ft
,w_city
,w_county
,w_state
,w_country
,d_year
union all
select
w_warehouse_name
,w_warehouse_sq_ft
,w_city
,w_county
,w_state
,w_country
,'DIAMOND' || ',' || 'AIRBORNE' as ship_carriers
,d_year as year
,sum(case when d_moy = 1
then cs_ext_sales_price* cs_quantity else 0 end) as jan_sales
,sum(case when d_moy = 2
then cs_ext_sales_price* cs_quantity else 0 end) as feb_sales
,sum(case when d_moy = 3
then cs_ext_sales_price* cs_quantity else 0 end) as mar_sales
,sum(case when d_moy = 4
then cs_ext_sales_price* cs_quantity else 0 end) as apr_sales
,sum(case when d_moy = 5
then cs_ext_sales_price* cs_quantity else 0 end) as may_sales
,sum(case when d_moy = 6
then cs_ext_sales_price* cs_quantity else 0 end) as jun_sales
,sum(case when d_moy = 7
then cs_ext_sales_price* cs_quantity else 0 end) as jul_sales
,sum(case when d_moy = 8
then cs_ext_sales_price* cs_quantity else 0 end) as aug_sales
,sum(case when d_moy = 9
then cs_ext_sales_price* cs_quantity else 0 end) as sep_sales
,sum(case when d_moy = 10
then cs_ext_sales_price* cs_quantity else 0 end) as oct_sales
,sum(case when d_moy = 11
then cs_ext_sales_price* cs_quantity else 0 end) as nov_sales
,sum(case when d_moy = 12
then cs_ext_sales_price* cs_quantity else 0 end) as dec_sales
,sum(case when d_moy = 1
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jan_net
,sum(case when d_moy = 2
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as feb_net
,sum(case when d_moy = 3
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as mar_net
,sum(case when d_moy = 4
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as apr_net
,sum(case when d_moy = 5
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as may_net
,sum(case when d_moy = 6
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jun_net
,sum(case when d_moy = 7
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as jul_net
,sum(case when d_moy = 8
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as aug_net
,sum(case when d_moy = 9
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as sep_net
,sum(case when d_moy = 10
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as oct_net
,sum(case when d_moy = 11
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as nov_net
,sum(case when d_moy = 12
then cs_net_paid_inc_ship_tax * cs_quantity else 0 end) as dec_net
from
catalog_sales
,warehouse
,date_dim
,time_dim
,ship_mode
where
cs_warehouse_sk = w_warehouse_sk
and cs_sold_date_sk = d_date_sk
and cs_sold_time_sk = t_time_sk
and cs_ship_mode_sk = sm_ship_mode_sk
and d_year = 2002
and t_time between 49530 AND 49530+28800
and sm_carrier in ('DIAMOND','AIRBORNE')
group by
w_warehouse_name
,w_warehouse_sq_ft
,w_city
,w_county
,w_state
,w_country
,d_year
) x
group by
w_warehouse_name
,w_warehouse_sq_ft
,w_city
,w_county
,w_state
,w_country
,ship_carriers
,year
order by w_warehouse_name
limit 100;
log:
psql:q66.sql:221: ERROR: Kunlun-db: MySQL storage node (2, 2) returned error: 1054, Unknown column 'DIAMOND' in 'where clause'.
jd-zhang
commented
2 years ago
Issue migrated from trac ticket # 431
component: computing nodes | priority: major
2022-01-25 16:29:18: charles@zettadb.com created the issue