[jira] [Commented] (PHOENIX-3933) Start row is skipped when iterating a result set with ScanUtil.setReversed(scan)

[Biju Nair (JIRA)]

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

Biju Nair commented on PHOENIX-3933:


Thanks [~giacomotaylor]. I was trying to make changes to the Phoenix code with 
the assumption that {{scan}} iteration will mimic the behavior in {{HBase}} and 
in hindsight the assumption is not correct.

> Start row is skipped when iterating a result set with 
> ScanUtil.setReversed(scan)
> 
>
> Key: PHOENIX-3933
> URL: https://issues.apache.org/jira/browse/PHOENIX-3933
> Project: Phoenix
>  Issue Type: Bug
>Reporter: Biju Nair
>Priority: Minor
>
> {code}
> ResultSet rs = statement.executeQuery("SELECT * FROM " + tableName );
> QueryPlan plan = 
> statement.unwrap(PhoenixStatement.class).getQueryPlan();
> Scan scan = plan.getContext().getScan();
>  while(rs.next()) {
> LOG.debug(" "+rs.getInt(1));
> }
> {code}
> This section of the code returns
> {code}
> [main] org.apache.phoenix.end2end.ReverseScanTest(126):  0
> [main] org.apache.phoenix.end2end.ReverseScanTest(126):  1
> [main] org.apache.phoenix.end2end.ReverseScanTest(126):  2
> [main] org.apache.phoenix.end2end.ReverseScanTest(126):  3
> [main] org.apache.phoenix.end2end.ReverseScanTest(126):  4
> [main] org.apache.phoenix.end2end.ReverseScanTest(126):  5
> [main] org.apache.phoenix.end2end.ReverseScanTest(126):  6
> [main] org.apache.phoenix.end2end.ReverseScanTest(126):  7
> [main] org.apache.phoenix.end2end.ReverseScanTest(126):  8
> {code}
> If the {{scan}} is set to reverse the start and stop key is set to 4 &  8 the 
> resulting result set doesn't seem to include 8 in the result which is 
> different from the HBase scan when reversed.
> {code}
> ScanUtil.setReversed(scan);
> scan.setStartRow(PInteger.INSTANCE.toBytes(4));
> scan.setStopRow(PInteger.INSTANCE.toBytes(8));
> rs = new PhoenixResultSet(plan.iterator(), plan.getProjector(), 
> plan.getContext());
> while(rs.next()){
> LOG.debug("**rev*** "+rs.getInt(1));
> }
> {code}
> the result is
> {code}
> org.apache.phoenix.end2end.ReverseScanTest(136): **rev*** 7
> org.apache.phoenix.end2end.ReverseScanTest(136): **rev*** 6
> org.apache.phoenix.end2end.ReverseScanTest(136): **rev*** 5
> org.apache.phoenix.end2end.ReverseScanTest(136): **rev*** 4
> {code}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3807) Add server level metrics for secondary indexes

[Josh Elser (JIRA)]

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

Josh Elser commented on PHOENIX-3807:
-

Just getting around to taking a look:

bq. Count of deployed secondary indexes by type. Will help understand and trend 
index usage, or catch deploy of an unwanted index type.

I'm not sure about the value of this. I would have just looked at the number of 
regions hosted by table that the Region already has. I'm not -1 on including 
it, just figured it was worth a mention.

Otherwise, the changes look pretty good. 

On the naming of the metrics: instead of "IndexWriteCompletionTime" is 
"IndexWriteTime" sufficient? I thought that would make it closer to its sister 
"IndexPrepareTime". Similarly, for "IndexTotalUpdateCount", would 
"GlobalIndexUpdateCount" make more sense (best as I can tell, this is the total 
number of index updates across all tables). I like that these are much more 
"high-level" consumable -- my patch was primarily re-using the terminology on 
the coprocessor itself (e.g. preBatchMutate, postBatchMutateIndispensably).

> Add server level metrics for secondary indexes
> --
>
> Key: PHOENIX-3807
> URL: https://issues.apache.org/jira/browse/PHOENIX-3807
> Project: Phoenix
>  Issue Type: Improvement
>Reporter: Andrew Purtell
>Assignee: Vincent Poon
> Attachments: PHOENIX-3807.v1.master.patch, Screen Shot 2017-05-31 at 
> 4.00.16 PM.png
>
>
> Add server level metrics for secondary indexes
> - Histogram metrics for time to complete all secondary index updates per 
> primary table update per index type. Will help us trend perf over time and 
> catch impending issues. 
> - Histogram metrics for number of updates dispatched to secondary index 
> stores to service one primary table update per index type. Will help us catch 
> inefficient behavior or problematic schema design. 
> - Count of deployed secondary indexes by type. Will help understand and trend 
> index usage, or catch deploy of an unwanted index type.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3817) VerifyReplication using SQL

[James Taylor (JIRA)]

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

James Taylor commented on PHOENIX-3817:
---

I'm probably not the best person to review this (though it looks very good to 
me). Would [~sergey.soldatov], [~gjacoby], [~maghamraviki...@gmail.com], or 
[~jmahonin] have any spare cycles to take a look?

Just out of curiousity, would this patch give us the capability of writing to 
both the data table and index table in one pass to build indexes during a bulk 
load?

> VerifyReplication using SQL
> ---
>
> Key: PHOENIX-3817
> URL: https://issues.apache.org/jira/browse/PHOENIX-3817
> Project: Phoenix
>  Issue Type: Improvement
>Reporter: Alex Araujo
>Assignee: Alex Araujo
>Priority: Minor
> Fix For: 4.12.0
>
> Attachments: PHOENIX-3817.v1.patch, PHOENIX-3817.v2.patch, 
> PHOENIX-3817.v3.patch
>
>
> Certain use cases may copy or replicate a subset of a table to a different 
> table or cluster. For example, application topologies may map data for 
> specific tenants to different peer clusters.
> It would be useful to have a Phoenix VerifyReplication tool that accepts an 
> SQL query, a target table, and an optional target cluster. The tool would 
> compare data returned by the query on the different tables and update various 
> result counters (similar to HBase's VerifyReplication).



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (PHOENIX-3817) VerifyReplication using SQL

[James Taylor (JIRA)]

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

James Taylor updated PHOENIX-3817:
--
Fix Version/s: 4.12.0

> VerifyReplication using SQL
> ---
>
> Key: PHOENIX-3817
> URL: https://issues.apache.org/jira/browse/PHOENIX-3817
> Project: Phoenix
>  Issue Type: Improvement
>Reporter: Alex Araujo
>Assignee: Alex Araujo
>Priority: Minor
> Fix For: 4.12.0
>
> Attachments: PHOENIX-3817.v1.patch, PHOENIX-3817.v2.patch, 
> PHOENIX-3817.v3.patch
>
>
> Certain use cases may copy or replicate a subset of a table to a different 
> table or cluster. For example, application topologies may map data for 
> specific tenants to different peer clusters.
> It would be useful to have a Phoenix VerifyReplication tool that accepts an 
> SQL query, a target table, and an optional target cluster. The tool would 
> compare data returned by the query on the different tables and update various 
> result counters (similar to HBase's VerifyReplication).



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3534) Support multi region SYSTEM.CATALOG table

[ASF GitHub Bot (JIRA)]

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

ASF GitHub Bot commented on PHOENIX-3534:
-

Github user JamesRTaylor commented on the issue:

https://github.com/apache/phoenix/pull/248
  
Looks like there are still conflicts, @churrodog. Would you mind amending 
your commit message to "PHOENIX-3534 Support multi region SYSTEM.CATALOG table" 
so that the JIRA is linked to the PR?


> Support multi region SYSTEM.CATALOG table
> -
>
> Key: PHOENIX-3534
> URL: https://issues.apache.org/jira/browse/PHOENIX-3534
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>Assignee: churro morales
>
> Currently Phoenix requires that the SYSTEM.CATALOG table is single region 
> based on the server-side row locks being held for operations that impact a 
> table and all of it's views. For example, adding/removing a column from a 
> base table pushes this change to all views.
> As an alternative to making the SYSTEM.CATALOG transactional (PHOENIX-2431), 
> when a new table is created we can do a lazy cleanup  of any rows that may be 
> left over from a failed DDL call (kudos to [~lhofhansl] for coming up with 
> this idea). To implement this efficiently, we'd need to also do PHOENIX-2051 
> so that we can efficiently find derived views.
> The implementation would rely on an optimistic concurrency model based on 
> checking our sequence numbers for each table/view before/after updating. Each 
> table/view row would be individually locked for their change (metadata for a 
> view or table cannot span regions due to our split policy), with the sequence 
> number being incremented under lock and then returned to the client.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[GitHub] phoenix issue #248: Phoenix 3534

[JamesRTaylor]

Github user JamesRTaylor commented on the issue:

https://github.com/apache/phoenix/pull/248
  
Looks like there are still conflicts, @churrodog. Would you mind amending 
your commit message to "PHOENIX-3534 Support multi region SYSTEM.CATALOG table" 
so that the JIRA is linked to the PR?


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[jira] [Commented] (PHOENIX-153) Implement TABLESAMPLE clause

[James Taylor (JIRA)]

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

James Taylor commented on PHOENIX-153:
--

+1. Do you have something you can point us to for the Calcite TABLESAMPLE 
syntax?

> Implement TABLESAMPLE clause
> 
>
> Key: PHOENIX-153
> URL: https://issues.apache.org/jira/browse/PHOENIX-153
> Project: Phoenix
>  Issue Type: Task
>Reporter: James Taylor
>Assignee: Ethan Wang
>  Labels: enhancement
>
> Support the standard SQL TABLESAMPLE clause by implementing a filter that 
> uses a skip next hint based on the region boundaries of the table to only 
> return n rows per region.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3390) Custom UDAF for HyperLogLogPlus

[James Taylor (JIRA)]

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

James Taylor commented on PHOENIX-3390:
---

[~talktoswa...@gmail.com] - [~aertoria] is interested in continuing your work 
here. Would it be possible for you to put a pull request (ideally rebased, but 
even what ever you have now would be much appreciated).

> Custom UDAF for HyperLogLogPlus
> ---
>
> Key: PHOENIX-3390
> URL: https://issues.apache.org/jira/browse/PHOENIX-3390
> Project: Phoenix
>  Issue Type: New Feature
>Reporter: Swapna Kasula
>Priority: Minor
>
> With ref # PHOENIX-2069
> Custome UDAF to aggregate/union of Hyperloglog's of a column and returns a 
> Hyperloglog.
> select hllUnion(col1) from table;  //returns a Hyperloglog, which is the 
> union of all hyperloglog's from all rows for column 'col1'



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (PHOENIX-3955) Indexes should inherit the KEEP_DELETED_CELLS and TTL properties from the base table

[Samarth Jain (JIRA)]

Samarth Jain created PHOENIX-3955:
-

 Summary: Indexes should inherit the KEEP_DELETED_CELLS and TTL 
properties from the base table
 Key: PHOENIX-3955
 URL: https://issues.apache.org/jira/browse/PHOENIX-3955
 Project: Phoenix
  Issue Type: Bug
Reporter: Samarth Jain


