search

citusdata / citus

Distributed PostgreSQL as an extension
https://www.citusdata.com
GNU Affero General Public License v3.0
10.39k stars 661 forks source link

Assert failure in TPC-DS query #2583

Open marcocitus opened 5 years ago

marcocitus commented 5 years ago

The query does seem to run (slowly, using re-partitioning) on production builds, but gives an assert failure in LocalJoin.

create table store_sales
(
    ss_sold_date_sk           integer                       ,
    ss_sold_time_sk           integer                       ,
    ss_item_sk                integer               not null,
    ss_customer_sk            integer                       ,
    ss_cdemo_sk               integer                       ,
    ss_hdemo_sk               integer                       ,
    ss_addr_sk                integer                       ,
    ss_store_sk               integer                       ,
    ss_promo_sk               integer                       ,
    ss_ticket_number          integer               not null,
    ss_quantity               integer                       ,
    ss_wholesale_cost         decimal(7,2)                  ,
    ss_list_price             decimal(7,2)                  ,
    ss_sales_price            decimal(7,2)                  ,
    ss_ext_discount_amt       decimal(7,2)                  ,
    ss_ext_sales_price        decimal(7,2)                  ,
    ss_ext_wholesale_cost     decimal(7,2)                  ,
    ss_ext_list_price         decimal(7,2)                  ,
    ss_ext_tax                decimal(7,2)                  ,
    ss_coupon_amt             decimal(7,2)                  ,
    ss_net_paid               decimal(7,2)                  ,
    ss_net_paid_inc_tax       decimal(7,2)                  ,
    ss_net_profit             decimal(7,2)                  ,
    primary key (ss_item_sk, ss_ticket_number)
);

