You are viewing a plain text version of this content. The canonical link for it is here.
Posted to reviews@spark.apache.org by cloud-fan <gi...@git.apache.org> on 2016/03/11 15:46:02 UTC
[GitHub] spark pull request: [SPARK-XXXX][SQL] Can't add subquery to an ope...
GitHub user cloud-fan opened a pull request:
https://github.com/apache/spark/pull/11658
[SPARK-XXXX][SQL] Can't add subquery to an operator with same-name outputs while generate SQL string
## What changes were proposed in this pull request?
This PR tries to solve a fundamental issue in the `SQLBuilder`. When we want to turn a logical plan into SQL string and put it after FROM clause, we need to wrap it with a sub-query. However, a logical plan is allowed to have same-name outputs with different qualifiers(e.g. the `Join` operator), and this kind of plan can't be put under a subquery as we will erase and assign a new qualifier to all outputs and make it impossible to distinguish same-name outputs.
To solve this problem, this PR renames all attributes with globally unique names(using exprId), so that we don't need qualifiers to resolve ambiguity anymore.
## How was this patch tested?
existing tests and new tests in LogicalPlanToSQLSuite.
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/cloud-fan/spark gensql
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/spark/pull/11658.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 #11658
----
commit f4b1ae8b30c2a572d4f44cd68b66c224aeee553b
Author: Wenchen Fan <we...@databricks.com>
Date: 2016-03-11T06:29:45Z
tmp
commit 198b406a643d908483408ec6ccea26ffdc464aa9
Author: Wenchen Fan <we...@databricks.com>
Date: 2016-03-11T14:35:50Z
assign globally unique names to all attributes to avoid ambiguity
----
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r56274664
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/namedExpressions.scala ---
@@ -185,8 +185,7 @@ case class Alias(child: Expression, name: String)(
override def sql: String = {
val qualifiersString =
if (qualifiers.isEmpty) "" else qualifiers.map(quoteIdentifier).mkString("", ".", ".")
- val aliasName = if (isGenerated) s"$name#${exprId.id}" else s"$name"
--- End diff --
`isGenerated` is still needed to avoid resolving column names on these generated internal attributes. However, it should not affect the `sql` anymore, as this PR need to control the format of attribute names and alias names.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196788731
Merged build finished. Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r55916858
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -54,8 +55,26 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
def toSQL: String = {
val canonicalizedPlan = Canonicalizer.execute(logicalPlan)
+ val outputNames = logicalPlan.output.map(_.name)
+ val qualifiers = logicalPlan.output.flatMap(_.qualifiers).distinct
+
+ // Keep the qualifier information by using it as sub-query name, if there is only one qualifier
+ // present.
+ val finalName = if (qualifiers.isEmpty || qualifiers.length > 1) {
+ SQLBuilder.newSubqueryName
+ } else {
+ qualifiers.head
+ }
+
+ // Canonicalizer will remove all naming information, we should add it back by adding an extra
+ // Project and alias the outputs.
+ val aliasedOutput = canonicalizedPlan.output.zip(outputNames).map {
+ case (attr, name) => Alias(attr.withQualifiers(Nil), name)()
+ }
+ val finalPlan = Project(aliasedOutput, SubqueryAlias(finalName, canonicalizedPlan))
--- End diff --
The top Project in the `finalPlan` still has the name ambiguity issues. I think it could be still a problem when we use it in the native view supports. https://github.com/apache/spark/pull/10733
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195660473
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/52987/
Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by yhuai <gi...@git.apache.org>.
Github user yhuai commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r56272587
--- Diff: sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/expressions/namedExpressions.scala ---
@@ -185,8 +185,7 @@ case class Alias(child: Expression, name: String)(
override def sql: String = {
val qualifiersString =
if (qualifiers.isEmpty) "" else qualifiers.map(quoteIdentifier).mkString("", ".", ".")
- val aliasName = if (isGenerated) s"$name#${exprId.id}" else s"$name"
--- End diff --
Is isGenerated still needed? (if not, we do not need to remove it in this PR). Also, what is the reason of this change?
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by yhuai <gi...@git.apache.org>.
Github user yhuai commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r56272606
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -109,23 +128,6 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
case Limit(limitExpr, child) =>
s"${toSQL(child)} LIMIT ${limitExpr.sql}"
- case p: Sample if p.isTableSample =>
- val fraction = math.min(100, math.max(0, (p.upperBound - p.lowerBound) * 100))
- p.child match {
- case m: MetastoreRelation =>
- val aliasName = m.alias.getOrElse("")
- build(
- s"`${m.databaseName}`.`${m.tableName}`",
- "TABLESAMPLE(" + fraction + " PERCENT)",
- aliasName)
- case s: SubqueryAlias =>
- val aliasName = if (s.child.isInstanceOf[SubqueryAlias]) s.alias else ""
- val plan = if (s.child.isInstanceOf[SubqueryAlias]) s.child else s
- build(toSQL(plan), "TABLESAMPLE(" + fraction + " PERCENT)", aliasName)
- case _ =>
- build(toSQL(p.child), "TABLESAMPLE(" + fraction + " PERCENT)")
- }
--- End diff --
Explain the reason?
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195651607
Merged build finished. Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196265015
Merged build finished. Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r56274804
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -316,31 +319,55 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
// `Aggregate`s.
CollapseProject),
Batch("Recover Scoping Info", Once,
- // Used to handle other auxiliary `Project`s added by analyzer (e.g.
- // `ResolveAggregateFunctions` rule)
- AddSubquery,
- // Previous rule will add extra sub-queries, this rule is used to re-propagate and update
- // the qualifiers bottom up, e.g.:
- //
- // Sort
- // ordering = t1.a
- // Project
- // projectList = [t1.a, t1.b]
- // Subquery gen_subquery
- // child ...
- //
- // will be transformed to:
- //
- // Sort
- // ordering = gen_subquery.a
- // Project
- // projectList = [gen_subquery.a, gen_subquery.b]
- // Subquery gen_subquery
- // child ...
- UpdateQualifiers
+ // Remove all sub queries, as we will insert new ones when it's necessary.
+ EliminateSubqueryAliases,
+ // A logical plan is allowed to have same-name outputs with different qualifiers(e.g. the
+ // `Join` operator). However, this kind of plan can't be put under a sub query as we will
+ // erase and assign a new qualifier to all outputs and make it impossible to distinguish
+ // same-name outputs. This rule renames all attributes, to guarantee different
+ // attributes(with different exprId) always have different names. It also removes all
+ // qualifiers, as attributes have unique names now and we don't need qualifiers to resolve
+ // ambiguity.
--- End diff --
at the beginning of `toSQL` method.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195396204
cc @liancheng @gatorsmile @yhuai
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by yhuai <gi...@git.apache.org>.
Github user yhuai commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r56272714
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -316,31 +319,55 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
// `Aggregate`s.
CollapseProject),
Batch("Recover Scoping Info", Once,
- // Used to handle other auxiliary `Project`s added by analyzer (e.g.
- // `ResolveAggregateFunctions` rule)
- AddSubquery,
- // Previous rule will add extra sub-queries, this rule is used to re-propagate and update
- // the qualifiers bottom up, e.g.:
- //
- // Sort
- // ordering = t1.a
- // Project
- // projectList = [t1.a, t1.b]
- // Subquery gen_subquery
- // child ...
- //
- // will be transformed to:
- //
- // Sort
- // ordering = gen_subquery.a
- // Project
- // projectList = [gen_subquery.a, gen_subquery.b]
- // Subquery gen_subquery
- // child ...
- UpdateQualifiers
+ // Remove all sub queries, as we will insert new ones when it's necessary.
+ EliminateSubqueryAliases,
+ // A logical plan is allowed to have same-name outputs with different qualifiers(e.g. the
+ // `Join` operator). However, this kind of plan can't be put under a sub query as we will
+ // erase and assign a new qualifier to all outputs and make it impossible to distinguish
+ // same-name outputs. This rule renames all attributes, to guarantee different
+ // attributes(with different exprId) always have different names. It also removes all
+ // qualifiers, as attributes have unique names now and we don't need qualifiers to resolve
+ // ambiguity.
--- End diff --
Where do we reassign the needed column names?
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196188528
retest this please
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196694484
**[Test build #53172 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/53172/consoleFull)** for PR 11658 at commit [`5b12aa0`](https://github.com/apache/spark/commit/5b12aa08add3cf97c938ad6d7dd7de8e0118e55a).
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195660471
Merged build finished. Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196788738
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/53182/
Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196891886
**[Test build #53203 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/53203/consoleFull)** for PR 11658 at commit [`5ef9fd4`](https://github.com/apache/spark/commit/5ef9fd4956eb76d7d6988e445fbd6ae61a1d0481).
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196940385
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/53203/
Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196264540
**[Test build #53058 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/53058/consoleFull)** for PR 11658 at commit [`5b12aa0`](https://github.com/apache/spark/commit/5b12aa08add3cf97c938ad6d7dd7de8e0118e55a).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r55916721
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -168,11 +176,12 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
toSQL(p.right),
p.condition.map(" ON " + _.sql).getOrElse(""))
- case p: MetastoreRelation =>
- build(
- s"${quoteIdentifier(p.databaseName)}.${quoteIdentifier(p.tableName)}",
- p.alias.map(a => s" AS ${quoteIdentifier(a)}").getOrElse("")
- )
+ case SQLTable(database, table, _, sample) =>
+ val qualifiedName = s"${quoteIdentifier(database)}.${quoteIdentifier(table)}"
+ sample.map { case (lowerBound, upperBound) =>
--- End diff --
After this change, you can remove [case Sample](https://github.com/cloud-fan/spark/blob/gensql/sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala#L131-L146)
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195452256
**[Test build #52926 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/52926/consoleFull)** for PR 11658 at commit [`198b406`](https://github.com/apache/spark/commit/198b406a643d908483408ec6ccea26ffdc464aa9).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196787722
**[Test build #53182 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/53182/consoleFull)** for PR 11658 at commit [`8de6365`](https://github.com/apache/spark/commit/8de6365a70c9830f4e3a70a28debdd100093c539).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds the following public classes _(experimental)_:
* ` case class SQLTable(`
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195676361
I think it's because our optimizer is not smart enough, these alias-only `Project` should be removed, name ambiguity is not a problem anymore after analysis phase.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195476480
You are so fast! Will do the review tonight or tomorrow. I have another test case for this issue. Maybe you can take it. This is Project -- Subquery -- Filter -- Aggregate --...
```SQL
SELECT Count(a.value),
b.KEY,
a.KEY
FROM parquet_t1 a,
parquet_t1 b
GROUP BY a.KEY,
b.KEY
HAVING Max(a.KEY) > 0
```
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r55917303
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -54,8 +55,26 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
def toSQL: String = {
val canonicalizedPlan = Canonicalizer.execute(logicalPlan)
+ val outputNames = logicalPlan.output.map(_.name)
+ val qualifiers = logicalPlan.output.flatMap(_.qualifiers).distinct
+
+ // Keep the qualifier information by using it as sub-query name, if there is only one qualifier
+ // present.
+ val finalName = if (qualifiers.isEmpty || qualifiers.length > 1) {
+ SQLBuilder.newSubqueryName
+ } else {
+ qualifiers.head
+ }
+
+ // Canonicalizer will remove all naming information, we should add it back by adding an extra
+ // Project and alias the outputs.
+ val aliasedOutput = canonicalizedPlan.output.zip(outputNames).map {
+ case (attr, name) => Alias(attr.withQualifiers(Nil), name)()
+ }
+ val finalPlan = Project(aliasedOutput, SubqueryAlias(finalName, canonicalizedPlan))
--- End diff --
Yea, actually we should add this check in our `CreateView` operator. I checked PostgreSQL, it doesn't allow ambiguous column names when create view, do you wanna submit a PR?
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195676456
True. : )
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195452965
Merged build finished. Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r56274754
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -109,23 +128,6 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
case Limit(limitExpr, child) =>
s"${toSQL(child)} LIMIT ${limitExpr.sql}"
- case p: Sample if p.isTableSample =>
- val fraction = math.min(100, math.max(0, (p.upperBound - p.lowerBound) * 100))
- p.child match {
- case m: MetastoreRelation =>
- val aliasName = m.alias.getOrElse("")
- build(
- s"`${m.databaseName}`.`${m.tableName}`",
- "TABLESAMPLE(" + fraction + " PERCENT)",
- aliasName)
- case s: SubqueryAlias =>
- val aliasName = if (s.child.isInstanceOf[SubqueryAlias]) s.alias else ""
- val plan = if (s.child.isInstanceOf[SubqueryAlias]) s.child else s
- build(toSQL(plan), "TABLESAMPLE(" + fraction + " PERCENT)", aliasName)
- case _ =>
- build(toSQL(p.child), "TABLESAMPLE(" + fraction + " PERCENT)")
- }
--- End diff --
I explained it in the comment of `ResolveSQLTable` rule. We only support table sample so any `Sample` operator that is not on top of table relation should throw exception.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196265019
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/53058/
Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195697018
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/52993/
Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195637451
**[Test build #52985 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/52985/consoleFull)** for PR 11658 at commit [`21a142d`](https://github.com/apache/spark/commit/21a142d775b93f76dc16f2bf25af40f42f61774a).
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195651544
**[Test build #52985 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/52985/consoleFull)** for PR 11658 at commit [`21a142d`](https://github.com/apache/spark/commit/21a142d775b93f76dc16f2bf25af40f42f61774a).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195396390
**[Test build #52926 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/52926/consoleFull)** for PR 11658 at commit [`198b406`](https://github.com/apache/spark/commit/198b406a643d908483408ec6ccea26ffdc464aa9).
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195672838
**[Test build #52993 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/52993/consoleFull)** for PR 11658 at commit [`5b12aa0`](https://github.com/apache/spark/commit/5b12aa08add3cf97c938ad6d7dd7de8e0118e55a).
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by asfgit <gi...@git.apache.org>.
Github user asfgit closed the pull request at:
https://github.com/apache/spark/pull/11658
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195696968
**[Test build #52993 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/52993/consoleFull)** for PR 11658 at commit [`5b12aa0`](https://github.com/apache/spark/commit/5b12aa08add3cf97c938ad6d7dd7de8e0118e55a).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196940378
Merged build finished. Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196731413
**[Test build #53172 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/53172/consoleFull)** for PR 11658 at commit [`5b12aa0`](https://github.com/apache/spark/commit/5b12aa08add3cf97c938ad6d7dd7de8e0118e55a).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196731812
Merged build finished. Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r55917330
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -54,8 +55,26 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
def toSQL: String = {
val canonicalizedPlan = Canonicalizer.execute(logicalPlan)
+ val outputNames = logicalPlan.output.map(_.name)
+ val qualifiers = logicalPlan.output.flatMap(_.qualifiers).distinct
+
+ // Keep the qualifier information by using it as sub-query name, if there is only one qualifier
+ // present.
+ val finalName = if (qualifiers.isEmpty || qualifiers.length > 1) {
+ SQLBuilder.newSubqueryName
+ } else {
+ qualifiers.head
+ }
+
+ // Canonicalizer will remove all naming information, we should add it back by adding an extra
+ // Project and alias the outputs.
+ val aliasedOutput = canonicalizedPlan.output.zip(outputNames).map {
+ case (attr, name) => Alias(attr.withQualifiers(Nil), name)()
+ }
+ val finalPlan = Project(aliasedOutput, SubqueryAlias(finalName, canonicalizedPlan))
--- End diff --
Sure, I can do it tomorrow.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195697017
Merged build finished. Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r56274544
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -354,55 +381,56 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
// +- Filter ...
// +- Aggregate ...
// +- MetastoreRelation default, src, None
- case plan @ Project(_, Filter(_, _: Aggregate)) => wrapChildWithSubquery(plan)
+ case p @ Project(_, f @ Filter(_, _: Aggregate)) => p.copy(child = addSubquery(f))
- case w @ Window(_, _, _, Filter(_, _: Aggregate)) => wrapChildWithSubquery(w)
+ case w @ Window(_, _, _, f @ Filter(_, _: Aggregate)) => w.copy(child = addSubquery(f))
- case plan @ Project(_,
- _: SubqueryAlias
- | _: Filter
- | _: Join
- | _: MetastoreRelation
- | OneRowRelation
- | _: LocalLimit
- | _: GlobalLimit
- | _: Sample
- ) => plan
-
- case plan: Project => wrapChildWithSubquery(plan)
+ case p: Project => p.copy(child = addSubqueryIfNeeded(p.child))
// We will generate "SELECT ... FROM ..." for Window operator, so its child operator should
// be able to put in the FROM clause, or we wrap it with a subquery.
- case w @ Window(_, _, _,
- _: SubqueryAlias
- | _: Filter
- | _: Join
- | _: MetastoreRelation
- | OneRowRelation
- | _: LocalLimit
- | _: GlobalLimit
- | _: Sample
- ) => w
-
- case w: Window => wrapChildWithSubquery(w)
- }
+ case w: Window => w.copy(child = addSubqueryIfNeeded(w.child))
- private def wrapChildWithSubquery(plan: UnaryNode): LogicalPlan = {
- val newChild = SubqueryAlias(SQLBuilder.newSubqueryName, plan.child)
- plan.withNewChildren(Seq(newChild))
+ case j: Join => j.copy(
+ left = addSubqueryIfNeeded(j.left),
+ right = addSubqueryIfNeeded(j.right))
}
}
- object UpdateQualifiers extends Rule[LogicalPlan] {
- override def apply(tree: LogicalPlan): LogicalPlan = tree transformUp {
- case plan =>
- val inputAttributes = plan.children.flatMap(_.output)
- plan transformExpressions {
- case a: AttributeReference if !plan.producedAttributes.contains(a) =>
- val qualifier = inputAttributes.find(_ semanticEquals a).map(_.qualifiers)
- a.withQualifiers(qualifier.getOrElse(Nil))
- }
- }
+ private def addSubquery(plan: LogicalPlan): SubqueryAlias = {
+ SubqueryAlias(SQLBuilder.newSubqueryName, plan)
+ }
+
+ private def addSubqueryIfNeeded(plan: LogicalPlan): LogicalPlan = plan match {
+ case _: SubqueryAlias => plan
+ case _: Filter => plan
+ case _: Join => plan
+ case _: LocalLimit => plan
+ case _: GlobalLimit => plan
+ case _: SQLTable => plan
+ case OneRowRelation => plan
--- End diff --
As the [comments](https://github.com/apache/spark/pull/11658/files#diff-9a541c718fe730b5a10f2b24041ce1f5R336) says, we don't need to add sub-query if this operator can be put after FROM. So obviously, `SubqueryAlias`, `Join`, `SQLTable`, `OneRowRelation` don't need extra sub-query. Currently we only support convert logical plan that is parsed from SQL string to SQL string, this implies, `Filter`, `Limit` will always appear after `Project` and they will generate SQL string like `... WHERE ... LIMIT ...` which can be put after FROM.
Anyway this logical is just copied from original code.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r55930362
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -54,8 +55,26 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
def toSQL: String = {
val canonicalizedPlan = Canonicalizer.execute(logicalPlan)
+ val outputNames = logicalPlan.output.map(_.name)
+ val qualifiers = logicalPlan.output.flatMap(_.qualifiers).distinct
+
+ // Keep the qualifier information by using it as sub-query name, if there is only one qualifier
+ // present.
+ val finalName = if (qualifiers.isEmpty || qualifiers.length > 1) {
+ SQLBuilder.newSubqueryName
+ } else {
+ qualifiers.head
+ }
+
+ // Canonicalizer will remove all naming information, we should add it back by adding an extra
+ // Project and alias the outputs.
+ val aliasedOutput = canonicalizedPlan.output.zip(outputNames).map {
+ case (attr, name) => Alias(attr.withQualifiers(Nil), name)()
+ }
+ val finalPlan = Project(aliasedOutput, SubqueryAlias(finalName, canonicalizedPlan))
--- End diff --
Just realized Hive will issue an error when having duplicate names in CREATE VIEW. Thus, I guess we do not need to redo it in Spark?
BTW, the top Project could have a duplicate names when creating a view if users specify the schema. For example,
```SQL
CREATE VIEW testView(id3, id4) AS SELECT * FROM jt1 JOIN jt2 ON jt1.id1 == jt2.id1
```
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195651608
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/52985/
Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r55934217
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -54,8 +55,26 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
def toSQL: String = {
val canonicalizedPlan = Canonicalizer.execute(logicalPlan)
+ val outputNames = logicalPlan.output.map(_.name)
+ val qualifiers = logicalPlan.output.flatMap(_.qualifiers).distinct
+
+ // Keep the qualifier information by using it as sub-query name, if there is only one qualifier
+ // present.
+ val finalName = if (qualifiers.isEmpty || qualifiers.length > 1) {
+ SQLBuilder.newSubqueryName
+ } else {
+ qualifiers.head
+ }
+
+ // Canonicalizer will remove all naming information, we should add it back by adding an extra
+ // Project and alias the outputs.
+ val aliasedOutput = canonicalizedPlan.output.zip(outputNames).map {
+ case (attr, name) => Alias(attr.withQualifiers(Nil), name)()
+ }
+ val finalPlan = Project(aliasedOutput, SubqueryAlias(finalName, canonicalizedPlan))
--- End diff --
that's true, and we can add one more renaming project before passing the plan to SQLBuilder when creating view.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by yhuai <gi...@git.apache.org>.
Github user yhuai commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-197114133
@cloud-fan It will be super helpful if we can have an example in the description as well as in the code. I feel this kind of changes is hard to fully understand without good examples. Thanks!
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by yhuai <gi...@git.apache.org>.
Github user yhuai commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r56272551
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -354,55 +381,56 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
// +- Filter ...
// +- Aggregate ...
// +- MetastoreRelation default, src, None
- case plan @ Project(_, Filter(_, _: Aggregate)) => wrapChildWithSubquery(plan)
+ case p @ Project(_, f @ Filter(_, _: Aggregate)) => p.copy(child = addSubquery(f))
- case w @ Window(_, _, _, Filter(_, _: Aggregate)) => wrapChildWithSubquery(w)
+ case w @ Window(_, _, _, f @ Filter(_, _: Aggregate)) => w.copy(child = addSubquery(f))
- case plan @ Project(_,
- _: SubqueryAlias
- | _: Filter
- | _: Join
- | _: MetastoreRelation
- | OneRowRelation
- | _: LocalLimit
- | _: GlobalLimit
- | _: Sample
- ) => plan
-
- case plan: Project => wrapChildWithSubquery(plan)
+ case p: Project => p.copy(child = addSubqueryIfNeeded(p.child))
// We will generate "SELECT ... FROM ..." for Window operator, so its child operator should
// be able to put in the FROM clause, or we wrap it with a subquery.
- case w @ Window(_, _, _,
- _: SubqueryAlias
- | _: Filter
- | _: Join
- | _: MetastoreRelation
- | OneRowRelation
- | _: LocalLimit
- | _: GlobalLimit
- | _: Sample
- ) => w
-
- case w: Window => wrapChildWithSubquery(w)
- }
+ case w: Window => w.copy(child = addSubqueryIfNeeded(w.child))
- private def wrapChildWithSubquery(plan: UnaryNode): LogicalPlan = {
- val newChild = SubqueryAlias(SQLBuilder.newSubqueryName, plan.child)
- plan.withNewChildren(Seq(newChild))
+ case j: Join => j.copy(
+ left = addSubqueryIfNeeded(j.left),
+ right = addSubqueryIfNeeded(j.right))
}
}
- object UpdateQualifiers extends Rule[LogicalPlan] {
- override def apply(tree: LogicalPlan): LogicalPlan = tree transformUp {
- case plan =>
- val inputAttributes = plan.children.flatMap(_.output)
- plan transformExpressions {
- case a: AttributeReference if !plan.producedAttributes.contains(a) =>
- val qualifier = inputAttributes.find(_ semanticEquals a).map(_.qualifiers)
- a.withQualifiers(qualifier.getOrElse(Nil))
- }
- }
+ private def addSubquery(plan: LogicalPlan): SubqueryAlias = {
+ SubqueryAlias(SQLBuilder.newSubqueryName, plan)
+ }
+
+ private def addSubqueryIfNeeded(plan: LogicalPlan): LogicalPlan = plan match {
+ case _: SubqueryAlias => plan
+ case _: Filter => plan
+ case _: Join => plan
+ case _: LocalLimit => plan
+ case _: GlobalLimit => plan
+ case _: SQLTable => plan
+ case OneRowRelation => plan
--- End diff --
Why we do not need to add a subquery for these kinds of nodes?
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196190307
**[Test build #53058 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/53058/consoleFull)** for PR 11658 at commit [`5b12aa0`](https://github.com/apache/spark/commit/5b12aa08add3cf97c938ad6d7dd7de8e0118e55a).
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by AmplabJenkins <gi...@git.apache.org>.
Github user AmplabJenkins commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196731814
Test PASSed.
Refer to this link for build results (access rights to CI server needed):
https://amplab.cs.berkeley.edu/jenkins//job/SparkPullRequestBuilder/53172/
Test PASSed.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195645853
**[Test build #52987 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/52987/consoleFull)** for PR 11658 at commit [`ade17d8`](https://github.com/apache/spark/commit/ade17d8b6c1cd31a961ac57597bc3a3ba91711ba).
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195672850
```SQL
SELECT x.key, x.value, y.a, y.b, y.c, y.d FROM parquet_t1 x JOIN parquet_t2 y ON x.key = y.a
```
The generated SQL is
```SQL
SELECT `gen_attr_30` AS `key`, `gen_attr_31` AS `value`, `gen_attr_32` AS `a`, `gen_attr_33` AS `b`, `gen_attr_34` AS `c`, `gen_attr_35` AS `d` FROM (SELECT `gen_attr_30`, `gen_attr_31`, `gen_attr_32`, `gen_attr_33`, `gen_attr_34`, `gen_attr_35` FROM (SELECT `key` AS `gen_attr_30`, `value` AS `gen_attr_31` FROM `default`.`parquet_t1`) AS gen_subquery_0 INNER JOIN (SELECT `a` AS `gen_attr_32`, `b` AS `gen_attr_33`, `c` AS `gen_attr_34`, `d` AS `gen_attr_35` FROM `default`.`parquet_t2`) AS gen_subquery_1 ON (`gen_attr_30` = `gen_attr_32`)) AS gen_subquery_2
```
I compared the Optimized Logical Plan of these two queries:
```
Join Inner, Some((key#30L = a#32L))
:- Filter isnotnull(key#30L)
: +- Relation[key#30L,value#31] ParquetFormat part: struct<>, data: struct<key:bigint,value:string>
+- Filter isnotnull(a#32L)
+- Relation[a#32L,b#33L,c#34L,d#35L] ParquetFormat part: struct<>, data: struct<a:bigint,b:bigint,c:bigint,d:bigint>
```
```
Project [gen_attr_30#71L AS key#77L,gen_attr_31#72 AS value#78,gen_attr_32#73L AS a#79L,gen_attr_33#74L AS b#80L,gen_attr_34#75L AS c#81L,gen_attr_35#76L AS d#82L]
+- Join Inner, Some((gen_attr_30#71L = gen_attr_32#73L))
:- Project [key#30L AS gen_attr_30#71L,value#31 AS gen_attr_31#72]
: +- Filter isnotnull(key#30L)
: +- Relation[key#30L,value#31] ParquetFormat part: struct<>, data: struct<key:bigint,value:string>
+- Project [a#32L AS gen_attr_32#73L,b#33L AS gen_attr_33#74L,c#34L AS gen_attr_34#75L,d#35L AS gen_attr_35#76L]
+- Filter isnotnull(a#32L)
+- Relation[a#32L,b#33L,c#34L,d#35L] ParquetFormat part: struct<>, data: struct<a:bigint,b:bigint,c:bigint,d:bigint>
```
Here, we always add extra `Project`s in SQL generation. I am just thinking if we need to do it even if no name ambiguity exists?
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by yhuai <gi...@git.apache.org>.
Github user yhuai commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-197489369
Thank you for the example. That's super helpful. We should also put that in the code. Changes look good. @liancheng It will be good if you can take a look at it later.
I am merging this to master. We can put this example in the comment while working on another PR related to view support.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by cloud-fan <gi...@git.apache.org>.
Github user cloud-fan commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196693333
retest this please.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196939804
**[Test build #53203 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/53203/consoleFull)** for PR 11658 at commit [`5ef9fd4`](https://github.com/apache/spark/commit/5ef9fd4956eb76d7d6988e445fbd6ae61a1d0481).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds the following public classes _(experimental)_:
* ` case class SQLTable(`
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by gatorsmile <gi...@git.apache.org>.
Github user gatorsmile commented on a diff in the pull request:
https://github.com/apache/spark/pull/11658#discussion_r55916941
--- Diff: sql/hive/src/main/scala/org/apache/spark/sql/hive/SQLBuilder.scala ---
@@ -54,8 +55,26 @@ class SQLBuilder(logicalPlan: LogicalPlan, sqlContext: SQLContext) extends Loggi
def toSQL: String = {
val canonicalizedPlan = Canonicalizer.execute(logicalPlan)
+ val outputNames = logicalPlan.output.map(_.name)
+ val qualifiers = logicalPlan.output.flatMap(_.qualifiers).distinct
+
+ // Keep the qualifier information by using it as sub-query name, if there is only one qualifier
+ // present.
+ val finalName = if (qualifiers.isEmpty || qualifiers.length > 1) {
+ SQLBuilder.newSubqueryName
+ } else {
+ qualifiers.head
+ }
+
+ // Canonicalizer will remove all naming information, we should add it back by adding an extra
+ // Project and alias the outputs.
+ val aliasedOutput = canonicalizedPlan.output.zip(outputNames).map {
+ case (attr, name) => Alias(attr.withQualifiers(Nil), name)()
+ }
+ val finalPlan = Project(aliasedOutput, SubqueryAlias(finalName, canonicalizedPlan))
--- End diff --
nvm, this should not be an issue. Name ambiguity should not exist when creating a view.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-196728124
**[Test build #53182 has started](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/53182/consoleFull)** for PR 11658 at commit [`8de6365`](https://github.com/apache/spark/commit/8de6365a70c9830f4e3a70a28debdd100093c539).
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org
[GitHub] spark pull request: SPARK-13827[SQL] Can't add subquery to an oper...
Posted by SparkQA <gi...@git.apache.org>.
Github user SparkQA commented on the pull request:
https://github.com/apache/spark/pull/11658#issuecomment-195660417
**[Test build #52987 has finished](https://amplab.cs.berkeley.edu/jenkins/job/SparkPullRequestBuilder/52987/consoleFull)** for PR 11658 at commit [`ade17d8`](https://github.com/apache/spark/commit/ade17d8b6c1cd31a961ac57597bc3a3ba91711ba).
* This patch passes all tests.
* This patch merges cleanly.
* This patch adds no public classes.
---
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.
---
---------------------------------------------------------------------
To unsubscribe, e-mail: reviews-unsubscribe@spark.apache.org
For additional commands, e-mail: reviews-help@spark.apache.org