[jira] [Created] (TRAFODION-2136) union all can't work efficiently
Joshua Liu created TRAFODION-2136:
-
Summary: union all can't work efficiently
Key: TRAFODION-2136
URL: https://issues.apache.org/jira/browse/TRAFODION-2136
Project: Apache Trafodion
Issue Type: Bug
Components: sql-exe
Affects Versions: any
Reporter: Joshua Liu
there is one query called a
the first time we run
a;
and get the elapsed time time1
reconnect the database;
the second time we run
a union all a;
and get another elapsed time.time2
here time2 ≈ time1*2
-
see below for detailed info
a is
select count(*)
from F_INCOME_DAY_RH a left join D_IN_SORTCODE_NEW b
on (A.INCOMESORTCODE_N = B.CODE1)
where (((a.BBQ >= date '2014-01-01') AND (a.BBQ <= date '2014-12-31')) AND
((a.BUDGETLEVEL = '3') AND
((a.TAXORGCODE in ('1', '2', '3', '4', '5'))) AND
(a.DISTRICT_CO_CODE like 'A%' or
a.DISTRICT_CO_CODE like 'B%')) AND
((a.INCOMESORTCODE_N LIKE 'A%') OR
(a.INCOMESORTCODE_N LIKE 'B%') OR
(a.INCOMESORTCODE_N LIKE 'C%') OR
(a.INCOMESORTCODE_N LIKE 'F%') OR
(a.INCOMESORTCODE_N LIKE 'G%')))
group by b.CODE5
-
SQL>explain options 'f' s1;
LC RC OP OPERATOR OPT DESCRIPTION CARD
-
7.8root 6.00E+000
6.7hash_partial_groupby 6.00E+000
5.6esp_exchange1:16(hash2) 6.00E+000
4.5hash_partial_groupby 6.00E+000
324left_hybrid_hash_joi 1.06E+006
..3trafodion_scan F_INCOME_DAY_RH 1.06E+006
1.2esp_exchange16(rep-b):1 (m) 7.24E+002
..1trafodion_scan D_IN_SORTCODE_NEW 7.24E+002
--- SQL operation complete.
SQL>set statistics on;
SQL>execute s1;
(EXPR)
977626
16080
34817
53722
5573
--- 5 row(s) selected.
Start Time 2016/07/30 09:47:50.913298
End Time 2016/07/30 09:48:05.018125
Elapsed Time 00:00:14.104827
Compile Time 00:00:05.641437
Execution Time00:00:14.104827
---
reconnect database and run a union all a;
SQL>explain options 'f' s1;
LC RC OP OPERATOR OPT DESCRIPTION CARD
-
15 .16 root 1.20E+001
714 15 merge_union 1.20E+001
13 .14 hash_partial_groupby 6.00E+000
12 .13 esp_exchange1:16(hash2) 6.00E+000
11 .12 hash_partial_groupby 6.00E+000
10 911 left_hybrid_hash_joi 1.06E+006
..10 trafodion_scan F_INCOME_DAY_RH 1.06E+006
8.9esp_exchange16(rep-b):1 (m) 7.24E+002
..8trafodion_scan D_IN_SORTCODE_NEW 7.24E+002
6.7hash_partial_groupby 6.00E+000
5.6esp_exchange1:16(hash2) 6.00E+000
4.5hash_partial_groupby 6.00E+000
324left_hybrid_hash_joi 1.06E+006
..3trafodion_scan F_INCOME_DAY_RH 1.06E+006
1.2esp_exchange16(rep-b):1 (m) 7.24E+002
..1trafodion_scan D_IN_SORTCODE_NEW 7.24E+002
--- SQL operation complete.
Start Time 2016/07/30 09:48:28.230061
End Time 2016/07/30 09:48:45.506429
Elapsed Time 00:00:17.276368
Compile Time 00:00:17.276368
Execution Time00:00:00.00
SQL>execute s1;
(EXPR)
977626
16080
34817
53722
5573
977626
16080
34817
53722
5573
--- 10 row(s) selected.
Start Time 2016/07/30 09:50:39.754195
End Time 2016/07/30 09:51:10.680951
Elapsed Time 00:00:30.926756
Compile Time 00:00:17.276368
Execution Time00:00:30.926756
---
[jira] [Commented] (TRAFODION-2134) Change the overflow_mode to 'DISK' by default
[ https://issues.apache.org/jira/browse/TRAFODION-2134?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15400310#comment-15400310 ] ASF GitHub Bot commented on TRAFODION-2134: --- Github user asfgit closed the pull request at: https://github.com/apache/incubator-trafodion/pull/628 > Change the overflow_mode to 'DISK' by default > - > > Key: TRAFODION-2134 > URL: https://issues.apache.org/jira/browse/TRAFODION-2134 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-exe >Affects Versions: 2.1-incubating >Reporter: Selvaganesan Govindarajan >Assignee: Selvaganesan Govindarajan > Fix For: 2.1-incubating > > Original Estimate: 24h > Remaining Estimate: 24h > > In queries involving Block Memory Operators (BMOs), Trafodion can overflow > the data from the data flow into scratch files. These scratch files are > currently being memory mapped which puts pressure on the memory usage on the > node when there is large amount of overflow. The default needs to be changed > to DISK -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-2129) Trafodion to avoid use of deprecated HBase APIs/Classes
[
https://issues.apache.org/jira/browse/TRAFODION-2129?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15400174#comment-15400174
]
ASF GitHub Bot commented on TRAFODION-2129:
---
Github user selvaganesang commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/627#discussion_r72871851
--- Diff:
core/sqf/src/seatrans/hbase-trx/src/main/java/org/apache/hadoop/hbase/client/transactional/TransactionalTable.java
---
@@ -99,7 +98,7 @@
*/
public class TransactionalTable extends HTable implements
TransactionalTableClient {
static final Log LOG = LogFactory.getLog(RMInterface.class);
-static private HConnection connection = null;
+static private Connection connection = null;
static Configuration config = HBaseConfiguration.create();
static ExecutorService threadPool;
--- End diff --
Tried. But, It looks like there is no HTable constructor that takes byte[]
and Connection.
> Trafodion to avoid use of deprecated HBase APIs/Classes
> ---
>
> Key: TRAFODION-2129
> URL: https://issues.apache.org/jira/browse/TRAFODION-2129
> Project: Apache Trafodion
> Issue Type: Improvement
> Components: dtm, sql-exe
>Affects Versions: 2.0-incubating
>Reporter: Selvaganesan Govindarajan
>Assignee: Selvaganesan Govindarajan
> Original Estimate: 168h
> Remaining Estimate: 168h
>
> Avoid the use of HConnection, HConnectionManager and, HBaseAdmin in Trafodion
> and use Connection, ConnectionFactory and Admin respectively.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-2129) Trafodion to avoid use of deprecated HBase APIs/Classes
[
https://issues.apache.org/jira/browse/TRAFODION-2129?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15400169#comment-15400169
]
ASF GitHub Bot commented on TRAFODION-2129:
---
Github user selvaganesang commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/627#discussion_r72871514
--- Diff:
core/sqf/src/seatrans/hbase-trx/src/main/java/org/apache/hadoop/hbase/client/transactional/SsccTransactionalTable.java
---
@@ -130,12 +129,6 @@
static {
config.set("hbase.hregion.impl",
"org.apache.hadoop.hbase.regionserver.transactional.TransactionalRegion");
--- End diff --
Done.
> Trafodion to avoid use of deprecated HBase APIs/Classes
> ---
>
> Key: TRAFODION-2129
> URL: https://issues.apache.org/jira/browse/TRAFODION-2129
> Project: Apache Trafodion
> Issue Type: Improvement
> Components: dtm, sql-exe
>Affects Versions: 2.0-incubating
>Reporter: Selvaganesan Govindarajan
>Assignee: Selvaganesan Govindarajan
> Original Estimate: 168h
> Remaining Estimate: 168h
>
> Avoid the use of HConnection, HConnectionManager and, HBaseAdmin in Trafodion
> and use Connection, ConnectionFactory and Admin respectively.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Resolved] (TRAFODION-2131) UPDATE STATS cores, buffer overrun, on TINYINT and small INTERVAL cols
[ https://issues.apache.org/jira/browse/TRAFODION-2131?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] David Wayne Birdsall resolved TRAFODION-2131. - Resolution: Fixed Fix Version/s: 2.1-incubating > UPDATE STATS cores, buffer overrun, on TINYINT and small INTERVAL cols > -- > > Key: TRAFODION-2131 > URL: https://issues.apache.org/jira/browse/TRAFODION-2131 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp >Affects Versions: 2.1-incubating > Environment: All >Reporter: David Wayne Birdsall >Assignee: David Wayne Birdsall > Fix For: 2.1-incubating > > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-2130) Incorrect subquery transformation for tables w/o key
[
https://issues.apache.org/jira/browse/TRAFODION-2130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15400040#comment-15400040
]
ASF GitHub Bot commented on TRAFODION-2130:
---
Github user DaveBirdsall commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/624#discussion_r72864739
--- Diff: core/sql/optimizer/NormRelExpr.cpp ---
@@ -3044,14 +3044,23 @@ Join::pullUpGroupByTransformation()
// a copy of join as the child. The original tree has not
changed.
// The predicates in the new groupBy and the new Join will have
// changed according to the comments above.
+//
--*/
GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
{
CollHeap *stmtHeap = CmpCommon::statementHeap() ;
- // Determine a set of unique columns for the left sub-tree
+ // Determine a set of unique columns for the left sub-tree.
+
+ // Note: Scans and joins synthesize uniqueness constraints even for
+ // columns that are not in the characteristic outputs. Other
+ // operators such as groupby or union don't. We make use of these
+ // extra uniqeness constraints here. Any needed columns not yet
+ // added to the characteristic outputs will be added later, in
+ // method getMoreOutputsIfPossible().
+
ValueIdSet leftUniqueCols ;
- if (NOT (child(0)-getGroupAttr()->findUniqueCols(leftUniqueCols)))
+ if (NOT (child(0)->getGroupAttr()->findUniqueCols(leftUniqueCols)))
--- End diff --
In answer to @selvaganesang, it called
getGroupAttr()->findUniqueCols(leftUniqueCols) on "this", and subtracted that
results (an NABoolean?) from the pointer child(0). That would almost always be
non-zero. So the NOT would render it zero, and the "if" would not be taken.
> Incorrect subquery transformation for tables w/o key
>
>
> Key: TRAFODION-2130
> URL: https://issues.apache.org/jira/browse/TRAFODION-2130
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.2-incubating
>Reporter: Hans Zeller
>Assignee: Hans Zeller
>
> Some of the subquery unnesting transformations rely on a unique key on the
> result of the main query. It turns out, however, that the transformation
> happens even if there is no unique key. Example:
> select *
> from (values (1,1), (1,1), (2,2)) T(a,b)
> where t.a in (select max(a)
> from (values (1,1), (1,1), (3,3)) S(a,b)
> where S.b = T.b);
> This should return two rows, but it actually just returns one, because it
> does not include a unique key for the main query, T.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-2130) Incorrect subquery transformation for tables w/o key
[
https://issues.apache.org/jira/browse/TRAFODION-2130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399662#comment-15399662
]
ASF GitHub Bot commented on TRAFODION-2130:
---
Github user sureshsubbiah commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/624#discussion_r72824380
--- Diff: core/sql/optimizer/NormRelExpr.cpp ---
@@ -3062,6 +3071,35 @@ GroupByAgg*
Join::pullUpGroupByTransformation(NormWA& normWARef)
if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
*CmpCommon::diags() << DgSqlCode(2997)
<< DgString1("Subquery was not unnested. Reason: Left child does not
have a unique constraint");
+
+// Things to consider (referring to the picture above): If the all of
the
+// following are true:
+// * {pred2} has only equals/VEG predicates of the form X.col = Y.col
+// * {aggr} does not have any outer references
+// * {pred3} does not have any outer references
--- End diff --
Lack of outer references in {aggr} and {pred3} may indicate that this
transformation may not apply when we more than one 1 level of correlated
subqueries. This set of conditions clearly explains when this transformation
can be applied. I hope we can add the change needed for this soon. The
description here also indicates that NULL preserving semantics is not affected
by this new transformation. So we could use this change for OR subqueries where
the Join could be a Left Join.
> Incorrect subquery transformation for tables w/o key
>
>
> Key: TRAFODION-2130
> URL: https://issues.apache.org/jira/browse/TRAFODION-2130
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.2-incubating
>Reporter: Hans Zeller
>Assignee: Hans Zeller
>
> Some of the subquery unnesting transformations rely on a unique key on the
> result of the main query. It turns out, however, that the transformation
> happens even if there is no unique key. Example:
> select *
> from (values (1,1), (1,1), (2,2)) T(a,b)
> where t.a in (select max(a)
> from (values (1,1), (1,1), (3,3)) S(a,b)
> where S.b = T.b);
> This should return two rows, but it actually just returns one, because it
> does not include a unique key for the main query, T.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-2130) Incorrect subquery transformation for tables w/o key
[
https://issues.apache.org/jira/browse/TRAFODION-2130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399656#comment-15399656
]
ASF GitHub Bot commented on TRAFODION-2130:
---
Github user sureshsubbiah commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/624#discussion_r72823645
--- Diff: core/sql/optimizer/NormRelExpr.cpp ---
@@ -3062,6 +3071,35 @@ GroupByAgg*
Join::pullUpGroupByTransformation(NormWA& normWARef)
if (CmpCommon::getDefault(SUBQUERY_UNNESTING) == DF_DEBUG)
*CmpCommon::diags() << DgSqlCode(2997)
<< DgString1("Subquery was not unnested. Reason: Left child does not
have a unique constraint");
+
+// Things to consider (referring to the picture above): If the all of
the
+// following are true:
+// * {pred2} has only equals/VEG predicates of the form X.col = Y.col
+// * {aggr} does not have any outer references
+// * {pred3} does not have any outer references
+//
+// then we could do an alternative transformation, not yet implemented:
+//
+// TSJ Join {pred2: X.a=Y.b,
...}
+// / \ / \
+/// \/ \
+// X ScalarAgg {pred3} --> X grby {Y.b, ...}
{pred3}
+// | {aggr} \ {aggr}
+// | \
+// Filter {pred2: X.a=Y.b, ...} Y {pred1}
+// |
+// |
+// Y {pred1}
+//
+// Pros: - The groupby is already at a place where it will likely
+// end up in the optimal plan
+// Cons: - We don't get a nice join backbone with all base tables
+//
+// Cases where we could attempt this transformation:
+// - We fail to find a unique key for X (i.e. we reach here)
+// - pred2 has a very high selectivity, making newJoin (in the
picture
--- End diff --
If pred2 is any thing other than an equality predicate, or an equality
predicate involving complex expressions, then the join effectively becomes a
cartesian product. In such cases doing the groupby early will help reduce
dataflow.
> Incorrect subquery transformation for tables w/o key
>
>
> Key: TRAFODION-2130
> URL: https://issues.apache.org/jira/browse/TRAFODION-2130
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.2-incubating
>Reporter: Hans Zeller
>Assignee: Hans Zeller
>
> Some of the subquery unnesting transformations rely on a unique key on the
> result of the main query. It turns out, however, that the transformation
> happens even if there is no unique key. Example:
> select *
> from (values (1,1), (1,1), (2,2)) T(a,b)
> where t.a in (select max(a)
> from (values (1,1), (1,1), (3,3)) S(a,b)
> where S.b = T.b);
> This should return two rows, but it actually just returns one, because it
> does not include a unique key for the main query, T.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-2130) Incorrect subquery transformation for tables w/o key
[
https://issues.apache.org/jira/browse/TRAFODION-2130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399649#comment-15399649
]
ASF GitHub Bot commented on TRAFODION-2130:
---
Github user sureshsubbiah commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/624#discussion_r72823287
--- Diff: core/sql/optimizer/NormRelExpr.cpp ---
@@ -3044,14 +3044,23 @@ Join::pullUpGroupByTransformation()
// a copy of join as the child. The original tree has not
changed.
// The predicates in the new groupBy and the new Join will have
// changed according to the comments above.
+//
--*/
GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
{
CollHeap *stmtHeap = CmpCommon::statementHeap() ;
- // Determine a set of unique columns for the left sub-tree
+ // Determine a set of unique columns for the left sub-tree.
+
+ // Note: Scans and joins synthesize uniqueness constraints even for
+ // columns that are not in the characteristic outputs. Other
+ // operators such as groupby or union don't. We make use of these
+ // extra uniqeness constraints here. Any needed columns not yet
+ // added to the characteristic outputs will be added later, in
+ // method getMoreOutputsIfPossible().
+
ValueIdSet leftUniqueCols ;
- if (NOT (child(0)-getGroupAttr()->findUniqueCols(leftUniqueCols)))
+ if (NOT (child(0)->getGroupAttr()->findUniqueCols(leftUniqueCols)))
--- End diff --
Good catch Hans. Amazing that this has remained undetected for about 9
years!
> Incorrect subquery transformation for tables w/o key
>
>
> Key: TRAFODION-2130
> URL: https://issues.apache.org/jira/browse/TRAFODION-2130
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.2-incubating
>Reporter: Hans Zeller
>Assignee: Hans Zeller
>
> Some of the subquery unnesting transformations rely on a unique key on the
> result of the main query. It turns out, however, that the transformation
> happens even if there is no unique key. Example:
> select *
> from (values (1,1), (1,1), (2,2)) T(a,b)
> where t.a in (select max(a)
> from (values (1,1), (1,1), (3,3)) S(a,b)
> where S.b = T.b);
> This should return two rows, but it actually just returns one, because it
> does not include a unique key for the main query, T.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Resolved] (TRAFODION-1874) find a new way to check CLUSTER environment instead of checking pdsh
[ https://issues.apache.org/jira/browse/TRAFODION-1874?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] liu ming resolved TRAFODION-1874. - Resolution: Fixed Fix Version/s: 2.1-incubating > find a new way to check CLUSTER environment instead of checking pdsh > > > Key: TRAFODION-1874 > URL: https://issues.apache.org/jira/browse/TRAFODION-1874 > Project: Apache Trafodion > Issue Type: Improvement > Components: foundation, installer >Reporter: liu ming >Assignee: liu ming > Fix For: 2.1-incubating > > > Some Trafodion script need to tell if it is running in a dev workstation or a > real cluster, and behave differently. > Current, some script tell this difference by checking the rpm package pdsh, > if it is installed, then the script think it is running on a cluster. > PDSH is a public package, and can be installed in a workstation as well. So > it is better to find a new testing logic for this purpose. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-1874) find a new way to check CLUSTER environment instead of checking pdsh
[ https://issues.apache.org/jira/browse/TRAFODION-1874?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399570#comment-15399570 ] liu ming commented on TRAFODION-1874: - I cannot find scripts checking PDSH for cluster now. So close this JIRA. > find a new way to check CLUSTER environment instead of checking pdsh > > > Key: TRAFODION-1874 > URL: https://issues.apache.org/jira/browse/TRAFODION-1874 > Project: Apache Trafodion > Issue Type: Improvement > Components: foundation, installer >Reporter: liu ming >Assignee: liu ming > > Some Trafodion script need to tell if it is running in a dev workstation or a > real cluster, and behave differently. > Current, some script tell this difference by checking the rpm package pdsh, > if it is installed, then the script think it is running on a cluster. > PDSH is a public package, and can be installed in a workstation as well. So > it is better to find a new testing logic for this purpose. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-2130) Incorrect subquery transformation for tables w/o key
[
https://issues.apache.org/jira/browse/TRAFODION-2130?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399410#comment-15399410
]
ASF GitHub Bot commented on TRAFODION-2130:
---
Github user selvaganesang commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/624#discussion_r72800580
--- Diff: core/sql/optimizer/NormRelExpr.cpp ---
@@ -3044,14 +3044,23 @@ Join::pullUpGroupByTransformation()
// a copy of join as the child. The original tree has not
changed.
// The predicates in the new groupBy and the new Join will have
// changed according to the comments above.
+//
--*/
GroupByAgg* Join::pullUpGroupByTransformation(NormWA& normWARef)
{
CollHeap *stmtHeap = CmpCommon::statementHeap() ;
- // Determine a set of unique columns for the left sub-tree
+ // Determine a set of unique columns for the left sub-tree.
+
+ // Note: Scans and joins synthesize uniqueness constraints even for
+ // columns that are not in the characteristic outputs. Other
+ // operators such as groupby or union don't. We make use of these
+ // extra uniqeness constraints here. Any needed columns not yet
+ // added to the characteristic outputs will be added later, in
+ // method getMoreOutputsIfPossible().
+
ValueIdSet leftUniqueCols ;
- if (NOT (child(0)-getGroupAttr()->findUniqueCols(leftUniqueCols)))
+ if (NOT (child(0)->getGroupAttr()->findUniqueCols(leftUniqueCols)))
--- End diff --
Interesting. Just curious what did the C++ compiler do earlier?
> Incorrect subquery transformation for tables w/o key
>
>
> Key: TRAFODION-2130
> URL: https://issues.apache.org/jira/browse/TRAFODION-2130
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
>Affects Versions: 1.2-incubating
>Reporter: Hans Zeller
>Assignee: Hans Zeller
>
> Some of the subquery unnesting transformations rely on a unique key on the
> result of the main query. It turns out, however, that the transformation
> happens even if there is no unique key. Example:
> select *
> from (values (1,1), (1,1), (2,2)) T(a,b)
> where t.a in (select max(a)
> from (values (1,1), (1,1), (3,3)) S(a,b)
> where S.b = T.b);
> This should return two rows, but it actually just returns one, because it
> does not include a unique key for the main query, T.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-2047) add SQL syntax support to INTERSECT
[ https://issues.apache.org/jira/browse/TRAFODION-2047?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399391#comment-15399391 ] ASF GitHub Bot commented on TRAFODION-2047: --- Github user traflm commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/623#discussion_r72797920 --- Diff: core/sql/optimizer/BindRelExpr.cpp --- @@ -717,7 +717,7 @@ static ItemExpr *intersectColumns(const RETDesc , ItemExpr *leftExpr = leftTable.getValueId(i).getItemExpr(); ItemExpr *rightExpr = rightTable.getValueId(i).getItemExpr(); BiRelat *compare = new (bindWA->wHeap()) - BiRelat(ITM_EQUAL, leftExpr, rightExpr); + BiRelat(ITM_EQUAL, leftExpr, rightExpr, TRUE); --- End diff -- I use CQD nested_joins 'off' and it generates a HASH JOIN plan, and the test still works for null value. I don't know why, the plan for HASH_JOIN has an option 'u', not sure what does that mean. > add SQL syntax support to INTERSECT > --- > > Key: TRAFODION-2047 > URL: https://issues.apache.org/jira/browse/TRAFODION-2047 > Project: Apache Trafodion > Issue Type: New Feature >Reporter: liu ming >Assignee: liu ming > Fix For: 2.1-incubating > > > INTERSECT is a common SQL syntax. > Trafodion should support it. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (TRAFODION-2047) add SQL syntax support to INTERSECT
[
https://issues.apache.org/jira/browse/TRAFODION-2047?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399376#comment-15399376
]
ASF GitHub Bot commented on TRAFODION-2047:
---
Github user traflm commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/623#discussion_r72796626
--- Diff: core/sql/optimizer/BindRelExpr.cpp ---
@@ -717,7 +717,7 @@ static ItemExpr *intersectColumns(const RETDesc
,
ItemExpr *leftExpr = leftTable.getValueId(i).getItemExpr();
ItemExpr *rightExpr = rightTable.getValueId(i).getItemExpr();
BiRelat *compare = new (bindWA->wHeap())
- BiRelat(ITM_EQUAL, leftExpr, rightExpr);
+ BiRelat(ITM_EQUAL, leftExpr, rightExpr, TRUE);
--- End diff --
the showplan output is too long. not sure if you can see it correctly...
Here is simpler version of explain, it is a nest loop join.
>>explain options 'f' select eno, ename, dno from t021
intersect
+>+>select eno, ename, dno from t022;
LC RC OP OPERATOR OPT DESCRIPTION CARD
-
4.5root
2.00E+000
234nested_join
2.00E+000
..3trafodion_vsbb_scan T021
1.00E+000
1.2hash_groupby
2.00E+000
..1trafodion_scan T022
2.50E+001
> add SQL syntax support to INTERSECT
> ---
>
> Key: TRAFODION-2047
> URL: https://issues.apache.org/jira/browse/TRAFODION-2047
> Project: Apache Trafodion
> Issue Type: New Feature
>Reporter: liu ming
>Assignee: liu ming
> Fix For: 2.1-incubating
>
>
> INTERSECT is a common SQL syntax.
> Trafodion should support it.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-2047) add SQL syntax support to INTERSECT
[ https://issues.apache.org/jira/browse/TRAFODION-2047?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399361#comment-15399361 ] ASF GitHub Bot commented on TRAFODION-2047: --- Github user traflm commented on a diff in the pull request: https://github.com/apache/incubator-trafodion/pull/623#discussion_r72794212 --- Diff: core/sql/optimizer/BindRelExpr.cpp --- @@ -717,7 +717,7 @@ static ItemExpr *intersectColumns(const RETDesc , ItemExpr *leftExpr = leftTable.getValueId(i).getItemExpr(); ItemExpr *rightExpr = rightTable.getValueId(i).getItemExpr(); BiRelat *compare = new (bindWA->wHeap()) - BiRelat(ITM_EQUAL, leftExpr, rightExpr); + BiRelat(ITM_EQUAL, leftExpr, rightExpr, TRUE); --- End diff -- EXPLAIN: >> explain select eno, ename, dno from t021 intersect +>+>select eno, ename, dno from t022; -- PLAN SUMMARY MODULE_NAME .. DYNAMICALLY COMPILED STATEMENT_NAME ... NOT NAMED PLAN_ID .. 212336560049570734 ROWS_OUT . 2 EST_TOTAL_COST ... 0.02 STATEMENT select eno, ename, dno from t021 intersect select eno, ename, dno from t022; -- NODE LISTING ROOT == SEQ_NO 5ONLY CHILD 4 REQUESTS_IN .. 1 ROWS_OUT . 2 EST_OPER_COST 0 EST_TOTAL_COST ... 0.02 DESCRIPTION max_card_est ... 2 fragment_id 0 parent_frag (none) fragment_type .. master statement_index 0 affinity_value . 0 max_max_cardinality100 total_overflow_size 0.00 KB xn_access_mode . read_only xn_autoabort_interval0 auto_query_retry ... enabled plan_version ... 2,600 embedded_arkcmp used IS_SQLCI ... ON LDAP_USERNAME MODE_SPECIAL_4 . ON GENERATE_EXPLAIN ... ON ObjectUIDs . 9084990775623580461, 9084990775623580331 select_list TRAFODION.SEABASE.T022.ENO, TRAFODION.SEABASE.T022.ENAME, TRAFODION.SEABASE.T022.DNO NESTED_JOIN === SEQ_NO 4CHILDREN 2, 3 REQUESTS_IN .. 1 ROWS_OUT . 2 EST_OPER_COST 0.01 EST_TOTAL_COST ... 0.02 DESCRIPTION max_card_est ... 2 fragment_id 0 parent_frag (none) fragment_type .. master join_type .. inner join_method nested TRAFODION_VSBB_SCAN === SEQ_NO 3NO CHILDREN TABLE_NAME ... T021 REQUESTS_IN .. 1 ROWS_OUT . 1 EST_OPER_COST 0.02 EST_TOTAL_COST ... 0.02 DESCRIPTION max_card_est ... 2 fragment_id 0 parent_frag (none) fragment_type .. master scan_type .. subset scan of table TRAFODION.SEABASE.T021 object_type Trafodion cache_size ... 100 probes . 2 successful_probes .. 2 unique_probes .. 2 rows_accessed .. 2 column_retrieved ... #1:1,#1:2,#1:3 key_columns ENO executor_predicates (ENAME = TRAFODION.SEABASE.T022.ENAME) and (DNO = TRAFODION.SEABASE.T022.DNO) begin_key .. (ENO = TRAFODION.SEABASE.T022.ENO) end_key (ENO = TRAFODION.SEABASE.T022.ENO) HASH_GROUPBY == SEQ_NO 2ONLY CHILD 1 REQUESTS_IN .. 1 ROWS_OUT . 2 EST_OPER_COST 0.01 EST_TOTAL_COST ... 0.01 DESCRIPTION memory_quota ... 1200 MB max_card_est ... 8 fragment_id 0 parent_frag (none) fragment_type .. master grouping_columns ... TRAFODION.SEABASE.T022.ENO, TRAFODION.SEABASE.T022.ENAME, TRAFODION.SEABASE.T022.DNO TRAFODION_SCAN SEQ_NO 1NO CHILDREN TABLE_NAME ... T022 REQUESTS_IN .. 1 ROWS_OUT 25 EST_OPER_COST 0.01 EST_TOTAL_COST ... 0.01
[jira] [Commented] (TRAFODION-2129) Trafodion to avoid use of deprecated HBase APIs/Classes
[
https://issues.apache.org/jira/browse/TRAFODION-2129?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399340#comment-15399340
]
ASF GitHub Bot commented on TRAFODION-2129:
---
Github user narendragoyal commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/627#discussion_r72790206
--- Diff:
core/sqf/src/seatrans/hbase-trx/src/main/java/org/apache/hadoop/hbase/client/transactional/TransactionalTable.java
---
@@ -99,7 +98,7 @@
*/
public class TransactionalTable extends HTable implements
TransactionalTableClient {
static final Log LOG = LogFactory.getLog(RMInterface.class);
-static private HConnection connection = null;
+static private Connection connection = null;
static Configuration config = HBaseConfiguration.create();
static ExecutorService threadPool;
--- End diff --
Please check - Dont think we need the threadPool object any more.
> Trafodion to avoid use of deprecated HBase APIs/Classes
> ---
>
> Key: TRAFODION-2129
> URL: https://issues.apache.org/jira/browse/TRAFODION-2129
> Project: Apache Trafodion
> Issue Type: Improvement
> Components: dtm, sql-exe
>Affects Versions: 2.0-incubating
>Reporter: Selvaganesan Govindarajan
>Assignee: Selvaganesan Govindarajan
> Original Estimate: 168h
> Remaining Estimate: 168h
>
> Avoid the use of HConnection, HConnectionManager and, HBaseAdmin in Trafodion
> and use Connection, ConnectionFactory and Admin respectively.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-2129) Trafodion to avoid use of deprecated HBase APIs/Classes
[
https://issues.apache.org/jira/browse/TRAFODION-2129?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399309#comment-15399309
]
ASF GitHub Bot commented on TRAFODION-2129:
---
Github user narendragoyal commented on a diff in the pull request:
https://github.com/apache/incubator-trafodion/pull/627#discussion_r72787731
--- Diff:
core/sqf/src/seatrans/hbase-trx/src/main/java/org/apache/hadoop/hbase/client/transactional/SsccTransactionalTable.java
---
@@ -130,12 +129,6 @@
static {
config.set("hbase.hregion.impl",
"org.apache.hadoop.hbase.regionserver.transactional.TransactionalRegion");
--- End diff --
Perhaps, this line is also not needed.
> Trafodion to avoid use of deprecated HBase APIs/Classes
> ---
>
> Key: TRAFODION-2129
> URL: https://issues.apache.org/jira/browse/TRAFODION-2129
> Project: Apache Trafodion
> Issue Type: Improvement
> Components: dtm, sql-exe
>Affects Versions: 2.0-incubating
>Reporter: Selvaganesan Govindarajan
>Assignee: Selvaganesan Govindarajan
> Original Estimate: 168h
> Remaining Estimate: 168h
>
> Avoid the use of HConnection, HConnectionManager and, HBaseAdmin in Trafodion
> and use Connection, ConnectionFactory and Admin respectively.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (TRAFODION-2133) support Hive native data type CHAR
[
https://issues.apache.org/jira/browse/TRAFODION-2133?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15399299#comment-15399299
]
liu ming commented on TRAFODION-2133:
-
This is a good JIRA candidate for beginners.
> support Hive native data type CHAR
> --
>
> Key: TRAFODION-2133
> URL: https://issues.apache.org/jira/browse/TRAFODION-2133
> Project: Apache Trafodion
> Issue Type: Sub-task
> Components: sql-cmp, sql-general
>Reporter: liu ming
> Labels: newbie
>
> This JIRA is not too complex, so a good beginner's JIRA.
> support CHAR data type from Hive DDL.
> create table tbl_char
> {
> c1 char(10),
> c2 char(20)
> };
> in hive, and insert some value, it should be able to read from Trafodion via
> sqlci:
> select * from hive.hive.tbl_char
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
