search

facebookincubator / velox

A composable and fully extensible C++ execution engine library for data management systems.
https://velox-lib.io/
Apache License 2.0
3.51k stars 1.15k forks source link

Two tests fail in AbstractTestNativeTpcdsQueries #5412

Open spershin opened 1 year ago

spershin commented 1 year ago

Bug description

Failing tests are disabled in the PR advancing Velox version for Presto Native: https://github.com/prestodb/presto/pull/19987

File path: presto/presto-native-execution/src/test/java/com/facebook/presto/nativeworker/AbstractTestNativeTpcdsQueries.java

testTpcdsQ2 fails all the time testTpcdsQ78 fails all the time

I had testTpcdsQ39_2 failed once, but couldn't reproduce it even with 20 query runs in a row and didn't record the error.

System information

spershin@spershin-mbp velox % ./scripts/info.sh

Velox System Info v0.0.2 Commit: 36a1507025ed40ec1344e97c22416ec7b071139e CMake Version: 3.26.4 System: Darwin-22.5.0 Arch: x86_64 C++ Compiler: /Library/Developer/CommandLineTools/usr/bin/c++ C++ Compiler Version: 12.0.0.12000032 C Compiler: /Library/Developer/CommandLineTools/usr/bin/cc C Compiler Version: 12.0.0.12000032 CMake Prefix Path: /Library/Developer/CommandLineTools/SDKs/MacOSX12.3.sdk/usr;/usr/local;/usr;/;/usr/local/Cellar/cmake/3.26.4;/usr/local;/usr/X11R6;/usr/pkg;/opt;/sw;/opt/local

Relevant logs

======================== testTpcdsQ2

java.lang.AssertionError: Expected query to fail: WITH
  wscs AS (
   SELECT
     "sold_date_sk"
   , "sales_price"
   FROM
     (
      SELECT
        "ws_sold_date_sk" "sold_date_sk"
      , "ws_ext_sales_price" "sales_price"
      FROM
        web_sales
   )  
UNION ALL (
      SELECT
        "cs_sold_date_sk" "sold_date_sk"
      , "cs_ext_sales_price" "sales_price"
      FROM
        catalog_sales
   ) ) 
, wswscs AS (
   SELECT
     "d_week_seq"
   , "sum"((CASE WHEN ("d_day_name" = 'Sunday') THEN "sales_price" ELSE null END)) "sun_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Monday') THEN "sales_price" ELSE null END)) "mon_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Tuesday') THEN "sales_price" ELSE null END)) "tue_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Wednesday') THEN "sales_price" ELSE null END)) "wed_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Thursday') THEN "sales_price" ELSE null END)) "thu_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Friday') THEN "sales_price" ELSE null END)) "fri_sales"
   , "sum"((CASE WHEN ("d_day_name" = 'Saturday') THEN "sales_price" ELSE null END)) "sat_sales"
   FROM
     wscs
   , date_dim
   WHERE ("d_date_sk" = "sold_date_sk")
   GROUP BY "d_week_seq"
) 
SELECT
  "d_week_seq1"
