Why hint does not traverse down subquery alias

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Why hint does not traverse down subquery alias

John Zhuge
Hi Reynold and Maryann,

ResolveHints javadoc indicates the traversal does not go past subquery alias. Is there any specific reason?

Thanks,
John Zhuge
Reply | Threaded
Open this post in threaded view
|

Re: Why hint does not traverse down subquery alias

Maryann Xue-2
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 <[hidden email]> wrote:
Hi Reynold and Maryann,

ResolveHints javadoc indicates the traversal does not go past subquery alias. Is there any specific reason?

Thanks,
John Zhuge
Reply | Threaded
Open this post in threaded view
|

Re: Why hint does not traverse down subquery alias

John Zhuge
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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Why hint does not traverse down subquery alias

Maryann Xue
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Why hint does not traverse down subquery alias

Maryann Xue-2
BTW, I've actually just done some work on hint error handling, which might be helpful to what you mentioned:


On Tue, Jun 11, 2019 at 8:04 PM Maryann Xue <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: Why hint does not traverse down subquery alias

John Zhuge
A meaningful error message will be great!

On Tue, Jun 11, 2019 at 6:15 PM Maryann Xue <[hidden email]> wrote:
BTW, I've actually just done some work on hint error handling, which might be helpful to what you mentioned:


On Tue, Jun 11, 2019 at 8:04 PM Maryann Xue <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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