[jira] [Commented] (TRAFODION-2047) add SQL syntax support to INTERSECT

[ASF GitHub Bot (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2047?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15404779#comment-15404779
 ] 

ASF GitHub Bot commented on TRAFODION-2047:
---

Github user asfgit closed the pull request at:

https://github.com/apache/incubator-trafodion/pull/623


> 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

[ASF GitHub Bot (JIRA)]

[ 
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

[ASF GitHub Bot (JIRA)]

[ 
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

[ASF GitHub Bot (JIRA)]

[ 
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-2047) add SQL syntax support to INTERSECT

[ASF GitHub Bot (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2047?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15398074#comment-15398074
 ] 

ASF GitHub Bot commented on TRAFODION-2047:
---

Github user zellerh commented on a diff in the pull request:

https://github.com/apache/incubator-trafodion/pull/623#discussion_r72685632
  
--- 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 --

My hope is that hash and merge join are carefully implemented to make this 
type of equals predicate with the "special nulls" semantics work as an 
equi-join predicate. If we hash/sort NULL values as regular values, and 
preserve the "special nulls" semantics for the actual comparison that's done in 
the hash/merge join, it might work just fine, and hopefully your example proves 
that this is indeed how it's implemented. The easiest way to check this is 
probably an EXPLAIN and a SHOWPLAN. If you want to send me this info for the 
example you added to the regression test (the one that includes a NULL value), 
I can take a look.


> 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

[ASF GitHub Bot (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2047?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15398060#comment-15398060
 ] 

ASF GitHub Bot commented on TRAFODION-2047:
---

Github user zellerh commented on a diff in the pull request:

https://github.com/apache/incubator-trafodion/pull/623#discussion_r72684626
  
--- 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 --

Here are two things I wonder: First, would it make sense to replace the 
TRUE here with "leftExpr->getValueId().getType().supportsSQLnull() && 
rightExpr->getValueId().getType().supportsSQLnull()". I'm not sure we have 
optimizations elsewhere that set the "special nulls" semantics back to FALSE if 
one of the operands it not nullable.

Second, I wonder whether we have bugs elsewhere in the code that may not 
check for the "special nulls" semantics. For example, hash and merge joins 
probably can't use such predicates as equi-join predicates - one of the reasons 
for the optimization above. When I look at method 
ItemExpr::isAnEquiJoinPredicate() in core/sql/optimizer/OptItemExpr.cpp, I 
don't see a check for that. You have an example with a NULL value that is 
working, so hopefully this is not an issue, but I'm not sure how and why it 
works.


> 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

[ASF GitHub Bot (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2047?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15397842#comment-15397842
 ] 

ASF GitHub Bot commented on TRAFODION-2047:
---

GitHub user traflm opened a pull request:

https://github.com/apache/incubator-trafodion/pull/623

[TRAFODION-2047] add support of SQL syntax INTERSECT

support of SQL syntax INTERSECT, INTERSECT DISTINCT
not able to support INTERSECT ALL, which will raise an error.

TPCDS quries only need INTERSECT, and the semantics is same, while two 
child input of INTERSECT must have same number of columns and in same order and 
each column type must be matching.

Also a minor change to TM Makefile to include log4cxx header file path, if 
log4cxx is not installed into system include path.

You can merge this pull request into a Git repository by running:

$ git pull https://github.com/traflm/incubator-trafodion TRAFODION-2047-1

Alternatively you can review and apply these changes as the patch at:

https://github.com/apache/incubator-trafodion/pull/623.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 #623


commit 23fe6d5a6c5c9832bf85bb2348bba5d75f91c8ea
Author: Liu Ming 
Date:   2016-07-28T16:58:20Z

[TRAFODION-2047] add support of SQL syntax INTERSECT




> 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

[liu ming (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2047?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15393007#comment-15393007
 ] 

liu ming commented on TRAFODION-2047:
-

As Hans correctly pointed out, it is hard to support 'INTERSECT ALL' at 
present, so this JIRA will only add support for 'INTERSECT' or 'INTERSECT 
DISTINCT', and raise syntax error for 'INTERSECT ALL'
A new JIRA will be created to cover 'INTERSECT ALL' later.

> 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

[liu ming (JIRA)]

[ 
https://issues.apache.org/jira/browse/TRAFODION-2047?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15323913#comment-15323913
 ] 

liu ming commented on TRAFODION-2047:
-

Most of INTERSECT code is already in Trafodion, but not enabled.
The current logic is to transform the INTERSECT into JOIN and remove the output 
from left table, which I believe is same as SEMI JOIN.

So I propose to change the JOIN into SEMI_JOIN and enable the support of 
INTERSECT.

I think all changes are in Binder.

> 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
>
> INTERSECT is a common SQL syntax. 
> Trafodion should support it.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)