create table store
(
    s_store_sk                integer               not null,
    s_store_id                char(16)              not null,
    s_rec_start_date          date                          ,
    s_rec_end_date            date                          ,
    s_closed_date_sk          integer                       ,
    s_store_name              varchar(50)                   ,
    s_number_employees        integer                       ,
    s_floor_space             integer                       ,
    s_hours                   char(20)                      ,
    s_manager                 varchar(40)                   ,
    s_market_id               integer                       ,
    s_geography_class         varchar(100)                  ,
    s_market_desc             varchar(100)                  ,
    s_market_manager          varchar(40)                   ,
    s_division_id             integer                       ,
    s_division_name           varchar(50)                   ,
    s_company_id              integer                       ,
    s_company_name            varchar(50)                   ,
    s_street_number           varchar(10)                   ,
    s_street_name             varchar(60)                   ,
    s_street_type             char(15)                      ,
    s_suite_number            char(10)                      ,
    s_city                    varchar(60)                   ,
    s_county                  varchar(30)                   ,
    s_state                   char(2)                       ,
    s_zip                     char(10)                      ,
    s_country                 varchar(20)                   ,
    s_gmt_offset              decimal(5,2)                  ,
    s_tax_precentage          decimal(5,2)                  ,
    primary key (s_store_sk)
);
create table promotion
(
    p_promo_sk                integer               not null,
    p_promo_id                char(16)              not null,
    p_start_date_sk           integer                       ,
    p_end_date_sk             integer                       ,
    p_item_sk                 integer                       ,
    p_cost                    decimal(15,2)                 ,
    p_response_target         integer                       ,
    p_promo_name              char(50)                      ,
    p_channel_dmail           char(1)                       ,
    p_channel_email           char(1)                       ,
    p_channel_catalog         char(1)                       ,
    p_channel_tv              char(1)                       ,
    p_channel_radio           char(1)                       ,
    p_channel_press           char(1)                       ,
    p_channel_event           char(1)                       ,
    p_channel_demo            char(1)                       ,
    p_channel_details         varchar(100)                  ,
    p_purpose                 char(15)                      ,
    p_discount_active         char(1)                       ,
    primary key (p_promo_sk)
);
create table customer_address
(
    ca_address_sk             integer               not null,
    ca_address_id             char(16)              not null,
    ca_street_number          char(10)                      ,
    ca_street_name            varchar(60)                   ,
    ca_street_type            char(15)                      ,
    ca_suite_number           char(10)                      ,
    ca_city                   varchar(60)                   ,
    ca_county                 varchar(30)                   ,
    ca_state                  char(2)                       ,
    ca_zip                    char(10)                      ,
    ca_country                varchar(20)                   ,
    ca_gmt_offset             decimal(5,2)                  ,
    ca_location_type          char(20)                      ,
    primary key (ca_address_sk)
);
create table date_dim
(
    d_date_sk                 integer               not null,
    d_date_id                 char(16)              not null,
    d_date                    date                          ,
    d_month_seq               integer                       ,
    d_week_seq                integer                       ,
    d_quarter_seq             integer                       ,
    d_year                    integer                       ,
    d_dow                     integer                       ,
    d_moy                     integer                       ,
    d_dom                     integer                       ,
    d_qoy                     integer                       ,
    d_fy_year                 integer                       ,
    d_fy_quarter_seq          integer                       ,
    d_fy_week_seq             integer                       ,
    d_day_name                char(9)                       ,
    d_quarter_name            char(6)                       ,
    d_holiday                 char(1)                       ,
    d_weekend                 char(1)                       ,
    d_following_holiday       char(1)                       ,
    d_first_dom               integer                       ,
    d_last_dom                integer                       ,
    d_same_day_ly             integer                       ,
    d_same_day_lq             integer                       ,
    d_current_day             char(1)                       ,
    d_current_week            char(1)                       ,
    d_current_month           char(1)                       ,
    d_current_quarter         char(1)                       ,
    d_current_year            char(1)                       ,
    primary key (d_date_sk)
);
create table customer
(
    c_customer_sk             integer               not null,
    c_customer_id             char(16)              not null,
    c_current_cdemo_sk        integer                       ,
    c_current_hdemo_sk        integer                       ,
    c_current_addr_sk         integer                       ,
    c_first_shipto_date_sk    integer                       ,
    c_first_sales_date_sk     integer                       ,
    c_salutation              char(10)                      ,
    c_first_name              char(20)                      ,
    c_last_name               char(30)                      ,
    c_preferred_cust_flag     char(1)                       ,
    c_birth_day               integer                       ,
    c_birth_month             integer                       ,
    c_birth_year              integer                       ,
    c_birth_country           varchar(20)                   ,
    c_login                   char(13)                      ,
    c_email_address           char(50)                      ,
    c_last_review_date_sk     integer                       ,
    primary key (c_customer_sk)
);
create table item
(
    i_item_sk                 integer               not null,
    i_item_id                 char(16)              not null,
    i_rec_start_date          date                          ,
    i_rec_end_date            date                          ,
    i_item_desc               varchar(200)                  ,
    i_current_price           decimal(7,2)                  ,
    i_wholesale_cost          decimal(7,2)                  ,
    i_brand_id                integer                       ,
    i_brand                   char(50)                      ,
    i_class_id                integer                       ,
    i_class                   char(50)                      ,
    i_category_id             integer                       ,
    i_category                char(50)                      ,
    i_manufact_id             integer                       ,
    i_manufact                char(50)                      ,
    i_size                    char(20)                      ,
    i_formulation             char(20)                      ,
    i_color                   char(20)                      ,
    i_units                   char(10)                      ,
    i_container               char(10)                      ,
    i_manager_id              integer                       ,
    i_product_name            char(50)                      ,
    primary key (i_item_sk)
);

SELECT create_distributed_table('store_sales','ss_item_sk');
SELECT create_distributed_table('customer','c_customer_sk');
SELECT create_reference_table('store');
SELECT create_reference_table('date_dim');
SELECT create_reference_table('customer_address');
SELECT create_reference_table('item');

Query 61:

