When users call rand(seed) with a specific seed number, users expect the results should be deterministic no matter whether this is pushed down or not. rand(seed) is stateful. Thus, the order of predicates in the same join condition even matters. For example, in the same join condition, if the first predicate is false and the second one is skipped. If we simply push it down to the child, the number of rand(seed) calls is different.
Thanks, Xiao 2017-07-17 9:28 GMT-07:00 Chang Chen <baibaic...@gmail.com>: > I see. > > Actually, it isn't about evaluation order which user can't specify. It's > about how many times we evaluate the non-deterministic expression for the > same row. > > For example, given the SQL: > > SELECT a.col1 > FROM tbl1 a > LEFT OUTER JOIN tbl2 b > ON > CASE WHEN a.col2 IS NULL TNEN cast(rand(9)*1000 - 9999999999 as string) > ELSE a.col2 END > = > CASE WHEN b.col3 IS NULL TNEN cast(rand(9)*1000 - 9999999999 as string) > ELSE b.col3 END; > > I think if we exactly evaluate join key one time for each row of a and > b in the whole pipeline, even if the result isn't deterministic, but the > computation is correct. > > Thanks > Chang > > > On Mon, Jul 17, 2017 at 10:49 PM, Liang-Chi Hsieh <vii...@gmail.com> > wrote: > >> >> IIUC, the evaluation order of rows in Join can be different in different >> physical operators, e.g., Sort-based and Hash-based. >> >> But for non-deterministic expressions, different evaluation orders change >> results. >> >> >> >> Chang Chen wrote >> > I see the issue. I will try https://github.com/apache/spark/pull/18652, >> I >> > think >> > >> > 1 For Join Operator, the left and right plan can't be non-deterministic. >> > 2 If Filter can support non-deterministic, why not join condition? >> > 3 We can't push down or project non-deterministic expression, since it >> may >> > change semantics. >> > >> > Actually, the real problem is #2. If the join condition could be >> > non-deterministic, then we needn't insert project. >> > >> > Thanks >> > Chang >> > >> > >> > >> > >> > On Mon, Jul 17, 2017 at 3:59 PM, 蒋星博 < >> >> > jiangxb1987@ >> >> > > wrote: >> > >> >> FYI there have been a related discussion here: >> https://github.com/apache/ >> >> spark/pull/15417#discussion_r85295977 >> >> >> >> 2017-07-17 15:44 GMT+08:00 Chang Chen < >> >> > baibaichen@ >> >> > >: >> >> >> >>> Hi All >> >>> >> >>> I don't understand the difference between the semantics, I found Spark >> >>> does the same thing for GroupBy non-deterministic. From Map-Reduce >> point >> >>> of >> >>> view, Join is also GroupBy in essence . >> >>> >> >>> @Liang Chi Hsieh >> >>> <https://plus.google.com/u/0/103179362592085650735?prsrc=4> >> >>> >> >>> in which situation, semantics will be changed? >> >>> >> >>> Thanks >> >>> Chang >> >>> >> >>> On Mon, Jul 17, 2017 at 3:29 PM, Liang-Chi Hsieh < >> >> > viirya@ >> >> > > >> >>> wrote: >> >>> >> >>>> >> >>>> Thinking about it more, I think it changes the semantics only under >> >>>> certain >> >>>> scenarios. >> >>>> >> >>>> For the example SQL query shown in previous discussion, it looks the >> >>>> same >> >>>> semantics. >> >>>> >> >>>> >> >>>> Xiao Li wrote >> >>>> > If the join condition is non-deterministic, pushing it down to the >> >>>> > underlying project will change the semantics. Thus, we are unable >> to >> >>>> do it >> >>>> > in PullOutNondeterministic. Users can do it manually if they do not >> >>>> care >> >>>> > the semantics difference. >> >>>> > >> >>>> > Thanks, >> >>>> > >> >>>> > Xiao >> >>>> > >> >>>> > >> >>>> > >> >>>> > 2017-07-16 20:07 GMT-07:00 Chang Chen < >> >>>> >> >>>> > baibaichen@ >> >>>> >> >>>> > >: >> >>>> > >> >>>> >> It is tedious since we have lots of Hive SQL being migrated to >> >>>> Spark. >> >>>> >> And >> >>>> >> this workaround is equivalent to insert a Project between Join >> >>>> operator >> >>>> >> and its child. >> >>>> >> >> >>>> >> Why not do it in PullOutNondeterministic? >> >>>> >> >> >>>> >> Thanks >> >>>> >> Chang >> >>>> >> >> >>>> >> >> >>>> >> On Fri, Jul 14, 2017 at 5:29 PM, Liang-Chi Hsieh < >> >>>> >> >>>> > viirya@ >> >>>> >> >>>> > > wrote: >> >>>> >> >> >>>> >>> >> >>>> >>> A possible workaround is to add the rand column into tbl1 with a >> >>>> >>> projection >> >>>> >>> before the join. >> >>>> >>> >> >>>> >>> SELECT a.col1 >> >>>> >>> FROM ( >> >>>> >>> SELECT col1, >> >>>> >>> CASE >> >>>> >>> WHEN col2 IS NULL >> >>>> >>> THEN cast(rand(9)*1000 - 9999999999 as string) >> >>>> >>> ELSE >> >>>> >>> col2 >> >>>> >>> END AS col2 >> >>>> >>> FROM tbl1) a >> >>>> >>> LEFT OUTER JOIN tbl2 b >> >>>> >>> ON a.col2 = b.col3; >> >>>> >>> >> >>>> >>> >> >>>> >>> >> >>>> >>> Chang Chen wrote >> >>>> >>> > Hi Wenchen >> >>>> >>> > >> >>>> >>> > Yes. We also find this error is caused by Rand. However, this >> is >> >>>> >>> classic >> >>>> >>> > way to solve data skew in Hive. Is there any equivalent way in >> >>>> Spark? >> >>>> >>> > >> >>>> >>> > Thanks >> >>>> >>> > Chang >> >>>> >>> > >> >>>> >>> > On Thu, Jul 13, 2017 at 8:25 PM, Wenchen Fan < >> >>>> >>> >> >>>> >>> > cloud0fan@ >> >>>> >>> >> >>>> >>> > > wrote: >> >>>> >>> > >> >>>> >>> >> It’s not about case when, but about rand(). Non-deterministic >> >>>> >>> expressions >> >>>> >>> >> are not allowed in join condition. >> >>>> >>> >> >> >>>> >>> >> > On 13 Jul 2017, at 6:43 PM, wangshuang < >> >>>> >>> >> >>>> >>> > cn_wss@ >> >>>> >>> >> >>>> >>> > > wrote: >> >>>> >>> >> > >> >>>> >>> >> > I'm trying to execute hive sql on spark sql (Also on spark >> >>>> >>> >> thriftserver), For >> >>>> >>> >> > optimizing data skew, we use "case when" to handle null. >> >>>> >>> >> > Simple sql as following: >> >>>> >>> >> > >> >>>> >>> >> > >> >>>> >>> >> > SELECT a.col1 >> >>>> >>> >> > FROM tbl1 a >> >>>> >>> >> > LEFT OUTER JOIN tbl2 b >> >>>> >>> >> > ON >> >>>> >>> >> > * CASE >> >>>> >>> >> > WHEN a.col2 IS NULL >> >>>> >>> >> > TNEN cast(rand(9)*1000 - 9999999999 as >> >>>> >>> string) >> >>>> >>> >> > ELSE >> >>>> >>> >> > a.col2 END * >> >>>> >>> >> > = b.col3; >> >>>> >>> >> > >> >>>> >>> >> > >> >>>> >>> >> > But I get the error: >> >>>> >>> >> > >> >>>> >>> >> > == Physical Plan == >> >>>> >>> >> > *org.apache.spark.sql.AnalysisException: nondeterministic >> >>>> >>> expressions >> >>>> >>> >> are >> >>>> >>> >> > only allowed in >> >>>> >>> >> > Project, Filter, Aggregate or Window, found:* >> >>>> >>> >> > (((CASE WHEN (a.`nav_tcdt` IS NULL) THEN CAST(((rand(9) * >> >>>> CAST(1000 >> >>>> >>> AS >> >>>> >>> >> > DOUBLE)) - CAST(9999999999L AS DOUBLE)) AS STRING) ELSE >> >>>> >>> a.`nav_tcdt` >> >>>> >>> >> END >> >>>> >>> >> = >> >>>> >>> >> > c.`site_categ_id`) AND (CAST(a.`nav_tcd` AS INT) = 9)) AND >> >>>> >>> >> (c.`cur_flag` >> >>>> >>> >> = >> >>>> >>> >> > 1)) >> >>>> >>> >> > in operator Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) >> >>>> THEN >> >>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as >> >>>> >>> double)) >> >>>> >>> as >> >>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && >> >>>> >>> (cast(nav_tcd#26 >> >>>> >>> as >> >>>> >>> >> int) >> >>>> >>> >> > = 9)) && (cur_flag#77 = 1)) >> >>>> >>> >> > ;; >> >>>> >>> >> > GlobalLimit 10 >> >>>> >>> >> > +- LocalLimit 10 >> >>>> >>> >> > +- Aggregate [date_id#7, CASE WHEN (cast(city_id#10 as >> >>>> string) IN >> >>>> >>> >> > (cast(19596 as string),cast(20134 as string),cast(10997 as >> >>>> string)) >> >>>> >>> && >> >>>> >>> >> > nav_tcdt#25 RLIKE ^[0-9]+$) THEN city_id#10 ELSE >> nav_tpa_id#21 >> >>>> >>> END], >> >>>> >>> >> > [date_id#7] >> >>>> >>> >> > +- Filter (date_id#7 = 2017-07-12) >> >>>> >>> >> > +- Join LeftOuter, (((CASE WHEN isnull(nav_tcdt#25) >> >>>> THEN >> >>>> >>> >> > cast(((rand(9) * cast(1000 as double)) - cast(9999999999 as >> >>>> >>> double)) >> >>>> >>> as >> >>>> >>> >> > string) ELSE nav_tcdt#25 END = site_categ_id#80) && >> >>>> >>> (cast(nav_tcd#26 >> >>>> >>> as >> >>>> >>> >> int) >> >>>> >>> >> > = 9)) && (cur_flag#77 = 1)) >> >>>> >>> >> > :- SubqueryAlias a >> >>>> >>> >> > : +- SubqueryAlias tmp_lifan_trfc_tpa_hive >> >>>> >>> >> > : +- CatalogRelation >> >>>> >>> `tmp`.`tmp_lifan_trfc_tpa_hive`, >> >>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, >> >>>> [date_id#7, >> >>>> >>> >> chanl_id#8L, >> >>>> >>> >> > pltfm_id#9, city_id#10, sessn_id#11, gu_id#12, >> >>>> >>> >> nav_refer_page_type_id#13, >> >>>> >>> >> > nav_refer_page_value#14, nav_refer_tpa#15, >> >>>> nav_refer_tpa_id#16, >> >>>> >>> >> > nav_refer_tpc#17, nav_refer_tpi#18, nav_page_type_id#19, >> >>>> >>> >> nav_page_value#20, >> >>>> >>> >> > nav_tpa_id#21, nav_tpa#22, nav_tpc#23, nav_tpi#24, >> >>>> nav_tcdt#25, >> >>>> >>> >> nav_tcd#26, >> >>>> >>> >> > nav_tci#27, nav_tce#28, detl_refer_page_type_id#29, >> >>>> >>> >> > detl_refer_page_value#30, ... 33 more fields] >> >>>> >>> >> > +- SubqueryAlias c >> >>>> >>> >> > +- SubqueryAlias dim_site_categ_ext >> >>>> >>> >> > +- CatalogRelation >> `dw`.`dim_site_categ_ext`, >> >>>> >>> >> > org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, >> >>>> >>> >> [site_categ_skid#64L, >> >>>> >>> >> > site_categ_type#65, site_categ_code#66, site_categ_name#67, >> >>>> >>> >> > site_categ_parnt_skid#68L, site_categ_kywrd#69, >> leaf_flg#70L, >> >>>> >>> >> sort_seq#71L, >> >>>> >>> >> > site_categ_srch_name#72, vsbl_flg#73, delet_flag#74, >> >>>> >>> etl_batch_id#75L, >> >>>> >>> >> > updt_time#76, cur_flag#77, bkgrnd_categ_skid#78L, >> >>>> >>> bkgrnd_categ_id#79L, >> >>>> >>> >> > site_categ_id#80, site_categ_parnt_id#81] >> >>>> >>> >> > >> >>>> >>> >> > Does spark sql not support syntax "case when" in JOIN? >> >>>> Additional, >> >>>> >>> my >> >>>> >>> >> spark >> >>>> >>> >> > version is 2.2.0. >> >>>> >>> >> > Any help would be greatly appreciated. >> >>>> >>> >> > >> >>>> >>> >> > >> >>>> >>> >> > >> >>>> >>> >> > >> >>>> >>> >> > -- >> >>>> >>> >> > View this message in context: >> http://apache-spark-developers >> >>>> >>> >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t- >> >>>> >>> >> be-supported-in-JOIN-tp21953.html >> >>>> >>> >> > Sent from the Apache Spark Developers List mailing list >> >>>> archive >> >>>> at >> >>>> >>> >> Nabble.com. >> >>>> >>> >> > >> >>>> >>> >> > ------------------------------ >> ------------------------------ >> >>>> >>> --------- >> >>>> >>> >> > To unsubscribe e-mail: >> >>>> >>> >> >>>> >>> > dev-unsubscribe@.apache >> >>>> >>> >> >>>> >>> >> > >> >>>> >>> >> >> >>>> >>> >> >> >>>> >>> >> ------------------------------------------------------------ >> >>>> --------- >> >>>> >>> >> To unsubscribe e-mail: >> >>>> >>> >> >>>> >>> > dev-unsubscribe@.apache >> >>>> >>> >> >>>> >>> >> >> >>>> >>> >> >> >>>> >>> >> >>>> >>> >> >>>> >>> >> >>>> >>> >> >>>> >>> >> >>>> >>> ----- >> >>>> >>> Liang-Chi Hsieh | @viirya >> >>>> >>> Spark Technology Center >> >>>> >>> http://www.spark.tc/ >> >>>> >>> -- >> >>>> >>> View this message in context: http://apache-spark-developers >> >>>> >>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be- >> >>>> >>> supported-in-JOIN-tp21953p21961.html >> >>>> >>> Sent from the Apache Spark Developers List mailing list archive >> at >> >>>> >>> Nabble.com. >> >>>> >>> >> >>>> >>> ------------------------------------------------------------ >> >>>> --------- >> >>>> >>> To unsubscribe e-mail: >> >>>> >> >>>> > dev-unsubscribe@.apache >> >>>> >> >>>> >>> >> >>>> >>> >> >>>> >> >> >>>> >> >>>> >> >>>> >> >>>> >> >>>> >> >>>> ----- >> >>>> Liang-Chi Hsieh | @viirya >> >>>> Spark Technology Center >> >>>> http://www.spark.tc/ >> >>>> -- >> >>>> View this message in context: http://apache-spark-developers >> >>>> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t-be- >> >>>> supported-in-JOIN-tp21953p21973.html >> >>>> Sent from the Apache Spark Developers List mailing list archive at >> >>>> Nabble.com. >> >>>> >> >>>> ------------------------------------------------------------ >> --------- >> >>>> To unsubscribe e-mail: >> >> > dev-unsubscribe@.apache >> >> >>>> >> >>>> >> >>> >> >> >> >> >> >> >> >> ----- >> Liang-Chi Hsieh | @viirya >> Spark Technology Center >> http://www.spark.tc/ >> -- >> View this message in context: http://apache-spark-developers >> -list.1001551.n3.nabble.com/SQL-Syntax-case-when-doesn-t- >> be-supported-in-JOIN-tp21953p21982.html >> Sent from the Apache Spark Developers List mailing list archive at >> Nabble.com. >> >> --------------------------------------------------------------------- >> To unsubscribe e-mail: dev-unsubscr...@spark.apache.org >> >> >
