[KQP] Many result sets added (#9696)

2 files changed, 178 insertions, 22 deletions

diff --git a/ydb/core/kqp/ut/join/data/queries/tpcds23.sql b/ydb/core/kqp/ut/join/data/queries/tpcds23.sql
new file mode 100644
index 0000000000..c3262f794f
--- /dev/null
+++ b/ydb/core/kqp/ut/join/data/queries/tpcds23.sql
@@ -0,0 +1,169 @@
+pragma TablePathPrefix = "/Root/test/ds/";
+
+-- NB: Subquerys
+
+$blabla = (
+
+    select substring(cast(item.i_item_desc as string),0,30) itemdesc,item.i_item_sk item_sk,date_dim.d_date solddate
+
+  from store_sales as store_sales
+
+      cross join date_dim as date_dim
+
+      cross join item as item
+
+  where ss_sold_date_sk = d_date_sk
+
+    and ss_item_sk = i_item_sk
+
+    and d_year in (2000,2000+1,2000+2,2000+3)
+
+);
+
+$frequent_ss_items =
+
+ (select itemdesc, item_sk, solddate,count(*) cnt
+
+  from $blabla
+
+  group by itemdesc,item_sk,solddate
+
+  having count(*) >4);
+
+
+
+$max_store_sales =
+
+ (select max(csales) tpcds_cmax
+
+  from (select customer.c_customer_sk c_customer_sk,sum(ss_quantity*ss_sales_price) csales
+
+        from store_sales as store_sales
+
+            cross join customer as customer
+
+            cross join date_dim as date_dim
+
+        where ss_customer_sk = c_customer_sk
+
+         and ss_sold_date_sk = d_date_sk
+
+         and d_year in (2000,2000+1,2000+2,2000+3)
+
+        group by customer.c_customer_sk) x);
+
+
+
+$best_ss_customer =
+
+ (select customer.c_customer_sk c_customer_sk,sum(ss_quantity*ss_sales_price) ssales
+
+  from store_sales as store_sales
+
+      cross join customer as customer
+
+  where ss_customer_sk = c_customer_sk
+
+  group by customer.c_customer_sk
+
+  having sum(ss_quantity*ss_sales_price) > (95/100.0) * $max_store_sales);
+
+
+
+-- start query 1 in stream 0 using template query23.tpl and seed 2031708268
+
+select  sum(sales)
+
+ from (select cs_quantity*cs_list_price sales
+
+       from catalog_sales as catalog_sales
+
+           cross join date_dim as date_dim
+
+       where d_year = 2000
+
+         and d_moy = 3
+
+         and cs_sold_date_sk = d_date_sk
+
+         and cs_item_sk in (select item_sk from $frequent_ss_items)
+
+         and cs_bill_customer_sk in (select c_customer_sk from $best_ss_customer)
+
+      union all
+
+      select ws_quantity*ws_list_price sales
+
+       from web_sales as web_sales
+
+           cross join date_dim as date_dim
+
+       where d_year = 2000
+
+         and d_moy = 3
+
+         and ws_sold_date_sk = d_date_sk
+
+         and ws_item_sk in (select item_sk from $frequent_ss_items)
+
+         and ws_bill_customer_sk in (select c_customer_sk from $best_ss_customer)) y
+
+ limit 100;
+
+
+
+select  c_last_name,c_first_name,sales
+
+ from (select customer.c_last_name c_last_name,customer.c_first_name c_first_name,sum(cs_quantity*cs_list_price) sales
+
+        from catalog_sales as catalog_sales
+
+            cross join customer as customer
+
+            cross join date_dim as date_dim
+
+        where d_year = 2000
+
+         and d_moy = 3
+
+         and cs_sold_date_sk = d_date_sk
+
+         and cs_item_sk in (select item_sk from $frequent_ss_items)
+
+         and cs_bill_customer_sk in (select c_customer_sk from $best_ss_customer)
+
+         and cs_bill_customer_sk = c_customer_sk
+
+       group by customer.c_last_name,customer.c_first_name
+
+      union all
+
+      select customer.c_last_name c_last_name,customer.c_first_name c_first_name,sum(ws_quantity*ws_list_price) sales
+
+       from web_sales as web_sales
+
+           cross join customer as customer
+
+           cross join date_dim as date_dim
+
+       where d_year = 2000
+
+         and d_moy = 3
+
+         and ws_sold_date_sk = d_date_sk
+
+         and ws_item_sk in (select item_sk from $frequent_ss_items)
+
+         and ws_bill_customer_sk in (select c_customer_sk from $best_ss_customer)
+
+         and ws_bill_customer_sk = c_customer_sk
+
+       group by customer.c_last_name,customer.c_first_name) y
+
+     order by c_last_name,c_first_name,sales
+
+  limit 100;
+
+
+
+-- end query 1 in stream 0 using template query23.tpl
\ No newline at end of file
diff --git a/ydb/core/kqp/ut/join/kqp_join_order_ut.cpp b/ydb/core/kqp/ut/join/kqp_join_order_ut.cpp
index dcc929dd23..1027b08e01 100644
--- a/ydb/core/kqp/ut/join/kqp_join_order_ut.cpp
+++ b/ydb/core/kqp/ut/join/kqp_join_order_ut.cpp
@@ -173,8 +173,8 @@ private:
     size_t ChainSize; 
 };
 