We need to make sure that indexes inherit the KEEP_DELETED_CELLS and TTL 
properties from the base table. Otherwise we can run into situations where the 
data was removed (or not removed) from the data table but was removed (or not 
removed) from the index. Or vice-versa. We also need to make sure that any 
ALTER TABLE SET TTL or ALTER TABLE SET KEEP_DELETED_CELLS statements propagate 
the properties to the indexes too.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-153) Implement TABLESAMPLE clause

[Julian Hyde (JIRA)]

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

Julian Hyde commented on PHOENIX-153:
-

Since Calcite already supports TABLESAMPLE let's save ourselves a headache and 
make sure that the 4.x syntax is compatible with Calcite's syntax.

> Implement TABLESAMPLE clause
> 
>
> Key: PHOENIX-153
> URL: https://issues.apache.org/jira/browse/PHOENIX-153
> Project: Phoenix
>  Issue Type: Task
>Reporter: James Taylor
>Assignee: Ethan Wang
>  Labels: enhancement
>
> Support the standard SQL TABLESAMPLE clause by implementing a filter that 
> uses a skip next hint based on the region boundaries of the table to only 
> return n rows per region.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (PHOENIX-3184) Wrap HBase calls in separate thread to reliably honor query timeout

[James Taylor (JIRA)]

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

James Taylor updated PHOENIX-3184:
--
Labels: SFDC  (was: )

> Wrap HBase calls in separate thread to reliably honor query timeout
> ---
>
> Key: PHOENIX-3184
> URL: https://issues.apache.org/jira/browse/PHOENIX-3184
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>  Labels: SFDC
>
> If an HBase call stalls, we don't detect and account for this in our query 
> execution time. To solve this, we can wrap any HBase calls that will make an 
> RPC and cancel the thread if our timeout expires. Almost all RPCs are 
> encapsulated in ConnectionQueryServicesImpl.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-1505) Support defining a VIEW over multiple tables

[James Taylor (JIRA)]

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

James Taylor commented on PHOENIX-1505:
---

Indexes on views are for updatable views that allow columns to be defined. It 
wouldn't make sense to add them on read-only views - not sure if that's 
enforced or not, but hopefully it is.

The complication in supporting other more complex queries in is tracking the 
multiple parents such that a {{CREATE VIEW v AS SELECT * ...}} projects all the 
right columns as the underlying tables metadata change. We have this ability in 
the way we represent this in our underlying Phoenix table, but the code likely 
makes some assumptions that a view has only a single parent. It's all very 
doable - we just need someone to volunteer to do the work.

> Support defining a VIEW over multiple tables
> 
>
> Key: PHOENIX-1505
> URL: https://issues.apache.org/jira/browse/PHOENIX-1505
> Project: Phoenix
>  Issue Type: Sub-task
>Reporter: James Taylor
>  Labels: SFDC
>
> Our current view implementation only supports views over a single table. We 
> should enhance this to support creating a view over multiple tables. For 
> example: CREATE VIEW v AS SELECT * FROM DEPT d, EMPL e WHERE d.dept_id = 
> e.dept_id



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3184) Wrap HBase calls in separate thread to reliably honor query timeout

[James Taylor (JIRA)]

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

James Taylor commented on PHOENIX-3184:
---

[~akshita.malhotra] - here's another timeout related issue, but of bigger scope.

> Wrap HBase calls in separate thread to reliably honor query timeout
> ---
>
> Key: PHOENIX-3184
> URL: https://issues.apache.org/jira/browse/PHOENIX-3184
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>
> If an HBase call stalls, we don't detect and account for this in our query 
> execution time. To solve this, we can wrap any HBase calls that will make an 
> RPC and cancel the thread if our timeout expires. Almost all RPCs are 
> encapsulated in ConnectionQueryServicesImpl.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (PHOENIX-3184) Wrap HBase calls in separate thread to reliably honor query timeout

[James Taylor (JIRA)]

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

James Taylor updated PHOENIX-3184:
--
Description: If an HBase call stalls, we don't detect and account for this 
in our query execution time. To solve this, we can wrap any HBase calls that 
will make an RPC and cancel the thread if our timeout expires. Almost all RPCs 
are encapsulated in ConnectionQueryServicesImpl.  (was: If an HBase call 
stalls, we don't detect and account for this in our query execution time. To 
solve this, we can wrap any HBase calls that will make an RPC and cancel the 
thread if our timeout expires.)

> Wrap HBase calls in separate thread to reliably honor query timeout
> ---
>
> Key: PHOENIX-3184
> URL: https://issues.apache.org/jira/browse/PHOENIX-3184
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>
> If an HBase call stalls, we don't detect and account for this in our query 
> execution time. To solve this, we can wrap any HBase calls that will make an 
> RPC and cancel the thread if our timeout expires. Almost all RPCs are 
> encapsulated in ConnectionQueryServicesImpl.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Re: Simple and complex views

[Julian Hyde]

Thanks, I've added some comments to
https://issues.apache.org/jira/browse/PHOENIX-1505.

I'd also like to understand why Phoenix needs indexes on views (most
DBMSs only allow indexes on base tables). We can cover that in the
same discussion.

On Fri, Jun 16, 2017 at 8:05 AM, James Taylor  wrote:
> Yes, that's the case today (documented here[1]). The two relevant JIRAs are
> PHOENIX-1505 and PHOENIX-1506. It'd be great if someone was interested in
> pursuing these. If so, let's discuss on the JIRAs.
>
> James
>
> [1] http://phoenix.apache.org/views.html#Limitations
>
> On Thu, Jun 15, 2017 at 9:20 PM Julian Hyde  wrote:
>
>> I was speaking with Rajesh and Ankit at DataWorks Summit today and they
>> said that Phoenix views have to be “simple”, that is, made up of only
>> SELECT … WHERE.
>>
>> Is that the case? If so, are there any plans to lift the restriction, and
>> allow views with, say, JOIN, UNION and GROUP BY?
>>
>> The more complex views would not allow inserts/upserts/deletes, because a
>> single row in the view would not necessarily correspond to a single row in
>> the underlying table, but nevertheless, complex views are extremely useful
>> for read-only queries. Calcite allows view expansion for complex views, and
>> even for views upon views, so it should be straightforward to do in 5.0.
>>
>> Julian
>>
>>

[jira] [Commented] (PHOENIX-1505) Support defining a VIEW over multiple tables

[Julian Hyde (JIRA)]

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

Julian Hyde commented on PHOENIX-1505:
--

In the Calcite branch, defining views and expanding them in queries is very 
straightforward. I suggest that we allow arbitrary SELECT statements in views 
(JOIN, UNION, GROUP BY, and even ORDER BY), so this could cover PHOENIX-1506 
and PHOENIX-1507 also.

The only wrinkle is DML. Complex views won't support DML, because there may not 
be a well-defined row underlying each output row. DML-capable views have an 
underlying table (or view, which must be DML-capable).

Calcite can figure out default expressions for all columns that are filtered & 
projected away by a DML-capable view. (This is by definition. If Calcite can't 
figure it out, the view is not considered DML-capable.)

Also, DML-incapable views do not allow schema extensions (the EXTEND clause).

In a project view that is DML-capable, some columns might not have base 
columns. For example, {{CREATE VIEW v AS SELECT empno, deptno, 10 as ten FROM 
Emp}}. You would not be able to specify {{ten}} in a DML statement.

I have see several JIRA cases talking about indexes on views. In the Calcite 
branch, do these provide any advantages over indexes on tables? If I'd defined 
an index, I would hope that my query would use it, and my DML statement would 
cause it to be modified, regardless of whether my statement references the 
table or the view.

> Support defining a VIEW over multiple tables
> 
>
> Key: PHOENIX-1505
> URL: https://issues.apache.org/jira/browse/PHOENIX-1505
> Project: Phoenix
>  Issue Type: Sub-task
>Reporter: James Taylor
>  Labels: SFDC
>
> Our current view implementation only supports views over a single table. We 
> should enhance this to support creating a view over multiple tables. For 
> example: CREATE VIEW v AS SELECT * FROM DEPT d, EMPL e WHERE d.dept_id = 
> e.dept_id



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Comment Edited] (PHOENIX-418) Support approximate COUNT DISTINCT

[Ethan Wang (JIRA)]

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

Ethan Wang edited comment on PHOENIX-418 at 6/16/17 10:27 PM:
--

Reply to [~pctony]: also in Blink DB, they have similar approximate aggregate 
feature which they use a sql clause. Example would be:
`select count(id) From table ERROR 0.1 CONFIDENCE 95%

http://blinkdb.org/#Performance


was (Author: aertoria):
Reply to [~pctony]: also in Blink DB, they have similar approximate aggregate 
feature which they use a sql clause. Example would be:
`select count(id) From table ERROR 0.1 CONFIDENCE 95%

