[ https://issues.apache.org/jira/browse/SPARK-15372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
JESSE CHEN updated SPARK-15372: ------------------------------- Description: The official TPC-DS query 84 returns wrong results when compared to its official answer set. The query itself is: {noformat} select c_customer_id as customer_id ,concat(c_last_name , ', ' , c_first_name) as customername from customer ,customer_address ,customer_demographics ,household_demographics ,income_band ,store_returns where ca_city = 'Edgewood' and c_current_addr_sk = ca_address_sk and ib_lower_bound >= 38128 and ib_upper_bound <= 38128 + 50000 and ib_income_band_sk = hd_income_band_sk and cd_demo_sk = c_current_cdemo_sk and hd_demo_sk = c_current_hdemo_sk and sr_cdemo_sk = cd_demo_sk order by c_customer_id limit 100; {noformat} Spark 2.0 build 0517 returned the following result: {noformat} AAAAAAAAAIPGAAAA Carter, Rodney AAAAAAAAAKMBBAAA Mcarthur, Emma AAAAAAAACBNHBAAA Wells, Ron AAAAAAAADBMEAAAA Vera, Tina AAAAAAAADBMEAAAA Vera, Tina AAAAAAAADHKGBAAA Scott, Pamela AAAAAAAAEIIBBAAA Atkins, Susan AAAAAAAAFKAHAAAA Batiste, Ernest AAAAAAAAGHMAAAAA Mitchell, Gregory AAAAAAAAIAODBAAA Murray, Karen AAAAAAAAIEOKAAAA Solomon, Clyde AAAAAAAAIIBOAAAA Owens, David AAAAAAAAIPDCAAAA Wallace, Eric AAAAAAAAIPIMAAAA Hayward, Benjamin AAAAAAAAJCIKAAAA Ramos, Donald AAAAAAAAKFJEAAAA Roberts, Yvonne AAAAAAAAKPGBBAAA NULL <------------------------ ??? questionable row AAAAAAAALCLABAAA Whitaker, Lettie AAAAAAAAMGMEAAAA Sharp, Michael AAAAAAAAMIGBBAAA Montgomery, Jesenia AAAAAAAAMPDKAAAA Lopez, Isabel AAAAAAAANEOMAAAA Powell, Linda AAAAAAAANKPCAAAA Shaffer, Sergio AAAAAAAANOCKAAAA Vargas, James AAAAAAAAOGJEBAAA Owens, Denice {noformat} Official answer set (which is correct!) {noformat} AAAAAAAAAIPGAAAA Carter , Rodney AAAAAAAAAKMBBAAA Mcarthur , Emma AAAAAAAACBNHBAAA Wells , Ron AAAAAAAADBMEAAAA Vera , Tina AAAAAAAADBMEAAAA Vera , Tina AAAAAAAADHKGBAAA Scott , Pamela AAAAAAAAEIIBBAAA Atkins , Susan AAAAAAAAFKAHAAAA Batiste , Ernest AAAAAAAAGHMAAAAA Mitchell , Gregory AAAAAAAAIAODBAAA Murray , Karen AAAAAAAAIEOKAAAA Solomon , Clyde AAAAAAAAIIBOAAAA Owens , David AAAAAAAAIPDCAAAA Wallace , Eric AAAAAAAAIPIMAAAA Hayward , Benjamin AAAAAAAAJCIKAAAA Ramos , Donald AAAAAAAAKFJEAAAA Roberts , Yvonne AAAAAAAAKPGBBAAA Moore , AAAAAAAALCLABAAA Whitaker , Lettie AAAAAAAAMGMEAAAA Sharp , Michael AAAAAAAAMIGBBAAA Montgomery , Jesenia AAAAAAAAMPDKAAAA Lopez , Isabel AAAAAAAANEOMAAAA Powell , Linda AAAAAAAANKPCAAAA Shaffer , Sergio AAAAAAAANOCKAAAA Vargas , James AAAAAAAAOGJEBAAA Owens , Denice {noformat} The issue is with the "concat" function in Spark SQL (also behaves the same in Hive). When 'concat' meets any NULL string, it returns NULL as the answer. But is this right? When I concatenate a person's last name and first name, if the first name is missing (empty string or NULL), I should see the last name still, not NULL, i.e., "Smith" + "" = "Smith", not NULL. Simplest repeatable test: hive> select c_first_name, c_last_name from customer where c_customer_id = 'AAAAAAAAKPGBBAAA'; OK NULL Moore Time taken: 0.07 seconds, Fetched: 1 row(s) hive> select concat(c_last_name, ', ', c_first_name) from customer where c_customer_id = 'AAAAAAAAKPGBBAAA'; OK NULL Time taken: 0.1 seconds, Fetched: 1 row(s) hive> select concat(c_last_name, c_first_name) from customer where c_customer_id = 'AAAAAAAAKPGBBAAA'; OK NULL Time taken: 0.055 seconds, Fetched: 1 row(s) hive> select concat(c_last_name, ', ', c_first_name) from customer where c_customer_id = 'AAAAAAAAKPGBBAAA'; OK NULL Time taken: 0.061 seconds, Fetched: 1 row(s) hive> select concat(c_last_name, ', ', c_customer_id) from customer where c_customer_id = 'AAAAAAAAKPGBBAAA'; OK Moore, AAAAAAAAKPGBBAAA Same in 'spark-sql' shell: ... 16/05/17 15:57:10 INFO spark.ContextCleaner: Cleaned accumulator 45 16/05/17 15:57:10 INFO spark.ContextCleaner: Cleaned accumulator 46 16/05/17 15:57:10 INFO spark.ContextCleaner: Cleaned accumulator 47 16/05/17 15:57:10 INFO spark.ContextCleaner: Cleaned accumulator 48 select concat(c_last_name, c_first_name) from customer where c_customer_id = 'AAAAAAAAKPGBBAAA'; NULL Time taken: 0.296 seconds, Fetched 1 row(s) was: The official TPC-DS query 41 fails with the following error: {noformat} Error in query: The correlated scalar subquery can only contain equality predicates: (((i_manufact#38 = i_manufact#16) && (((((i_category#36 = Women) && ((i_color#41 = powder) || (i_color#41 = khaki))) && (((i_units#42 = Ounce) || (i_units#42 = Oz)) && ((i_size#39 = medium) || (i_size#39 = extra large)))) || (((i_category#36 = Women) && ((i_color#41 = brown) || (i_color#41 = honeydew))) && (((i_units#42 = Bunch) || (i_units#42 = Ton)) && ((i_size#39 = N/A) || (i_size#39 = small))))) || ((((i_category#36 = Men) && ((i_color#41 = floral) || (i_color#41 = deep))) && (((i_units#42 = N/A) || (i_units#42 = Dozen)) && ((i_size#39 = petite) || (i_size#39 = large)))) || (((i_category#36 = Men) && ((i_color#41 = light) || (i_color#41 = cornflower))) && (((i_units#42 = Box) || (i_units#42 = Pound)) && ((i_size#39 = medium) || (i_size#39 = extra large))))))) || ((i_manufact#38 = i_manufact#16) && (((((i_category#36 = Women) && ((i_color#41 = midnight) || (i_color#41 = snow))) && (((i_units#42 = Pallet) || (i_units#42 = Gross)) && ((i_size#39 = medium) || (i_size#39 = extra large)))) || (((i_category#36 = Women) && ((i_color#41 = cyan) || (i_color#41 = papaya))) && (((i_units#42 = Cup) || (i_units#42 = Dram)) && ((i_size#39 = N/A) || (i_size#39 = small))))) || ((((i_category#36 = Men) && ((i_color#41 = orange) || (i_color#41 = frosted))) && (((i_units#42 = Each) || (i_units#42 = Tbl)) && ((i_size#39 = petite) || (i_size#39 = large)))) || (((i_category#36 = Men) && ((i_color#41 = forest) || (i_color#41 = ghost))) && (((i_units#42 = Lb) || (i_units#42 = Bundle)) && ((i_size#39 = medium) || (i_size#39 = extra large)))))))); {noformat} The output plans showed the following errors {noformat} == Parsed Logical Plan == 'GlobalLimit 100 +- 'LocalLimit 100 +- 'Sort ['i_product_name ASC], true +- 'Distinct +- 'Project ['i_product_name] +- 'Filter ((('i_manufact_id >= 738) && ('i_manufact_id <= (738 + 40))) && (scalar-subquery#1 [] > 0)) : +- 'SubqueryAlias scalar-subquery#1 [] : +- 'Project ['count(1) AS item_cnt#0] : +- 'Filter ((('i_manufact = 'i1.i_manufact) && ((((('i_category = Women) && (('i_color = powder) || ('i_color = khaki))) && ((('i_units = Ounce) || ('i_units = Oz)) && (('i_size = medium) || ('i_size = extra large)))) || ((('i_category = Women) && (('i_color = brown) || ('i_color = honeydew))) && ((('i_units = Bunch) || ('i_units = Ton)) && (('i_size = N/A) || ('i_size = small))))) || (((('i_category = Men) && (('i_color = floral) || ('i_color = deep))) && ((('i_units = N/A) || ('i_units = Dozen)) && (('i_size = petite) || ('i_size = large)))) || ((('i_category = Men) && (('i_color = light) || ('i_color = cornflower))) && ((('i_units = Box) || ('i_units = Pound)) && (('i_size = medium) || ('i_size = extra large))))))) || (('i_manufact = 'i1.i_manufact) && ((((('i_category = Women) && (('i_color = midnight) || ('i_color = snow))) && ((('i_units = Pallet) || ('i_units = Gross)) && (('i_size = medium) || ('i_size = extra large)))) || ((('i_category = Women) && (('i_color = cyan) || ('i_color = papaya))) && ((('i_units = Cup) || ('i_units = Dram)) && (('i_size = N/A) || ('i_size = small))))) || (((('i_category = Men) && (('i_color = orange) || ('i_color = frosted))) && ((('i_units = Each) || ('i_units = Tbl)) && (('i_size = petite) || ('i_size = large)))) || ((('i_category = Men) && (('i_color = forest) || ('i_color = ghost))) && ((('i_units = Lb) || ('i_units = Bundle)) && (('i_size = medium) || ('i_size = extra large)))))))) : +- 'UnresolvedRelation `item`, None +- 'UnresolvedRelation `item`, Some(i1) == Analyzed Logical Plan == i_product_name: string GlobalLimit 100 +- LocalLimit 100 +- Sort [i_product_name#24 ASC], true +- Distinct +- Project [i_product_name#24] +- Filter (((i_manufact_id#16L >= cast(738 as bigint)) && (i_manufact_id#16L <= cast((738 + 40) as bigint))) && (scalar-subquery#1 [(((i_manufact#39 = i_manufact#17) && (((((i_category#37 = Women) && ((i_color#42 = powder) || (i_color#42 = khaki))) && (((i_units#43 = Ounce) || (i_units#43 = Oz)) && ((i_size#40 = medium) || (i_size#40 = extra large)))) || (((i_category#37 = Women) && ((i_color#42 = brown) || (i_color#42 = honeydew))) && (((i_units#43 = Bunch) || (i_units#43 = Ton)) && ((i_size#40 = N/A) || (i_size#40 = small))))) || ((((i_category#37 = Men) && ((i_color#42 = floral) || (i_color#42 = deep))) && (((i_units#43 = N/A) || (i_units#43 = Dozen)) && ((i_size#40 = petite) || (i_size#40 = large)))) || (((i_category#37 = Men) && ((i_color#42 = light) || (i_color#42 = cornflower))) && (((i_units#43 = Box) || (i_units#43 = Pound)) && ((i_size#40 = medium) || (i_size#40 = extra large))))))) || ((i_manufact#39 = i_manufact#17) && (((((i_category#37 = Women) && ((i_color#42 = midnight) || (i_color#42 = snow))) && (((i_units#43 = Pallet) || (i_units#43 = Gross)) && ((i_size#40 = medium) || (i_size#40 = extra large)))) || (((i_category#37 = Women) && ((i_color#42 = cyan) || (i_color#42 = papaya))) && (((i_units#43 = Cup) || (i_units#43 = Dram)) && ((i_size#40 = N/A) || (i_size#40 = small))))) || ((((i_category#37 = Men) && ((i_color#42 = orange) || (i_color#42 = frosted))) && (((i_units#43 = Each) || (i_units#43 = Tbl)) && ((i_size#40 = petite) || (i_size#40 = large)))) || (((i_category#37 = Men) && ((i_color#42 = forest) || (i_color#42 = ghost))) && (((i_units#43 = Lb) || (i_units#43 = Bundle)) && ((i_size#40 = medium) || (i_size#40 = extra large))))))))] > cast(0 as bigint))) : +- SubqueryAlias scalar-subquery#1 [(((i_manufact#39 = i_manufact#17) && (((((i_category#37 = Women) && ((i_color#42 = powder) || (i_color#42 = khaki))) && (((i_units#43 = Ounce) || (i_units#43 = Oz)) && ((i_size#40 = medium) || (i_size#40 = extra large)))) || (((i_category#37 = Women) && ((i_color#42 = brown) || (i_color#42 = honeydew))) && (((i_units#43 = Bunch) || (i_units#43 = Ton)) && ((i_size#40 = N/A) || (i_size#40 = small))))) || ((((i_category#37 = Men) && ((i_color#42 = floral) || (i_color#42 = deep))) && (((i_units#43 = N/A) || (i_units#43 = Dozen)) && ((i_size#40 = petite) || (i_size#40 = large)))) || (((i_category#37 = Men) && ((i_color#42 = light) || (i_color#42 = cornflower))) && (((i_units#43 = Box) || (i_units#43 = Pound)) && ((i_size#40 = medium) || (i_size#40 = extra large))))))) || ((i_manufact#39 = i_manufact#17) && (((((i_category#37 = Women) && ((i_color#42 = midnight) || (i_color#42 = snow))) && (((i_units#43 = Pallet) || (i_units#43 = Gross)) && ((i_size#40 = medium) || (i_size#40 = extra large)))) || (((i_category#37 = Women) && ((i_color#42 = cyan) || (i_color#42 = papaya))) && (((i_units#43 = Cup) || (i_units#43 = Dram)) && ((i_size#40 = N/A) || (i_size#40 = small))))) || ((((i_category#37 = Men) && ((i_color#42 = orange) || (i_color#42 = frosted))) && (((i_units#43 = Each) || (i_units#43 = Tbl)) && ((i_size#40 = petite) || (i_size#40 = large)))) || (((i_category#37 = Men) && ((i_color#42 = forest) || (i_color#42 = ghost))) && (((i_units#43 = Lb) || (i_units#43 = Bundle)) && ((i_size#40 = medium) || (i_size#40 = extra large))))))))] : +- Aggregate [i_manufact#39,i_category#37,i_size#40,i_units#43,i_color#42], [(count(1),mode=Complete,isDistinct=false) AS item_cnt#0L,i_manufact#39,i_category#37,i_size#40,i_units#43,i_color#42] : +- MetastoreRelation hadoopds1g, item, None +- SubqueryAlias i1 +- Relation[i_item_sk#3,i_item_id#4,i_rec_start_date#5,i_rec_end_date#6,i_item_desc#7,i_current_price#8,i_wholesale_cost#9,i_brand_id#10L,i_brand#11,i_class_id#12L,i_class#13,i_category_id#14L,i_category#15,i_manufact_id#16L,i_manufact#17,i_size#18,i_formulation#19,i_color#20,i_units#21,i_container#22,i_manager_id#23L,i_product_name#24] HadoopFiles {noformat} Note that the q41 in https://github.com/sameeragarwal/spark/blob/tpcds-more/sql/core/src/test/scala/org/apache/spark/sql/execution/datasources/parquet/tpcds/TPCDS_1_4_Benchmark.scala is NOT the official TPC-DS query nor does it have allowed "minor query modification". It works in the nightly build. But we cannot claim it is a TPC-DS query. > TPC-DS Qury 84 returns wrong results against TPC official > --------------------------------------------------------- > > Key: SPARK-15372 > URL: https://issues.apache.org/jira/browse/SPARK-15372 > Project: Spark > Issue Type: Bug > Components: SQL > Affects Versions: 2.0.0 > Reporter: JESSE CHEN > Assignee: Herman van Hovell > Priority: Critical > Labels: SPARK-15071 > Fix For: 2.0.0 > > > The official TPC-DS query 84 returns wrong results when compared to its > official answer set. > The query itself is: > {noformat} > select c_customer_id as customer_id > ,concat(c_last_name , ', ' , c_first_name) as customername > from customer > ,customer_address > ,customer_demographics > ,household_demographics > ,income_band > ,store_returns > where ca_city = 'Edgewood' > and c_current_addr_sk = ca_address_sk > and ib_lower_bound >= 38128 > and ib_upper_bound <= 38128 + 50000 > and ib_income_band_sk = hd_income_band_sk > and cd_demo_sk = c_current_cdemo_sk > and hd_demo_sk = c_current_hdemo_sk > and sr_cdemo_sk = cd_demo_sk > order by c_customer_id > limit 100; > {noformat} > Spark 2.0 build 0517 returned the following result: > {noformat} > AAAAAAAAAIPGAAAA Carter, Rodney > AAAAAAAAAKMBBAAA Mcarthur, Emma > AAAAAAAACBNHBAAA Wells, Ron > AAAAAAAADBMEAAAA Vera, Tina > AAAAAAAADBMEAAAA Vera, Tina > AAAAAAAADHKGBAAA Scott, Pamela > AAAAAAAAEIIBBAAA Atkins, Susan > AAAAAAAAFKAHAAAA Batiste, Ernest > AAAAAAAAGHMAAAAA Mitchell, Gregory > AAAAAAAAIAODBAAA Murray, Karen > AAAAAAAAIEOKAAAA Solomon, Clyde > AAAAAAAAIIBOAAAA Owens, David > AAAAAAAAIPDCAAAA Wallace, Eric > AAAAAAAAIPIMAAAA Hayward, Benjamin > AAAAAAAAJCIKAAAA Ramos, Donald > AAAAAAAAKFJEAAAA Roberts, Yvonne > AAAAAAAAKPGBBAAA NULL <------------------------ ??? questionable row > AAAAAAAALCLABAAA Whitaker, Lettie > AAAAAAAAMGMEAAAA Sharp, Michael > AAAAAAAAMIGBBAAA Montgomery, Jesenia > AAAAAAAAMPDKAAAA Lopez, Isabel > AAAAAAAANEOMAAAA Powell, Linda > AAAAAAAANKPCAAAA Shaffer, Sergio > AAAAAAAANOCKAAAA Vargas, James > AAAAAAAAOGJEBAAA Owens, Denice > {noformat} > Official answer set (which is correct!) > {noformat} > AAAAAAAAAIPGAAAA Carter , Rodney > AAAAAAAAAKMBBAAA Mcarthur , Emma > AAAAAAAACBNHBAAA Wells , Ron > AAAAAAAADBMEAAAA Vera , Tina > AAAAAAAADBMEAAAA Vera , Tina > AAAAAAAADHKGBAAA Scott , Pamela > AAAAAAAAEIIBBAAA Atkins , Susan > AAAAAAAAFKAHAAAA Batiste , Ernest > AAAAAAAAGHMAAAAA Mitchell , Gregory > AAAAAAAAIAODBAAA Murray , Karen > AAAAAAAAIEOKAAAA Solomon , Clyde > AAAAAAAAIIBOAAAA Owens , David > AAAAAAAAIPDCAAAA Wallace , Eric > AAAAAAAAIPIMAAAA Hayward , Benjamin > AAAAAAAAJCIKAAAA Ramos , Donald > AAAAAAAAKFJEAAAA Roberts , Yvonne > AAAAAAAAKPGBBAAA Moore , > AAAAAAAALCLABAAA Whitaker , Lettie > AAAAAAAAMGMEAAAA Sharp , Michael > AAAAAAAAMIGBBAAA Montgomery , Jesenia > AAAAAAAAMPDKAAAA Lopez , Isabel > AAAAAAAANEOMAAAA Powell , Linda > AAAAAAAANKPCAAAA Shaffer , Sergio > AAAAAAAANOCKAAAA Vargas , James > AAAAAAAAOGJEBAAA Owens , Denice > {noformat} > The issue is with the "concat" function in Spark SQL (also behaves the same > in Hive). When 'concat' meets any NULL string, it returns NULL as the answer. > But is this right? When I concatenate a person's last name and first name, if > the first name is missing (empty string or NULL), I should see the last name > still, not NULL, i.e., "Smith" + "" = "Smith", not NULL. > Simplest repeatable test: > hive> select c_first_name, c_last_name from customer where c_customer_id = > 'AAAAAAAAKPGBBAAA'; > OK > NULL Moore > Time taken: 0.07 seconds, Fetched: 1 row(s) > hive> select concat(c_last_name, ', ', c_first_name) from customer where > c_customer_id = 'AAAAAAAAKPGBBAAA'; > OK > NULL > Time taken: 0.1 seconds, Fetched: 1 row(s) > hive> select concat(c_last_name, c_first_name) from customer where > c_customer_id = 'AAAAAAAAKPGBBAAA'; > OK > NULL > Time taken: 0.055 seconds, Fetched: 1 row(s) > hive> select concat(c_last_name, ', ', c_first_name) from customer where > c_customer_id = 'AAAAAAAAKPGBBAAA'; > OK > NULL > Time taken: 0.061 seconds, Fetched: 1 row(s) > hive> select concat(c_last_name, ', ', c_customer_id) from customer where > c_customer_id = 'AAAAAAAAKPGBBAAA'; > OK > Moore, AAAAAAAAKPGBBAAA > Same in 'spark-sql' shell: > ... > 16/05/17 15:57:10 INFO spark.ContextCleaner: Cleaned accumulator 45 > 16/05/17 15:57:10 INFO spark.ContextCleaner: Cleaned accumulator 46 > 16/05/17 15:57:10 INFO spark.ContextCleaner: Cleaned accumulator 47 > 16/05/17 15:57:10 INFO spark.ContextCleaner: Cleaned accumulator 48 > select concat(c_last_name, c_first_name) from customer where c_customer_id = > 'AAAAAAAAKPGBBAAA'; > NULL > Time taken: 0.296 seconds, Fetched 1 row(s) -- This message was sent by Atlassian JIRA (v6.3.4#6332) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org