Unknown plan node type: com.facebook.presto.sql.planner.plan.WindowNode

[storm-dance]

These features are available in velox now or soon. I expect to run and verify some sqls end-to-end.
Is there any plan to support when converting presto plan to velox plan in presto-native-execution?

[mbasmanova]

CC: @aditi-pandit

[mbasmanova]

@XuPingyong Are you seeing failure running some queries? Would you share some examples of the queries and failures?

[aditi-pandit]

@XuPingyong might be referring to the presto_cpp wiring from Presto Fragment PlanNode to Velox PlanNode for Window to be able to run Presto SQL queries end to end.

I am working on that PR and will have it out by eow.

[storm-dance]

Thanks @aditi-pandit @mbasmanova very much. Looking forward to it.

Failures occur when running some common Presto SQL queries:

1. Unsupported Filter over SemiJoin
   "Unsupported Filter over SemiJoin: {}", toJsonString(node->predicate));
2. Unknown plan node type: com.facebook.presto.sql.planner.plan.WindowNode

[aditi-pandit]

@XuPingyong : Please can you give us the SQL query you are using for the first error.
For the second error we have prestodb/presto#18486

[storm-dance]

@XuPingyong : Please can you give us the SQL query you are using for the first error. For the second error we have prestodb/presto#18486

TPCDS q45.sql can incur the first error.

--TPC-DS Q45
select ca_zip, ca_city, sum(ws_sales_price)
from web_sales, customer, customer_address, date_dim, item
where ws_bill_customer_sk = c_customer_sk
and c_current_addr_sk = ca_address_sk
and ws_item_sk = i_item_sk
and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792')
or
i_item_id in (select i_item_id
from item
where i_item_sk in (2, 3, 5, 7, 11, 13, 17, 19, 23, 29)
)
)
and ws_sold_date_sk = d_date_sk
and d_qoy = 2 and d_year = 2002
group by ca_zip, ca_city
order by ca_zip, ca_city
limit 100;

[aditi-pandit]

prestodb/presto#18486 for WindowNode SQL error is submit now. Do you still see errors for the window queries ? Please can you give us more info about the specific window sql to ensure we have the function coverage.

[aditi-pandit]

@XuPingyong : Please can you give us the SQL query you are using for the first error. For the second error we have prestodb/presto#18486

TPCDS q45.sql can incur the first error.

--TPC-DS Q45 select ca_zip, ca_city, sum(ws_sales_price) from web_sales, customer, customer_address, date_dim, item where ws_bill_customer_sk = c_customer_sk and c_current_addr_sk = ca_address_sk and ws_item_sk = i_item_sk and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') or i_item_id in (select i_item_id from item where i_item_sk in (2, 3, 5, 7, 11, 13, 17, 19, 23, 29) ) ) and ws_sold_date_sk = d_date_sk and d_qoy = 2 and d_year = 2002 group by ca_zip, ca_city order by ca_zip, ca_city limit 100;

Have created another issue #2877 for the Semi-join filter issue. Using this one to only track the Window sql related queries.

[storm-dance]

prestodb/presto#18486 for WindowNode SQL error is submit now. Do you still see errors for the window queries ? Please can you give us more info about the specific window sql to ensure we have the function coverage.

Thanks a lot @aditi-pandit. Some errors are resolved while accumulator functions in window are still not supported:
VeloxUserError: Window function not registered: avg

You can try to run tpcds 63.sql please.

select  * 
from (select i_manager_id
             ,sum(ss_sales_price) sum_sales
             ,avg(sum(ss_sales_price)) over (partition by i_manager_id) avg_monthly_sales
      from item
          ,store_sales
          ,date_dim
          ,store
      where ss_item_sk = i_item_sk
        and ss_sold_date_sk = d_date_sk
        and ss_store_sk = s_store_sk
        and d_month_seq in (1193,1193+1,1193+2,1193+3,1193+4,1193+5,1193+6,1193+7,1193+8,1193+9,1193+10,1193+11)
        and ((    i_category in ('Books','Children','Electronics')
              and i_class in ('personal','portable','reference','self-help')
              and i_brand in ('scholaramalgamalg #14','scholaramalgamalg #7',
		                  'exportiunivamalg #9','scholaramalgamalg #9'))
           or(    i_category in ('Women','Music','Men')
              and i_class in ('accessories','classical','fragrances','pants')
              and i_brand in ('amalgimporto #1','edu packscholar #1','exportiimporto #1',
		                 'importoamalg #1')))
group by i_manager_id, d_moy) tmp1
where case when avg_monthly_sales > 0 then abs (sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
order by i_manager_id
        ,avg_monthly_sales
        ,sum_sales
limit 100;

[aditi-pandit]

Thanks. I was familiar with the use of aggregate functions with windows in TPC-DS. I'm working on adding aggregate functions as windows next on the Velox side. Will keep this issue updated with the PRs.

[aditi-pandit]

#2919 is submit today for supporting aggregates as window functions.

Please let me know if that works on your side.

[storm-dance]

#2919 is submit today for supporting aggregates as window functions.

Please let me know if that works on your side.

Thanks very much. My problem is sloved.