[
https://issues.apache.org/jira/browse/SPARK-13864?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15216483#comment-15216483
]
JESSE CHEN commented on SPARK-13864:
------------------------------------
Validated successfully. Returned the correct result set in order:
{noformat}
AAAAAAAAAMGDAAAA Kenneth Harlan
AAAAAAAAANFAAAAA Philip Banks
AAAAAAAAAOPFBAAA Jerry Fields
AAAAAAAABLEIBAAA Paula Wakefield
AAAAAAAABNBBAAAA Irma Smith
AAAAAAAACADPAAAA Cristobal Thomas
AAAAAAAACFCGBAAA Marcus Sanders
AAAAAAAACFENAAAA Christopher Dawson
AAAAAAAACIJMAAAA Elizabeth Thomas
AAAAAAAACJDIAAAA James Kerr
AAAAAAAACNAGBAAA Virginia May
AAAAAAAADBEFBAAA Bennie Bowers
AAAAAAAADCKOAAAA Robert Gonzalez
AAAAAAAADFIEBAAA John Gray
AAAAAAAADFKABAAA Latoya Craft
AAAAAAAADIIOAAAA David Carroll
AAAAAAAADIJGBAAA Ruth Sanders
AAAAAAAADLHBBAAA Henry Bertrand
AAAAAAAAEADJAAAA Ruth Carroll
AAAAAAAAEJDLAAAA Alice Wright
AAAAAAAAEKFPAAAA Annika Chin
AAAAAAAAEKJLAAAA Aisha Carlson
AAAAAAAAEOAKAAAA Molly Benjamin
AAAAAAAAEPOGAAAA Felisha Mendes
AAAAAAAAFACEAAAA Priscilla Miller
AAAAAAAAFBAHAAAA Michael Williams
AAAAAAAAFGIGAAAA Eduardo Miller
AAAAAAAAFGPGAAAA Albert Wadsworth
AAAAAAAAFHACBAAA
AAAAAAAAFJHFAAAA Larissa Roy
AAAAAAAAFMHIAAAA Emilio Darling
AAAAAAAAFOGIAAAA Michelle Greene
AAAAAAAAFOJAAAAA Don Castillo
AAAAAAAAGEHIAAAA Tyler Miller
AAAAAAAAGFMDBAAA Kathleen Gibson
AAAAAAAAGHPBBAAA Nick Mendez
AAAAAAAAGNDAAAAA Terry Mcdowell
AAAAAAAAHGOABAAA Sonia White
AAAAAAAAHHCABAAA William Stewart
AAAAAAAAHJLAAAAA Audrey Beltran
AAAAAAAAHMJNAAAA Ryan Baptiste
AAAAAAAAHMOIAAAA Grace Henderson
AAAAAAAAHNFHAAAA Rebecca Wilson
AAAAAAAAIADEBAAA Diane Aldridge
AAAAAAAAIBAEBAAA Sandra Wilson
AAAAAAAAIBFCBAAA Ruth Grantham
AAAAAAAAIBHHAAAA Jennifer Ballard
AAAAAAAAICHFAAAA Linda Mccoy
AAAAAAAAIDKFAAAA Michael Mack
AAAAAAAAIJEMAAAA Charlie Cummings
AAAAAAAAIMHBAAAA Kathy Knowles
AAAAAAAAIMHHBAAA Lillian Davidson
AAAAAAAAJEKFBAAA Norma Burkholder
AAAAAAAAJGMMAAAA Richard Larson
AAAAAAAAJIALAAAA Santos Gutierrez
AAAAAAAAJKBNAAAA Julie Kern
AAAAAAAAJONHBAAA Warren Orozco
AAAAAAAAKAECAAAA Milton Mackey
AAAAAAAAKBCABAAA Debra Bell
AAAAAAAAKJBKAAAA Georgia Scott
AAAAAAAAKJBLAAAA Kerry Davis
AAAAAAAAKKGEAAAA Katie Dunbar
AAAAAAAAKLHHBAAA Manuel Castaneda
AAAAAAAAKNAKAAAA Gladys Banks
AAAAAAAAKOJJAAAA Gracie Mendoza
AAAAAAAALFKKAAAA Ignacio Miller
AAAAAAAALHMCAAAA Brooke Nelson
AAAAAAAALIOPAAAA Derek Allen
AAAAAAAALJNCBAAA George Gamez
AAAAAAAAMDCAAAAA Louann Hamel
AAAAAAAAMFFLAAAA Margret Gray
AAAAAAAAMMOBBAAA Margaret Smith
AAAAAAAANFBDBAAA Vernice Fernandez
AAAAAAAANGDBBAAA Carlos Jewell
AAAAAAAANIPLAAAA Eric Lawrence
AAAAAAAANJAGAAAA Allen Hood
AAAAAAAANJHCBAAA Christopher Schreiber
AAAAAAAAOBADBAAA Elizabeth Burnham
AAAAAAAAOCAJAAAA Jenna Staton
AAAAAAAAOCDJAAAA Nina Sanchez
AAAAAAAAOCICAAAA Zachary Pennington
AAAAAAAAOCLBBAAA
AAAAAAAAOFLCAAAA James Taylor
AAAAAAAAOPDLAAAA Ann Pence
AAAAAAAAPDFBAAAA Terrance Banks
AAAAAAAAPEHEBAAA Edith Molina
AAAAAAAAPFCLAAAA Felicia Neville
AAAAAAAAPJENAAAA Ashley Norton
AAAAAAAAPKBCBAAA Andrea White
AAAAAAAAPKIKAAAA Wendy Horvath
AAAAAAAAPMMBBAAA Paul Jordan
AAAAAAAAPPIBBAAA Candice Lee
{noformat}
> TPCDS query 74 returns wrong results compared to TPC official result set
> -------------------------------------------------------------------------
>
> Key: SPARK-13864
> URL: https://issues.apache.org/jira/browse/SPARK-13864
> Project: Spark
> Issue Type: Bug
> Components: SQL
> Affects Versions: 1.6.0
> Reporter: JESSE CHEN
> Labels: tpcds-result-mismatch
>
> Testing Spark SQL using TPC queries. Query 74 returns wrong results compared
> to official result set. This is at 1GB SF (validation run).
> Spark SQL has right answer but in wrong order (and there is an 'order by' in
> the query).
> Actual results:
> {noformat}
> [AAAAAAAABLEIBAAA,Paula,Wakefield]
> [AAAAAAAADFIEBAAA,John,Gray]
> [AAAAAAAAOCLBBAAA,null,null]
> [AAAAAAAAPKBCBAAA,Andrea,White]
> [AAAAAAAAEJDLAAAA,Alice,Wright]
> [AAAAAAAAFACEAAAA,Priscilla,Miller]
> [AAAAAAAALFKKAAAA,Ignacio,Miller]
> [AAAAAAAALJNCBAAA,George,Gamez]
> [AAAAAAAALIOPAAAA,Derek,Allen]
> [AAAAAAAAEADJAAAA,Ruth,Carroll]
> [AAAAAAAAJGMMAAAA,Richard,Larson]
> [AAAAAAAAPKIKAAAA,Wendy,Horvath]
> [AAAAAAAAFJHFAAAA,Larissa,Roy]
> [AAAAAAAAEPOGAAAA,Felisha,Mendes]
> [AAAAAAAAEKJLAAAA,Aisha,Carlson]
> [AAAAAAAAHNFHAAAA,Rebecca,Wilson]
> [AAAAAAAAIBFCBAAA,Ruth,Grantham]
> [AAAAAAAAOPDLAAAA,Ann,Pence]
> [AAAAAAAANIPLAAAA,Eric,Lawrence]
> [AAAAAAAAOCICAAAA,Zachary,Pennington]
> [AAAAAAAAOFLCAAAA,James,Taylor]
> [AAAAAAAAGEHIAAAA,Tyler,Miller]
> [AAAAAAAACADPAAAA,Cristobal,Thomas]
> [AAAAAAAAJIALAAAA,Santos,Gutierrez]
> [AAAAAAAAPMMBBAAA,Paul,Jordan]
> [AAAAAAAADIIOAAAA,David,Carroll]
> [AAAAAAAADFKABAAA,Latoya,Craft]
> [AAAAAAAAHMOIAAAA,Grace,Henderson]
> [AAAAAAAAPPIBBAAA,Candice,Lee]
> [AAAAAAAAJONHBAAA,Warren,Orozco]
> [AAAAAAAAGNDAAAAA,Terry,Mcdowell]
> [AAAAAAAACIJMAAAA,Elizabeth,Thomas]
> [AAAAAAAADIJGBAAA,Ruth,Sanders]
> [AAAAAAAANFBDBAAA,Vernice,Fernandez]
> [AAAAAAAAIDKFAAAA,Michael,Mack]
> [AAAAAAAAIMHBAAAA,Kathy,Knowles]
> [AAAAAAAALHMCAAAA,Brooke,Nelson]
> [AAAAAAAACFCGBAAA,Marcus,Sanders]
> [AAAAAAAANJHCBAAA,Christopher,Schreiber]
> [AAAAAAAAPDFBAAAA,Terrance,Banks]
> [AAAAAAAAANFAAAAA,Philip,Banks]
> [AAAAAAAAIADEBAAA,Diane,Aldridge]
> [AAAAAAAAICHFAAAA,Linda,Mccoy]
> [AAAAAAAACFENAAAA,Christopher,Dawson]
> [AAAAAAAAKOJJAAAA,Gracie,Mendoza]
> [AAAAAAAAFOJAAAAA,Don,Castillo]
> [AAAAAAAAFGPGAAAA,Albert,Wadsworth]
> [AAAAAAAAKJBKAAAA,Georgia,Scott]
> [AAAAAAAAEKFPAAAA,Annika,Chin]
> [AAAAAAAAIBAEBAAA,Sandra,Wilson]
> [AAAAAAAAMFFLAAAA,Margret,Gray]
> [AAAAAAAAKNAKAAAA,Gladys,Banks]
> [AAAAAAAACJDIAAAA,James,Kerr]
> [AAAAAAAAOBADBAAA,Elizabeth,Burnham]
> [AAAAAAAAAMGDAAAA,Kenneth,Harlan]
> [AAAAAAAAHJLAAAAA,Audrey,Beltran]
> [AAAAAAAAAOPFBAAA,Jerry,Fields]
> [AAAAAAAACNAGBAAA,Virginia,May]
> [AAAAAAAAHGOABAAA,Sonia,White]
> [AAAAAAAAKBCABAAA,Debra,Bell]
> [AAAAAAAANJAGAAAA,Allen,Hood]
> [AAAAAAAAMMOBBAAA,Margaret,Smith]
> [AAAAAAAANGDBBAAA,Carlos,Jewell]
> [AAAAAAAAFOGIAAAA,Michelle,Greene]
> [AAAAAAAAJEKFBAAA,Norma,Burkholder]
> [AAAAAAAAOCAJAAAA,Jenna,Staton]
> [AAAAAAAAPFCLAAAA,Felicia,Neville]
> [AAAAAAAADLHBBAAA,Henry,Bertrand]
> [AAAAAAAADBEFBAAA,Bennie,Bowers]
> [AAAAAAAADCKOAAAA,Robert,Gonzalez]
> [AAAAAAAAKKGEAAAA,Katie,Dunbar]
> [AAAAAAAAGFMDBAAA,Kathleen,Gibson]
> [AAAAAAAAIJEMAAAA,Charlie,Cummings]
> [AAAAAAAAKJBLAAAA,Kerry,Davis]
> [AAAAAAAAJKBNAAAA,Julie,Kern]
> [AAAAAAAAMDCAAAAA,Louann,Hamel]
> [AAAAAAAAEOAKAAAA,Molly,Benjamin]
> [AAAAAAAAIBHHAAAA,Jennifer,Ballard]
> [AAAAAAAAPJENAAAA,Ashley,Norton]
> [AAAAAAAAKLHHBAAA,Manuel,Castaneda]
> [AAAAAAAAIMHHBAAA,Lillian,Davidson]
> [AAAAAAAAGHPBBAAA,Nick,Mendez]
> [AAAAAAAABNBBAAAA,Irma,Smith]
> [AAAAAAAAFBAHAAAA,Michael,Williams]
> [AAAAAAAAPEHEBAAA,Edith,Molina]
> [AAAAAAAAFMHIAAAA,Emilio,Darling]
> [AAAAAAAAKAECAAAA,Milton,Mackey]
> [AAAAAAAAOCDJAAAA,Nina,Sanchez]
> [AAAAAAAAFGIGAAAA,Eduardo,Miller]
> [AAAAAAAAFHACBAAA,null,null]
> [AAAAAAAAHMJNAAAA,Ryan,Baptiste]
> [AAAAAAAAHHCABAAA,William,Stewart]
> {noformat}
> Expected results:
> {noformat}
> +------------------+---------------------+--------------------+
> | CUSTOMER_ID | CUSTOMER_FIRST_NAME | CUSTOMER_LAST_NAME |
> +------------------+---------------------+--------------------+
> | AAAAAAAAAMGDAAAA | Kenneth | Harlan |
> | AAAAAAAAANFAAAAA | Philip | Banks |
> | AAAAAAAAAOPFBAAA | Jerry | Fields |
> | AAAAAAAABLEIBAAA | Paula | Wakefield |
> | AAAAAAAABNBBAAAA | Irma | Smith |
> | AAAAAAAACADPAAAA | Cristobal | Thomas |
> | AAAAAAAACFCGBAAA | Marcus | Sanders |
> | AAAAAAAACFENAAAA | Christopher | Dawson |
> | AAAAAAAACIJMAAAA | Elizabeth | Thomas |
> | AAAAAAAACJDIAAAA | James | Kerr |
> | AAAAAAAACNAGBAAA | Virginia | May |
> | AAAAAAAADBEFBAAA | Bennie | Bowers |
> | AAAAAAAADCKOAAAA | Robert | Gonzalez |
> | AAAAAAAADFIEBAAA | John | Gray |
> | AAAAAAAADFKABAAA | Latoya | Craft |
> | AAAAAAAADIIOAAAA | David | Carroll |
> | AAAAAAAADIJGBAAA | Ruth | Sanders |
> | AAAAAAAADLHBBAAA | Henry | Bertrand |
> | AAAAAAAAEADJAAAA | Ruth | Carroll |
> | AAAAAAAAEJDLAAAA | Alice | Wright |
> | AAAAAAAAEKFPAAAA | Annika | Chin |
> | AAAAAAAAEKJLAAAA | Aisha | Carlson |
> | AAAAAAAAEOAKAAAA | Molly | Benjamin |
> | AAAAAAAAEPOGAAAA | Felisha | Mendes |
> | AAAAAAAAFACEAAAA | Priscilla | Miller |
> | AAAAAAAAFBAHAAAA | Michael | Williams |
> | AAAAAAAAFGIGAAAA | Eduardo | Miller |
> | AAAAAAAAFGPGAAAA | Albert | Wadsworth |
> | AAAAAAAAFHACBAAA | [NULL] | [NULL] |
> | AAAAAAAAFJHFAAAA | Larissa | Roy |
> | AAAAAAAAFMHIAAAA | Emilio | Darling |
> | AAAAAAAAFOGIAAAA | Michelle | Greene |
> | AAAAAAAAFOJAAAAA | Don | Castillo |
> | AAAAAAAAGEHIAAAA | Tyler | Miller |
> | AAAAAAAAGFMDBAAA | Kathleen | Gibson |
> | AAAAAAAAGHPBBAAA | Nick | Mendez |
> | AAAAAAAAGNDAAAAA | Terry | Mcdowell |
> | AAAAAAAAHGOABAAA | Sonia | White |
> | AAAAAAAAHHCABAAA | William | Stewart |
> | AAAAAAAAHJLAAAAA | Audrey | Beltran |
> | AAAAAAAAHMJNAAAA | Ryan | Baptiste |
> | AAAAAAAAHMOIAAAA | Grace | Henderson |
> | AAAAAAAAHNFHAAAA | Rebecca | Wilson |
> | AAAAAAAAIADEBAAA | Diane | Aldridge |
> | AAAAAAAAIBAEBAAA | Sandra | Wilson |
> | AAAAAAAAIBFCBAAA | Ruth | Grantham |
> | AAAAAAAAIBHHAAAA | Jennifer | Ballard |
> | AAAAAAAAICHFAAAA | Linda | Mccoy |
> | AAAAAAAAIDKFAAAA | Michael | Mack |
> | AAAAAAAAIJEMAAAA | Charlie | Cummings |
> | AAAAAAAAIMHBAAAA | Kathy | Knowles |
> | AAAAAAAAIMHHBAAA | Lillian | Davidson |
> | AAAAAAAAJEKFBAAA | Norma | Burkholder |
> | AAAAAAAAJGMMAAAA | Richard | Larson |
> | AAAAAAAAJIALAAAA | Santos | Gutierrez |
> | AAAAAAAAJKBNAAAA | Julie | Kern |
> | AAAAAAAAJONHBAAA | Warren | Orozco |
> | AAAAAAAAKAECAAAA | Milton | Mackey |
> | AAAAAAAAKBCABAAA | Debra | Bell |
> | AAAAAAAAKJBKAAAA | Georgia | Scott |
> | AAAAAAAAKJBLAAAA | Kerry | Davis |
> | AAAAAAAAKKGEAAAA | Katie | Dunbar |
> | AAAAAAAAKLHHBAAA | Manuel | Castaneda |
> | AAAAAAAAKNAKAAAA | Gladys | Banks |
> | AAAAAAAAKOJJAAAA | Gracie | Mendoza |
> | AAAAAAAALFKKAAAA | Ignacio | Miller |
> | AAAAAAAALHMCAAAA | Brooke | Nelson |
> | AAAAAAAALIOPAAAA | Derek | Allen |
> | AAAAAAAALJNCBAAA | George | Gamez |
> | AAAAAAAAMDCAAAAA | Louann | Hamel |
> | AAAAAAAAMFFLAAAA | Margret | Gray |
> | AAAAAAAAMMOBBAAA | Margaret | Smith |
> | AAAAAAAANFBDBAAA | Vernice | Fernandez |
> | AAAAAAAANGDBBAAA | Carlos | Jewell |
> | AAAAAAAANIPLAAAA | Eric | Lawrence |
> | AAAAAAAANJAGAAAA | Allen | Hood |
> | AAAAAAAANJHCBAAA | Christopher | Schreiber |
> | AAAAAAAAOBADBAAA | Elizabeth | Burnham |
> | AAAAAAAAOCAJAAAA | Jenna | Staton |
> | AAAAAAAAOCDJAAAA | Nina | Sanchez |
> | AAAAAAAAOCICAAAA | Zachary | Pennington |
> | AAAAAAAAOCLBBAAA | [NULL] | [NULL] |
> | AAAAAAAAOFLCAAAA | James | Taylor |
> | AAAAAAAAOPDLAAAA | Ann | Pence |
> | AAAAAAAAPDFBAAAA | Terrance | Banks |
> | AAAAAAAAPEHEBAAA | Edith | Molina |
> | AAAAAAAAPFCLAAAA | Felicia | Neville |
> | AAAAAAAAPJENAAAA | Ashley | Norton |
> | AAAAAAAAPKBCBAAA | Andrea | White |
> | AAAAAAAAPKIKAAAA | Wendy | Horvath |
> | AAAAAAAAPMMBBAAA | Paul | Jordan |
> | AAAAAAAAPPIBBAAA | Candice | Lee |
> +------------------+---------------------+--------------------+
> {noformat}
> Query used:
> {noformat}
> -- start query 74 in stream 0 using template query74.tpl and seed
> QUALIFICATION
> with year_total as (
> select c_customer_id customer_id
> ,c_first_name customer_first_name
> ,c_last_name customer_last_name
> ,d_year as year
> ,sum(ss_net_paid) year_total
> ,'s' sale_type
> from customer
> ,store_sales
> ,date_dim
> where c_customer_sk = ss_customer_sk
> and ss_sold_date_sk = d_date_sk
> and d_year in (2001,2001+1)
> group by c_customer_id
> ,c_first_name
> ,c_last_name
> ,d_year
> union all
> select c_customer_id customer_id
> ,c_first_name customer_first_name
> ,c_last_name customer_last_name
> ,d_year as year
> ,sum(ws_net_paid) year_total
> ,'w' sale_type
> from customer
> ,web_sales
> ,date_dim
> where c_customer_sk = ws_bill_customer_sk
> and ws_sold_date_sk = d_date_sk
> and d_year in (2001,2001+1)
> group by c_customer_id
> ,c_first_name
> ,c_last_name
> ,d_year
> )
> select
> t_s_secyear.customer_id, t_s_secyear.customer_first_name,
> t_s_secyear.customer_last_name
> from year_total t_s_firstyear
> ,year_total t_s_secyear
> ,year_total t_w_firstyear
> ,year_total t_w_secyear
> where t_s_secyear.customer_id = t_s_firstyear.customer_id
> and t_s_firstyear.customer_id = t_w_secyear.customer_id
> and t_s_firstyear.customer_id = t_w_firstyear.customer_id
> and t_s_firstyear.sale_type = 's'
> and t_w_firstyear.sale_type = 'w'
> and t_s_secyear.sale_type = 's'
> and t_w_secyear.sale_type = 'w'
> and t_s_firstyear.year = 2001
> and t_s_secyear.year = 2001+1
> and t_w_firstyear.year = 2001
> and t_w_secyear.year = 2001+1
> and t_s_firstyear.year_total > 0
> and t_w_firstyear.year_total > 0
> and case when t_w_firstyear.year_total > 0 then
> t_w_secyear.year_total / t_w_firstyear.year_total else null end
> > case when t_s_firstyear.year_total > 0 then
> t_s_secyear.year_total / t_s_firstyear.year_total else null end
> order by 1,1,1
> limit 100;
> -- end query 74 in stream 0 using template query74.tpl
> {noformat}
--
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
