[jira] [Commented] (PHOENIX-3699) Test suite PhoenixSparkITTenantSpecific fails

[Sneha Kanekar (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3699?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15934136#comment-15934136
 ] 

Sneha Kanekar commented on PHOENIX-3699:


Any update on this?

> Test suite PhoenixSparkITTenantSpecific fails
> -
>
> Key: PHOENIX-3699
> URL: https://issues.apache.org/jira/browse/PHOENIX-3699
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0, 4.10.0
> Environment: Ubuntu: 14.04
>Reporter: Sneha Kanekar
>  Labels: ppc64le, x86
>
> In project Phoenix-Spark, the test suite PhoenixSparkITTenantSpecific fails 
> with a Run Aborted error. I have executed the test on both x86 as well as 
> ppc64le architechture and it fails on both of them.
> The error message is as follows:
> {code:borderStyle=solid}
> *** RUN ABORTED *** 
>   org.apache.phoenix.schema.TableAlreadyExistsException: ERROR 1013 (42M04): 
> Table already exists. tableName=TABLE1
>   at 
> org.apache.phoenix.schema.MetaDataClient.createTableInternal(MetaDataClient.java:2311)
>   at 
> org.apache.phoenix.schema.MetaDataClient.createTable(MetaDataClient.java:957)
>   at 
> org.apache.phoenix.compile.CreateTableCompiler$2.execute(CreateTableCompiler.java:211)
>   at 
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:358)
>   at 
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:341)
>   at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>   at 
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:340)
>   at 
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1511)
>   at 
> org.apache.phoenix.spark.AbstractPhoenixSparkIT$$anonfun$setupTables$1.apply(AbstractPhoenixSparkIT.scala:82)
>   at 
> org.apache.phoenix.spark.AbstractPhoenixSparkIT$$anonfun$setupTables$1.apply(AbstractPhoenixSparkIT.scala:80)
>   at scala.collection.Iterator$class.foreach(Iterator.scala:893)
>   at scala.collection.AbstractIterator.foreach(Iterator.scala:1336)
>   at 
> org.apache.phoenix.spark.AbstractPhoenixSparkIT.setupTables(AbstractPhoenixSparkIT.scala:80)
>   at 
> org.apache.phoenix.spark.AbstractPhoenixSparkIT.beforeAll(AbstractPhoenixSparkIT.scala:91)
>   at 
> org.scalatest.BeforeAndAfterAll$class.beforeAll(BeforeAndAfterAll.scala:187)
>   at 
> org.apache.phoenix.spark.AbstractPhoenixSparkIT.beforeAll(AbstractPhoenixSparkIT.scala:44)
>   at org.scalatest.BeforeAndAfterAll$class.run(BeforeAndAfterAll.scala:253)
>   at 
> org.apache.phoenix.spark.AbstractPhoenixSparkIT.run(AbstractPhoenixSparkIT.scala:44)
>   at org.scalatest.Suite$class.callExecuteOnSuite$1(Suite.scala:1492)
>   at org.scalatest.Suite$$anonfun$runNestedSuites$1.apply(Suite.scala:1528)
>   at org.scalatest.Suite$$anonfun$runNestedSuites$1.apply(Suite.scala:1526)
>   at 
> scala.collection.IndexedSeqOptimized$class.foreach(IndexedSeqOptimized.scala:33)
>   at scala.collection.mutable.ArrayOps$ofRef.foreach(ArrayOps.scala:186)
>   at org.scalatest.Suite$class.runNestedSuites(Suite.scala:1526)
>   at 
> org.scalatest.tools.DiscoverySuite.runNestedSuites(DiscoverySuite.scala:29)
>   at org.scalatest.Suite$class.run(Suite.scala:1421)
>   at org.scalatest.tools.DiscoverySuite.run(DiscoverySuite.scala:29)
>   at org.scalatest.tools.SuiteRunner.run(SuiteRunner.scala:55)
>   at 
> org.scalatest.tools.Runner$$anonfun$doRunRunRunDaDoRunRun$3.apply(Runner.scala:2563)
>   at 
> org.scalatest.tools.Runner$$anonfun$doRunRunRunDaDoRunRun$3.apply(Runner.scala:2557)
>   at scala.collection.immutable.List.foreach(List.scala:381)
>   at org.scalatest.tools.Runner$.doRunRunRunDaDoRunRun(Runner.scala:2557)
>   at 
> org.scalatest.tools.Runner$$anonfun$runOptionallyWithPassFailReporter$2.apply(Runner.scala:1044)
>   at 
> org.scalatest.tools.Runner$$anonfun$runOptionallyWithPassFailReporter$2.apply(Runner.scala:1043)
>   at 
> org.scalatest.tools.Runner$.withClassLoaderAndDispatchReporter(Runner.scala:2722)
>   at 
> org.scalatest.tools.Runner$.runOptionallyWithPassFailReporter(Runner.scala:1043)
>   at org.scalatest.tools.Runner$.main(Runner.scala:860)
>   at org.scalatest.tools.Runner.main(Runner.scala)
> {code}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (PHOENIX-3598) Enable proxy access to Phoenix query server for third party on behalf of end users

[Josh Elser (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3598?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15934029#comment-15934029
 ] 

Josh Elser commented on PHOENIX-3598:
-

{code}
+public String extractRemoteUser(HttpServletRequest request) throws 
Exception {
+  if (request.getParameter("doAs") != null) {
+String doAsUser = request.getParameter("doAs");
+UserGroupInformation proxyUser = 
UserGroupInformation.createProxyUser(doAsUser, serverUgi);
+
+// Check if this user is allowed to be impersonated.
+// Will throw AuthorizationException if the impersonation as this user 
is not allowed
+ProxyUsers.authorize(proxyUser, request.getRemoteAddr(););
+this.remoteUserExtractor = new 
HttpQueryStringParameterRemoteUserExtractor();
{code}

This needs to be done via explicit configuration. Otherwise, it's introducing a 
security hole.

{code}
+  } else {
+this.remoteUserExtractor = new HttpRequestRemoteUserExtractor();
+  }
{code}

This is creating a new object unnecessarily for every request to PQS which is 
bad. Just create a single instance in the constructor.

> Enable proxy access to Phoenix query server for third party on behalf of end 
> users
> --
>
> Key: PHOENIX-3598
> URL: https://issues.apache.org/jira/browse/PHOENIX-3598
> Project: Phoenix
>  Issue Type: Improvement
>Reporter: Jerry He
>Assignee: Shi Wang
> Attachments: 0001-PHOENIX-3598.patch
>
>
> This JIRA tracks the follow-on work of CALCITE-1539 needed on Phoenix query 
> server side.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15933995#comment-15933995
 ] 

chenglei commented on PHOENIX-3745:
---

[~maryannxue], thank you for the review,I will verify following your 
suggestions and add more tests.
Next time I will create a pull request if the patch gets over a few 
lines,thanks.

> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>Assignee: chenglei
> Attachments: PHOENIX-3745_v1.patch
>
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>  (select aid,age from merge1  where age >=11 and age<=33) a inner join 
>  (select bid,code from merge2  order by code limit 1) b on a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (PHOENIX-3693) Update to Tephra 0.11.0

[Thomas D'Silva (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3693?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15933969#comment-15933969
 ] 

Thomas D'Silva commented on PHOENIX-3693:
-

+1

> Update to Tephra 0.11.0
> ---
>
> Key: PHOENIX-3693
> URL: https://issues.apache.org/jira/browse/PHOENIX-3693
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.11.0
>
> Attachments: PHOENIX-3693.patch
>
>
> When Tephra 0.11.0 is released, we should upgrade to it.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3747) regionserver.wal UT fails