> Support approximate COUNT DISTINCT
> --
>
> Key: PHOENIX-418
> URL: https://issues.apache.org/jira/browse/PHOENIX-418
> Project: Phoenix
>  Issue Type: Task
>Reporter: James Taylor
>Assignee: maghamravikiran
>  Labels: gsoc2016
>
> Support an "approximation" of count distinct to prevent having to hold on to 
> all distinct values (since this will not scale well when the number of 
> distinct values is huge). The Apache Drill folks have had some interesting 
> discussions on this 
> [here](http://mail-archives.apache.org/mod_mbox/incubator-drill-dev/201306.mbox/%3CJIRA.12650169.1369931282407.88049.1370645900553%40arcas%3E).
>  They recommend using  [Welford's 
> method](http://en.wikipedia.org/wiki/Algorithms_for_calculating_variance_Online_algorithm).
>  I'm open to having a config option that uses exact versus approximate. I 
> don't have experience implementing an approximate implementation, so I'm not 
> sure how much state is required to keep on the server and return to the 
> client (other than realizing it'd be much less that returning all distinct 
> values and their counts).



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-418) Support approximate COUNT DISTINCT

[Ethan Wang (JIRA)]

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

Ethan Wang commented on PHOENIX-418:


What is the current state of this jira?
@maghamravikiran
[~maghamraviki...@gmail.com]

> Support approximate COUNT DISTINCT
> --
>
> Key: PHOENIX-418
> URL: https://issues.apache.org/jira/browse/PHOENIX-418
> Project: Phoenix
>  Issue Type: Task
>Reporter: James Taylor
>Assignee: maghamravikiran
>  Labels: gsoc2016
>
> Support an "approximation" of count distinct to prevent having to hold on to 
> all distinct values (since this will not scale well when the number of 
> distinct values is huge). The Apache Drill folks have had some interesting 
> discussions on this 
> [here](http://mail-archives.apache.org/mod_mbox/incubator-drill-dev/201306.mbox/%3CJIRA.12650169.1369931282407.88049.1370645900553%40arcas%3E).
>  They recommend using  [Welford's 
> method](http://en.wikipedia.org/wiki/Algorithms_for_calculating_variance_Online_algorithm).
>  I'm open to having a config option that uses exact versus approximate. I 
> don't have experience implementing an approximate implementation, so I'm not 
> sure how much state is required to keep on the server and return to the 
> client (other than realizing it'd be much less that returning all distinct 
> values and their counts).



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-418) Support approximate COUNT DISTINCT

[Ethan Wang (JIRA)]

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

Ethan Wang commented on PHOENIX-418:


Reply to [~pctony]: also in Blink DB, they have similar approximate aggregate 
feature which they use a sql clause. Example would be:
`select count(id) From table ERROR 0.1 CONFIDENCE 95%

> Support approximate COUNT DISTINCT
> --
>
> Key: PHOENIX-418
> URL: https://issues.apache.org/jira/browse/PHOENIX-418
> Project: Phoenix
>  Issue Type: Task
>Reporter: James Taylor
>Assignee: maghamravikiran
>  Labels: gsoc2016
>
> Support an "approximation" of count distinct to prevent having to hold on to 
> all distinct values (since this will not scale well when the number of 
> distinct values is huge). The Apache Drill folks have had some interesting 
> discussions on this 
> [here](http://mail-archives.apache.org/mod_mbox/incubator-drill-dev/201306.mbox/%3CJIRA.12650169.1369931282407.88049.1370645900553%40arcas%3E).
>  They recommend using  [Welford's 
> method](http://en.wikipedia.org/wiki/Algorithms_for_calculating_variance_Online_algorithm).
>  I'm open to having a config option that uses exact versus approximate. I 
> don't have experience implementing an approximate implementation, so I'm not 
> sure how much state is required to keep on the server and return to the 
> client (other than realizing it'd be much less that returning all distinct 
> values and their counts).



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (PHOENIX-3954) Message displayed during "Alter view" is not accurate

[Ethan Wang (JIRA)]

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

Ethan Wang updated PHOENIX-3954:

Summary: Message displayed during "Alter view" is not accurate   (was: 
Message displayed during "Alter view" is not correct )

> Message displayed during "Alter view" is not accurate 
> --
>
> Key: PHOENIX-3954
> URL: https://issues.apache.org/jira/browse/PHOENIX-3954
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.8.0
> Environment: MacOS. Phoenix 4.8. Java 1.8
>Reporter: Ethan Wang
>Priority: Minor
>
> When "Alter view" command failed, the error message refer view as "TABLE." 
> example below:
> ALTER VIEW V_T02 ADD k3 VARCHAR PRIMARY KEY, k2 VARCHAR PRIMARY KEY, v2 
> INTEGER;
> Error: ERROR 514 (42892): A duplicate column name was detected in the object 
> definition or ALTER TABLE statement. columnName=V_T02.K2 
> (state=42892,code=514)
> org.apache.phoenix.schema.ColumnAlreadyExistsException: ERROR 514 (42892): A 
> duplicate column name was detected in the object definition or ALTER TABLE 
> statement. columnName=V_T02.K2
>   at 
> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:3557)
>   at 
> org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:3124)
>   at 
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableAddColumnStatement$1.execute(PhoenixStatement.java:1342)
>   at 
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:393)
>   at 
> org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:376)
>   at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>   at 
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:375)
>   at 
> org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:363)
>   at 
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1707)
>   at sqlline.Commands.execute(Commands.java:822)
>   at sqlline.Commands.sql(Commands.java:732)
>   at sqlline.SqlLine.dispatch(SqlLine.java:813)
>   at sqlline.SqlLine.begin(SqlLine.java:686)
>   at sqlline.SqlLine.start(SqlLine.java:398)
>   at sqlline.SqlLine.main(SqlLine.java:291)



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (PHOENIX-3954) Message displayed during "Alter view" is not correct

[Ethan Wang (JIRA)]

Ethan Wang created PHOENIX-3954:
---

 Summary: Message displayed during "Alter view" is not correct 
 Key: PHOENIX-3954
 URL: https://issues.apache.org/jira/browse/PHOENIX-3954
 Project: Phoenix
  Issue Type: Bug
Affects Versions: 4.8.0
 Environment: MacOS. Phoenix 4.8. Java 1.8
Reporter: Ethan Wang
Priority: Minor


When "Alter view" command failed, the error message refer view as "TABLE." 
example below:

ALTER VIEW V_T02 ADD k3 VARCHAR PRIMARY KEY, k2 VARCHAR PRIMARY KEY, v2 
INTEGER;


Error: ERROR 514 (42892): A duplicate column name was detected in the object 
definition or ALTER TABLE statement. columnName=V_T02.K2 
(state=42892,code=514)
org.apache.phoenix.schema.ColumnAlreadyExistsException: ERROR 514 (42892): A 
duplicate column name was detected in the object definition or ALTER TABLE 
statement. columnName=V_T02.K2
at 
org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:3557)
at 
org.apache.phoenix.schema.MetaDataClient.addColumn(MetaDataClient.java:3124)
at 
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableAddColumnStatement$1.execute(PhoenixStatement.java:1342)
at 
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:393)
at 
org.apache.phoenix.jdbc.PhoenixStatement$2.call(PhoenixStatement.java:376)
at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
at 
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:375)
at 
org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:363)
at 
org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1707)
at sqlline.Commands.execute(Commands.java:822)
at sqlline.Commands.sql(Commands.java:732)
at sqlline.SqlLine.dispatch(SqlLine.java:813)
at sqlline.SqlLine.begin(SqlLine.java:686)
at sqlline.SqlLine.start(SqlLine.java:398)
at sqlline.SqlLine.main(SqlLine.java:291)




--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (PHOENIX-3953) Clear INDEX_DISABLED_TIMESTAMP and disable index on compaction

[James Taylor (JIRA)]

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

James Taylor updated PHOENIX-3953:
--
Labels: globalMutableSecondaryIndex  (was: )

> Clear INDEX_DISABLED_TIMESTAMP and disable index on compaction
> --
>
> Key: PHOENIX-3953
> URL: https://issues.apache.org/jira/browse/PHOENIX-3953
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>  Labels: globalMutableSecondaryIndex
>
> To guard against a compaction occurring (which would potentially clear delete 
> markers and puts that the partial index rebuild process counts on to properly 
> catch up an index with the data table), we should clear the 
> INDEX_DISABLED_TIMESTAMP and mark the index as disabled. At this point, a 
> manual rebuild of the index would be required.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3953) Clear INDEX_DISABLED_TIMESTAMP and disable index on compaction

[James Taylor (JIRA)]

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

James Taylor commented on PHOENIX-3953:
---

FYI, [~samarthjain].

> Clear INDEX_DISABLED_TIMESTAMP and disable index on compaction
> --
>
> Key: PHOENIX-3953
> URL: https://issues.apache.org/jira/browse/PHOENIX-3953
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>
> To guard against a compaction occurring (which would potentially clear delete 
> markers and puts that the partial index rebuild process counts on to properly 
> catch up an index with the data table), we should clear the 
> INDEX_DISABLED_TIMESTAMP and mark the index as disabled. At this point, a 
> manual rebuild of the index would be required.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Created] (PHOENIX-3953) Clear INDEX_DISABLED_TIMESTAMP and disable index on compaction

[James Taylor (JIRA)]

James Taylor created PHOENIX-3953:
-

 Summary: Clear INDEX_DISABLED_TIMESTAMP and disable index on 
compaction
 Key: PHOENIX-3953
 URL: https://issues.apache.org/jira/browse/PHOENIX-3953
 Project: Phoenix
  Issue Type: Bug
Reporter: James Taylor


To guard against a compaction occurring (which would potentially clear delete 
markers and puts that the partial index rebuild process counts on to properly 
catch up an index with the data table), we should clear the 
INDEX_DISABLED_TIMESTAMP and mark the index as disabled. At this point, a 
manual rebuild of the index would be required.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (PHOENIX-3947) Increase scan time out for partial index rebuild

[James Taylor (JIRA)]

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

James Taylor updated PHOENIX-3947:
--
Labels: globalMutableSecondaryIndex  (was: indexImprovement)

> Increase scan time out for partial index rebuild
> 
>
> Key: PHOENIX-3947
> URL: https://issues.apache.org/jira/browse/PHOENIX-3947
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>  Labels: globalMutableSecondaryIndex
>
> The scan done from MetaDataRegionObserver needs to have a higher timeout so 
> that it can always complete successfully. If it times out, it'll start over 
> again which is not great.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (PHOENIX-3948) Enable shorter time outs for server-side index writes

[James Taylor (JIRA)]

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

James Taylor updated PHOENIX-3948:
--
Labels: globalMutableSecondaryIndex  (was: indexImprovement)

> Enable shorter time outs for server-side index writes
> -
>
> Key: PHOENIX-3948
> URL: https://issues.apache.org/jira/browse/PHOENIX-3948
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>  Labels: globalMutableSecondaryIndex
>
> The default timeouts are far too high for a RS->RS global index update as 
> we're holding on to a handler thread when the retries occur. We should be 
> able to set them to be inline with the client-side timeouts.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (PHOENIX-3949) Stop index maintenance until INDEX_DISABLED_TIMESTAMP is cleared

[James Taylor (JIRA)]

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

James Taylor updated PHOENIX-3949:
--
Labels: globalMutableSecondaryIndex  (was: indexImprovement)

> Stop index maintenance until INDEX_DISABLED_TIMESTAMP is cleared
> 
>
> Key: PHOENIX-3949
> URL: https://issues.apache.org/jira/browse/PHOENIX-3949
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>  Labels: globalMutableSecondaryIndex
>
> An index can be configured to remain online if a write failure occurs as our 
> partial index rebuilder will kick in upon failure and catch it up. However, 
> by keeping the index active, subsequent writes to the data table will likely 
> time out when they attempt to update the index given the current timeouts 
> (see PHOENIX-3948).
> An alternative would be to hold off on any index updates until the partial 
> index rebuilder has completed the catch up process and cleared the 
> INDEX_DISABLED_TIMESTAMP field. This creates a race condition with writes 
> that happen right as the INDEX_DISABLED_TIMESTAMP field is cleared. We could 
> run a catchup query on the partial index rebuild after the field is cleared 
> to ensure we find rows that were committed before the regular index 
> maintenance starts again.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Comment Edited] (PHOENIX-153) Implement TABLESAMPLE clause

[Ethan Wang (JIRA)]

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

Ethan Wang edited comment on PHOENIX-153 at 6/16/17 8:29 PM:
-

Spec of this patch. Feedback plz.
++
++Belows are SUPPORTED
++
===BASE CASE
select * from Person;
select * from PERSON TABLESAMPLE 0.45;

===WHERE CLAUSE
select * from PERSON where ADDRESS = 'CA' OR name>'tina3';
select * from PERSON TABLESAMPLE 0.49 where ADDRESS = 'CA' OR name>'tina3';
select * from PERSON TABLESAMPLE 0.49 where ADDRESS = 'CA' OR name>'tina3' 
LIMIT 1;

===Wired Table===
select * from LOCAL_ADDRESS TABLESAMPLE 0.79;
select * from SYSTEM.STATS TABLESAMPLE 0.41;

===CORNER CASE===
select * from PERSON TABLESAMPLE 0;
select * from PERSON TABLESAMPLE 1.45;
select * from PERSON TABLESAMPLE kko;

===AGGREGATION===
select count(*) from PERSON TABLESAMPLE 0.5 LIMIT 2
select count(*) from (select NAME from PERSON limit 20)

===SUB QUERY===
select * from (select /*+NO_INDEX*/ * from PERSON tablesample 0.1 where Name > 
'tina10') where ADDRESS = 'CA'

===JOINS===
select * from PERSON1, PERSON2 tablesample 0.7 where PERSON1.Name = PERSON2.NAME

===QUERY being OPTMIZED===
select * from PERSON tablesample 0.8 (goes to IDX_ADDRESS_PERSON index table, 
table sample carry on)

===INSERT SELECT
upsert into personbig(ID, ADDRESS) select id, address from personbig 
tablesample 0.01;


was (Author: aertoria):
Spec of this patch. Feedback plz.
++
++Belows are SUPPORTED
++
===BASE CASE
select * from Person;
select * from PERSON TABLESAMPLE 0.45;

===WHERE CLAUSE
select * from PERSON where ADDRESS = 'CA' OR name>'tina3';
select * from PERSON TABLESAMPLE 0.49 where ADDRESS = 'CA' OR name>'tina3';
select * from PERSON TABLESAMPLE 0.49 where ADDRESS = 'CA' OR name>'tina3' 
LIMIT 1;

===Wired Table===
select * from LOCAL_ADDRESS TABLESAMPLE 0.79;
select * from SYSTEM.STATS TABLESAMPLE 0.41;

===CORNER CASE===
select * from PERSON TABLESAMPLE 0;
select * from PERSON TABLESAMPLE 1.45;
select * from PERSON TABLESAMPLE kko;

===AGGREGATION===
select count( * ) from PERSON TABLESAMPLE 0.5 LIMIT 2
select count( * ) from (select NAME from PERSON limit 20)

===SUB QUERY===
select * from (select /*+NO_INDEX*/ * from PERSON tablesample 0.1 where Name > 
'tina10') where ADDRESS = 'CA'

===QUERY being OPTMIZED===
select * from PERSON tablesample 0.8 (goes to IDX_ADDRESS_PERSON index table, 
table sample carry on)



++
++belows are NOT SUPPORTED
++
===outter join not supporting===
select * from PERSON1, PERSON2 tablesample 0.7 where PERSON1.Name = PERSON2.NAME


> Implement TABLESAMPLE clause
> 
>
> Key: PHOENIX-153
> URL: https://issues.apache.org/jira/browse/PHOENIX-153
> Project: Phoenix
>  Issue Type: Task
>Reporter: James Taylor
>Assignee: Ethan Wang
>  Labels: enhancement
>
> Support the standard SQL TABLESAMPLE clause by implementing a filter that 
> uses a skip next hint based on the region boundaries of the table to only 
> return n rows per region.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Comment Edited] (PHOENIX-153) Implement TABLESAMPLE clause

[Ethan Wang (JIRA)]

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

Ethan Wang edited comment on PHOENIX-153 at 6/16/17 8:29 PM:
-

Spec of this patch. Feedback plz.
++
++Belows are SUPPORTED
++
===BASE CASE
select * from Person;
select * from PERSON TABLESAMPLE 0.45;

===WHERE CLAUSE
select * from PERSON where ADDRESS = 'CA' OR name>'tina3';
select * from PERSON TABLESAMPLE 0.49 where ADDRESS = 'CA' OR name>'tina3';
select * from PERSON TABLESAMPLE 0.49 where ADDRESS = 'CA' OR name>'tina3' 
LIMIT 1;

===Wired Table===
select * from LOCAL_ADDRESS TABLESAMPLE 0.79;
select * from SYSTEM.STATS TABLESAMPLE 0.41;

===CORNER CASE===
select * from PERSON TABLESAMPLE 0;
select * from PERSON TABLESAMPLE 1.45;
select * from PERSON TABLESAMPLE kko;

===AGGREGATION===
select count( * ) from PERSON TABLESAMPLE 0.5 LIMIT 2
select count( * ) from (select NAME from PERSON limit 20)

===SUB QUERY===
select * from (select /*+NO_INDEX*/ * from PERSON tablesample 0.1 where Name > 
'tina10') where ADDRESS = 'CA'

===JOINS===
select * from PERSON1, PERSON2 tablesample 0.7 where PERSON1.Name = PERSON2.NAME

===QUERY being OPTMIZED===
select * from PERSON tablesample 0.8 (goes to IDX_ADDRESS_PERSON index table, 
table sample carry on)

===INSERT SELECT
upsert into personbig(ID, ADDRESS) select id, address from personbig 
tablesample 0.01;


was (Author: aertoria):
Spec of this patch. Feedback plz.
++
++Belows are SUPPORTED
++
===BASE CASE
select * from Person;
select * from PERSON TABLESAMPLE 0.45;

===WHERE CLAUSE
select * from PERSON where ADDRESS = 'CA' OR name>'tina3';
select * from PERSON TABLESAMPLE 0.49 where ADDRESS = 'CA' OR name>'tina3';
select * from PERSON TABLESAMPLE 0.49 where ADDRESS = 'CA' OR name>'tina3' 
LIMIT 1;

===Wired Table===
select * from LOCAL_ADDRESS TABLESAMPLE 0.79;
select * from SYSTEM.STATS TABLESAMPLE 0.41;

===CORNER CASE===
select * from PERSON TABLESAMPLE 0;
select * from PERSON TABLESAMPLE 1.45;
select * from PERSON TABLESAMPLE kko;

===AGGREGATION===
select count(*) from PERSON TABLESAMPLE 0.5 LIMIT 2
select count(*) from (select NAME from PERSON limit 20)

===SUB QUERY===
select * from (select /*+NO_INDEX*/ * from PERSON tablesample 0.1 where Name > 
'tina10') where ADDRESS = 'CA'

===JOINS===
select * from PERSON1, PERSON2 tablesample 0.7 where PERSON1.Name = PERSON2.NAME

===QUERY being OPTMIZED===
select * from PERSON tablesample 0.8 (goes to IDX_ADDRESS_PERSON index table, 
table sample carry on)

===INSERT SELECT
upsert into personbig(ID, ADDRESS) select id, address from personbig 
tablesample 0.01;

> Implement TABLESAMPLE clause
> 
>
> Key: PHOENIX-153
> URL: https://issues.apache.org/jira/browse/PHOENIX-153
> Project: Phoenix
>  Issue Type: Task
>Reporter: James Taylor
>Assignee: Ethan Wang
>  Labels: enhancement
>
> Support the standard SQL TABLESAMPLE clause by implementing a filter that 
> uses a skip next hint based on the region boundaries of the table to only 
> return n rows per region.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[GitHub] phoenix issue #262: PHOENIX 153 implement TABLESAMPLE clause

[aertoria]

Github user aertoria commented on the issue:

https://github.com/apache/phoenix/pull/262
  
@JamesRTaylor @gjacoby126  @samarthjain  


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[GitHub] phoenix pull request #262: PHOENIX 153 implement TABLESAMPLE clause

[aertoria]

GitHub user aertoria opened a pull request:

https://github.com/apache/phoenix/pull/262

PHOENIX 153 implement TABLESAMPLE clause

table sampling on each Table basis (at the 'FROM' part of the query). 
Sample size decided by the input sampling rate applies on Primary Key's 
frequency.

Syntax: 
`select name from person SAMPLE(0.10) where name='ethan'`


++
++Belows are SUPPORTED
++
===BASE CASE
select * from Person;
select * from PERSON TABLESAMPLE 0.45;

===WHERE CLAUSE
select * from PERSON where ADDRESS = 'CA' OR name>'tina3';
select * from PERSON TABLESAMPLE 0.49 where ADDRESS = 'CA' OR name>'tina3';
select * from PERSON TABLESAMPLE 0.49 where ADDRESS = 'CA' OR name>'tina3' 
LIMIT 1;

===Wired Table===
select * from LOCAL_ADDRESS TABLESAMPLE 0.79;
select * from SYSTEM.STATS TABLESAMPLE 0.41;

===CORNER CASE===
select * from PERSON TABLESAMPLE 0;
select * from PERSON TABLESAMPLE 1.45;
select * from PERSON TABLESAMPLE kko;

===AGGREGATION===
select count(*) from PERSON TABLESAMPLE 0.5 LIMIT 2
select count(*) from (select NAME from PERSON limit 20)

===SUB QUERY===
select * from (select /*+NO_INDEX*/ * from PERSON tablesample 0.1 where 
Name > 'tina10') where ADDRESS = 'CA'

===JOINS===
select * from PERSON1, PERSON2 tablesample 0.7 where PERSON1.Name = 
PERSON2.NAME

===QUERY being OPTMIZED===
select * from PERSON tablesample 0.8 (goes to IDX_ADDRESS_PERSON index 
table, table sample carry on)

===INSERT SELECT
upsert into personbig(ID, ADDRESS) select id, address from personbig 
tablesample 0.01;


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

$ git pull https://github.com/aertoria/phoenix master

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

https://github.com/apache/phoenix/pull/262.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 #262


commit c872defc5cc3ef7f4d264b207d831ceb3a71ef2d
Author: aertoria 
Date:   2017-06-16T20:21:34Z

PHOENIX 153 implement TABLESAMPLE clause




---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[jira] [Commented] (PHOENIX-3942) Fix failing PhoenixMetricsIT test

[Hudson (JIRA)]

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

Hudson commented on PHOENIX-3942:
-

FAILURE: Integrated in Jenkins build Phoenix-master #1663 (See 
[https://builds.apache.org/job/Phoenix-master/1663/])
PHOENIX-3942 Fix failing PhoenixMetricsIT test (samarth: rev 
98db5d63bd3572328da6ba52ba53357f692c6222)
* (edit) 
phoenix-core/src/main/java/org/apache/phoenix/iterate/ScanningResultIterator.java


> Fix failing PhoenixMetricsIT test
> -
>
> Key: PHOENIX-3942
> URL: https://issues.apache.org/jira/browse/PHOENIX-3942
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>Assignee: Karan Mehta
> Attachments: PHOENIX-3942.001.patch, 
> PHOENIX-3942.4.x-HBase-1.1.001.patch, PHOENIX-3942.4.x-HBase-1.1.002.patch
>
>
> The PhoenixMetricsIT seems to be consistently failing in setup with the 
> following exception:
> {code}
> [ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.028 
> s <<< FAILURE! - in org.apache.phoenix.monitoring.PhoenixMetricsIT
> [ERROR] org.apache.phoenix.monitoring.PhoenixMetricsIT  Time elapsed: 0.025 s 
>  <<< ERROR!
> org.apache.phoenix.exception.PhoenixIOException: 
> java.lang.NullPointerException
> at 
> org.apache.phoenix.monitoring.PhoenixMetricsIT.doSetup(PhoenixMetricsIT.java:95)
> Caused by: java.util.concurrent.ExecutionException: 
> java.lang.NullPointerException
> at 
> org.apache.phoenix.monitoring.PhoenixMetricsIT.doSetup(PhoenixMetricsIT.java:95)
> Caused by: java.lang.NullPointerException
> {code}
> See 
> https://builds.apache.org/job/Phoenix-4.x-HBase-1.1/436/display/redirect?page=changes
>  for more information.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3928) Consider retrying once after any SQLException

[Thomas D'Silva (JIRA)]

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

Thomas D'Silva commented on PHOENIX-3928:
-

[~sukuna...@gmail.com], can you upload a patch with your changes?

> Consider retrying once after any SQLException
> -
>
> Key: PHOENIX-3928
> URL: https://issues.apache.org/jira/browse/PHOENIX-3928
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>Assignee: Maddineni Sukumar
> Fix For: 4.12.0
>
>
> There are more cases in which a retry would successfully execute than when a 
> MetaDataEntityNotFoundException. For example, certain error cases that depend 
> on the state of the metadata would work on retry if the metadata had changed. 
> We may want to retry on any SQLException and simply loop through the tables 
> involved (plan.getSourceRefs().iterator()), and if any meta data was updated, 
> go ahead and retry once.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3946) Update phoenix-spark to use 2.0.2 version

[Kanagha Pradha (JIRA)]

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

Kanagha Pradha commented on PHOENIX-3946:
-

Thanks [~jmahonin]. So, I see spark 2.0.2 version and scala 2.11 is already 
being set as default for HBase-4.10 release.

> Update phoenix-spark to use 2.0.2 version
> -
>
> Key: PHOENIX-3946
> URL: https://issues.apache.org/jira/browse/PHOENIX-3946
> Project: Phoenix
>  Issue Type: Task
>Affects Versions: 4.10.0
>Reporter: Kanagha Pradha
>Assignee: Kanagha Pradha
>
> Phoenix uses spark 1.6.1 currently. This needs to be updated to 2.0.2 version.
> It is already set up to use scala 2.10 version.
> [~mujtabachohan] [~jamestaylor]



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3654) Load Balancer for thin client

[ASF GitHub Bot (JIRA)]

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

ASF GitHub Bot commented on PHOENIX-3654:
-

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

https://github.com/apache/phoenix/pull/236#discussion_r122508226
  
--- Diff: 
phoenix-queryserver/src/main/java/org/apache/phoenix/queryserver/server/QueryServer.java
 ---
@@ -233,16 +240,29 @@ public int run(String[] args) throws Exception {
   // Build and start the HttpServer
   server = builder.build();
   server.start();
+  registerToServiceProvider(hostname);
   runningLatch.countDown();
   server.join();
   return 0;
 } catch (Throwable t) {
   LOG.fatal("Unrecoverable service error. Shutting down.", t);
   this.t = t;
   return -1;
+} finally {
+  deRegister();
 }
   }
 
+  private void registerToServiceProvider(String hostName) throws Exception 
{
+  PqsZookeeperConf pqsZookeeperConf = new 
PqsZookeeperConfImpl(getConf());
--- End diff --

Move any IT exercising loadbalancer functionality to the loadbalancer 
module :)

Tests in the queryserver module would have no load balancer implementation. 
PQS should pass gracefully in this case (e.g. not try to register with the 
loadbalancer because it would be null).


> Load Balancer for thin client
> -
>
> Key: PHOENIX-3654
> URL: https://issues.apache.org/jira/browse/PHOENIX-3654
> Project: Phoenix
>  Issue Type: New Feature
>Affects Versions: 4.8.0
> Environment: Linux 3.13.0-107-generic kernel, v4.9.0-HBase-0.98
>Reporter: Rahul Shrivastava
>Assignee: Rahul Shrivastava
> Fix For: 4.9.0
>
> Attachments: LoadBalancerDesign.pdf, Loadbalancer.patch
>
>   Original Estimate: 240h
>  Remaining Estimate: 240h
>
> We have been having internal discussion on load balancer for thin client for 
> PQS. The general consensus we have is to have an embedded load balancer with 
> the thin client instead of using external load balancer such as haproxy. The 
> idea is to not to have another layer between client and PQS. This reduces 
> operational cost for system, which currently leads to delay in executing 
> projects.
> But this also comes with challenge of having an embedded load balancer which 
> can maintain sticky sessions, do fair load balancing knowing the load 
> downstream of PQS server. In addition, load balancer needs to know location 
> of multiple PQS server. Now, the thin client needs to keep track of PQS 
> servers via zookeeper ( or other means). 
> In the new design, the client ( PQS client) , it is proposed to  have an 
> embedded load balancer.
> Where will the load Balancer sit ?
> The load load balancer will embedded within the app server client.  
> How will the load balancer work ? 
> Load balancer will contact zookeeper to get location of PQS. In this case, 
> PQS needs to register to ZK itself once it comes online. Zookeeper location 
> is in hbase-site.xml. It will maintain a small cache of connection to the 
> PQS. When a request comes in, it will check for an open connection from the 
> cache. 
> How will load balancer know load on PQS ?
> To start with, it will pick a random open connection to PQS. This means that 
> load balancer does not know PQS load. Later , we can augment the code so that 
> thin client can receive load info from PQS and make intelligent decisions.  
> How will load balancer maintain sticky sessions ?
> While we still need to investigate how to implement sticky sessions. We can 
> look for some open source implementation for the same.
> How will PQS register itself to service locator ?
> PQS will have location of zookeeper in hbase-site.xml and it would register 
> itself to the zookeeper. Thin client will find out PQS location using 
> zookeeper.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[GitHub] phoenix pull request #236: PHOENIX-3654 Load Balancer for thin client

[joshelser]

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

https://github.com/apache/phoenix/pull/236#discussion_r122508226
  
--- Diff: 
phoenix-queryserver/src/main/java/org/apache/phoenix/queryserver/server/QueryServer.java
 ---
@@ -233,16 +240,29 @@ public int run(String[] args) throws Exception {
   // Build and start the HttpServer
   server = builder.build();
   server.start();
+  registerToServiceProvider(hostname);
   runningLatch.countDown();
   server.join();
   return 0;
 } catch (Throwable t) {
   LOG.fatal("Unrecoverable service error. Shutting down.", t);
   this.t = t;
   return -1;
+} finally {
+  deRegister();
 }
   }
 
+  private void registerToServiceProvider(String hostName) throws Exception 
{
+  PqsZookeeperConf pqsZookeeperConf = new 
PqsZookeeperConfImpl(getConf());
--- End diff --

Move any IT exercising loadbalancer functionality to the loadbalancer 
module :)

Tests in the queryserver module would have no load balancer implementation. 
PQS should pass gracefully in this case (e.g. not try to register with the 
loadbalancer because it would be null).


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[jira] [Commented] (PHOENIX-3654) Load Balancer for thin client

[ASF GitHub Bot (JIRA)]

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

ASF GitHub Bot commented on PHOENIX-3654:
-

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

https://github.com/apache/phoenix/pull/236#discussion_r122505939
  
--- Diff: 
phoenix-queryserver/src/main/java/org/apache/phoenix/queryserver/server/QueryServer.java
 ---
@@ -233,16 +240,29 @@ public int run(String[] args) throws Exception {
   // Build and start the HttpServer
   server = builder.build();
   server.start();
+  registerToServiceProvider(hostname);
   runningLatch.countDown();
   server.join();
   return 0;
 } catch (Throwable t) {
   LOG.fatal("Unrecoverable service error. Shutting down.", t);
   this.t = t;
   return -1;
+} finally {
+  deRegister();
 }
   }
 
+  private void registerToServiceProvider(String hostName) throws Exception 
{
+  PqsZookeeperConf pqsZookeeperConf = new 
PqsZookeeperConfImpl(getConf());
--- End diff --

Question: How to make the IT test pass for Queryserver? In the IT tests, 
the queryserver loads up and registers itself to a ZKTestingServer . This uses 
curator client which is part of Registry class implementation. Now, this 
implementation is part of loadbalancer module. One way is to provide a mocked 
out implementation of Registry and LoadBalancerConf in the queryserver module. 
Do you think something else from your experience ?


> Load Balancer for thin client
> -
>
> Key: PHOENIX-3654
> URL: https://issues.apache.org/jira/browse/PHOENIX-3654
> Project: Phoenix
>  Issue Type: New Feature
>Affects Versions: 4.8.0
> Environment: Linux 3.13.0-107-generic kernel, v4.9.0-HBase-0.98
>Reporter: Rahul Shrivastava
>Assignee: Rahul Shrivastava
> Fix For: 4.9.0
>
> Attachments: LoadBalancerDesign.pdf, Loadbalancer.patch
>
>   Original Estimate: 240h
>  Remaining Estimate: 240h
>
> We have been having internal discussion on load balancer for thin client for 
> PQS. The general consensus we have is to have an embedded load balancer with 
> the thin client instead of using external load balancer such as haproxy. The 
> idea is to not to have another layer between client and PQS. This reduces 
> operational cost for system, which currently leads to delay in executing 
> projects.
> But this also comes with challenge of having an embedded load balancer which 
> can maintain sticky sessions, do fair load balancing knowing the load 
> downstream of PQS server. In addition, load balancer needs to know location 
> of multiple PQS server. Now, the thin client needs to keep track of PQS 
> servers via zookeeper ( or other means). 
> In the new design, the client ( PQS client) , it is proposed to  have an 
> embedded load balancer.
> Where will the load Balancer sit ?
> The load load balancer will embedded within the app server client.  
> How will the load balancer work ? 
> Load balancer will contact zookeeper to get location of PQS. In this case, 
> PQS needs to register to ZK itself once it comes online. Zookeeper location 
> is in hbase-site.xml. It will maintain a small cache of connection to the 
> PQS. When a request comes in, it will check for an open connection from the 
> cache. 
> How will load balancer know load on PQS ?
> To start with, it will pick a random open connection to PQS. This means that 
> load balancer does not know PQS load. Later , we can augment the code so that 
> thin client can receive load info from PQS and make intelligent decisions.  
> How will load balancer maintain sticky sessions ?
> While we still need to investigate how to implement sticky sessions. We can 
> look for some open source implementation for the same.
> How will PQS register itself to service locator ?
> PQS will have location of zookeeper in hbase-site.xml and it would register 
> itself to the zookeeper. Thin client will find out PQS location using 
> zookeeper.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[GitHub] phoenix pull request #236: PHOENIX-3654 Load Balancer for thin client

[rahulsIOT]

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

https://github.com/apache/phoenix/pull/236#discussion_r122505939
  
--- Diff: 
phoenix-queryserver/src/main/java/org/apache/phoenix/queryserver/server/QueryServer.java
 ---
@@ -233,16 +240,29 @@ public int run(String[] args) throws Exception {
   // Build and start the HttpServer
   server = builder.build();
   server.start();
+  registerToServiceProvider(hostname);
   runningLatch.countDown();
   server.join();
   return 0;
 } catch (Throwable t) {
   LOG.fatal("Unrecoverable service error. Shutting down.", t);
   this.t = t;
   return -1;
+} finally {
+  deRegister();
 }
   }
 
+  private void registerToServiceProvider(String hostName) throws Exception 
{
+  PqsZookeeperConf pqsZookeeperConf = new 
PqsZookeeperConfImpl(getConf());
--- End diff --

Question: How to make the IT test pass for Queryserver? In the IT tests, 
the queryserver loads up and registers itself to a ZKTestingServer . This uses 
curator client which is part of Registry class implementation. Now, this 
implementation is part of loadbalancer module. One way is to provide a mocked 
out implementation of Registry and LoadBalancerConf in the queryserver module. 
Do you think something else from your experience ?


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastruct...@apache.org or file a JIRA ticket
with INFRA.
---

[jira] [Commented] (PHOENIX-3940) NullPointerException on PERCENTILE_CONT over an empty result set

[Hudson (JIRA)]

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

Hudson commented on PHOENIX-3940:
-

FAILURE: Integrated in Jenkins build Phoenix-master #1662 (See 
[https://builds.apache.org/job/Phoenix-master/1662/])
PHOENIX-3940 Handle PERCENTILE_CONT against no rows (elserj: rev 
64121a3c403a3c5206174b33b3c8762d530279f0)
* (edit) phoenix-core/src/it/java/org/apache/phoenix/end2end/PercentileIT.java
* (edit) 
phoenix-core/src/main/java/org/apache/phoenix/expression/aggregator/PercentileClientAggregator.java


> NullPointerException on PERCENTILE_CONT over an empty result set
> 
>
> Key: PHOENIX-3940
> URL: https://issues.apache.org/jira/browse/PHOENIX-3940
> Project: Phoenix
>  Issue Type: Bug
>Reporter: Josh Elser
>Assignee: Josh Elser
>Priority: Minor
> Fix For: 4.12.0
>
> Attachments: PHOENIX-3940.001.patch, PHOENIX-3940.002.patch
>
>
> A user reported that when running a query with {{PERCENTILE_CONT}} where the 
> selection has no results (e.g. nothing to compute the P_C on), a 
> NullPointerException is thrown.
> Glancing at the code, it can handle the case where there is only one value, 
> but handling the case where there are no values fails.
> Reading over Oracle and SqlServer docs, it doesn't saw what "should" happen 
> in these cases. Is "0" a sane value? Or is an exception actually a good idea?
> https://docs.microsoft.com/en-us/sql/t-sql/functions/percentile-cont-transact-sql
> https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions115.htm#SQLRF00687



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Updated] (PHOENIX-3942) Fix failing PhoenixMetricsIT test

[Karan Mehta (JIRA)]

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

Karan Mehta updated PHOENIX-3942:
-
Attachment: PHOENIX-3942.001.patch

Thanks [~samarthjain] for the review.
Patch applies to master as well as 4.x-HBase-1.2


> Fix failing PhoenixMetricsIT test
> -
>
> Key: PHOENIX-3942
> URL: https://issues.apache.org/jira/browse/PHOENIX-3942
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>Assignee: Karan Mehta
> Attachments: PHOENIX-3942.001.patch, 
> PHOENIX-3942.4.x-HBase-1.1.001.patch, PHOENIX-3942.4.x-HBase-1.1.002.patch
>
>
> The PhoenixMetricsIT seems to be consistently failing in setup with the 
> following exception:
> {code}
> [ERROR] Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.028 
> s <<< FAILURE! - in org.apache.phoenix.monitoring.PhoenixMetricsIT
> [ERROR] org.apache.phoenix.monitoring.PhoenixMetricsIT  Time elapsed: 0.025 s 
>  <<< ERROR!
> org.apache.phoenix.exception.PhoenixIOException: 
> java.lang.NullPointerException
> at 
> org.apache.phoenix.monitoring.PhoenixMetricsIT.doSetup(PhoenixMetricsIT.java:95)
> Caused by: java.util.concurrent.ExecutionException: 
> java.lang.NullPointerException
> at 
> org.apache.phoenix.monitoring.PhoenixMetricsIT.doSetup(PhoenixMetricsIT.java:95)
> Caused by: java.lang.NullPointerException
> {code}
> See 
> https://builds.apache.org/job/Phoenix-4.x-HBase-1.1/436/display/redirect?page=changes
>  for more information.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3757) System mutex table not being created in SYSTEM namespace when namespace mapping is enabled

[Josh Elser (JIRA)]

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

Josh Elser commented on PHOENIX-3757:
-

bq. Was this committed, Josh Elser and if not should it be?

No, I never got back around to Samarth's and Ankit's comments. Will try to get 
it updated today.

> System mutex table not being created in SYSTEM namespace when namespace 
> mapping is enabled
> --
>
> Key: PHOENIX-3757
> URL: https://issues.apache.org/jira/browse/PHOENIX-3757
> Project: Phoenix
>  Issue Type: Bug
>Reporter: Josh Elser
>Assignee: Josh Elser
>Priority: Critical
> Fix For: 4.12.0
>
> Attachments: PHOENIX-3757.001.patch, PHOENIX-3757.002.patch
>
>
> Noticed this issue while writing a test for PHOENIX-3756:
> The SYSTEM.MUTEX table is always created in the default namespace, even when 
> {{phoenix.schema.isNamespaceMappingEnabled=true}}. At a glance, it looks like 
> the logic for the other system tables isn't applied to the mutex table.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3952) "Ambiguous or non-equi join condition specified" Exception thrown for usage of OR expression in join conditions

[James Taylor (JIRA)]

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

James Taylor commented on PHOENIX-3952:
---

Your can try rewriting the query using UNION instead of OR conditions in your 
ON clause.

> "Ambiguous or non-equi join condition specified" Exception thrown for usage 
> of  OR expression in join conditions
> 
>
> Key: PHOENIX-3952
> URL: https://issues.apache.org/jira/browse/PHOENIX-3952
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.8.1
> Environment: HBase v-1.2.4 running on CentOS 6.0, Phoenix v-4.8.1, 
> Squirrel v-3.7 running on Windows 10
>Reporter: Aman Jha
>
> If I'm joining two tables, say inner join, on an OR based condition, then the 
> following exception is thrown in Squirrel: 
> {color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
> specified. Consider using table list with where clause.
> SQLState:  22017
> ErrorCode: 217{color}
> +*TEST CASE :*+
> Create the following tables and entries : 
> {code:java}
> CREATE TABLE IF NOT EXISTS CBL 
> ( 
>   COM_CODE VARCHAR NOT NULL , 
>   BU_CODE VARCHAR NOT NULL , 
>   LOC_CODE VARCHAR NOT NULL 
>   CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
> );
> CREATE TABLE IF NOT EXISTS PO_TEST
> (
>PO_ID VARCHAR PRIMARY KEY,
>BU_ID VARCHAR ,
>PO_NAME VARCHAR,
>C_ID VARCHAR,
>LOC_ID VARCHAR
> );
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
> 'Devcast', 'C3', 'B4', 'L5');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
> 'Thought', 'C2', 'B1', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
> 'Jabber', 'C3', 'B4', 'L1');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
> 'Yakijo', 'C3', 'B8', 'L9');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
> 'Youfeed', 'C2', 'B3', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 
> 'Jayo', 'C2', 'B1', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
> 'Trilia', 'C1', 'B10', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
> 'Podcat', 'C1', 'B3', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
> 'Twitter', 'C1', 'B5', 'L8');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 
> 'DabZ', 'C1', 'B8', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 
> 'DabZ', 'C1', 'B1', 'L10');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
> {code}
> +*Run the following query :*+
> {code:java}
> SELECT * FROM po_test INNER JOIN cbl ON
> (
>( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
>AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
>AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
> );
> {code}
> +*Expected O/P :*+
> {noformat}
> 1 Devcast C3  B4  L5  C3  *   *
> 3 Jabber  C3  B4  L1  C3  *   *
> 4 Yakijo  C3  B8  L9  C3  *   *
> 11DabZC1  B1  L10 C1  B1  *
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Comment Edited] (PHOENIX-3952) "Ambiguous or non-equi join condition specified" Exception thrown for usage of OR expression in join conditions

[Aman Jha (JIRA)]

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

Aman Jha edited comment on PHOENIX-3952 at 6/16/17 4:12 PM:


[~jamestaylor] Still getting the same error using the suggested solution. Is 
there any thing else I can trying out? 


was (Author: aman@zycus.com):
[~jamestaylor] still getting the same error using the suggested solution

> "Ambiguous or non-equi join condition specified" Exception thrown for usage 
> of  OR expression in join conditions
> 
>
> Key: PHOENIX-3952
> URL: https://issues.apache.org/jira/browse/PHOENIX-3952
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.8.1
> Environment: HBase v-1.2.4 running on CentOS 6.0, Phoenix v-4.8.1, 
> Squirrel v-3.7 running on Windows 10
>Reporter: Aman Jha
>
> If I'm joining two tables, say inner join, on an OR based condition, then the 
> following exception is thrown in Squirrel: 
> {color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
> specified. Consider using table list with where clause.
> SQLState:  22017
> ErrorCode: 217{color}
> +*TEST CASE :*+
> Create the following tables and entries : 
> {code:java}
> CREATE TABLE IF NOT EXISTS CBL 
> ( 
>   COM_CODE VARCHAR NOT NULL , 
>   BU_CODE VARCHAR NOT NULL , 
>   LOC_CODE VARCHAR NOT NULL 
>   CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
> );
> CREATE TABLE IF NOT EXISTS PO_TEST
> (
>PO_ID VARCHAR PRIMARY KEY,
>BU_ID VARCHAR ,
>PO_NAME VARCHAR,
>C_ID VARCHAR,
>LOC_ID VARCHAR
> );
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
> 'Devcast', 'C3', 'B4', 'L5');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
> 'Thought', 'C2', 'B1', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
> 'Jabber', 'C3', 'B4', 'L1');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
> 'Yakijo', 'C3', 'B8', 'L9');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
> 'Youfeed', 'C2', 'B3', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 
> 'Jayo', 'C2', 'B1', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
> 'Trilia', 'C1', 'B10', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
> 'Podcat', 'C1', 'B3', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
> 'Twitter', 'C1', 'B5', 'L8');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 
> 'DabZ', 'C1', 'B8', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 
> 'DabZ', 'C1', 'B1', 'L10');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
> {code}
> +*Run the following query :*+
> {code:java}
> SELECT * FROM po_test INNER JOIN cbl ON
> (
>( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
>AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
>AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
> );
> {code}
> +*Expected O/P :*+
> {noformat}
> 1 Devcast C3  B4  L5  C3  *   *
> 3 Jabber  C3  B4  L1  C3  *   *
> 4 Yakijo  C3  B8  L9  C3  *   *
> 11DabZC1  B1  L10 C1  B1  *
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Comment Edited] (PHOENIX-3952) "Ambiguous or non-equi join condition specified" Exception thrown for usage of OR expression in join conditions

[Aman Jha (JIRA)]

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

Aman Jha edited comment on PHOENIX-3952 at 6/16/17 4:12 PM:


[~jamestaylor] Still getting the same error using the suggested solution. Is 
there any thing else I can try out? 


was (Author: aman@zycus.com):
[~jamestaylor] Still getting the same error using the suggested solution. Is 
there any thing else I can trying out? 

> "Ambiguous or non-equi join condition specified" Exception thrown for usage 
> of  OR expression in join conditions
> 
>
> Key: PHOENIX-3952
> URL: https://issues.apache.org/jira/browse/PHOENIX-3952
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.8.1
> Environment: HBase v-1.2.4 running on CentOS 6.0, Phoenix v-4.8.1, 
> Squirrel v-3.7 running on Windows 10
>Reporter: Aman Jha
>
> If I'm joining two tables, say inner join, on an OR based condition, then the 
> following exception is thrown in Squirrel: 
> {color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
> specified. Consider using table list with where clause.
> SQLState:  22017
> ErrorCode: 217{color}
> +*TEST CASE :*+
> Create the following tables and entries : 
> {code:java}
> CREATE TABLE IF NOT EXISTS CBL 
> ( 
>   COM_CODE VARCHAR NOT NULL , 
>   BU_CODE VARCHAR NOT NULL , 
>   LOC_CODE VARCHAR NOT NULL 
>   CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
> );
> CREATE TABLE IF NOT EXISTS PO_TEST
> (
>PO_ID VARCHAR PRIMARY KEY,
>BU_ID VARCHAR ,
>PO_NAME VARCHAR,
>C_ID VARCHAR,
>LOC_ID VARCHAR
> );
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
> 'Devcast', 'C3', 'B4', 'L5');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
> 'Thought', 'C2', 'B1', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
> 'Jabber', 'C3', 'B4', 'L1');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
> 'Yakijo', 'C3', 'B8', 'L9');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
> 'Youfeed', 'C2', 'B3', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 
> 'Jayo', 'C2', 'B1', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
> 'Trilia', 'C1', 'B10', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
> 'Podcat', 'C1', 'B3', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
> 'Twitter', 'C1', 'B5', 'L8');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 
> 'DabZ', 'C1', 'B8', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 
> 'DabZ', 'C1', 'B1', 'L10');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
> {code}
> +*Run the following query :*+
> {code:java}
> SELECT * FROM po_test INNER JOIN cbl ON
> (
>( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
>AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
>AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
> );
> {code}
> +*Expected O/P :*+
> {noformat}
> 1 Devcast C3  B4  L5  C3  *   *
> 3 Jabber  C3  B4  L1  C3  *   *
> 4 Yakijo  C3  B8  L9  C3  *   *
> 11DabZC1  B1  L10 C1  B1  *
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3952) "Ambiguous or non-equi join condition specified" Exception thrown for usage of OR expression in join conditions

[Aman Jha (JIRA)]

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

Aman Jha commented on PHOENIX-3952:
---

[~jamestaylor] still getting the same error using the suggested solution

> "Ambiguous or non-equi join condition specified" Exception thrown for usage 
> of  OR expression in join conditions
> 
>
> Key: PHOENIX-3952
> URL: https://issues.apache.org/jira/browse/PHOENIX-3952
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.8.1
> Environment: HBase v-1.2.4 running on CentOS 6.0, Phoenix v-4.8.1, 
> Squirrel v-3.7 running on Windows 10
>Reporter: Aman Jha
>
> If I'm joining two tables, say inner join, on an OR based condition, then the 
> following exception is thrown in Squirrel: 
> {color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
> specified. Consider using table list with where clause.
> SQLState:  22017
> ErrorCode: 217{color}
> +*TEST CASE :*+
> Create the following tables and entries : 
> {code:java}
> CREATE TABLE IF NOT EXISTS CBL 
> ( 
>   COM_CODE VARCHAR NOT NULL , 
>   BU_CODE VARCHAR NOT NULL , 
>   LOC_CODE VARCHAR NOT NULL 
>   CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
> );
> CREATE TABLE IF NOT EXISTS PO_TEST
> (
>PO_ID VARCHAR PRIMARY KEY,
>BU_ID VARCHAR ,
>PO_NAME VARCHAR,
>C_ID VARCHAR,
>LOC_ID VARCHAR
> );
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
> 'Devcast', 'C3', 'B4', 'L5');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
> 'Thought', 'C2', 'B1', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
> 'Jabber', 'C3', 'B4', 'L1');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
> 'Yakijo', 'C3', 'B8', 'L9');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
> 'Youfeed', 'C2', 'B3', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 
> 'Jayo', 'C2', 'B1', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
> 'Trilia', 'C1', 'B10', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
> 'Podcat', 'C1', 'B3', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
> 'Twitter', 'C1', 'B5', 'L8');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 
> 'DabZ', 'C1', 'B8', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 
> 'DabZ', 'C1', 'B1', 'L10');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
> {code}
> +*Run the following query :*+
> {code:java}
> SELECT * FROM po_test INNER JOIN cbl ON
> (
>( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
>AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
>AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
> );
> {code}
> +*Expected O/P :*+
> {noformat}
> 1 Devcast C3  B4  L5  C3  *   *
> 3 Jabber  C3  B4  L1  C3  *   *
> 4 Yakijo  C3  B8  L9  C3  *   *
> 11DabZC1  B1  L10 C1  B1  *
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3952) "Ambiguous or non-equi join condition specified" Exception thrown for usage of OR expression in join conditions

[James Taylor (JIRA)]

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

James Taylor commented on PHOENIX-3952:
---

That's a known limitation of hash joins - only equi joins are supported. You 
can try using the USE_SORT_MERGE_JOIN hint like this:
{code}
SELECT /*+ USE_SORT_MERGE_JOIN */ * FROM po_test INNER JOIN cbl ON
(
   ( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
   AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
   AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
);
{code}

> "Ambiguous or non-equi join condition specified" Exception thrown for usage 
> of  OR expression in join conditions
> 
>
> Key: PHOENIX-3952
> URL: https://issues.apache.org/jira/browse/PHOENIX-3952
> Project: Phoenix
>  Issue Type: Bug
>Affects Versions: 4.8.1
> Environment: HBase v-1.2.4 running on CentOS 6.0, Phoenix v-4.8.1, 
> Squirrel v-3.7 running on Windows 10
>Reporter: Aman Jha
>
> If I'm joining two tables, say inner join, on an OR based condition, then the 
> following exception is thrown in Squirrel: 
> {color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
> specified. Consider using table list with where clause.
> SQLState:  22017
> ErrorCode: 217{color}
> +*TEST CASE :*+
> Create the following tables and entries : 
> {code:java}
> CREATE TABLE IF NOT EXISTS CBL 
> ( 
>   COM_CODE VARCHAR NOT NULL , 
>   BU_CODE VARCHAR NOT NULL , 
>   LOC_CODE VARCHAR NOT NULL 
>   CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
> );
> CREATE TABLE IF NOT EXISTS PO_TEST
> (
>PO_ID VARCHAR PRIMARY KEY,
>BU_ID VARCHAR ,
>PO_NAME VARCHAR,
>C_ID VARCHAR,
>LOC_ID VARCHAR
> );
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
> 'Devcast', 'C3', 'B4', 'L5');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
> 'Thought', 'C2', 'B1', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
> 'Jabber', 'C3', 'B4', 'L1');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
> 'Yakijo', 'C3', 'B8', 'L9');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
> 'Youfeed', 'C2', 'B3', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 
> 'Jayo', 'C2', 'B1', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
> 'Trilia', 'C1', 'B10', 'L2');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
> 'Podcat', 'C1', 'B3', 'L10');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
> 'Twitter', 'C1', 'B5', 'L8');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 
> 'DabZ', 'C1', 'B8', 'L4');
> upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 
> 'DabZ', 'C1', 'B1', 'L10');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
> upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
> {code}
> +*Run the following query :*+
> {code:java}
> SELECT * FROM po_test INNER JOIN cbl ON
> (
>( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
>AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
>AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
> );
> {code}
> +*Expected O/P :*+
> {noformat}
> 1 Devcast C3  B4  L5  C3  *   *
> 3 Jabber  C3  B4  L1  C3  *   *
> 4 Yakijo  C3  B8  L9  C3  *   *
> 11DabZC1  B1  L10 C1  B1  *
> {noformat}



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Re: Simple and complex views

[James Taylor]

Yes, that's the case today (documented here[1]). The two relevant JIRAs are
PHOENIX-1505 and PHOENIX-1506. It'd be great if someone was interested in
pursuing these. If so, let's discuss on the JIRAs.

James

[1] http://phoenix.apache.org/views.html#Limitations

On Thu, Jun 15, 2017 at 9:20 PM Julian Hyde  wrote:

> I was speaking with Rajesh and Ankit at DataWorks Summit today and they
> said that Phoenix views have to be “simple”, that is, made up of only
> SELECT … WHERE.
>
> Is that the case? If so, are there any plans to lift the restriction, and
> allow views with, say, JOIN, UNION and GROUP BY?
>
> The more complex views would not allow inserts/upserts/deletes, because a
> single row in the view would not necessarily correspond to a single row in
> the underlying table, but nevertheless, complex views are extremely useful
> for read-only queries. Calcite allows view expansion for complex views, and
> even for views upon views, so it should be straightforward to do in 5.0.
>
> Julian
>
>

[jira] [Updated] (PHOENIX-3952) "Ambiguous or non-equi join condition specified" Exception thrown for usage of OR expression in join conditions

[Aman Jha (JIRA)]

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

Aman Jha updated PHOENIX-3952:
--
Description: 
If I'm joining two tables, say inner join, on an OR based condition, then the 
following exception is thrown in Squirrel: 
{color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
specified. Consider using table list with where clause.
SQLState:  22017
ErrorCode: 217{color}

+*TEST CASE :*+
Create the following tables and entries : 
{code:java}
CREATE TABLE IF NOT EXISTS CBL 
( 
COM_CODE VARCHAR NOT NULL , 
BU_CODE VARCHAR NOT NULL , 
LOC_CODE VARCHAR NOT NULL 
CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
);
CREATE TABLE IF NOT EXISTS PO_TEST
(
   PO_ID VARCHAR PRIMARY KEY,
   BU_ID VARCHAR ,
   PO_NAME VARCHAR,
   C_ID VARCHAR,
   LOC_ID VARCHAR
);

upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
'Devcast', 'C3', 'B4', 'L5');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
'Thought', 'C2', 'B1', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
'Jabber', 'C3', 'B4', 'L1');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
'Yakijo', 'C3', 'B8', 'L9');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
'Youfeed', 'C2', 'B3', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 'Jayo', 
'C2', 'B1', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
'Trilia', 'C1', 'B10', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
'Podcat', 'C1', 'B3', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
'Twitter', 'C1', 'B5', 'L8');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 'DabZ', 
'C1', 'B8', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 'DabZ', 
'C1', 'B1', 'L10');

upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
{code}

+*Run the following query :*+

{code:java}
SELECT * FROM po_test INNER JOIN cbl ON
(
   ( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
   AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
   AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
);
{code}

+*Expected O/P :*+

{noformat}

1   Devcast C3  B4  L5  C3  *   *
3   Jabber  C3  B4  L1  C3  *   *
4   Yakijo  C3  B8  L9  C3  *   *
11  DabZC1  B1  L10 C1  B1  *
{noformat}





  was:
If I'm joining two tables, say inner join, on an OR based condition, then the 
following exception is thrown in Squirrel: 
{color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
specified. Consider using table list with where clause.
SQLState:  22017
ErrorCode: 217{color}

+*TEST CASE :*+
Create the following tables : 
{code:java}
CREATE TABLE IF NOT EXISTS CBL 
( 
COM_CODE VARCHAR NOT NULL , 
BU_CODE VARCHAR NOT NULL , 
LOC_CODE VARCHAR NOT NULL 
CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
);
CREATE TABLE IF NOT EXISTS PO_TEST
(
   PO_ID VARCHAR PRIMARY KEY,
   BU_ID VARCHAR ,
   PO_NAME VARCHAR,
   C_ID VARCHAR,
   LOC_ID VARCHAR
);
{code}
Make following entries : 
{code:java}
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
'Devcast', 'C3', 'B4', 'L5');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
'Thought', 'C2', 'B1', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
'Jabber', 'C3', 'B4', 'L1');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
'Yakijo', 'C3', 'B8', 'L9');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
'Youfeed', 'C2', 'B3', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 'Jayo', 
'C2', 'B1', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
'Trilia', 'C1', 'B10', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
'Podcat', 'C1', 'B3', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
'Twitter', 'C1', 'B5', 'L8');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 'DabZ', 
'C1', 'B8', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 'DabZ', 
'C1', 'B1', 'L10');

upsert into CBL 

[jira] [Updated] (PHOENIX-3952) "Ambiguous or non-equi join condition specified" Exception thrown for usage of OR expression in join conditions

[Aman Jha (JIRA)]

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

Aman Jha updated PHOENIX-3952:
--
Description: 
If I'm joining two tables, say inner join, on an OR based condition, then the 
following exception is thrown in Squirrel: 
{color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
specified. Consider using table list with where clause.
SQLState:  22017
ErrorCode: 217{color}

+*TEST CASE :*+
Create the following tables : 
{code:java}
CREATE TABLE IF NOT EXISTS CBL 
( 
COM_CODE VARCHAR NOT NULL , 
BU_CODE VARCHAR NOT NULL , 
LOC_CODE VARCHAR NOT NULL 
CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
);
CREATE TABLE IF NOT EXISTS PO_TEST
(
   PO_ID VARCHAR PRIMARY KEY,
   BU_ID VARCHAR ,
   PO_NAME VARCHAR,
   C_ID VARCHAR,
   LOC_ID VARCHAR
);
{code}
Make following entries : 
{code:java}
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
'Devcast', 'C3', 'B4', 'L5');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
'Thought', 'C2', 'B1', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
'Jabber', 'C3', 'B4', 'L1');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
'Yakijo', 'C3', 'B8', 'L9');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
'Youfeed', 'C2', 'B3', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 'Jayo', 
'C2', 'B1', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
'Trilia', 'C1', 'B10', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
'Podcat', 'C1', 'B3', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
'Twitter', 'C1', 'B5', 'L8');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 'DabZ', 
'C1', 'B8', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 'DabZ', 
'C1', 'B1', 'L10');

upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
{code}

+*Run the following query :*+

{code:java}
SELECT * FROM po_test INNER JOIN cbl ON
(
   ( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
   AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
   AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
);
{code}

+*Expected O/P :*+

{noformat}

1   Devcast C3  B4  L5  C3  *   *
3   Jabber  C3  B4  L1  C3  *   *
4   Yakijo  C3  B8  L9  C3  *   *
11  DabZC1  B1  L10 C1  B1  *
{noformat}





  was:
If I'm joining two tables, say inner join, on an OR based condition, then the 
following exception is thrown in Squirrel: 
{color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
specified. Consider using table list with where clause.
SQLState:  22017
ErrorCode: 217{color}

+*TEST CASE :*+
Create the following tables : 


{code:java}
CREATE TABLE IF NOT EXISTS CBL 
( 
COM_CODE VARCHAR NOT NULL , 
BU_CODE VARCHAR NOT NULL , 
LOC_CODE VARCHAR NOT NULL 
CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
);
CREATE TABLE IF NOT EXISTS PO_TEST
(
   PO_ID VARCHAR PRIMARY KEY,
   BU_ID VARCHAR ,
   PO_NAME VARCHAR,
   C_ID VARCHAR,
   LOC_ID VARCHAR
);
{code}
Make following entries : 


{code:java}
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
'Devcast', 'C3', 'B4', 'L5');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
'Thought', 'C2', 'B1', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
'Jabber', 'C3', 'B4', 'L1');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
'Yakijo', 'C3', 'B8', 'L9');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
'Youfeed', 'C2', 'B3', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 'Jayo', 
'C2', 'B1', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
'Trilia', 'C1', 'B10', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
'Podcat', 'C1', 'B3', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
'Twitter', 'C1', 'B5', 'L8');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 'DabZ', 
'C1', 'B8', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 'DabZ', 
'C1', 'B1', 

[jira] [Updated] (PHOENIX-3952) "Ambiguous or non-equi join condition specified" Exception thrown for usage of OR expression in join conditions

[Aman Jha (JIRA)]

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

Aman Jha updated PHOENIX-3952:
--
Description: 
If I'm joining two tables, say inner join, on an OR based condition, then the 
following exception is thrown in Squirrel: 
{color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
specified. Consider using table list with where clause.
SQLState:  22017
ErrorCode: 217{color}

+*TEST CASE :*+
Create the following tables : 


{code:java}
CREATE TABLE IF NOT EXISTS CBL 
( 
COM_CODE VARCHAR NOT NULL , 
BU_CODE VARCHAR NOT NULL , 
LOC_CODE VARCHAR NOT NULL 
CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
);
CREATE TABLE IF NOT EXISTS PO_TEST
(
   PO_ID VARCHAR PRIMARY KEY,
   BU_ID VARCHAR ,
   PO_NAME VARCHAR,
   C_ID VARCHAR,
   LOC_ID VARCHAR
);
{code}
Make following entries : 


{code:java}
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
'Devcast', 'C3', 'B4', 'L5');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
'Thought', 'C2', 'B1', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
'Jabber', 'C3', 'B4', 'L1');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
'Yakijo', 'C3', 'B8', 'L9');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
'Youfeed', 'C2', 'B3', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 'Jayo', 
'C2', 'B1', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
'Trilia', 'C1', 'B10', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
'Podcat', 'C1', 'B3', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
'Twitter', 'C1', 'B5', 'L8');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 'DabZ', 
'C1', 'B8', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 'DabZ', 
'C1', 'B1', 'L10');

upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');
{code}




*+Run the following query : +*


{code:java}
SELECT * FROM po_test INNER JOIN cbl ON
(
   ( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
   AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
   AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
);
{code}

*Expected O/P :*

{noformat}

1   Devcast C3  B4  L5  C3  *   *
3   Jabber  C3  B4  L1  C3  *   *
4   Yakijo  C3  B8  L9  C3  *   *
11  DabZC1  B1  L10 C1  B1  *
{noformat}





  was:
If I'm joining two tables, say inner join, on an OR based condition, then the 
following exception is thrown in Squirrel: 
{color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
specified. Consider using table list with where clause.
SQLState:  22017
ErrorCode: 217{color}

*+TEST CASE : +*
Create the following tables : 


{code:java}
CREATE TABLE IF NOT EXISTS CBL 
( 
COM_CODE VARCHAR NOT NULL , 
BU_CODE VARCHAR NOT NULL , 
LOC_CODE VARCHAR NOT NULL 
CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
);
CREATE TABLE IF NOT EXISTS PO_TEST
(
   PO_ID VARCHAR PRIMARY KEY,
   BU_ID VARCHAR ,
   PO_NAME VARCHAR,
   C_ID VARCHAR,
   LOC_ID VARCHAR
);

Make following entries : 

upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
'Devcast', 'C3', 'B4', 'L5');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
'Thought', 'C2', 'B1', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
'Jabber', 'C3', 'B4', 'L1');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
'Yakijo', 'C3', 'B8', 'L9');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
'Youfeed', 'C2', 'B3', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 'Jayo', 
'C2', 'B1', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
'Trilia', 'C1', 'B10', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
'Podcat', 'C1', 'B3', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
'Twitter', 'C1', 'B5', 'L8');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 'DabZ', 
'C1', 'B8', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 'DabZ', 
'C1', 'B1', 'L10');

[jira] [Created] (PHOENIX-3952) "Ambiguous or non-equi join condition specified" Exception thrown for usage of OR expression in join conditions

[Aman Jha (JIRA)]

Aman Jha created PHOENIX-3952:
-

 Summary: "Ambiguous or non-equi join condition specified" 
Exception thrown for usage of  OR expression in join conditions
 Key: PHOENIX-3952
 URL: https://issues.apache.org/jira/browse/PHOENIX-3952
 Project: Phoenix
  Issue Type: Bug
Affects Versions: 4.8.1
 Environment: HBase v-1.2.4 running on CentOS 6.0, Phoenix v-4.8.1, 
Squirrel v-3.7 running on Windows 10
Reporter: Aman Jha


If I'm joining two tables, say inner join, on an OR based condition, then the 
following exception is thrown in Squirrel: 
{color:red}Error: ERROR 217 (22017): Ambiguous or non-equi join condition 
specified. Consider using table list with where clause.
SQLState:  22017
ErrorCode: 217{color}

*+TEST CASE : +*
Create the following tables : 


{code:java}
CREATE TABLE IF NOT EXISTS CBL 
( 
COM_CODE VARCHAR NOT NULL , 
BU_CODE VARCHAR NOT NULL , 
LOC_CODE VARCHAR NOT NULL 
CONSTRAINT PK_CBL PRIMARY KEY (COM_CODE, BU_CODE, LOC_CODE)
);
CREATE TABLE IF NOT EXISTS PO_TEST
(
   PO_ID VARCHAR PRIMARY KEY,
   BU_ID VARCHAR ,
   PO_NAME VARCHAR,
   C_ID VARCHAR,
   LOC_ID VARCHAR
);

Make following entries : 

upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('1', 
'Devcast', 'C3', 'B4', 'L5');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('2', 
'Thought', 'C2', 'B1', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('3', 
'Jabber', 'C3', 'B4', 'L1');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('4', 
'Yakijo', 'C3', 'B8', 'L9');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('5', 
'Youfeed', 'C2', 'B3', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('6', 'Jayo', 
'C2', 'B1', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('7', 
'Trilia', 'C1', 'B10', 'L2');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('8', 
'Podcat', 'C1', 'B3', 'L10');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('9', 
'Twitter', 'C1', 'B5', 'L8');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('10', 'DabZ', 
'C1', 'B8', 'L4');
upsert into PO_TEST (PO_ID, PO_NAME, C_ID, BU_ID, LOC_ID) values ('11', 'DabZ', 
'C1', 'B1', 'L10');

upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B1','*');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B2','L2');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L3');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C1','B3','L5');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C2','*','L1');
upsert into CBL (COM_CODE,BU_CODE,LOC_CODE) values ('C3','*','*');

{code}

*+Run the following query : +*


{code:java}
SELECT * FROM po_test INNER JOIN cbl ON
(
   ( cbl.com_code = '*' OR cbl.com_code = po_test.c_id )
   AND ( cbl.bu_code = '*' OR cbl.bu_code = po_test.bu_id )
   AND ( cbl.loc_code = '*' OR cbl.loc_code = po_test.loc_id )
);
{code}

*Expected O/P :*

{noformat}

1   Devcast C3  B4  L5  C3  *   *
3   Jabber  C3  B4  L1  C3  *   *
4   Yakijo  C3  B8  L9  C3  *   *
11  DabZC1  B1  L10 C1  B1  *
{noformat}







--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

[jira] [Commented] (PHOENIX-3928) Consider retrying once after any SQLException

[James Taylor (JIRA)]

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

James Taylor commented on PHOENIX-3928:
---

Sounds like it might be a bug. Does the server send over the updated table and 
then it's ignored because the time stamp didn't change? Perhaps we can remove 
the cached table and add the new one if the server sent it over (as opposed to 
the case where it sends null back)?

> Consider retrying once after any SQLException
> -
>
> Key: PHOENIX-3928
> URL: https://issues.apache.org/jira/browse/PHOENIX-3928
> Project: Phoenix
>  Issue Type: Bug
>Reporter: James Taylor
>Assignee: Maddineni Sukumar
> Fix For: 4.12.0
>
>
> There are more cases in which a retry would successfully execute than when a 
> MetaDataEntityNotFoundException. For example, certain error cases that depend 
> on the state of the metadata would work on retry if the metadata had changed. 
> We may want to retry on any SQLException and simply loop through the tables 
> involved (plan.getSourceRefs().iterator()), and if any meta data was updated, 
> go ahead and retry once.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)