Re: Why hint does not traverse down subquery alias
A meaningful error message will be great! On Tue, Jun 11, 2019 at 6:15 PM Maryann Xue wrote: > BTW, I've actually just done some work on hint error handling, which might > be helpful to what you mentioned: > > https://github.com/apache/spark/pull/24653 > > On Tue, Jun 11, 2019 at 8:04 PM Maryann Xue > wrote: > >> I believe in the SQL standard, the original name cannot be accessed once >> it’s aliased. >> >> On Tue, Jun 11, 2019 at 7:54 PM John Zhuge wrote: >> >>> Yeah, it is a touch scenario. >>> >>> I actually have much simpler cases: >>> >>> 1) select /*+ broadcast(t1) */ * from db.t1 join db.t2 on t1.id = t2.id; >>> 2) select /*+ broadcast(t1) */ * from db.t1 a1 join db.t2 a2 on a1.id = >>> a2.id; >>> >>> 2) is the same as 1) but with aliases. Many users were surprised that 2) >>> stopped working. >>> >>> Thanks, >>> John >>> >>> >>> On Tue, Jun 11, 2019 at 4:38 PM Maryann Xue >>> wrote: >>> Yes, and for a good reason: the hint relation has exactly the same scope with other elements of queries/sub-queries. Suppose there's a query like: select /*+ broadcast(s) */ from (select a, b from s) t join (select a, b from t) s on t1.a = t2.b If we allowed the hint resolving to "cross" the scopes, we'd end up with a really confusing spec. Thanks, Maryann On Tue, Jun 11, 2019 at 5:26 PM John Zhuge wrote: > Hi Reynold and Maryann, > > ResolveHints javadoc indicates the traversal does not go past subquery > alias. Is there any specific reason? > > Thanks, > John Zhuge > >>> >>> -- >>> John Zhuge >>> >> -- John Zhuge
Re: Why hint does not traverse down subquery alias
BTW, I've actually just done some work on hint error handling, which might be helpful to what you mentioned: https://github.com/apache/spark/pull/24653 On Tue, Jun 11, 2019 at 8:04 PM Maryann Xue wrote: > I believe in the SQL standard, the original name cannot be accessed once > it’s aliased. > > On Tue, Jun 11, 2019 at 7:54 PM John Zhuge wrote: > >> Yeah, it is a touch scenario. >> >> I actually have much simpler cases: >> >> 1) select /*+ broadcast(t1) */ * from db.t1 join db.t2 on t1.id = t2.id; >> 2) select /*+ broadcast(t1) */ * from db.t1 a1 join db.t2 a2 on a1.id = >> a2.id; >> >> 2) is the same as 1) but with aliases. Many users were surprised that 2) >> stopped working. >> >> Thanks, >> John >> >> >> On Tue, Jun 11, 2019 at 4:38 PM Maryann Xue >> wrote: >> >>> Yes, and for a good reason: the hint relation has exactly the same scope >>> with other elements of queries/sub-queries. >>> >>> Suppose there's a query like: >>> >>> select /*+ broadcast(s) */ from (select a, b from s) t join (select a, b >>> from t) s on t1.a = t2.b >>> >>> If we allowed the hint resolving to "cross" the scopes, we'd end up with >>> a really confusing spec. >>> >>> >>> Thanks, >>> Maryann >>> >>> On Tue, Jun 11, 2019 at 5:26 PM John Zhuge wrote: >>> Hi Reynold and Maryann, ResolveHints javadoc indicates the traversal does not go past subquery alias. Is there any specific reason? Thanks, John Zhuge >>> >> >> -- >> John Zhuge >> >
Re: Why hint does not traverse down subquery alias
I believe in the SQL standard, the original name cannot be accessed once it’s aliased. On Tue, Jun 11, 2019 at 7:54 PM John Zhuge wrote: > Yeah, it is a touch scenario. > > I actually have much simpler cases: > > 1) select /*+ broadcast(t1) */ * from db.t1 join db.t2 on t1.id = t2.id; > 2) select /*+ broadcast(t1) */ * from db.t1 a1 join db.t2 a2 on a1.id = > a2.id; > > 2) is the same as 1) but with aliases. Many users were surprised that 2) > stopped working. > > Thanks, > John > > > On Tue, Jun 11, 2019 at 4:38 PM Maryann Xue wrote: > >> Yes, and for a good reason: the hint relation has exactly the same scope >> with other elements of queries/sub-queries. >> >> Suppose there's a query like: >> >> select /*+ broadcast(s) */ from (select a, b from s) t join (select a, b >> from t) s on t1.a = t2.b >> >> If we allowed the hint resolving to "cross" the scopes, we'd end up with >> a really confusing spec. >> >> >> Thanks, >> Maryann >> >> On Tue, Jun 11, 2019 at 5:26 PM John Zhuge wrote: >> >>> Hi Reynold and Maryann, >>> >>> ResolveHints javadoc indicates the traversal does not go past subquery >>> alias. Is there any specific reason? >>> >>> Thanks, >>> John Zhuge >>> >> > > -- > John Zhuge >
Re: Why hint does not traverse down subquery alias
Yeah, it is a touch scenario. I actually have much simpler cases: 1) select /*+ broadcast(t1) */ * from db.t1 join db.t2 on t1.id = t2.id; 2) select /*+ broadcast(t1) */ * from db.t1 a1 join db.t2 a2 on a1.id = a2.id; 2) is the same as 1) but with aliases. Many users were surprised that 2) stopped working. Thanks, John On Tue, Jun 11, 2019 at 4:38 PM Maryann Xue wrote: > Yes, and for a good reason: the hint relation has exactly the same scope > with other elements of queries/sub-queries. > > Suppose there's a query like: > > select /*+ broadcast(s) */ from (select a, b from s) t join (select a, b > from t) s on t1.a = t2.b > > If we allowed the hint resolving to "cross" the scopes, we'd end up with a > really confusing spec. > > > Thanks, > Maryann > > On Tue, Jun 11, 2019 at 5:26 PM John Zhuge wrote: > >> Hi Reynold and Maryann, >> >> ResolveHints javadoc indicates the traversal does not go past subquery >> alias. Is there any specific reason? >> >> Thanks, >> John Zhuge >> > -- John Zhuge
Re: Why hint does not traverse down subquery alias
Yes, and for a good reason: the hint relation has exactly the same scope with other elements of queries/sub-queries. Suppose there's a query like: select /*+ broadcast(s) */ from (select a, b from s) t join (select a, b from t) s on t1.a = t2.b If we allowed the hint resolving to "cross" the scopes, we'd end up with a really confusing spec. Thanks, Maryann On Tue, Jun 11, 2019 at 5:26 PM John Zhuge wrote: > Hi Reynold and Maryann, > > ResolveHints javadoc indicates the traversal does not go past subquery > alias. Is there any specific reason? > > Thanks, > John Zhuge >
Why hint does not traverse down subquery alias
Hi Reynold and Maryann, ResolveHints javadoc indicates the traversal does not go past subquery alias. Is there any specific reason? Thanks, John Zhuge