[Yesha Vora (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3747?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Yesha Vora updated PHOENIX-3747:

Attachment: WALReplayWithIndexWritesAndUncompressedWALInHBase_094_9_IT.log
testReplayEditsWrittenViaHRegion.log

> regionserver.wal UT fails
> -
>
> Key: PHOENIX-3747
> URL: https://issues.apache.org/jira/browse/PHOENIX-3747
> Project: Phoenix
>  Issue Type: Bug
>Reporter: Yesha Vora
> Attachments: testReplayEditsWrittenViaHRegion.log, 
> WALReplayWithIndexWritesAndUncompressedWALInHBase_094_9_IT.log
>
>
> Below unit test fails with "Index wasn't propertly updated from WAL replay! 
> expected:<1> but was:<0>" Error message
> * 
> org.apache.hadoop.hbase.regionserver.wal.WALReplayWithIndexWritesAndCompressedWALIT.testReplayEditsWrittenViaHRegion
> * 
> org.apache.hadoop.hbase.regionserver.wal.WALReplayWithIndexWritesAndUncompressedWALInHBase_094_9_IT.testReplayEditsWrittenViaHRegion



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Created] (PHOENIX-3747) regionserver.wal UT fails

[Yesha Vora (JIRA)]

Yesha Vora created PHOENIX-3747:
---

 Summary: regionserver.wal UT fails
 Key: PHOENIX-3747
 URL: https://issues.apache.org/jira/browse/PHOENIX-3747
 Project: Phoenix
  Issue Type: Bug
Reporter: Yesha Vora


Below unit test fails with "Index wasn't propertly updated from WAL replay! 
expected:<1> but was:<0>" Error message

* 
org.apache.hadoop.hbase.regionserver.wal.WALReplayWithIndexWritesAndCompressedWALIT.testReplayEditsWrittenViaHRegion
* 
org.apache.hadoop.hbase.regionserver.wal.WALReplayWithIndexWritesAndUncompressedWALInHBase_094_9_IT.testReplayEditsWrittenViaHRegion




--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Assigned] (PHOENIX-3746) NTH_VALUE returns 'null' when aggregating results of aggregate sub-query