-void ExplainJoinOrderTestDataQuery(const TString& queryPath, bool useStreamLookupJoin, bool useColumnStore) {
-    auto kikimr = GetKikimrWithJoinSettings(useStreamLookupJoin);
+void ExplainJoinOrderTestDataQueryWithStats(const TString& queryPath, const TString& statsPath, bool useStreamLookupJoin, bool useColumnStore) {
+    auto kikimr = GetKikimrWithJoinSettings(useStreamLookupJoin, GetStatic(statsPath));
     auto db = kikimr.GetTableClient();
     auto session = db.CreateSession().GetValueSync().GetSession();
 
@@ -191,24 +191,6 @@ void ExplainJoinOrderTestDataQuery(const TString& queryPath, bool useStreamLooku
     }
 }
 
-void ExecuteJoinOrderTestDataQuery(const TString& queryPath, bool useStreamLookupJoin, bool useColumnStore) {
-    auto kikimr = GetKikimrWithJoinSettings(useStreamLookupJoin);
-    auto db = kikimr.GetTableClient();
-    auto session = db.CreateSession().GetValueSync().GetSession();
-
-    CreateSampleTable(session, useColumnStore);
-
-    /* join with parameters */
-    {
-        const TString query = GetStatic(queryPath);
-        
-        auto result = session.ExecuteDataQuery(query, TTxControl::BeginTx().CommitTx()).ExtractValueSync();
-        result.GetIssues().PrintTo(Cerr);
-        UNIT_ASSERT_VALUES_EQUAL(result.GetStatus(), EStatus::SUCCESS);
-        PrintPlan(result.GetQueryPlan());
-    }
-}
-
 void TestOlapEstimationRowsCorrectness(const TString& queryPath, const TString& statsPath) {
     auto kikimr = GetKikimrWithJoinSettings(false, GetStatic(statsPath));
     auto db = kikimr.GetTableClient();
@@ -373,6 +355,13 @@ Y_UNIT_TEST_SUITE(KqpJoinOrder) {
         }
     }
 
+    /* tpcds23 has > 1 result sets */
+    Y_UNIT_TEST_XOR_OR_BOTH_FALSE(TPCDS23, StreamLookupJoin, ColumnStore) {
+        ExplainJoinOrderTestDataQueryWithStats(
+            "queries/tpcds23.sql", "stats/tpcds1000s.json", StreamLookupJoin, ColumnStore
+        );
+    }
+
     Y_UNIT_TEST_XOR_OR_BOTH_FALSE(FiveWayJoin, StreamLookupJoin, ColumnStore) {
         ExecuteJoinOrderTestDataQueryWithStats(
             "queries/five_way_join.sql", "stats/basic.json", StreamLookupJoin, ColumnStore
@@ -541,8 +530,6 @@ Y_UNIT_TEST_SUITE(KqpJoinOrder) {
             PrintPlan(result.GetPlan());
             UNIT_ASSERT_VALUES_EQUAL(result.GetStatus(), EStatus::SUCCESS);
 
-            NYdb::NConsoleClient::TQueryPlanPrinter queryPlanPrinter(NYdb::NConsoleClient::EDataFormat::PrettyTable, true, Cout, 0);
-            queryPlanPrinter.Print(result.GetPlan());
             if (useStreamLookupJoin) {
                 return;
             }