, "round"(("sun_sales1" / "sun_sales2"), 2)
, "round"(("mon_sales1" / "mon_sales2"), 2)
, "round"(("tue_sales1" / "tue_sales2"), 2)
, "round"(("wed_sales1" / "wed_sales2"), 2)
, "round"(("thu_sales1" / "thu_sales2"), 2)
, "round"(("fri_sales1" / "fri_sales2"), 2)
, "round"(("sat_sales1" / "sat_sales2"), 2)
FROM
  (
   SELECT
     "wswscs"."d_week_seq" "d_week_seq1"
   , "sun_sales" "sun_sales1"
   , "mon_sales" "mon_sales1"
   , "tue_sales" "tue_sales1"
   , "wed_sales" "wed_sales1"
   , "thu_sales" "thu_sales1"
   , "fri_sales" "fri_sales1"
   , "sat_sales" "sat_sales1"
   FROM
     wswscs
   , date_dim
   WHERE ("date_dim"."d_week_seq" = "wswscs"."d_week_seq")
      AND ("d_year" = 2001)
)  y
, (
   SELECT
     "wswscs"."d_week_seq" "d_week_seq2"
   , "sun_sales" "sun_sales2"
   , "mon_sales" "mon_sales2"
   , "tue_sales" "tue_sales2"
   , "wed_sales" "wed_sales2"
   , "thu_sales" "thu_sales2"
   , "fri_sales" "fri_sales2"
   , "sat_sales" "sat_sales2"
   FROM
     wswscs
   , date_dim
   WHERE ("date_dim"."d_week_seq" = "wswscs"."d_week_seq")
      AND ("d_year" = (2001 + 1))
)  z
WHERE ("d_week_seq1" = ("d_week_seq2" - 53))
ORDER BY "d_week_seq1" ASC

    at org.testng.Assert.fail(Assert.java:110)
    at com.facebook.presto.tests.QueryAssertions.assertQueryFails(QueryAssertions.java:329)
    at com.facebook.presto.tests.AbstractTestQueryFramework.assertQueryFails(AbstractTestQueryFramework.java:265)
    at com.facebook.presto.nativeworker.AbstractTestNativeTpcdsQueries.testTpcdsQ2(AbstractTestNativeTpcdsQueries.java:438)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.testng.internal.invokers.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:135)
    at org.testng.internal.invokers.TestInvoker.invokeMethod(TestInvoker.java:673)
    at org.testng.internal.invokers.TestInvoker.invokeTestMethod(TestInvoker.java:220)
    at org.testng.internal.invokers.MethodRunner.runInSequence(MethodRunner.java:50)
    at org.testng.internal.invokers.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:945)
    at org.testng.internal.invokers.TestInvoker.invokeTestMethods(TestInvoker.java:193)
    at org.testng.internal.invokers.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
    at org.testng.internal.invokers.TestMethodWorker.run(TestMethodWorker.java:128)
    at java.util.ArrayList.forEach(ArrayList.java:1259)
    at org.testng.TestRunner.privateRun(TestRunner.java:808)
    at org.testng.TestRunner.run(TestRunner.java:603)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:429)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:423)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:383)
    at org.testng.SuiteRunner.run(SuiteRunner.java:326)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:95)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1249)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1169)
    at org.testng.TestNG.runSuites(TestNG.java:1092)
    at org.testng.TestNG.run(TestNG.java:1060)
    at com.intellij.rt.testng.IDEARemoteTestNG.run(IDEARemoteTestNG.java:66)
    at com.intellij.rt.testng.RemoteTestNGStarter.main(RemoteTestNGStarter.java:110)

======================== testTpcdsQ78

java.lang.AssertionError: Expected query to fail: WITH
  ws AS (
   SELECT
     "d_year" "ws_sold_year"
   , "ws_item_sk"
   , "ws_bill_customer_sk" "ws_customer_sk"
   , "sum"("ws_quantity") "ws_qty"
   , "sum"("ws_wholesale_cost") "ws_wc"
   , "sum"("ws_sales_price") "ws_sp"
   FROM
     ((web_sales
   LEFT JOIN web_returns ON ("wr_order_number" = "ws_order_number")
      AND ("ws_item_sk" = "wr_item_sk"))
   INNER JOIN date_dim ON ("ws_sold_date_sk" = "d_date_sk"))
   WHERE ("wr_order_number" IS NULL)
   GROUP BY "d_year", "ws_item_sk", "ws_bill_customer_sk"
) 
, cs AS (
   SELECT
     "d_year" "cs_sold_year"
   , "cs_item_sk"
   , "cs_bill_customer_sk" "cs_customer_sk"
   , "sum"("cs_quantity") "cs_qty"
   , "sum"("cs_wholesale_cost") "cs_wc"
   , "sum"("cs_sales_price") "cs_sp"
   FROM
     ((catalog_sales
   LEFT JOIN catalog_returns ON ("cr_order_number" = "cs_order_number")
      AND ("cs_item_sk" = "cr_item_sk"))
   INNER JOIN date_dim ON ("cs_sold_date_sk" = "d_date_sk"))
   WHERE ("cr_order_number" IS NULL)
   GROUP BY "d_year", "cs_item_sk", "cs_bill_customer_sk"
) 
, ss AS (
   SELECT
     "d_year" "ss_sold_year"
   , "ss_item_sk"
   , "ss_customer_sk"
   , "sum"("ss_quantity") "ss_qty"
   , "sum"("ss_wholesale_cost") "ss_wc"
   , "sum"("ss_sales_price") "ss_sp"
   FROM
     ((store_sales
   LEFT JOIN store_returns ON ("sr_ticket_number" = "ss_ticket_number")
      AND ("ss_item_sk" = "sr_item_sk"))
   INNER JOIN date_dim ON ("ss_sold_date_sk" = "d_date_sk"))
   WHERE ("sr_ticket_number" IS NULL)
   GROUP BY "d_year", "ss_item_sk", "ss_customer_sk"
) 
SELECT
  "ss_sold_year"
