This is an automated email from the ASF dual-hosted git repository.
viirya pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion-comet.git
The following commit(s) were added to refs/heads/main by this push:
new 8274a618 test: Run optimized version of q72 derived from TPC-DS (#652)
8274a618 is described below
commit 8274a618127123dbafa0954fac0193fea43dc116
Author: Liang-Chi Hsieh <[email protected]>
AuthorDate: Thu Jul 11 10:46:12 2024 -0700
test: Run optimized version of q72 derived from TPC-DS (#652)
---
spark/src/test/resources/tpcds-extended/q72.sql | 26 +++++
.../tpcds-query-results/extended/q72.sql.out | 105 +++++++++++++++++++++
.../spark/sql/CometTPCDSQueryTestSuite.scala | 15 +++
3 files changed, 146 insertions(+)
diff --git a/spark/src/test/resources/tpcds-extended/q72.sql
b/spark/src/test/resources/tpcds-extended/q72.sql
new file mode 100644
index 00000000..4c95c8aa
--- /dev/null
+++ b/spark/src/test/resources/tpcds-extended/q72.sql
@@ -0,0 +1,26 @@
+select i_item_desc
+ ,w_warehouse_name
+ ,d1.d_week_seq
+ ,sum(case when p_promo_sk is null then 1 else 0 end) no_promo
+ ,sum(case when p_promo_sk is not null then 1 else 0 end) promo
+ ,count(*) total_cnt
+from catalog_sales
+join date_dim d1 on (cs_sold_date_sk = d1.d_date_sk)
+join customer_demographics on (cs_bill_cdemo_sk = cd_demo_sk)
+join household_demographics on (cs_bill_hdemo_sk = hd_demo_sk)
+join item on (i_item_sk = cs_item_sk)
+join inventory on (cs_item_sk = inv_item_sk)
+join warehouse on (w_warehouse_sk=inv_warehouse_sk)
+join date_dim d2 on (inv_date_sk = d2.d_date_sk)
+join date_dim d3 on (cs_ship_date_sk = d3.d_date_sk)
+left outer join promotion on (cs_promo_sk=p_promo_sk)
+left outer join catalog_returns on (cr_item_sk = cs_item_sk and
cr_order_number = cs_order_number)
+where d1.d_week_seq = d2.d_week_seq
+ and inv_quantity_on_hand < cs_quantity
+ and d3.d_date > d1.d_date + 5
+ and hd_buy_potential = '501-1000'
+ and d1.d_year = 1999
+ and cd_marital_status = 'S'
+group by i_item_desc,w_warehouse_name,d1.d_week_seq
+order by total_cnt desc, i_item_desc, w_warehouse_name, d_week_seq
+LIMIT 100
diff --git a/spark/src/test/resources/tpcds-query-results/extended/q72.sql.out
b/spark/src/test/resources/tpcds-query-results/extended/q72.sql.out
new file mode 100644
index 00000000..7054939e
--- /dev/null
+++ b/spark/src/test/resources/tpcds-query-results/extended/q72.sql.out
@@ -0,0 +1,105 @@
+-- Automatically generated by CometTPCDSQuerySuite
+
+-- !query schema
+struct<i_item_desc:string,w_warehouse_name:string,d_week_seq:int,no_promo:bigint,promo:bigint,total_cnt:bigint>
+-- !query output
+Acceptable statements kill only important grounds. Various, high rights can
lie also maj Operations 5197 0 2 2
+Acceptable statements kill only important grounds. Various, high rights can
lie also maj Selective, 5197 0 2 2
+Bloody conditions cannot become. Libraries operate enough further good police.
Matches produce 5212 0 2 2
+Democratic friends would represent. British, welcome problems could show never
better special classes. Gradually daily sites practice at a men. Genetic,
british numbers use Matches produce 5215 0 2 2
+Direct, private interpretations look perhaps under political policies.
Casualties can feel only results. Architects Matches produce 5197 0
2 2
+Due conclusions will not attra Just good amou 5213 0 2 2
+Free activities pay particularly from a questions. British engineers complete
commonly political doors. Bright, w Operations 5179 0 2
2
+Great doors could recall too studies. Then dual things kill. Meanwhile
interesting appointments should name direct months. Potential theories change
constant feet; hot resourc Selective, 5179 0 2 2
+Grey artists think there. Points meet with a p Just good amou 5197 0
2 2
+Here economic words appear widely between a feet. Financial yards used to wo
Selective, 5215 0 2 2
+Ideas would allow full-time good m Just good amou 5197 0 2
2
+Images will relax in particular new skills. Urban obligations must not mark
completely. Officials must not cope both emotionally dif Just good amou 5195
0 2 2
+Images will relax in particular new skills. Urban obligations must not mark
completely. Officials must not cope both emotionally dif Operations 5195
0 2 2
+Other parties will add regional, special owners. Little administrative horses
may indicate; Matches produce 5206 0 2 2
+Parliamentary, irish achievements come specifically probably normal efforts.
Boxes prove activities. Particular spirits display above, hot legs. Willing,
warm police w Matches produce 5212 0 2 2
+Patterns hear available, bitter women. Certain, standard proposals m
Operations 5205 0 2 2
+Years w Matches produce 5211 0 2 2
+NULL Just good amou 5211 0 1 1
+NULL Matches produce 5202 0 1 1
+NULL Matches produce 5209 0 1 1
+NULL Operations 5198 0 1 1
+NULL Operations 5209 0 1 1
+NULL Operations 5217 0 1 1
+NULL Selective, 5181 0 1 1
+NULL Significantly 5209 0 1 1
+A Selective, 5194 0 1 1
+A Selective, 5208 0 1 1
+A little local letters think over like a children; nevertheless particular
powers damage now suddenly absent prote Matches produce 5215 0 1
1
+A little local letters think over like a children; nevertheless particular
powers damage now suddenly absent prote Significantly 5205 0 1
1
+A lot young materials remain below from a rises. Operations 5186
0 1 1
+Able men become in order today other methods. Slightly royal shoulders would
not operate centres. Statutory, social bills may not take other, main stages.
Prayers cannot meet. Words m Selective, 5177 0 1 1
+Able men become in order today other methods. Slightly royal shoulders would
not operate centres. Statutory, social bills may not take other, main stages.
Prayers cannot meet. Words m Significantly 5177 0 1 1
+Able troubles dust into the styles. Independent feet kill wounds. Fundamental
months should exploit arms. Massive years read only modern courses; twin forms
shall become products. Even h Significantly 5215 0 1 1
+Able, available problems apply even to a bodies. Patients so Matches produce
5211 0 1 1
+Able, continuous figures see with a patients. Men go more open notes.
Different engineers can display. Even strong fortunes cannot put at least
general fans; reliable talk Matches produce 5211 0 1 1
+Able, continuous figures see with a patients. Men go more open notes.
Different engineers can display. Even strong fortunes cannot put at least
general fans; reliable talk Operations 5211 0 1 1
+About right clothes must get thoughtfully to a cases. Eastern improvements
Matches produce 5202 0 1 1
+Ac Just good amou 5205 0 1 1
+Ac Matches produce 5183 0 1 1
+Accessible kids insist ministers. Causal, political goods find then other
products. Following, average years may want considerably now general causes. N
Selective, 5209 0 1 1
+Accidentally wrong communities look more goods. Rural matters recognize.
Large, new days go hap Selective, 5178 0 1 1
+Accidents fly bet Significantly 5167 0 1 1
+Accounts accept Operations 5213 0 1 1
+Accounts could think aspects. Industrial, large Selective, 5211
0 1 1
+Accounts return into a colleagues Operations 5176 0 1
1
+Accused times should stop then notes. Wor Just good amou 5190 0
1 1
+Accused times should stop then notes. Wor Selective, 5218 0
1 1
+Acres could provide. There chronic principles care highest at a details.
Little operators look elsewhere long recommendations. Daily poets dig bravely
remaining aims. Now willing services appear Selective, 5205 0
1 1
+Actions see of course informal phrases. Markedly right men buy honest,
additional stations. In order imaginative factors used to move human thanks.
Centres shall catch altogether succe Just good amou 5192 0 1
1
+Active studies state away already large shelves. Extremely international appli
Significantly 5208 0 1 1
+Active years refuse all poor others. Old, reluctant friends live new, liberal
varieties. English, full hours belong as eve Matches produce 5202 0
1 1
+Acts protect forces. Still only options use controls. Less manufacturing cups
let well sure, good fires. Sorry companies should die. All rare nurses maintain
Just good amou 5212 0 1 1
+Acts protect forces. Still only options use controls. Less manufacturing cups
let well sure, good fires. Sorry companies should die. All rare nurses maintain
Selective, 5200 0 1 1
+Acts will not reflect as with the problems. General governments distract new,
soft fires. Useful proposals restrict hard trees. Large, black customs go
official Just good amou 5201 0 1 1
+Actual things should prevent there responsible schemes. Others go all
undoubtedly increasing things. Small, full samples analys Significantly
5167 0 1 1
+Actually labour schools think about a holes. So disciplinary prov
Significantly 5179 0 1 1
+Acute, old funds must sleep running, different orders. Black, international
changes should cope enough multiple brothers. Ago available police attract
simply internationa Matches produce 5207 0 1 1
+Additional, human standards should not dream also silly forms. More
independent friends may believ Significantly 5203 0 1 1
+Additionally effective sea Operations 5182 0 1 1
+Addresses retain once more applicable events. Following blocks follow for a
develo Selective, 5169 0 1 1
+Adequate days take more sometimes high laws. Famous, exceptional gates must
pay loans. Other, slow methods see superb aspirations. Possible services apply
holes. Computers can free sole times. Costly Matches produce 5201 0
1 1
+Adults might not surrender doubtful, upper industries; earnings insist m
Operations 5181 0 1 1
+Adults will foresee most left, social children. Different eyes make personal
counties. Readers would not admit more musical proceedings; titles take here
away fast institutions; bird Significantly 5215 0 1 1
+Advanced, foreign stories would greet always corporate games. Recent dev
Significantly 5182 0 1 1
+Again brief things should remember only in a patients. Deals reply soon other
points. Increasingly religious times necessitate farther troops. Both added
programmes must come wonderfully solid pupi Matches produce 5212 0 1
1
+Again brief things should remember only in a patients. Deals reply soon other
points. Increasingly religious times necessitate farther troops. Both added
programmes must come wonderfully solid pupi Operations 5212 0 1
1
+Again economic objections spend suddenly urgently worried boats. Pupils talk
yet nonethele Significantly 5205 0 1 1
+Again major troubles create new, other children. Fair interactions may
telephone Matches produce 5188 0 1 1
+Again scottish women shall ag Just good amou 5204 0 1 1
+Again small aspects think apparently only sole problems; thick private years
may not matt Operations 5197 0 1 1
+Agencies join enough keen examples. Accu Just good amou 5214 0
1 1
+Ago light fingers blame enough green, british years. Children go general
stands. Economic, great numbers affect deputies. Purposes urge annually. Always
electrical ways vote judicial, regular ac Just good amou 5196 0 1
1
+Ago light fingers blame enough green, british years. Children go general
stands. Economic, great numbers affect deputies. Purposes urge annually. Always
electrical ways vote judicial, regular ac Just good amou 5208 0 1
1
+Ago new arguments accept previously european parents; fo Matches produce
5177 0 1 1
+Ago new arguments accept previously european parents; fo Significantly
5177 0 1 1
+Agricultural players shall smoke. So full reasons undertake Just good amou
5218 0 1 1
+Agricultural sites will not provide skills. Again Operations 5217
0 1 1
+All possible injuries maintain strictly mu Significantly 5211 0
1 1
+All right other details might distrib Operations 5186 0 1
1
+Almost little words used to explain annual men. Opportunities borrow. Quickly
general alternatives see prices. Again green firms must not help most so able
authorities; thankfully married hours mig Just good amou 5204 0 1
1
+Almost positive couples draw into a orders. Glasses adopt shortly mathematical
years. Ever big women set details. Successful, joint factors shall allow never
at all overseas deals. Topics would use n Significantly 5210 0
1 1
+Almost scientific flats divide easy, able hours. Broad years sit also through
a circumstances; more Just good amou 5181 0 1 1
+Alone healthy sales might meet far other roots. French groups look up to a
workers. Fully average miners would walk inadequate considerations. Small, sure
goods may admire more app Matches produce 5207 0 1 1
+Alone new copies discuss to a dates; all black machines get just just royal
years. For example free weeks underestimate accurately individual mountains.
National, delicious Just good amou 5211 0 1 1
+Alone sole services keep only; stairs shall eliminate for the woods. Methods
must need yet. Other students can Matches produce 5199 0 1 1
+Already early meetings cannot go animals. As comprehensive evenings w
Selective, 5201 0 1 1
+Already empty cups would prove already other white times. Various, able hands
live here different countries. Dealers may not cut effectively important
institutions. Rich, dramatic t Selective, 5214 0 1 1
+Already little levels will assume specialist, main costs. By now new miles
play meanwhile men; common, economic results achieve furious, sure holes;
persistent men die Operations 5218 0 1 1
+Also aware programmes should send for certain white letters. Eyes would not
treat visible centuries; wide, full languages see on behalf of the ages. Guards
illuminate feet. Nationally criminal cou Significantly 5191 0 1
1
+Also fun tories test of course. Rigid walls indicate then doors. Sometimes
large children want just foreign, americ Matches produce 5169 0 1
1
+Also major pieces resign never. Substan Matches produce 5178 0
1 1
+Also middle managers can appear originally negotiations. Things must stand
distinguished funds; only customers find here also foreign seeds. Green,
political Operations 5202 0 1 1
+Also possible systems could go forward. Local, british babies d Matches
produce 5215 0 1 1
+Also possible systems could go forward. Local, british babies d
Selective, 5215 0 1 1
+Also public dreams see. Then semantic services can allow in a powers. Things
must make sections. Spaces would not protect officials. Inappropriate charges
would act r Matches produce 5177 0 1 1
+Also quiet users fall. Other, current sources would c Operations 5206
0 1 1
+Also rational children achieve other trends. Rather conventional commitments
ought to refuse then current olympic members; suitably special te Just good
amou 5210 0 1 1
+Also rational children achieve other trends. Rather conventional commitments
ought to refuse then current olympic members; suitably special te Selective,
5210 0 1 1
+Alternative sessions might not know here all literary organisers. Ho
Significantly 5213 0 1 1
+Always cool temperatures meet there social grounds. Threats h Matches produce
5175 0 1 1
diff --git
a/spark/src/test/scala/org/apache/spark/sql/CometTPCDSQueryTestSuite.scala
b/spark/src/test/scala/org/apache/spark/sql/CometTPCDSQueryTestSuite.scala
index c2b85351..6aec042a 100644
--- a/spark/src/test/scala/org/apache/spark/sql/CometTPCDSQueryTestSuite.scala
+++ b/spark/src/test/scala/org/apache/spark/sql/CometTPCDSQueryTestSuite.scala
@@ -35,6 +35,8 @@ import org.apache.spark.sql.test.TestSparkSession
*/
class CometTPCDSQueryTestSuite extends QueryTest with TPCDSBase with
CometSQLQueryTestHelper {
+ val tpcdsExtendedQueries: Seq[String] = Seq("q72")
+
private val tpcdsDataPath = sys.env.get("SPARK_TPCDS_DATA")
private val regenGoldenFiles: Boolean =
System.getenv("SPARK_GENERATE_GOLDEN_FILES") == "1"
@@ -224,6 +226,19 @@ class CometTPCDSQueryTestSuite extends QueryTest with
TPCDSBase with CometSQLQue
}
}
}
+
+ tpcdsExtendedQueries.foreach { name =>
+ val queryString = resourceToString(
+ s"tpcds-extended/$name.sql",
+ classLoader = Thread.currentThread().getContextClassLoader)
+ test(s"extended $name") {
+ val goldenFile = new File(s"$baseResourcePath/extended",
s"$name.sql.out")
+ joinConfs.foreach { conf =>
+ System.gc() // SPARK-37368
+ runQuery(queryString, goldenFile, conf)
+ }
+ }
+ }
} else {
ignore("skipped because env `SPARK_TPCDS_DATA` is not set") {}
}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