[James Taylor (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3746?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

James Taylor reassigned PHOENIX-3746:
-

Assignee: Matthew Silverman

> NTH_VALUE returns 'null' when aggregating results of aggregate sub-query
> 
>
> Key: PHOENIX-3746
> URL: https://issues.apache.org/jira/browse/PHOENIX-3746
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: Matthew Silverman
>Assignee: Matthew Silverman
> Fix For: 4.11.0
>
> Attachments: PHOENIX-3476.patch
>
>
> {code:sql}
> CREATE TABLE IF NOT EXISTS TEST_TABLE (id INTEGER NOT NULL PRIMARY KEY, 
> page_id UNSIGNED_LONG, "DATE" INTEGER, "value" UNSIGNED_LONG);
>
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (1, 8, 0, 300);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (2, 8, 1, 7);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (3, 9, 2, 9);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (4, 9, 3, 4);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (5, 10, 4, 2);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (6, 10, 5, 150);
> SELECT NTH_VALUE(SUM_VALUE, 2) WITHIN GROUP (ORDER BY MIN_DATE DESC) FROM
>  (SELECT MIN("DATE") AS MIN_DATE, SUM("value") AS SUM_VALUE FROM TEST_TABLE 
> GROUP BY page_id) x;
> +--+
> | null(MIN_DATE, false, SUM_VALUE, 2)  |
> +--+
> | null |
> +--+
> {code}
> I would have expected '13' to be returned, as it is equal to 4+9.
> It appears that the {{offset}} field of the server-side aggregator is being 
> reset on every call to {{reset()}}, when it should persist permanently on the 
> aggregator once set.  I have attached the above example as an integration 
> test, together with a potential fix.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3746) NTH_VALUE returns 'null' when aggregating results of aggregate sub-query

[James Taylor (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3746?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

James Taylor updated PHOENIX-3746:
--
Fix Version/s: 4.11.0

> NTH_VALUE returns 'null' when aggregating results of aggregate sub-query
> 
>
> Key: PHOENIX-3746
> URL: https://issues.apache.org/jira/browse/PHOENIX-3746
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: Matthew Silverman
> Fix For: 4.11.0
>
> Attachments: PHOENIX-3476.patch
>
>
> {code:sql}
> CREATE TABLE IF NOT EXISTS TEST_TABLE (id INTEGER NOT NULL PRIMARY KEY, 
> page_id UNSIGNED_LONG, "DATE" INTEGER, "value" UNSIGNED_LONG);
>
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (1, 8, 0, 300);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (2, 8, 1, 7);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (3, 9, 2, 9);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (4, 9, 3, 4);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (5, 10, 4, 2);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (6, 10, 5, 150);
> SELECT NTH_VALUE(SUM_VALUE, 2) WITHIN GROUP (ORDER BY MIN_DATE DESC) FROM
>  (SELECT MIN("DATE") AS MIN_DATE, SUM("value") AS SUM_VALUE FROM TEST_TABLE 
> GROUP BY page_id) x;
> +--+
> | null(MIN_DATE, false, SUM_VALUE, 2)  |
> +--+
> | null |
> +--+
> {code}
> I would have expected '13' to be returned, as it is equal to 4+9.
> It appears that the {{offset}} field of the server-side aggregator is being 
> reset on every call to {{reset()}}, when it should persist permanently on the 
> aggregator once set.  I have attached the above example as an integration 
> test, together with a potential fix.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (PHOENIX-3746) NTH_VALUE returns 'null' when aggregating results of aggregate sub-query

[James Taylor (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3746?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15933833#comment-15933833
 ] 

James Taylor commented on PHOENIX-3746:
---

Thanks for the patch, [~masilverman_panaseer]. LGTM. Would you mind confirming 
that the test failure isn't related to your change, please?

> NTH_VALUE returns 'null' when aggregating results of aggregate sub-query
> 
>
> Key: PHOENIX-3746
> URL: https://issues.apache.org/jira/browse/PHOENIX-3746
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: Matthew Silverman
> Attachments: PHOENIX-3476.patch
>
>
> {code:sql}
> CREATE TABLE IF NOT EXISTS TEST_TABLE (id INTEGER NOT NULL PRIMARY KEY, 
> page_id UNSIGNED_LONG, "DATE" INTEGER, "value" UNSIGNED_LONG);
>
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (1, 8, 0, 300);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (2, 8, 1, 7);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (3, 9, 2, 9);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (4, 9, 3, 4);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (5, 10, 4, 2);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (6, 10, 5, 150);
> SELECT NTH_VALUE(SUM_VALUE, 2) WITHIN GROUP (ORDER BY MIN_DATE DESC) FROM
>  (SELECT MIN("DATE") AS MIN_DATE, SUM("value") AS SUM_VALUE FROM TEST_TABLE 
> GROUP BY page_id) x;
> +--+
> | null(MIN_DATE, false, SUM_VALUE, 2)  |
> +--+
> | null |
> +--+
> {code}
> I would have expected '13' to be returned, as it is equal to 4+9.
> It appears that the {{offset}} field of the server-side aggregator is being 
> reset on every call to {{reset()}}, when it should persist permanently on the 
> aggregator once set.  I have attached the above example as an integration 
> test, together with a potential fix.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3693) Update to Tephra 0.11.0

[James Taylor (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3693?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

James Taylor updated PHOENIX-3693:
--
Attachment: PHOENIX-3693.patch

[~tdsilva] - please review.

> Update to Tephra 0.11.0
> ---
>
> Key: PHOENIX-3693
> URL: https://issues.apache.org/jira/browse/PHOENIX-3693
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.11.0
>
> Attachments: PHOENIX-3693.patch
>
>
> When Tephra 0.11.0 is released, we should upgrade to it.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3598) Enable proxy access to Phoenix query server for third party on behalf of end users

[Shi Wang (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3598?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Shi Wang updated PHOENIX-3598:
--
Attachment: 0001-PHOENIX-3598.patch

> Enable proxy access to Phoenix query server for third party on behalf of end 
> users
> --
>
> Key: PHOENIX-3598
> URL: https://issues.apache.org/jira/browse/PHOENIX-3598
> Project: Phoenix
>  Issue Type: Improvement
>Reporter: Jerry He
>Assignee: Shi Wang
> Attachments: 0001-PHOENIX-3598.patch
>
>
> This JIRA tracks the follow-on work of CALCITE-1539 needed on Phoenix query 
> server side.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3598) Enable proxy access to Phoenix query server for third party on behalf of end users

[Shi Wang (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3598?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Shi Wang updated PHOENIX-3598:
--
Attachment: (was: 0001-PHOENIX-3598.patch)

> Enable proxy access to Phoenix query server for third party on behalf of end 
> users
> --
>
> Key: PHOENIX-3598
> URL: https://issues.apache.org/jira/browse/PHOENIX-3598
> Project: Phoenix
>  Issue Type: Improvement
>Reporter: Jerry He
>Assignee: Shi Wang
>
> This JIRA tracks the follow-on work of CALCITE-1539 needed on Phoenix query 
> server side.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (PHOENIX-3598) Enable proxy access to Phoenix query server for third party on behalf of end users

[Shi Wang (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3598?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15933770#comment-15933770
 ] 

Shi Wang commented on PHOENIX-3598:
---

Hi [~elserj],

Could you also take a look at this patch? It has dependency on CALCITE-1593 so 
cannot compile for now, but would like to have your opinion on the 
implementation of CALCITE1593, thanks!

> Enable proxy access to Phoenix query server for third party on behalf of end 
> users
> --
>
> Key: PHOENIX-3598
> URL: https://issues.apache.org/jira/browse/PHOENIX-3598
> Project: Phoenix
>  Issue Type: Improvement
>Reporter: Jerry He
>Assignee: Shi Wang
> Attachments: 0001-PHOENIX-3598.patch
>
>
> This JIRA tracks the follow-on work of CALCITE-1539 needed on Phoenix query 
> server side.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3598) Enable proxy access to Phoenix query server for third party on behalf of end users

[Shi Wang (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3598?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Shi Wang updated PHOENIX-3598:
--
Attachment: 0001-PHOENIX-3598.patch

> Enable proxy access to Phoenix query server for third party on behalf of end 
> users
> --
>
> Key: PHOENIX-3598
> URL: https://issues.apache.org/jira/browse/PHOENIX-3598
> Project: Phoenix
>  Issue Type: Improvement
>Reporter: Jerry He
>Assignee: Shi Wang
> Attachments: 0001-PHOENIX-3598.patch
>
>
> This JIRA tracks the follow-on work of CALCITE-1539 needed on Phoenix query 
> server side.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

Re: How can I assign jira to myself

[James Taylor]

Hi Shi,
I've added you as a contributed and assigned PHOENIX-3598 to you.
Thanks,
James

On Mon, Mar 20, 2017 at 1:06 PM, Shi Wang  wrote:

> Hi,
>
> I would like to work on PHOENIX-3598, can anyone grant me the privilege to
> assign jira to myself? Thanks!
>
> Best,
> Shi
>
>
>
>

[jira] [Assigned] (PHOENIX-3598) Enable proxy access to Phoenix query server for third party on behalf of end users

[James Taylor (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3598?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

James Taylor reassigned PHOENIX-3598:
-

Assignee: Shi Wang

> Enable proxy access to Phoenix query server for third party on behalf of end 
> users
> --
>
> Key: PHOENIX-3598
> URL: https://issues.apache.org/jira/browse/PHOENIX-3598
> Project: Phoenix
>  Issue Type: Improvement
>Reporter: Jerry He
>Assignee: Shi Wang
>
> This JIRA tracks the follow-on work of CALCITE-1539 needed on Phoenix query 
> server side.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

How can I assign jira to myself

[Shi Wang]

Hi,

I would like to work on PHOENIX-3598, can anyone grant me the privilege to 
assign jira to myself? Thanks!

Best,
Shi

[jira] [Commented] (PHOENIX-3746) NTH_VALUE returns 'null' when aggregating results of aggregate sub-query

[Hadoop QA (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3746?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15933322#comment-15933322
 ] 

Hadoop QA commented on PHOENIX-3746:


{color:red}-1 overall{color}.  Here are the results of testing the latest 
attachment 
  http://issues.apache.org/jira/secure/attachment/12859604/PHOENIX-3476.patch
  against master branch at commit 8093d10f1a481101d6c93fdf0744ff15ec48f4aa.
  ATTACHMENT ID: 12859604

{color:green}+1 @author{color}.  The patch does not contain any @author 
tags.

{color:red}-1 tests included{color}.  The patch doesn't appear to include 
any new or modified tests.
Please justify why no new tests are needed for this 
patch.
Also please list what manual steps were performed to 
verify this patch.

{color:green}+1 javac{color}.  The applied patch does not increase the 
total number of javac compiler warnings.

{color:red}-1 javadoc{color}.  The javadoc tool appears to have generated 
45 warning messages.

{color:green}+1 release audit{color}.  The applied patch does not increase 
the total number of release audit warnings.

{color:red}-1 lineLengths{color}.  The patch introduces the following lines 
longer than 100:
+"UPSERT INTO " + nth_test_table + " (id, page_id, 
\"DATE\", \"value\") VALUES (2, 8, 1, 7)");
+"UPSERT INTO " + nth_test_table + " (id, page_id, \"DATE\", 
\"value\") VALUES (3, 9, 2, 9)");
+"UPSERT INTO " + nth_test_table + " (id, page_id, \"DATE\", 
\"value\") VALUES (4, 9, 3, 4)");
+"UPSERT INTO " + nth_test_table + " (id, page_id, \"DATE\", 
\"value\") VALUES (5, 10, 4, 2)");

 {color:red}-1 core tests{color}.  The patch failed these unit tests:
 
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.RegexBulkLoadToolIT

Test results: 
https://builds.apache.org/job/PreCommit-PHOENIX-Build/808//testReport/
Javadoc warnings: 
https://builds.apache.org/job/PreCommit-PHOENIX-Build/808//artifact/patchprocess/patchJavadocWarnings.txt
Console output: 
https://builds.apache.org/job/PreCommit-PHOENIX-Build/808//console

This message is automatically generated.

> NTH_VALUE returns 'null' when aggregating results of aggregate sub-query
> 
>
> Key: PHOENIX-3746
> URL: https://issues.apache.org/jira/browse/PHOENIX-3746
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: Matthew Silverman
> Attachments: PHOENIX-3476.patch
>
>
> {code:sql}
> CREATE TABLE IF NOT EXISTS TEST_TABLE (id INTEGER NOT NULL PRIMARY KEY, 
> page_id UNSIGNED_LONG, "DATE" INTEGER, "value" UNSIGNED_LONG);
>
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (1, 8, 0, 300);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (2, 8, 1, 7);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (3, 9, 2, 9);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (4, 9, 3, 4);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (5, 10, 4, 2);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (6, 10, 5, 150);
> SELECT NTH_VALUE(SUM_VALUE, 2) WITHIN GROUP (ORDER BY MIN_DATE DESC) FROM
>  (SELECT MIN("DATE") AS MIN_DATE, SUM("value") AS SUM_VALUE FROM TEST_TABLE 
> GROUP BY page_id) x;
> +--+
> | null(MIN_DATE, false, SUM_VALUE, 2)  |
> +--+
> | null |
> +--+
> {code}
> I would have expected '13' to be returned, as it is equal to 4+9.
> It appears that the {{offset}} field of the server-side aggregator is being 
> reset on every call to {{reset()}}, when it should persist permanently on the 
> aggregator once set.  I have attached the above example as an integration 
> test, together with a potential fix.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[Maryann Xue (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15933262#comment-15933262
 ] 

Maryann Xue commented on PHOENIX-3745:
--

Thank you, [~comnetwork], for the fix!
A couple of suggestions/questions:
1. Checking if the sub-query is already ordered on the join key might be 
redundant, since that should be taken care of by the sub-query compilation 
itself later on. So could you please verify?
2. Shouldn't the {{subselectAsTableNode}} always be a {{DerivedTableNode}}? Is 
{{if(subselectAsTableNode instanceof DerivedTableNode)}} necessary? Shall we 
use {{assert}} or {{Preconditions}} check instead?
 
And BTW, could you please create a pull request next time if the patch gets 
over a few lines? It'll make review and discussions a lot easier. Thanks!

> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>Assignee: chenglei
> Attachments: PHOENIX-3745_v1.patch
>
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>  (select aid,age from merge1  where age >=11 and age<=33) a inner join 
>  (select bid,code from merge2  order by code limit 1) b on a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (PHOENIX-3218) First draft of Phoenix Tuning Guide

[Peter Conrad (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3218?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15933245#comment-15933245
 ] 

Peter Conrad commented on PHOENIX-3218:
---

Looking for feedback or LGTM so that we can either do the next round of edits 
or wrap this one up. Any comments [~apurtell], [~jamestaylor]? Anyone else?

> First draft of Phoenix Tuning Guide
> ---
>
> Key: PHOENIX-3218
> URL: https://issues.apache.org/jira/browse/PHOENIX-3218
> Project: Phoenix
>  Issue Type: Improvement
>Reporter: Peter Conrad
>Assignee: Peter Conrad
> Attachments: Phoenix-Tuning-Guide-20170110.md, 
> Phoenix-Tuning-Guide-20170223.md, Phoenix-Tuning-Guide.md, 
> Phoenix-Tuning-Guide.md
>
>
> Here's a first draft of a Tuning Guide for Phoenix performance. 



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3746) NTH_VALUE returns 'null' when aggregating results of aggregate sub-query

[Matthew Silverman (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3746?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

Matthew Silverman updated PHOENIX-3746:
---
Attachment: PHOENIX-3476.patch

> NTH_VALUE returns 'null' when aggregating results of aggregate sub-query
> 
>
> Key: PHOENIX-3746
> URL: https://issues.apache.org/jira/browse/PHOENIX-3746
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: Matthew Silverman
> Attachments: PHOENIX-3476.patch
>
>
> {code:sql}
> CREATE TABLE IF NOT EXISTS TEST_TABLE (id INTEGER NOT NULL PRIMARY KEY, 
> page_id UNSIGNED_LONG, "DATE" INTEGER, "value" UNSIGNED_LONG);
>
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (1, 8, 0, 300);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (2, 8, 1, 7);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (3, 9, 2, 9);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (4, 9, 3, 4);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (5, 10, 4, 2);
> UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (6, 10, 5, 150);
> SELECT NTH_VALUE(SUM_VALUE, 2) WITHIN GROUP (ORDER BY MIN_DATE DESC) FROM
>  (SELECT MIN("DATE") AS MIN_DATE, SUM("value") AS SUM_VALUE FROM TEST_TABLE 
> GROUP BY page_id) x;
> +--+
> | null(MIN_DATE, false, SUM_VALUE, 2)  |
> +--+
> | null |
> +--+
> {code}
> I would have expected '13' to be returned, as it is equal to 4+9.
> It appears that the {{offset}} field of the server-side aggregator is being 
> reset on every call to {{reset()}}, when it should persist permanently on the 
> aggregator once set.  I have attached the above example as an integration 
> test, together with a potential fix.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Created] (PHOENIX-3746) NTH_VALUE returns 'null' when aggregating results of aggregate sub-query

[Matthew Silverman (JIRA)]

Matthew Silverman created PHOENIX-3746:
--

 Summary: NTH_VALUE returns 'null' when aggregating results of 
aggregate sub-query
 Key: PHOENIX-3746
 URL: https://issues.apache.org/jira/browse/PHOENIX-3746
 Project: Phoenix
  Issue Type: Bug
Affects Versions: 4.9.0
Reporter: Matthew Silverman


{code:sql}
CREATE TABLE IF NOT EXISTS TEST_TABLE (id INTEGER NOT NULL PRIMARY KEY, page_id 
UNSIGNED_LONG, "DATE" INTEGER, "value" UNSIGNED_LONG);
   
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (1, 8, 0, 300);
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (2, 8, 1, 7);
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (3, 9, 2, 9);
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (4, 9, 3, 4);
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (5, 10, 4, 2);
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (6, 10, 5, 150);

SELECT NTH_VALUE(SUM_VALUE, 2) WITHIN GROUP (ORDER BY MIN_DATE DESC) FROM
 (SELECT MIN("DATE") AS MIN_DATE, SUM("value") AS SUM_VALUE FROM TEST_TABLE 
GROUP BY page_id) x;

+--+
| null(MIN_DATE, false, SUM_VALUE, 2)  |
+--+
| null |
+--+
{code}

I would have expected '13' to be returned, as it is equal to 4+9.

It appears that the {{offset}} field of the server-side aggregator is being 
reset on every call to {{reset()}}, when it should persist permanently on the 
aggregator once set.  I have attached the above example as an integration test, 
together with a potential fix.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Commented] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[Hadoop QA (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15933102#comment-15933102
 ] 

Hadoop QA commented on PHOENIX-3745:


{color:red}-1 overall{color}.  Here are the results of testing the latest 
attachment 
  http://issues.apache.org/jira/secure/attachment/12859577/PHOENIX-3745_v1.patch
  against master branch at commit 8093d10f1a481101d6c93fdf0744ff15ec48f4aa.
  ATTACHMENT ID: 12859577

{color:green}+1 @author{color}.  The patch does not contain any @author 
tags.

{color:green}+1 tests included{color}.  The patch appears to include 11 new 
or modified tests.

{color:green}+1 javac{color}.  The applied patch does not increase the 
total number of javac compiler warnings.

{color:red}-1 javadoc{color}.  The javadoc tool appears to have generated 
47 warning messages.

{color:green}+1 release audit{color}.  The applied patch does not increase 
the total number of release audit warnings.

{color:red}-1 lineLengths{color}.  The patch introduces the following lines 
longer than 100:
+sql="select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from 
(select aid,age from "+tableName1+" where age >=11 and age<=33) a inner join "+
+sql="select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from (select 
aid,age from "+tableName1+" where age >=11 and age<=33) a inner join "+
+sql="select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.codesum from 
(select aid,sum(age) agesum from "+tableName1+" where age >=11 and age<=33 
group by aid order by agesum limit 3) a inner join "+
+"(select bid,sum(code) codesum from "+tableName2+" group by 
bid order by codesum limit 2) b on a.aid=b.bid ";
+"(select a.aid,b.code from "+tableName1+" a inner join 
"+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 order by 
b.code limit 3) t1 inner join "+
+"(select a.aid,c.region from "+tableName1+" a inner join 
"+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 order by 
c.region desc limit 1) t2 on t1.aid=t2.aid";
+"(select a.aid,sum(b.code) codesum from "+tableName1+" a inner 
join "+tableName2+" b on a.aid=b.bid where b.code >=44 and b.code<=66 group by 
a.aid order by codesum limit 3) t1 inner join "+
+"(select a.aid,sum(c.region) regionsum from "+tableName1+" a 
inner join "+tableName3+" c on a.aid=c.cid where c.region>=77 and c.region<=99 
group by a.aid order by regionsum desc limit 2) t2 on t1.aid=t2.aid";
+SubselectRewriter.applyPostFilters(subselect, 
preFilters, tableNode.getAlias()),
+public static SelectStatement applyOrderBy(SelectStatement statement, 
List orderBy, String subqueryAlias,TableNode tableNode) throws 
SQLException {

 {color:red}-1 core tests{color}.  The patch failed these unit tests:
 
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.RenewLeaseIT

 {color:red}-1 core zombie tests{color}.  There are 4 zombie test(s): 

Test results: 
https://builds.apache.org/job/PreCommit-PHOENIX-Build/807//testReport/
Javadoc warnings: 
https://builds.apache.org/job/PreCommit-PHOENIX-Build/807//artifact/patchprocess/patchJavadocWarnings.txt
Console output: 
https://builds.apache.org/job/PreCommit-PHOENIX-Build/807//console

This message is automatically generated.

> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>Assignee: chenglei
> Attachments: PHOENIX-3745_v1.patch
>
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>  (select aid,age from merge1  where age >=11 and age<=33) a inner join 
>  (select bid,code from merge2  order by code limit 1) b on a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This 

[jira] [Commented] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[James Taylor (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15932975#comment-15932975
 ] 

James Taylor commented on PHOENIX-3745:
---

[~maryannxue] - would you have some spare cycles to review?

> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>Assignee: chenglei
> Attachments: PHOENIX-3745_v1.patch
>
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>  (select aid,age from merge1  where age >=11 and age<=33) a inner join 
>  (select bid,code from merge2  order by code limit 1) b on a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

chenglei updated PHOENIX-3745:
--
Description: 
Let us look a simple test case:

h4. 1. Create two tables
 {noformat}
   CREATE TABLE IF NOT EXISTS MERGE1 (
 AID INTEGER PRIMARY KEY
 AGE INTEGER
);

  CREATE TABLE IF NOT EXISTS MERGE2 (
 BID INTEGER PRIMARY KEY,
 CODE INTEGER
  );
 {noformat}

 h4. 2. Upsert values

 {noformat}
  UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
  UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
  UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);

  UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
  UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
  UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
 {noformat}

 h4. 3. Execute query
 {noformat}
select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
 (select aid,age from merge1  where age >=11 and age<=33) a inner join 
 (select bid,code from merge2  order by code limit 1) b on a.aid=b.bid 
 {noformat}

 h4. (/) Expected result
 {noformat}
3,44
 {noformat}

 h4. (!) Incorrect actual result
 {noformat}
1,66 
 {noformat}

  was:
Let us look a simple test case:

h4. 1. Create two tables
 {noformat}
   CREATE TABLE IF NOT EXISTS MERGE1 (
 AID INTEGER PRIMARY KEY
 AGE INTEGER
);

  CREATE TABLE IF NOT EXISTS MERGE2 (
 BID INTEGER PRIMARY KEY,
 CODE INTEGER
  );
 {noformat}

 h4. 2. Upsert values

 {noformat}
  UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
  UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
  UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);

  UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
  UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
  UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
 {noformat}

 h4. 3. Execute query
 {noformat}
select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
  (select aid,age from merge1  where age >=11 and age<=33) a inner join 
  (select bid,code from merge2  order by code limit 1) b on a.aid=b.bid 
 {noformat}

 h4. (/) Expected result
 {noformat}
3,44
 {noformat}

 h4. (!) Incorrect actual result
 {noformat}
1,66 
 {noformat}


> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>Assignee: chenglei
> Attachments: PHOENIX-3745_v1.patch
>
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>  (select aid,age from merge1  where age >=11 and age<=33) a inner join 
>  (select bid,code from merge2  order by code limit 1) b on a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Comment Edited] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15932836#comment-15932836
 ] 

chenglei edited comment on PHOENIX-3745 at 3/20/17 3:44 PM:


I uploaded my first patch,the patch fixs this issue in mainly two aspects:
(1) convert the sql "(select bid,code from merge2 order by code limit 1)"  to   
 "(select bid,code from (select bid,code from merge2 order by code limit 1) 
order by bid)" when there is limit statement in subquery.
(2) a simple optimization: if the sql is "(select bid,code from merge2 order by 
bid, code limit 1)" , because "order by bid" is the prefix of "order by bid, 
code",there is no need to modify the sql.