select  promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
from
  (select sum(ss_ext_sales_price) promotions
   from  store_sales
        ,store
        ,promotion
        ,date_dim
        ,customer
        ,customer_address 
        ,item
   where ss_sold_date_sk = d_date_sk
   and   ss_store_sk = s_store_sk
   and   ss_promo_sk = p_promo_sk
   and   ss_customer_sk= c_customer_sk
   and   ca_address_sk = c_current_addr_sk
   and   ss_item_sk = i_item_sk 
   and   ca_gmt_offset = -7
   and   i_category = 'Sports'
   and   (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
   and   s_gmt_offset = -7
   and   d_year = 2000
   and   d_moy  = 11) promotional_sales,
  (select sum(ss_ext_sales_price) total
   from  store_sales
        ,store
        ,date_dim
        ,customer
        ,customer_address
        ,item
   where ss_sold_date_sk = d_date_sk
   and   ss_store_sk = s_store_sk
   and   ss_customer_sk= c_customer_sk
   and   ca_address_sk = c_current_addr_sk
   and   ss_item_sk = i_item_sk
   and   ca_gmt_offset = -7
   and   i_category = 'Sports'
   and   s_gmt_offset = -7
   and   d_year = 2000
   and   d_moy  = 11) all_sales
order by promotions, total
limit 100;

stack trace:

0x00007fe969b74277 in raise () from /lib64/libc.so.6
(gdb) bt
#0  0x00007fe969b74277 in raise () from /lib64/libc.so.6
#1  0x00007fe969b75968 in abort () from /lib64/libc.so.6
#2  0x00000000008a0852 in ExceptionalCondition (conditionName=conditionName@entry=0x7fe961b85b90 "!(currentAnchorTable != ((void *)0))", 
    errorType=errorType@entry=0x7fe961b7aa1f "FailedAssertion", fileName=fileName@entry=0x7fe961b85a43 "planner/multi_join_order.c", lineNumber=lineNumber@entry=836)
    at assert.c:54
#3  0x00007fe961b350e4 in LocalJoin (currentJoinNode=0x7fe954af8798, candidateTable=0x7fe954ae3588, applicableJoinClauses=0x7fe954afc5b0, joinType=<optimized out>)
    at planner/multi_join_order.c:836
#4  0x00007fe961b34c93 in EvaluateJoinRules (joinedTableList=joinedTableList@entry=0x7fe954af3b38, currentJoinNode=currentJoinNode@entry=0x7fe954af8798, 
    candidateTable=0x7fe954ae3588, joinClauseList=joinClauseList@entry=0x7fe954ae2f38, joinType=joinType@entry=JOIN_INNER) at planner/multi_join_order.c:646
#5  0x00007fe961b34e05 in JoinOrderForTable (firstTable=<optimized out>, tableEntryList=tableEntryList@entry=0x7fe954ae3478, joinClauseList=joinClauseList@entry=0x7fe954ae2f38)
    at planner/multi_join_order.c:336
#6  0x00007fe961b34ebe in JoinOrderList (tableEntryList=tableEntryList@entry=0x7fe954ae3478, joinClauseList=joinClauseList@entry=0x7fe954ae2f38) at planner/multi_join_order.c:260
#7  0x00007fe961b3b619 in MultiNodeTree (queryTree=queryTree@entry=0x24f0f30) at planner/multi_logical_planner.c:733
#8  0x00007fe961b3b6e7 in MultiLogicalPlanCreate (originalQuery=originalQuery@entry=0x7fe95494f5c8, queryTree=queryTree@entry=0x24f0f30, 
    plannerRestrictionContext=plannerRestrictionContext@entry=0x7fe954991d78) at planner/multi_logical_planner.c:138
#9  0x00007fe961b30bbd in CreateDistributedPlan (planId=planId@entry=2, originalQuery=originalQuery@entry=0x7fe95494f5c8, query=query@entry=0x24f0f30, 
    boundParams=boundParams@entry=0x0, hasUnresolvedParams=hasUnresolvedParams@entry=false, plannerRestrictionContext=plannerRestrictionContext@entry=0x7fe954991d78)
    at planner/distributed_planner.c:757
#10 0x00007fe961b31066 in CreateDistributedPlannedStmt (planId=2, localPlan=localPlan@entry=0x7fe954abca98, originalQuery=originalQuery@entry=0x7fe95494f5c8, 
    query=query@entry=0x24f0f30, boundParams=boundParams@entry=0x0, plannerRestrictionContext=plannerRestrictionContext@entry=0x7fe954991d78) at planner/distributed_planner.c:498
