You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@tajo.apache.org by dongjoon-hyun <gi...@git.apache.org> on 2015/04/19 12:51:35 UTC
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
GitHub user dongjoon-hyun opened a pull request:
https://github.com/apache/tajo/pull/543
TAJO-1565: Simple column name in ORDER BY is failed with 'Ambiguous Column Name Error'
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/dongjoon-hyun/tajo TAJO-1565
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/tajo/pull/543.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #543
----
commit 314f50c9ad260e858dd0a3298c8c34ef26c4415a
Author: Dongjoon Hyun <do...@apache.org>
Date: 2015-04-19T10:49:08Z
TAJO-1565: Simple column name in ORDER BY is failed with 'Ambiguous Column Name ERROR'.
----
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by babokim <gi...@git.apache.org>.
Github user babokim commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94265732
@dongjoon-hyun OK, I will test this patch using TPC-DC queries.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94335916
I've tesed your patch. It looks good to me, but it would be better if you add several test cases.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94327920
Maybe there are some misunderstandings. I'm not disagreeing on this patch, but need a reference that explains this rule more detailed.
How did you figure out that this rule is applied to only order-by clauses? If you just run several queries on pgsql, it is insufficient.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94492582
Hi, @jihoonson . I added a testcase for this issue.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94273921
@jihoonson , up to my knowledge, sorting is based on the result of selection by the SQL definition. In other words, sorting stage is processed after producing the table. So, I added `normalizeSort` function that look the schema part up first and handle the other cases in pre-defined `normalizeSort`. I thought it is general. Could you elaborate what you mean? I'm not sure what you mean by `general rule`.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94592847
No problem at all. :)
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94567939
Thanks @dongjoon-hyun. The newly added test looks good, but have a limitation. That is, it cannot test that the qualifier is properly guessed for the simple column name in order-by clause. So, we need a test case to check the query execution result in addition to the plan test.
In addition, I found that this patch cannot handle the case when there are a column and an alias of same name. Please see the following example.
*Postgresql*
```
# explain select r_name as n_name from nation, region order by n_name;
QUERY PLAN
-------------------------------------------------------------------------
Sort (cost=284.00..293.25 rows=3700 width=32)
Sort Key: region.r_name
-> Nested Loop (cost=0.00..64.71 rows=3700 width=32)
-> Seq Scan on nation (cost=0.00..17.40 rows=740 width=0)
-> Materialize (cost=0.00..1.07 rows=5 width=32)
-> Seq Scan on region (cost=0.00..1.05 rows=5 width=32)
(6 rows)
```
*Tajo*
```
default> explain select r_name as n_name from nation, region order by n_name;
ERROR: Ambiguous column name: n_name
```
I think that this case also handled in this patch.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94264360
Hi, @babokim . I used the following sequences for easy testing.
```
create table t(a int);
insert overwrite into t select 1;
select t2.a from t t1, t t2, t t3 where t1.a=t2.a and t1.a=t3.a order by a;
```
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94274754
Hi, @jihoonson . I found a related description in PostgresSQL documentation.
http://www.postgresql.org/docs/9.4/static/queries-order.html
*After a query has produced an output table (after the select list has been processed) it can optionally be sorted*. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94327560
By the way, I added a bug-fix code like the following, but it causes test failures. I will investigate it.
```
- fieldsByName.put(newCol.getSimpleName(), TUtil.newList(fields.size() - 1));
+ if (fieldsByName.containsKey(newCol.getSimpleName())) {
+ fieldsByName.get(newCol.getSimpleName()).add(fields.size() - 1);
+ } else {
+ fieldsByName.put(newCol.getSimpleName(), TUtil.newList(fields.size() - 1));
+ }
```
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94330635
Ok. I found following quotes from the link you shard.
```
... A sort_expression can also be the column label or number of an output column, ...
```
```
... There is still ambiguity if an ORDER BY item is a simple name that could match either an output column name or a column from the table expression. The output column is used in such cases. This would only cause confusion if you use AS to rename an output column to match some other table column's name.
ORDER BY can be applied to the result of a UNION, INTERSECT, or EXCEPT combination, but in this case it is only permitted to sort by output column names or numbers, not by expressions.
```
The second quote looks to give a description related to this issue. According to this quote, column labels, expressions, and numbers of each output column can be resolved with the columns in the query's select list.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94589564
Thanks. Unfortunately I have some schedules today. I'll try to review soon.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94321615
Oh. Thank you, @jihoonson .Now, I understand more about your concern and what causes disagreement.
First, this issue is related ORDER BY SQL name resolution rules rather than SQL name resolution. The scope is totally different. I think we need to focus what is the meaning of ORDER BY clause. In that sense, please note that *"it can optionally be sorted"*. ORDER BY is defined on the SELECT clause. If you ask 'Why this rule focuses on only order-by clause?', the reason is ORDER BY clause is different from the others.
Second, let me answer your remaining concerns.
* Why does the same rule can be applied to group-by clauses or any other clauses? --> We can not. As you know, the other clause affect SELECT clause; e.g. GROUP BY clause will generate aggregate functions on SELECT clause.
* What happens if the select and order-by clauses involve two or more columns? --> Each column among them should be need to be resolved independently. In case of multiple matching, we should print out 'Ambiguous Column Name Error' again. I think it is logical. If you agree with this, I will add more codes and testcases for this.
Thank you for you kind guide, again, @jihoonson .
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94327419
What I mean is 'a name resolution rule for SQL order-by clause'. It's a narrow-scoped part of your SQL name resolution rule. I doesn't mean another rule.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94324349
I've never heard "ORDER BY SQL name resolution rules". Is it SQL standard?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94278974
Sorry for confusing you. I mean, this issue is related to SQL name resolution rules rather than sort execution.
Basically, when there are two or more columns that have the same name in a SQL, qualifiers must be explicitly specified for those columns. Tajo also follows this rule so far. And now, you are trying to add a new rule.
The general rule means that the new rule should be sufficiently general to handle most cases with a few unavoidable exceptions. So, we need to discuss to design the new rule. Without discussion, people like me can have many questions about the rule. For example, your patch allows to omit the qualifier of a column in the order-by clause if the select clause involves a column of the same name. So, some of my questions are, why this rule focuses on only order-by clause? why does the same rule can be applied to group-by clauses or any other clauses? what happens if the select and order-by clauses involve two or more columns?
I've also checked pgsql. It seems to use this rule for only the order-by clause. I think that we need the exact reason to make a solid rule.
The above quote looks to describe the order-by clause, and not much helpful to investigate this issue.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94984693
Ok. If so, we must resolve #545 first. After resolving that issue, you can add test cases.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94338754
Thank you, @jihoonson . I will follow your advice!
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94582003
Hi, @jihoonson . I found that that was a bug of `addColumn` ( TAJO-1573 ).
Please see the change of https://github.com/apache/tajo/pull/545 .
It causes `getNaturalJoinCondition`, `verifyProjectedFields`, `verifyIfTargetsCanBeEvaluated` bugs, too. So the theme is different, we had better handle those different issues in a different issue.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94267632
I think that is the first thing what contributors should do when they take an issue. I'll investigate too.
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun closed the pull request at:
https://github.com/apache/tajo/pull/543
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94265811
We need to have some discussion on this feature. In this case, as @dongjoon-hyun said, the column in the order-by clause can be identified as the column in the select clause. However, IMO, we need have a general rule to handle this issue. Do you guys have any knowledge about the well-know rules?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by dongjoon-hyun <gi...@git.apache.org>.
Github user dongjoon-hyun commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94267149
Hi, @jihoonson . I'm not sure about that point. If you give me some advice, I will revise this patch in more general form later. What about asking @hyunsik , too?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---
[GitHub] tajo pull request: TAJO-1565: Simple column name in ORDER BY is fa...
Posted by jihoonson <gi...@git.apache.org>.
Github user jihoonson commented on the pull request:
https://github.com/apache/tajo/pull/543#issuecomment-94328594
I got some of your points. You mean that columns in the order-by clause can be identified by the columns in the select clause because order-by is executed after selection. Right?
---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---