Please help me have a review,thanks.


was (Author: comnetwork):
I uploaded my first patch,the patch fixs this issue in mainly two aspects:
(1) convert the sql "(select bid,code from merge2 order by code limit 1)"  to   
 "(select bid,code from (select bid,code from merge2 order by code limit 1) 
order by bid)" when there is limit statement in subquery.
(2) a simple optimization: if the sql is "(select bid,code from merge2 order by 
bid, code limit 1)" , because "order by bid" is the prefix of "order by bid, 
code",there is no need to modify the sql.

> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>Assignee: chenglei
> Attachments: PHOENIX-3745_v1.patch
>
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>   (select aid,age from merge1  where age >=11 and age<=33) a inner 
> join 
>   (select bid,code from merge2  order by code limit 1) b on 
> a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

chenglei updated PHOENIX-3745:
--
Attachment: PHOENIX-3745_v1.patch

> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
> Attachments: PHOENIX-3745_v1.patch
>
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>   (select aid,age from merge1  where age >=11 and age<=33) a inner 
> join 
>   (select bid,code from merge2  order by code limit 1) b on 
> a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Comment Edited] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15932724#comment-15932724
 ] 

chenglei edited comment on PHOENIX-3745 at 3/20/17 3:36 PM:


The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the 
RHS subquery's OrderBy.