#11 0x00007fe961b31329 in distributed_planner (parse=0x24f0f30, cursorOptions=0, boundParams=0x0) at planner/distributed_planner.c:158
#12 0x00000000006df5d6 in planner (parse=parse@entry=0x24f0f30, cursorOptions=cursorOptions@entry=0, boundParams=boundParams@entry=0x0) at planner.c:260
#13 0x00007fe961b472a6 in CreateDistributedSubPlan (subPlanId=subPlanId@entry=1, subPlanQuery=subPlanQuery@entry=0x24f0f30) at planner/recursive_planning.c:1176
#14 0x00007fe961b47942 in RecursivelyPlanSubquery (subquery=subquery@entry=0x24f0f30, planningContext=planningContext@entry=0x7ffce02d7120) at planner/recursive_planning.c:1123
#15 0x00007fe961b48223 in RecursivelyPlanSubqueryWalker (node=0x24f0f30, context=0x7ffce02d7120) at planner/recursive_planning.c:836
#16 0x00000000006859ca in range_table_walker (rtable=<optimized out>, walker=walker@entry=0x7fe961b481cb <RecursivelyPlanSubqueryWalker>, context=context@entry=0x7ffce02d7120, 
    flags=flags@entry=0) at nodeFuncs.c:2328
#17 0x0000000000685be2 in query_tree_walker (query=query@entry=0x24efe70, walker=walker@entry=0x7fe961b481cb <RecursivelyPlanSubqueryWalker>, 
    context=context@entry=0x7ffce02d7120, flags=flags@entry=0) at nodeFuncs.c:2288
#18 0x00007fe961b4806d in RecursivelyPlanSubqueriesAndCTEs (query=query@entry=0x24efe70, context=context@entry=0x7ffce02d7120) at planner/recursive_planning.c:267
#19 0x00007fe961b48120 in GenerateSubplansForSubqueriesAndCTEs (planId=planId@entry=1, originalQuery=originalQuery@entry=0x24efe70, 
    plannerRestrictionContext=plannerRestrictionContext@entry=0x7fe96b38cf60) at planner/recursive_planning.c:205
#20 0x00007fe961b30a68 in CreateDistributedPlan (planId=planId@entry=1, originalQuery=originalQuery@entry=0x24efe70, query=query@entry=0x24d4018, 
    boundParams=boundParams@entry=0x0, hasUnresolvedParams=hasUnresolvedParams@entry=false, plannerRestrictionContext=plannerRestrictionContext@entry=0x7fe96b38cf60)
    at planner/distributed_planner.c:685
#21 0x00007fe961b31066 in CreateDistributedPlannedStmt (planId=1, localPlan=localPlan@entry=0x7fe95494fe98, originalQuery=originalQuery@entry=0x24efe70, 
    query=query@entry=0x24d4018, boundParams=boundParams@entry=0x0, plannerRestrictionContext=plannerRestrictionContext@entry=0x7fe96b38cf60) at planner/distributed_planner.c:498
#22 0x00007fe961b31329 in distributed_planner (parse=0x24d4018, cursorOptions=256, boundParams=0x0) at planner/distributed_planner.c:158
#23 0x00000000006df5d6 in planner (parse=parse@entry=0x24d4018, cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x0) at planner.c:260
#24 0x000000000079328a in pg_plan_query (querytree=querytree@entry=0x24d4018, cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x0) at postgres.c:832
#25 0x000000000079338f in pg_plan_queries (querytrees=<optimized out>, cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x0) at postgres.c:898
#26 0x000000000079366b in exec_simple_query (
    query_string=query_string@entry=0x23a2828 "select  promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100\nfrom\n  (select sum(ss_ext_sales_price) promotions\n   from  store_sales\n        ,store\n        ,promotion\n   "...) at postgres.c:1073
#27 0x0000000000795421 in PostgresMain (argc=<optimized out>, argv=argv@entry=0x2416d90, dbname=0x2416c28 "postgres", username=<optimized out>) at postgres.c:4182
#28 0x0000000000717164 in BackendRun (port=port@entry=0x240d340) at postmaster.c:4361
#29 0x0000000000719da3 in BackendStartup (port=port@entry=0x240d340) at postmaster.c:4033
#30 0x000000000071a04c in ServerLoop () at postmaster.c:1706
#31 0x000000000071b2e6 in PostmasterMain (argc=argc@entry=3, argv=argv@entry=0x239cb50) at postmaster.c:1379
#32 0x000000000068397f in main (argc=3, argv=0x239cb50) at main.c:228
onderkalaci commented 5 years ago

is it the same with https://github.com/citusdata/citus/issues/2547?

marcocitus commented 5 years ago

Yep, seems like the same failure.