konstantinb commented on PR #6842:
URL: https://github.com/apache/hive/pull/6842#issuecomment-6066355594
Thanks for taking a look at HIVE-30037. While working on the ticket (I filed
it and assigned it to myself) I ran this PR's change, as of 9522cd4e98 (the
PR's own changes are the same at the current head), against the queries below.
Two queries that run on master fail with an error, and in the remaining cases
the positions are still not resolved.
All results use this table:
```sql
create table t (d int, s string);
insert into t values (1, 'a'), (3, 'c'), (2, 'b');
create table x (d int);
create table y (d int);
```
- `select * from t tablesample (5 rows) order by 1 desc` fails with error
10219 ("Position in ORDER BY is not supported when using SELECT *"). On master
it returns 1, 3, 2 (the sort is ignored). Without the TABLESAMPLE, CBO plans it
and it returns 3, 2, 1.
- `select d, rank() over (order by d desc) as r from t tablesample (5 rows)
order by 2` fails with error 10002 ("Invalid column reference 'd'"). On master
it runs and returns d = 3, 2, 1. Without the TABLESAMPLE, CBO plans it and it
returns d = 3, 2, 1 with r = 1, 2, 3.
- In a multi-insert whose `FROM` is a join, the `ORDER BY` position in an
insert clause is still compiled as a constant: `from (select a.d from t a join
t b on a.d = b.d) j insert overwrite table x select d order by 1 desc limit 2
insert overwrite table y select d order by 1 limit 2` writes 1, 2 into `x`
instead of 3, 2. CBO plans the join in `FROM` here, so the branch where this PR
calls `processPositionAlias` (the one taken when CBO declines) is not reached.
- An `ORDER BY` position inside a view is still compiled as a constant when
the outer query is declined: with `create view v as select d, s from (select d,
s from t sort by d limit 5) q order by 1 desc limit 5`, `select * from v`
returns 1, 2, 3 instead of 3, 2, 1. CBO declines the outer statement because
the view body contains `SORT BY` with `LIMIT` (logged as "Not invoking CBO
because the statement has sort by with limit").
- This PR resolves positions by calling `processPositionAlias`, which
substitutes only `GROUP BY` and `ORDER BY` positions, so positions in `SORT
BY`, `DISTRIBUTE BY` and `CLUSTER BY` are compiled as constants as on master:
with TABLESAMPLE, `sort by 1 desc` plans no sort key and `distribute by 1`
partitions on the constant 1. The ticket's original summary covered only `ORDER
BY`; it now covers all four clauses.
#6842 resolves the positions in `SemanticAnalyzer.genReduceSinkPlan`,
against the select output (where `SELECT *` is already expanded), for both sort
keys and partition keys. With #6842 each query above returns the same rows as
its CBO-planned version; its description lists the tests. I'd suggest
continuing the review there, and if there's a case this PR handles that #6842
doesn't, please point it out.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