When we look into following line 421 in QueryCompiler.compileJoinQuery 
method,we can see QueryCompiler.compileJoinQuery method is invoked to compile 
the above test case's RHS subquery:  (select bid,code from merge2  order by 
code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by 
b.bid asc.
{code}
420Scan rhsScan = ScanUtil.newScan(originalScan);
421StatementContext rhsCtx = new StatementContext(statement, 
context.getResolver(), rhsScan, new SequenceManager(statement));
422QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true, 
true, rhsOrderBy);
{code}

QueryCompiler.compileJoinQuery then invokes following 
SubselectRewriter.applyOrderBy to apply the OrderBy which is "order by b.bid 
asc" to RHS subquery statement which is  (select bid,code from merge2  order by 
code limit 1), in line 259 we can see "order by code" in subquery is overrided 
by "order by b.bid", and the subquery becomes:
 (select bid,code from merge2  order by bid limit 1). 


{code}
252 private SelectStatement applyOrderBy(SelectStatement statement, 
List orderBy) throws SQLException {
253List orderByRewrite = Lists. 
newArrayListWithExpectedSize(orderBy.size());
254for (OrderByNode orderByNode : orderBy) {
255ParseNode node = orderByNode.getNode();
256orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this), 
orderByNode.isNullsLast(), orderByNode.isAscending()));
257}
258
259return NODE_FACTORY.select(statement, orderByRewrite);
260}
{code}