, "ss_item_sk"
, "ss_customer_sk"
, "round"((CAST("ss_qty" AS DECIMAL(10,2)) / COALESCE(("ws_qty" + "cs_qty"), 1)), 2) "ratio"
, "ss_qty" "store_qty"
, "ss_wc" "store_wholesale_cost"
, "ss_sp" "store_sales_price"
, (COALESCE("ws_qty", 0) + COALESCE("cs_qty", 0)) "other_chan_qty"
, (COALESCE("ws_wc", 0) + COALESCE("cs_wc", 0)) "other_chan_wholesale_cost"
, (COALESCE("ws_sp", 0) + COALESCE("cs_sp", 0)) "other_chan_sales_price"
FROM
  ((ss
LEFT JOIN ws ON ("ws_sold_year" = "ss_sold_year")
   AND ("ws_item_sk" = "ss_item_sk")
   AND ("ws_customer_sk" = "ss_customer_sk"))
LEFT JOIN cs ON ("cs_sold_year" = "ss_sold_year")
   AND ("cs_item_sk" = "cs_item_sk")
   AND ("cs_customer_sk" = "ss_customer_sk"))
WHERE (COALESCE("ws_qty", 0) > 0)
   AND (COALESCE("cs_qty", 0) > 0)
   AND ("ss_sold_year" = 2000)
ORDER BY "ss_sold_year" ASC, "ss_item_sk" ASC, "ss_customer_sk" ASC, "ss_qty" DESC, "ss_wc" DESC, "ss_sp" DESC, "other_chan_qty" ASC, "other_chan_wholesale_cost" ASC, "other_chan_sales_price" ASC, "round"((CAST("ss_qty" AS DECIMAL(10,2)) / COALESCE(("ws_qty" + "cs_qty"), 1)), 2) ASC
LIMIT 100

    at org.testng.Assert.fail(Assert.java:110)
    at com.facebook.presto.tests.QueryAssertions.assertQueryFails(QueryAssertions.java:329)
    at com.facebook.presto.tests.AbstractTestQueryFramework.assertQueryFails(AbstractTestQueryFramework.java:265)
    at com.facebook.presto.nativeworker.AbstractTestNativeTpcdsQueries.testTpcdsQ78(AbstractTestNativeTpcdsQueries.java:1015)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.testng.internal.invokers.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:135)
    at org.testng.internal.invokers.TestInvoker.invokeMethod(TestInvoker.java:673)
    at org.testng.internal.invokers.TestInvoker.invokeTestMethod(TestInvoker.java:220)
    at org.testng.internal.invokers.MethodRunner.runInSequence(MethodRunner.java:50)
    at org.testng.internal.invokers.TestInvoker$MethodInvocationAgent.invoke(TestInvoker.java:945)
    at org.testng.internal.invokers.TestInvoker.invokeTestMethods(TestInvoker.java:193)
    at org.testng.internal.invokers.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:146)
    at org.testng.internal.invokers.TestMethodWorker.run(TestMethodWorker.java:128)
    at java.util.ArrayList.forEach(ArrayList.java:1259)
    at org.testng.TestRunner.privateRun(TestRunner.java:808)
    at org.testng.TestRunner.run(TestRunner.java:603)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:429)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:423)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:383)
    at org.testng.SuiteRunner.run(SuiteRunner.java:326)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:95)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1249)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1169)
    at org.testng.TestNG.runSuites(TestNG.java:1092)
    at org.testng.TestNG.run(TestNG.java:1060)
    at com.intellij.rt.testng.IDEARemoteTestNG.run(IDEARemoteTestNG.java:66)
    at com.intellij.rt.testng.RemoteTestNGStarter.main(RemoteTestNGStarter.java:110)
spershin commented 1 year ago

cc @frankobe @aditi-pandit @majetideepak

frankobe commented 1 year ago

@spershin I am looking into this