Skip to content

Commit 0eea6d9

Browse files
author
sparksburnitt
committed
Add order-by clauses to queries where ordering might vary on different platforms, and fix the asserts that were not failing incorrect orderings.
1 parent a21dc91 commit 0eea6d9

File tree

1 file changed

+80
-50
lines changed

1 file changed

+80
-50
lines changed

‎src/test/scala/org/apache/spark/sql/hbase/HBaseTpcStringFormatMiniTestSuite.scala

Lines changed: 80 additions & 50 deletions
Original file line numberDiff line numberDiff line change
@@ -155,7 +155,11 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
155155
}
156156

157157
test("Query 1") {
158-
val sql = "SELECT ss_quantity, ss_wholesale_cost, ss_list_price FROM store_sales_stringformat WHERE ss_item_sk = 574 AND ss_ticket_number = 29"
158+
val sql =
159+
s"""SELECT ss_quantity, ss_wholesale_cost, ss_list_price
160+
|FROM store_sales_stringformat
161+
|WHERE ss_item_sk = 574
162+
|AND ss_ticket_number = 29""".stripMargin
159163
val rows = runSql(sql)
160164
// printRows(rows)
161165
assert(rows.size == 1)
@@ -167,7 +171,9 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
167171
test("Query 2") {
168172
val sql =
169173
s"""SELECT ss_sold_date_sk, ss_sold_time_sk, ss_store_sk
170-
|FROM store_sales_stringformat WHERE ss_item_sk = 3163 AND ss_ticket_number = 7"""
174+
|FROM store_sales_stringformat
175+
|WHERE ss_item_sk = 3163
176+
|AND ss_ticket_number = 7"""
171177
.stripMargin
172178
val rows = runSql(sql)
173179
// printRows(rows)
@@ -181,7 +187,8 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
181187
val sql =
182188
s"""SELECT ss_customer_sk, ss_promo_sk, ss_coupon_amt, ss_net_profit
183189
|FROM store_sales_stringformat
184-
|WHERE ss_item_sk = 18814 AND ss_ticket_number = 29"""
190+
|WHERE ss_item_sk = 18814
191+
|AND ss_ticket_number = 29"""
185192
.stripMargin
186193
val rows = runSql(sql)
187194
// printRows(rows)
@@ -255,19 +262,20 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
255262
s"""SELECT ss_item_sk, ss_ticket_number, sum(ss_wholesale_cost) as sum_wholesale_cost
256263
|FROM store_sales_stringformat
257264
|WHERE ss_item_sk > 9000 AND ss_item_sk < 18000
258-
|GROUP BY ss_item_sk, ss_ticket_number"""
265+
|GROUP BY ss_item_sk, ss_ticket_number
266+
|ORDER BY ss_item_sk, ss_ticket_number"""
259267
.stripMargin
260268
val rows = runSql(sql)
261269
// printRows(rows)
262270
assert(rows.length == 2)
263271

264-
assert(rows(0).get(0) == 16335)
265-
assert(rows(0).get(1) == 10)
266-
assert(rows(0).get(2) == 82.3499984741211)
272+
assert(rows(0).get(0) == 12919)
273+
assert(rows(0).get(1) == 30)
274+
assert(rows(0).get(2) == 61.959999084472656)
267275

268-
assert(rows(1).get(0) == 12919)
269-
assert(rows(1).get(1) == 30)
270-
assert(rows(1).get(2) == 61.959999084472656)
276+
assert(rows(1).get(0) == 16335)
277+
assert(rows(1).get(1) == 10)
278+
assert(rows(1).get(2) == 82.3499984741211)
271279
}
272280

273281
test("Query 8") {
@@ -278,23 +286,30 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
278286
|avg(ss_wholesale_cost) as avg_wholesale_cost
279287
|FROM store_sales_stringformat
280288
|WHERE ss_item_sk > 1000 AND ss_item_sk < 18000
281-
|GROUP BY ss_item_sk, ss_ticket_number"""
289+
|GROUP BY ss_item_sk, ss_ticket_number
290+
|ORDER BY ss_item_sk, ss_ticket_number"""
282291
.stripMargin
283292
val rows = runSql(sql)
284293
// printRows(rows)
285294
assert(rows.length == 5)
286295

287-
assert(rows(0).get(0) == 16335)
288-
assert(rows(0).get(1) == 10)
289-
assert(rows(0).get(2) == 82.35f)
290-
assert(rows(0).get(2) == 82.35f)
291-
assert(rows(0).get(2) == 82.3499984741211)
296+
assert(rows(0).get(0) == 1579)
297+
assert(rows(0).get(1) == 30)
298+
assert(rows(0).get(2) == 64.0f)
299+
assert(rows(0).get(3) == 64.0f)
300+
assert(rows(0).get(4) == 64.0)
292301

293-
assert(rows(4).get(0) == 3163)
294-
assert(rows(4).get(1) == 7)
295-
assert(rows(4).get(2) == 69.53f)
296-
assert(rows(4).get(2) == 69.53f)
297-
assert(rows(4).get(2) == 69.52999877929688)
302+
assert(rows(2).get(0) == 3163)
303+
assert(rows(2).get(1) == 7)
304+
assert(rows(2).get(2) == 69.53f)
305+
assert(rows(2).get(3) == 69.53f)
306+
assert(rows(2).get(4) == 69.52999877929688)
307+
308+
assert(rows(4).get(0) == 16335)
309+
assert(rows(4).get(1) == 10)
310+
assert(rows(4).get(2) == 82.35f)
311+
assert(rows(4).get(3) == 82.35f)
312+
assert(rows(4).get(4) == 82.3499984741211)
298313
}
299314

300315
test("Query 9") {
@@ -356,7 +371,10 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
356371
}
357372

358373
test("Query 12") {
359-
val sql = "SELECT count(distinct ss_customer_sk) as count_distinct_customer FROM store_sales_stringformat"
374+
val sql =
375+
s"""SELECT count(distinct ss_customer_sk) as count_distinct_customer
376+
|FROM store_sales_stringformat"""
377+
.stripMargin
360378
val rows = runSql(sql)
361379
// printRows(rows)
362380
assert(rows(0).get(0) == 5)
@@ -403,7 +421,8 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
403421
val sql =
404422
s"""SELECT count(ss_customer_sk) as count_customer
405423
|FROM store_sales_stringformat
406-
|WHERE ss_customer_sk <= 147954 AND ss_quantity < 5000"""
424+
|WHERE ss_customer_sk <= 147954
425+
|AND ss_quantity < 5000"""
407426
.stripMargin
408427
val rows = runSql(sql)
409428
// printRows(rows)
@@ -425,21 +444,23 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
425444
val sql =
426445
s"""SELECT ss_ticket_number, ss_quantity, ss_wholesale_cost, ss_list_price
427446
|FROM store_sales_stringformat
428-
|WHERE ss_ticket_number = 10 OR ss_wholesale_cost < 17.33"""
447+
|WHERE ss_ticket_number = 10
448+
|OR ss_wholesale_cost < 17.33
449+
|ORDER BY ss_ticket_number, ss_quantity, ss_wholesale_cost, ss_list_price"""
429450
.stripMargin
430451
val rows = runSql(sql)
431452
// printRows(rows)
432453
assert(rows.length == 3)
433454

434455
assert(rows(0).get(0) == 10)
435-
assert(rows(0).get(1) == 83)
436-
assert(rows(0).get(2) == 10.26f)
437-
assert(rows(0).get(3) == 17.33f)
456+
assert(rows(0).get(1) == 66)
457+
assert(rows(0).get(2) == 82.35f)
458+
assert(rows(0).get(3) == 137.52f)
438459

439460
assert(rows(1).get(0) == 10)
440-
assert(rows(1).get(1) == 66)
441-
assert(rows(1).get(2) == 82.35f)
442-
assert(rows(1).get(3) == 137.52f)
461+
assert(rows(1).get(1) == 83)
462+
assert(rows(1).get(2) == 10.26f)
463+
assert(rows(1).get(3) == 17.33f)
443464

444465
assert(rows(2).get(0) == 11)
445466
assert(rows(2).get(1) == 68)
@@ -525,7 +546,8 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
525546
s"""SELECT strkey, ss_item_sk, ss_ticket_number, count(1)
526547
|FROM store_sales_stringformat
527548
|WHERE ss_ticket_number >= 10 and ss_ticket_number <= 20
528-
|GROUP BY strkey, ss_item_sk, ss_ticket_number"""
549+
|GROUP BY strkey, ss_item_sk, ss_ticket_number
550+
|ORDER BY strkey, ss_item_sk, ss_ticket_number"""
529551
.stripMargin
530552
val rows = runSql(sql)
531553
// printRows(rows)
@@ -536,14 +558,14 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
536558
assert(rows(0).get(2) == 10)
537559
assert(rows(0).get(3) == 1)
538560

539-
assert(rows(1).get(0) == "18669000000011")
540-
assert(rows(1).get(1) == 18669)
541-
assert(rows(1).get(2) == 11)
561+
assert(rows(1).get(0) == "16335000000010")
562+
assert(rows(1).get(1) == 16335)
563+
assert(rows(1).get(2) == 10)
542564
assert(rows(1).get(3) == 1)
543565

544-
assert(rows(2).get(0) == "16335000000010")
545-
assert(rows(2).get(1) == 16335)
546-
assert(rows(2).get(2) == 10)
566+
assert(rows(2).get(0) == "18669000000011")
567+
assert(rows(2).get(1) == 18669)
568+
assert(rows(2).get(2) == 11)
547569
assert(rows(2).get(3) == 1)
548570
}
549571

@@ -552,7 +574,8 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
552574
s"""SELECT strkey, ss_item_sk, ss_ticket_number, SUM(ss_wholesale_cost) AS sum_wholesale_cost
553575
|FROM store_sales_stringformat
554576
|WHERE ss_ticket_number >= 10 and ss_ticket_number <= 20
555-
|GROUP BY strkey, ss_item_sk, ss_ticket_number"""
577+
|GROUP BY strkey, ss_item_sk, ss_ticket_number
578+
|ORDER BY strkey, ss_item_sk, ss_ticket_number"""
556579
.stripMargin
557580
val rows = runSql(sql)
558581
// printRows(rows)
@@ -564,15 +587,15 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
564587
assert(rows(0).get(2) == 10)
565588
assert(rows(0).get(3) == 10.260000228881836)
566589

567-
assert(rows(1).get(0) == "18669000000011")
568-
assert(rows(1).get(1) == 18669)
569-
assert(rows(1).get(2) == 11)
570-
assert(rows(1).get(3) == 7.159999847412109)
590+
assert(rows(1).get(0) == "16335000000010")
591+
assert(rows(1).get(1) == 16335)
592+
assert(rows(1).get(2) == 10)
593+
assert(rows(1).get(3) == 82.3499984741211)
571594

572-
assert(rows(2).get(0) == "16335000000010")
573-
assert(rows(2).get(1) == 16335)
574-
assert(rows(2).get(2) == 10)
575-
assert(rows(2).get(3) == 82.3499984741211)
595+
assert(rows(2).get(0) == "18669000000011")
596+
assert(rows(2).get(1) == 18669)
597+
assert(rows(2).get(2) == 11)
598+
assert(rows(2).get(3) == 7.159999847412109)
576599
}
577600

578601

@@ -710,7 +733,11 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
710733
}
711734

712735
test("Query 27") {
713-
val sql = "SELECT * FROM store_sales_stringformat WHERE ss_ticket_number + 0 = 10 and ss_sold_date_sk + 0 > 0"
736+
val sql =
737+
s"""SELECT * FROM store_sales_stringformat
738+
|WHERE ss_ticket_number + 0 = 10
739+
|AND ss_sold_date_sk + 0 > 0"""
740+
.stripMargin
714741
val rows = runSql(sql)
715742
// printRows(rows)
716743

@@ -730,7 +757,8 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
730757
test("Query 28") {
731758
val sql =
732759
s"""SELECT * FROM store_sales_stringformat
733-
|WHERE ss_cdemo_sk IS NULL""".stripMargin
760+
|WHERE ss_cdemo_sk IS NULL"""
761+
.stripMargin
734762
val rows = runSql(sql)
735763
// printRows(rows)
736764

@@ -755,7 +783,8 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
755783
test("Query 30") {
756784
val sql =
757785
s"""SELECT * FROM store_sales_stringformat
758-
|WHERE ss_cdemo_sk IS NOT NULL AND ss_ticket_number = 29"""
786+
|WHERE ss_cdemo_sk IS NOT NULL
787+
|AND ss_ticket_number = 29"""
759788
.stripMargin
760789
val rows = runSql(sql)
761790
// printRows(rows)
@@ -775,7 +804,8 @@ class HBaseTpcStringFormatMiniTestSuite extends TestBase {
775804
test("Query 31") {
776805
val sql =
777806
s"""SELECT * FROM store_sales_stringformat
778-
|WHERE ss_cdemo_sk IS NULL AND ss_ticket_number = 29"""
807+
|WHERE ss_cdemo_sk IS NULL
808+
|AND ss_ticket_number = 29"""
779809
.stripMargin
780810
val rows = runSql(sql)
781811
// printRows(rows)

0 commit comments

Comments
 (0)