Obviously ,because there is limit statement in subquery,we can not simply 
override OrderBy in subquery, and the subquery should be :

select bid,code from (select bid,code from merge2  order by code limit 1) order 
by bid



was (Author: comnetwork):
The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the 
RHS subquery's OrderBy.

When we look into following line 421 in QueryCompiler.compileJoinQuery 
method,we can see QueryCompiler.compileJoinQuery method is invoked to compile 
the above test case's RHS subquery:  (select bid,code from merge2  order by 
code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by 
b.bid asc.
{code}
420Scan rhsScan = ScanUtil.newScan(originalScan);
421StatementContext rhsCtx = new StatementContext(statement, 
context.getResolver(), rhsScan, new SequenceManager(statement));
422QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true, 
true, rhsOrderBy);
{code}

QueryCompiler.compileJoinQuery then invokes following 
SubselectRewriter.applyOrderBy to apply the OrderBy which is "order by b.bid 
asc" to RHS subquery statement which is  (select bid,code from merge2  order by 
code limit 1), in line 259 we can see "order by code" in subquery is overrided 
by "order by b.bid", and the subquery becomes:
 (select bid,code from merge2  order by bid limit 1). 


{code}
252 private SelectStatement applyOrderBy(SelectStatement statement, 
List orderBy) throws SQLException {
253List orderByRewrite = Lists. 
newArrayListWithExpectedSize(orderBy.size());
254for (OrderByNode orderByNode : orderBy) {
255ParseNode node = orderByNode.getNode();
256orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this), 
orderByNode.isNullsLast(), orderByNode.isAscending()));
257}
258
259return NODE_FACTORY.select(statement, orderByRewrite);
260}
{code}

Obviously ,because there is limit statement in subquery,we can not simply 
override OrderBy in subquery, and the subquery should be :

select bid,code  (select bid,code from merge2  order by code limit 1) order by 
bid


> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT 

[jira] [Comment Edited] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15932836#comment-15932836
 ] 

chenglei edited comment on PHOENIX-3745 at 3/20/17 3:35 PM:


I uploaded my first patch,the patch fixs this issue in mainly two aspects:
(1) convert the sql "(select bid,code from merge2 order by code limit 1)"  to   
 "(select bid,code from (select bid,code from merge2 order by code limit 1) 
order by bid)" when there is limit statement in subquery.
(2) a simple optimization: if the sql is "(select bid,code from merge2 order by 
bid, code limit 1)" , because "order by bid" is the prefix of "order by bid, 
code",there is no need to modify the sql.


was (Author: comnetwork):
I uploaded my first patch,the patch fixs this issue in mainly two aspects:
(1) convert the sql "(select bid,code from merge2 order by code limit 1)"  to 
 "select bid,code (select bid,code from merge2 order by code limit 1) order by 
bid" when there is limit statement in subquery.
(2) a simple optimization: if the sql is "(select bid,code from merge2 order by 
bid, code limit 1)" , because "order by bid" is the prefix of "order by bid, 
code",there is no need to modify the sql.

> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>   (select aid,age from merge1  where age >=11 and age<=33) a inner 
> join 
>   (select bid,code from merge2  order by code limit 1) b on 
> a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Comment Edited] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15932724#comment-15932724
 ] 

chenglei edited comment on PHOENIX-3745 at 3/20/17 2:34 PM:


The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the 
RHS subquery's OrderBy.

When we look into following line 421 in QueryCompiler.compileJoinQuery 
method,we can see QueryCompiler.compileJoinQuery method is invoked to compile 
the above test case's RHS subquery:  (select bid,code from merge2  order by 
code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by 
b.bid asc.
{code}
420Scan rhsScan = ScanUtil.newScan(originalScan);
421StatementContext rhsCtx = new StatementContext(statement, 
context.getResolver(), rhsScan, new SequenceManager(statement));
422QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true, 
true, rhsOrderBy);
{code}

QueryCompiler.compileJoinQuery then invokes following 
SubselectRewriter.applyOrderBy to apply the OrderBy which is order by b.bid asc 
to RHS subquery statement which is  (select bid,code from merge2  order by code 
limit 1), in line 259 we can see "order by code" in subquery is overrided by 
"order by b.bid", and the subquery becomes:
 (select bid,code from merge2  order by bid limit 1). 


{code}
252 private SelectStatement applyOrderBy(SelectStatement statement, 
List orderBy) throws SQLException {
253List orderByRewrite = Lists. 
newArrayListWithExpectedSize(orderBy.size());
254for (OrderByNode orderByNode : orderBy) {
255ParseNode node = orderByNode.getNode();
256orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this), 
orderByNode.isNullsLast(), orderByNode.isAscending()));
257}
258
259return NODE_FACTORY.select(statement, orderByRewrite);
260}
{code}

Obviously ,because there is limit statement in subquery,we can not simply 
override OrderBy in subquery, and the subquery should be :

select bid,code  (select bid,code from merge2  order by code limit 1) order by 
bid



was (Author: comnetwork):
The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the 
RHS subquery's OrderBy.

When we look into following line 421 in QueryCompiler.compileJoinQuery 
method,we can see QueryCompiler.compileJoinQuery method is invoked to compile 
the above test case's RHS subquery:  (select bid,code from merge2  order by 
code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by 
b.bid asc.
{code}
420Scan rhsScan = ScanUtil.newScan(originalScan);
421StatementContext rhsCtx = new StatementContext(statement, 
context.getResolver(), rhsScan, new SequenceManager(statement));
422QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true, 
true, rhsOrderBy);
{code}

QueryCompiler.compileJoinQuery then invokes following 
SubselectRewriter.applyOrderBy to apply the OrderBy which is order by b.bid asc 
to RHS subquery statement which is  (select bid,code from merge2  order by code 
limit 1), in line 259 we can see "order by code" in subquery is overrided by 
"order by b.bid", and the subquery becomes:
 (select bid,code from merge2  order by bid limit 1). 


{code}
252 private SelectStatement applyOrderBy(SelectStatement statement, 
List orderBy) throws SQLException {
253List orderByRewrite = Lists. 
newArrayListWithExpectedSize(orderBy.size());
254for (OrderByNode orderByNode : orderBy) {
255ParseNode node = orderByNode.getNode();
256orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this), 
orderByNode.isNullsLast(), orderByNode.isAscending()));
257}
258
259return NODE_FACTORY.select(statement, orderByRewrite);
260}
{code}

Obviously ,because there is limit statement in subquery,we can not simply 
override OrderBy in subquery, and the subquery should be :

select bid,code  (select bid,code from merge2  order by code limit 1) order by 
bid


> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 

[jira] [Updated] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

chenglei updated PHOENIX-3745:
--
Description: 
Let us look a simple test case:

h4. 1. Create two tables
 {noformat}
   CREATE TABLE IF NOT EXISTS MERGE1 (
 AID INTEGER PRIMARY KEY
 AGE INTEGER
);

  CREATE TABLE IF NOT EXISTS MERGE2 (
 BID INTEGER PRIMARY KEY,
 CODE INTEGER
  );
 {noformat}

 h4. 2. Upsert values

 {noformat}
  UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
  UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
  UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);

  UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
  UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
  UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
 {noformat}

 h4. 3. Execute query
 {noformat}
select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
  (select aid,age from merge1  where age >=11 and age<=33) a inner join 
  (select bid,code from merge2  order by code limit 1) b on a.aid=b.bid 
 {noformat}

 h4. (/) Expected result
 {noformat}
3,44
 {noformat}

 h4. (!) Incorrect actual result
 {noformat}
1,66 
 {noformat}

  was:
Let us look at a simple test case:

h4. 1. Create two tables
 {noformat}
   CREATE TABLE IF NOT EXISTS MERGE1 (
 AID INTEGER PRIMARY KEY
 AGE INTEGER
);

  CREATE TABLE IF NOT EXISTS MERGE2 (
 BID INTEGER PRIMARY KEY,
 CODE INTEGER
  );
 {noformat}

 h4. 2. Upsert values

 {noformat}
  UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
  UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
  UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);

  UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
  UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
  UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
 {noformat}

 h4. 3. Execute query
 {noformat}
select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
  (select aid,age from merge1  where age >=11 and age<=33) a inner join 
  (select bid,code from merge2  order by code limit 1) b on a.aid=b.bid 
 {noformat}

 h4. (/) Expected result
 {noformat}
3,44
 {noformat}

 h4. (!) Incorrect actual result
 {noformat}
1,66 
 {noformat}


> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>
> Let us look a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>   (select aid,age from merge1  where age >=11 and age<=33) a inner 
> join 
>   (select bid,code from merge2  order by code limit 1) b on 
> a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Comment Edited] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15932724#comment-15932724
 ] 

chenglei edited comment on PHOENIX-3745 at 3/20/17 2:31 PM:


The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the 
RHS subquery's OrderBy.

When we look into following line 421 in QueryCompiler.compileJoinQuery 
method,we can see QueryCompiler.compileJoinQuery method is invoked to compile 
the above test case's RHS subquery:  (select bid,code from merge2  order by 
code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by 
b.bid asc.
{code}
420Scan rhsScan = ScanUtil.newScan(originalScan);
421StatementContext rhsCtx = new StatementContext(statement, 
context.getResolver(), rhsScan, new SequenceManager(statement));
422QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true, 
true, rhsOrderBy);
{code}

QueryCompiler.compileJoinQuery then invokes following 
SubselectRewriter.applyOrderBy to apply the OrderBy which is order by b.bid asc 
to RHS subquery statement which is  (select bid,code from merge2  order by code 
limit 1), in line 259 we can see "order by code" in subquery is overrided by 
"order by b.bid", and the subquery becomes:
 (select bid,code from merge2  order by bid limit 1). 


{code}
252 private SelectStatement applyOrderBy(SelectStatement statement, 
List orderBy) throws SQLException {
253List orderByRewrite = Lists. 
newArrayListWithExpectedSize(orderBy.size());
254for (OrderByNode orderByNode : orderBy) {
255ParseNode node = orderByNode.getNode();
256orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this), 
orderByNode.isNullsLast(), orderByNode.isAscending()));
257}
258
259return NODE_FACTORY.select(statement, orderByRewrite);
260}
{code}

Obviously ,because there is limit statement in subquery,we can not simply 
override OrderBy in subquery, and the subquery should be :

select bid,code  (select bid,code from merge2  order by code limit 1) order by 
bid



was (Author: comnetwork):
The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the 
RHS subquery's OrderBy.

When we look into following line 421 in QueryCompiler.compileJoinQuery 
method,we can see QueryCompiler.compileJoinQuery method is invoked to compile 
the above test case's RHS subquery:  (select bid,code from merge2  order by 
code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by 
b.bid asc.
{code}
420Scan rhsScan = ScanUtil.newScan(originalScan);
421StatementContext rhsCtx = new StatementContext(statement, 
context.getResolver(), rhsScan, new SequenceManager(statement));
422QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true, 
true, rhsOrderBy);
{code}

QueryCompiler.compileJoinQuery then invokes following 
SubselectRewriter.applyOrderBy to apply the OrderBy which is order by b.bid asc 
to RHS subquery statement which is  (select bid,code from merge2  order by code 
limit 1), in line 259 we can see order by code in subquery is overrided by 
order by b.bid, and the subquery becomes:
 (select bid,code from merge2  order by bid limit 1). Obviously ,because there 
exits limit statement in subquery,we can not simply override OrderBy, and the 
subquery should be :

select bid,code  (select bid,code from merge2  order by code limit 1) order by 
bid

252 private SelectStatement applyOrderBy(SelectStatement statement, 
List orderBy) throws SQLException {
253List orderByRewrite = Lists. 
newArrayListWithExpectedSize(orderBy.size());
254for (OrderByNode orderByNode : orderBy) {
255ParseNode node = orderByNode.getNode();
256orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this), 
orderByNode.isNullsLast(), orderByNode.isAscending()));
257}
258
259return NODE_FACTORY.select(statement, orderByRewrite);
260}


> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>
> Let us look at a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);

[jira] [Commented] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

[ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15932724#comment-15932724
 ] 

chenglei commented on PHOENIX-3745:
---

The issus is caused by the SortMergeJoin QueryPlan incorrectly overrides the 
RHS subquery's OrderBy.

When we look into following line 421 in QueryCompiler.compileJoinQuery 
method,we can see QueryCompiler.compileJoinQuery method is invoked to compile 
the above test case's RHS subquery:  (select bid,code from merge2  order by 
code limit 1) ,and the rhsOrderBy parameter is RHS's join condition: order by 
b.bid asc.
{code}
420Scan rhsScan = ScanUtil.newScan(originalScan);
421StatementContext rhsCtx = new StatementContext(statement, 
context.getResolver(), rhsScan, new SequenceManager(statement));
422QueryPlan rhsPlan = compileJoinQuery(rhsCtx, binds, rhsJoin, true, 
true, rhsOrderBy);
{code}

QueryCompiler.compileJoinQuery then invokes following 
SubselectRewriter.applyOrderBy to apply the OrderBy which is order by b.bid asc 
to RHS subquery statement which is  (select bid,code from merge2  order by code 
limit 1), in line 259 we can see order by code in subquery is overrided by 
order by b.bid, and the subquery becomes:
 (select bid,code from merge2  order by bid limit 1). Obviously ,because there 
exits limit statement in subquery,we can not simply override OrderBy, and the 
subquery should be :

select bid,code  (select bid,code from merge2  order by code limit 1) order by 
bid

252 private SelectStatement applyOrderBy(SelectStatement statement, 
List orderBy) throws SQLException {
253List orderByRewrite = Lists. 
newArrayListWithExpectedSize(orderBy.size());
254for (OrderByNode orderByNode : orderBy) {
255ParseNode node = orderByNode.getNode();
256orderByRewrite.add(NODE_FACTORY.orderBy(node.accept(this), 
orderByNode.isNullsLast(), orderByNode.isAscending()));
257}
258
259return NODE_FACTORY.select(statement, orderByRewrite);
260}


> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>
> Let us look at a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>   (select aid,age from merge1  where age >=11 and age<=33) a inner 
> join 
>   (select bid,code from merge2  order by code limit 1) b on 
> a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

chenglei updated PHOENIX-3745:
--
Description: 
Let us look at a simple test case:

h4. 1. Create two tables
 {noformat}
   CREATE TABLE IF NOT EXISTS MERGE1 (
 AID INTEGER PRIMARY KEY
 AGE INTEGER
);

  CREATE TABLE IF NOT EXISTS MERGE2 (
 BID INTEGER PRIMARY KEY,
 CODE INTEGER
  );
 {noformat}

 h4. 2. Upsert values

 {noformat}
  UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
  UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
  UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);

  UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
  UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
  UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
 {noformat}

 h4. 3. Execute query
 {noformat}
select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
  (select aid,age from merge1  where age >=11 and age<=33) a inner join 
  (select bid,code from merge2  order by code limit 1) b on a.aid=b.bid 
 {noformat}

 h4. (/) Expected result
 {noformat}
3,44
 {noformat}

 h4. (!) Incorrect actual result
 {noformat}
1,66 
 {noformat}

  was:
Let us look at a simple test case:

h4. 1. Create two tables
 {noformat}
   CREATE TABLE IF NOT EXISTS MERGE1 (
 AID INTEGER PRIMARY KEY
 AGE INTEGER
);

  CREATE TABLE IF NOT EXISTS MERGE2 (
 BID INTEGER PRIMARY KEY,
 CODE INTEGER
  );
 {noformat}

 h4. 2. Upsert values

 {noformat}
  UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
  UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
  UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);

 UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
 UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
 UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
 {noformat}

 h4. 3. Execute query
 {noformat}
select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
(select aid,age from merge1 where age >=11 and age<=33) a inner 
join 
(select bid,code from merge2  order by code limit 1) b on 
a.aid=b.bid 
 {noformat}

 h4. (/) Expected result
 {noformat}
3,44
 {noformat}

 h4. (!) Incorrect actual result
 {noformat}
1,66 
 {noformat}


> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>
> Let us look at a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>   UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
>   (select aid,age from merge1  where age >=11 and age<=33) a inner 
> join 
>   (select bid,code from merge2  order by code limit 1) b on 
> a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

chenglei updated PHOENIX-3745:
--
Description: 
Let us look at a simple test case:

h4. 1. Create two tables
 {noformat}
   CREATE TABLE IF NOT EXISTS MERGE1 (
 AID INTEGER PRIMARY KEY
 AGE INTEGER
);

  CREATE TABLE IF NOT EXISTS MERGE2 (
 BID INTEGER PRIMARY KEY,
 CODE INTEGER
  );
 {noformat}

 h4. 2. Upsert values

 {noformat}
  UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
  UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
  UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);

 UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
 UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
 UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
 {noformat}

 h4. 3. Execute query
 {noformat}
select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
(select aid,age from merge1 where age >=11 and age<=33) a inner 
join 
(select bid,code from merge2  order by code limit 1) b on 
a.aid=b.bid 
 {noformat}

 h4. (/) Expected result
 {noformat}
3,44
 {noformat}

 h4. (!) Incorrect actual result
 {noformat}
1,66 
 {noformat}

  was:
Let us look at a simple test cases:

h4. 1. Create two tables
 {noformat}
   CREATE TABLE IF NOT EXISTS MERGE1 (
 AID INTEGER PRIMARY KEY
 AGE INTEGER
);

  CREATE TABLE IF NOT EXISTS MERGE2 (
 BID INTEGER PRIMARY KEY,
 CODE INTEGER
);
 {noformat}

 h4. 2. Upsert values

 {noformat}
  UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
  UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
  UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);

 UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
 UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
 UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
 {noformat}

 h4. 3. Execute query
 {noformat}
select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
(select aid,age from merge1 where age >=11 and age<=33) a inner 
join 
(select bid,code from merge2  order by code limit 1) b on 
a.aid=b.bid 
 {noformat}

 h4. (/) Expected result
 {noformat}
3,44
 {noformat}

 h4. (!) Incorrect actual result
 {noformat}
1,66 
 {noformat}


> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>
> Let us look at a simple test case:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
>   );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>  UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>  UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>  UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
> (select aid,age from merge1 where age >=11 and age<=33) a inner 
> join 
> (select bid,code from merge2  order by code limit 1) b on 
> a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Updated] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

 [ 
https://issues.apache.org/jira/browse/PHOENIX-3745?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

chenglei updated PHOENIX-3745:
--
Description: 
Let us look at a simple test cases:

h4. 1. Create two tables
 {noformat}
   CREATE TABLE IF NOT EXISTS MERGE1 (
 AID INTEGER PRIMARY KEY
 AGE INTEGER
);

  CREATE TABLE IF NOT EXISTS MERGE2 (
 BID INTEGER PRIMARY KEY,
 CODE INTEGER
);
 {noformat}

 h4. 2. Upsert values

 {noformat}
  UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
  UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
  UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);

 UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
 UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
 UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
 {noformat}

 h4. 3. Execute query
 {noformat}
select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
(select aid,age from merge1 where age >=11 and age<=33) a inner 
join 
(select bid,code from merge2  order by code limit 1) b on 
a.aid=b.bid 
 {noformat}

 h4. (/) Expected result
 {noformat}
3,44
 {noformat}

 h4. (!) Incorrect actual result
 {noformat}
1,66 
 {noformat}

> SortMergeJoin might incorrectly override the OrderBy of LHS or RHS
> --
>
> Key: PHOENIX-3745
> URL: https://issues.apache.org/jira/browse/PHOENIX-3745
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.9.0
>Reporter: chenglei
>
> Let us look at a simple test cases:
> h4. 1. Create two tables
>  {noformat}
>CREATE TABLE IF NOT EXISTS MERGE1 (
>  AID INTEGER PRIMARY KEY
>  AGE INTEGER
> );
>   CREATE TABLE IF NOT EXISTS MERGE2 (
>  BID INTEGER PRIMARY KEY,
>  CODE INTEGER
> );
>  {noformat}
>  h4. 2. Upsert values
>  {noformat}
>   UPSERT INTO MERGE1(AID,AGE) VALUES (1,11);
>   UPSERT INTO MERGE1(AID,AGE) VALUES (2,22);
>   UPSERT INTO MERGE1 (AID,AGE) VALUES (3,33);
>  UPSERT INTO MERGE2 (BID,CODE) VALUES (1,66);
>  UPSERT INTO MERGE2 (BID,CODE) VALUES (2,55);
>  UPSERT INTO MERGE2 (BID,CODE) VALUES (3,44);
>  {noformat}
>  h4. 3. Execute query
>  {noformat}
> select /*+ USE_SORT_MERGE_JOIN */ a.aid,b.code from
> (select aid,age from merge1 where age >=11 and age<=33) a inner 
> join 
> (select bid,code from merge2  order by code limit 1) b on 
> a.aid=b.bid 
>  {noformat}
>  h4. (/) Expected result
>  {noformat}
> 3,44
>  {noformat}
>  h4. (!) Incorrect actual result
>  {noformat}
> 1,66 
>  {noformat}



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)

[jira] [Created] (PHOENIX-3745) SortMergeJoin might incorrectly override the OrderBy of LHS or RHS

[chenglei (JIRA)]

chenglei created PHOENIX-3745:
-

 Summary: SortMergeJoin might incorrectly override the OrderBy of 
LHS or RHS
 Key: PHOENIX-3745
 URL: https://issues.apache.org/jira/browse/PHOENIX-3745
 Project: Phoenix
  Issue Type: Bug
Affects Versions: 4.9.0
Reporter: chenglei






--
This message was sent by Atlassian JIRA
(v6.3.15#6346)