[ 
https://issues.apache.org/jira/browse/SPARK-13864?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Yin Huai resolved SPARK-13864.
------------------------------
    Resolution: Duplicate

> 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

Reply via email to