[jira] [Commented] (PHOENIX-2679) Implement column family schema structure in Calcite-Phoenix
[
https://issues.apache.org/jira/browse/PHOENIX-2679?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15547766#comment-15547766
]
Maryann Xue commented on PHOENIX-2679:
--
I'm implementing it using shadow tables, which means each structured type
Phoenix table (referenced in the query) will have a corresponding flattened
type shadow table. At a point right before real query planning starts, we'll
have a Hep program that replaces a "flattening" Project on top of a TableScan
of the structured type table with a TableScan of the flattened type table.
After this point, everything will work exactly the same as they did before.
Secondary indexes will be defined based on these shadow tables, which I assume
would just work the same as before too.
I've also managed to avoid the side effects of shadow tables by adding them
through a Hook that's triggered after sql-to-rel conversion, so that they won't
be visible at validation time.
Part of it is already working, but with some changes on the Calcite side, as
shown below:
{code}
diff --git a/core/src/main/java/org/apache/calcite/prepare/Prepare.java
b/core/src/main/java/org/apache/calcite/prepare/Prepare.java
index 70cddf6..8d9b5f4 100644
--- a/core/src/main/java/org/apache/calcite/prepare/Prepare.java
+++ b/core/src/main/java/org/apache/calcite/prepare/Prepare.java
@@ -258,7 +258,7 @@ public PreparedResult prepareSql(
// Structured type flattening, view expansion, and plugging in physical
// storage.
-root = root.withRel(flattenTypes(root.rel, true));
+root = root.withRel(sqlToRelConverter.flattenTypes(root.rel, true));
if (this.context.config().forceDecorrelate()) {
// Subquery decorrelation.
@@ -363,7 +363,7 @@ private boolean shouldTrim(RelNode rootRel) {
// For now, don't trim if there are more than 3 joins. The projects
// near the leaves created by trim migrate past joins and seem to
// prevent join-reordering.
-return THREAD_TRIM.get() || RelOptUtil.countJoins(rootRel) < 2;
+return THREAD_TRIM.get() && RelOptUtil.countJoins(rootRel) < 2;
}
public RelRoot expandView(RelDataType rowType, String queryString,
{code}
The reasons are:
1. CalcitePrepareStmt.flattenTypes() doesn't really do anything to flatten
types, but SqlToRelConverter.flattenTypes() does.
2. trimUnusedFields() doesn't seem to work right for structured types after
flattening. The OR logic seems to be wrong by itself, or was it intended that
way?
{code}
diff --git
a/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
b/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
index 82b1f4e..db508d6 100644
---
a/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
+++
b/core/src/main/java/org/apache/calcite/sql2rel/RelStructuredTypeFlattener.java
@@ -27,6 +27,7 @@
import org.apache.calcite.rel.core.CorrelationId;
import org.apache.calcite.rel.core.Sample;
import org.apache.calcite.rel.core.Sort;
+import org.apache.calcite.rel.core.TableScan;
import org.apache.calcite.rel.core.Uncollect;
import org.apache.calcite.rel.logical.LogicalAggregate;
import org.apache.calcite.rel.logical.LogicalCalc;
@@ -39,7 +40,6 @@
import org.apache.calcite.rel.logical.LogicalSort;
import org.apache.calcite.rel.logical.LogicalTableFunctionScan;
import org.apache.calcite.rel.logical.LogicalTableModify;
-import org.apache.calcite.rel.logical.LogicalTableScan;
import org.apache.calcite.rel.logical.LogicalUnion;
import org.apache.calcite.rel.logical.LogicalValues;
import org.apache.calcite.rel.stream.LogicalChi;
@@ -648,7 +648,7 @@ private boolean isConstructor(RexNode rexNode) {
|| (call.isA(SqlKind.NEW_SPECIFICATION));
}
- public void rewriteRel(LogicalTableScan rel) {
+ public void rewriteRel(TableScan rel) {
RelNode newRel = rel.getTable().toRel(toRelContext);
if (!SqlTypeUtil.isFlat(rel.getRowType())) {
final List> flattenedExpList =
Lists.newArrayList();
{code}
3. Apparently it wouldn't work for PhoenixTableScan, which is part of rel.
[~julianhyde], for (2) and (3), do you think it would be reasonable to make the
changes into Calcite? for (1) can we make it an option?
Aside from the points listed above, one big blocker right now is that the
ProjectFilterTransposeRule could not push the "flattening" Project through the
filter, coz the "flattening" Project was thought to be a trivial Project. So
right now any query with a filter cannot work. I'll try to see if I can work
this out.
> Implement column family schema structure in Calcite-Phoenix
> ---
>
> Key: PHOENIX-2679
> URL: https://issues.apache.org/jira/browse/PHOENIX-2679
> Project: Phoenix
> Issue Type: Task
>Reporter: Maryann Xue
>Assignee:
[jira] [Commented] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15547158#comment-15547158
]
James Taylor commented on PHOENIX-3347:
---
+1 to change conformance to DEFAULT
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause. Either need to change conformance or stop supporting SELECT without
> FROM as Phoenix currently does.
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
> conformance is, say, STRICT_2003.}}
> Based on PhoenixCalciteEmbeddedDriver.java, we are using ORACLE_10
> conformance which does not support this
> {code}setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString()){code}
> Confirming this is the fact that it is specifically the SqlValidator throwing
> the exception in relevant test cases
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-3265) Surround columns named date with double quotes
[
https://issues.apache.org/jira/browse/PHOENIX-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15547152#comment-15547152
]
James Taylor commented on PHOENIX-3265:
---
I'd recommend surrounding any reserved words in tests in double quotes and
upper casing them, [~lomoree]. If we could document any new reserved words on
PHOENIX-3284, that'd help when we need to produce release notes.
> Surround columns named date with double quotes
> --
>
> Key: PHOENIX-3265
> URL: https://issues.apache.org/jira/browse/PHOENIX-3265
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: James Taylor
>Assignee: Eric Lomore
>
> In Phoenix {{date}} is not a reserved word, but perhaps in Calcite it is? If
> that's the case, we should surround occurrences of columns named {{date}} in
> double quotes to prevent the parser error.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15547105#comment-15547105
]
Eric Lomore edited comment on PHOENIX-3347 at 10/5/16 12:12 AM:
{code}public boolean isFromRequired()
Whether FROM clause is required in a SELECT statement.
True in ORACLE_10, STRICT_92, STRICT_99, STRICT_2003; false otherwise.{code}
Switching to any of these 3 should work as there are no other changes except
this (the fix passes 3 additional test cases as it should)
DEFAULT
PRAGMATIC_99
PRAGMATIC_2003
http://calcite.apache.org/apidocs/org/apache/calcite/sql/validate/SqlConformance.html
[~maryannxue], with your blessing, can I proceed with a quick patch to change
conformance to DEFAULT or one of the others if that's preferred? Thanks!
was (Author: lomoree):
{code}public boolean isFromRequired()
Whether FROM clause is required in a SELECT statement.
True in ORACLE_10, STRICT_92, STRICT_99, STRICT_2003; false otherwise.{code}
Switching to any of these 3 should work as there are no other changes except
this.
DEFAULT
PRAGMATIC_99
PRAGMATIC_2003
http://calcite.apache.org/apidocs/org/apache/calcite/sql/validate/SqlConformance.html
[~maryannxue], with your blessing, can I proceed with a quick patch to change
conformance to DEFAULT or one of the others if that's preferred? Thanks!
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause. Either need to change conformance or stop supporting SELECT without
> FROM as Phoenix currently does.
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
> conformance is, say, STRICT_2003.}}
> Based on PhoenixCalciteEmbeddedDriver.java, we are using ORACLE_10
> conformance which does not support this
> {code}setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString()){code}
> Confirming this is the fact that it is specifically the SqlValidator throwing
> the exception in relevant test cases
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15547135#comment-15547135
]
James Taylor commented on PHOENIX-6:
Would you mind filing a separate JIRA for your use case? If I understand it
correctly, you'd like to be able to have multiple atomic insert/update
statements for the same row in the same commit batch and have them sequentially
execute? Would the atomicity be for the entire commit batch (for all rows being
operated on) or if not, how would potentially overlapping rows from other
batches be handled?
For your question, I think you meant to use the same table name for both
statements, right? For non transactional tables, the UPSERT SELECT wouldn't see
the data from the UPSERT VALUES. When auto commit is off, the data is kept in
memory on the client until the commit occurs. The UPSERT VALUES would cause a
new row to be cached on the client (in memory) and the UPSERT SELECT would
cause N rows to be cached on the client (where N is the number of rows
currently in the table). The commit essentially generates the HBase Puts and
does a batch mutate.
For transactional tables, you do see your own updates. In that case, the UPSERT
SELECT would see the row from the UPSERT VALUES call.
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.9.0
>
>
> To support inserting a new row only if it doesn't already exist, we should
> support the "on duplicate key" construct for UPSERT. With this construct, the
> UPSERT VALUES statement would run atomically and would thus require a read
> before write which would obviously have a negative impact on performance. For
> an example of similar syntax , see MySQL documentation at
> http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> See this discussion for more detail:
> https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J.
> A related discussion is on PHOENIX-2909.
> Initially we'd support the following:
> # This would prevent the setting of VAL to 0 if the row already exists:
> {code}
> UPSERT INTO T (PK, VAL) VALUES ('a',0)
> ON DUPLICATE KEY IGNORE;
> {code}
> # This would increment the valueS of COUNTER1 and COUNTER2 if the row already
> exists and otherwise initialize them to 0:
> {code}
> UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
> ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
> {code}
> So the general form is:
> {code}
> UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE
> =, ...] ]
> {code}
> The following restrictions will apply:
> - The may not be part of the primary key constraint - only KeyValue
> columns will be allowed.
> - If the table is immutable, the may not appear in a secondary
> index. This is because the mutations for indexes on immutable tables are
> calculated on the client-side, while this new syntax would potentially modify
> the value on the server-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15547105#comment-15547105
]
Eric Lomore edited comment on PHOENIX-3347 at 10/4/16 11:59 PM:
{code}public boolean isFromRequired()
Whether FROM clause is required in a SELECT statement.
True in ORACLE_10, STRICT_92, STRICT_99, STRICT_2003; false otherwise.{code}
Switching to any of these 3 should work as there are no other changes except
this.
DEFAULT
PRAGMATIC_99
PRAGMATIC_2003
http://calcite.apache.org/apidocs/org/apache/calcite/sql/validate/SqlConformance.html
[~maryannxue], with your blessing, can I proceed with a quick patch to change
conformance to DEFAULT or one of the others if that's preferred? Thanks!
was (Author: lomoree):
{code}public boolean isFromRequired()
Whether FROM clause is required in a SELECT statement.
True in ORACLE_10, STRICT_92, STRICT_99, STRICT_2003; false otherwise.{code}
Switching to any of these 3 should work as there are no other changes except
this.
DEFAULT
PRAGMATIC_99
PRAGMATIC_2003
http://calcite.apache.org/apidocs/org/apache/calcite/sql/validate/SqlConformance.html
[~maryannxue] with your blessing, can I proceed with a quick patch to change to
DEFAULT or one of the others if that's preferred? Thanks!
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause. Either need to change conformance or stop supporting SELECT without
> FROM as Phoenix currently does.
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
> conformance is, say, STRICT_2003.}}
> Based on PhoenixCalciteEmbeddedDriver.java, we are using ORACLE_10
> conformance which does not support this
> {code}setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString()){code}
> Confirming this is the fact that it is specifically the SqlValidator throwing
> the exception in relevant test cases
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15547105#comment-15547105
]
Eric Lomore commented on PHOENIX-3347:
--
{code}public boolean isFromRequired()
Whether FROM clause is required in a SELECT statement.
True in ORACLE_10, STRICT_92, STRICT_99, STRICT_2003; false otherwise.{code}
Switching to any of these 3 should work as there are no other changes except
this.
DEFAULT
PRAGMATIC_99
PRAGMATIC_2003
http://calcite.apache.org/apidocs/org/apache/calcite/sql/validate/SqlConformance.html
[~maryannxue] with your blessing, can I proceed with a quick patch to change to
DEFAULT or one of the others if that's preferred? Thanks!
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause. Either need to change conformance or stop supporting SELECT without
> FROM as Phoenix currently does.
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
> conformance is, say, STRICT_2003.}}
> Based on PhoenixCalciteEmbeddedDriver.java, we are using ORACLE_10
> conformance which does not support this
> {code}setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString()){code}
> Confirming this is the fact that it is specifically the SqlValidator throwing
> the exception in relevant test cases
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3297) Combining an RVC query and a filter on a datatype smaller than 8 bytes causes an Illegal Data Exception
[
https://issues.apache.org/jira/browse/PHOENIX-3297?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Taylor updated PHOENIX-3297:
--
Fix Version/s: 4.9.0
> Combining an RVC query and a filter on a datatype smaller than 8 bytes causes
> an Illegal Data Exception
> ---
>
> Key: PHOENIX-3297
> URL: https://issues.apache.org/jira/browse/PHOENIX-3297
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.6.0, 4.8.0
>Reporter: Julian Jaffe
> Fix For: 4.9.0
>
>
> Combining an RVC query and a filter on a primary key column with a datatype
> smaller than 8 bytes causes an Illegal Data Exception.
> {code:sql}0: jdbc:phoenix:> CREATE TABLE IF NOT EXISTS TEST.RVC_TEST
> (COLONE BIGINT NOT NULL, COLTWO BIGINT NOT NULL, COLTHREE TINYINT NOT NULL,
> COLFOUR BIGINT NOT NULL, COLFIVE VARCHAR, CONSTRAINT "test_pk" PRIMARY KEY
> (COLONE, COLTWO, COLTHREE, COLFOUR));
> No rows affected (1.167 seconds)
> 0: jdbc:phoenix:>
> 0: jdbc:phoenix:> UPSERT INTO TEST.RVC_TEST VALUES (1, 2, 3, 4, 'row 1');
> 1 row affected (0.169 seconds)
> 0: jdbc:phoenix:labs-darth-journalnode-lv-102>
> 0: jdbc:phoenix:labs-darth-journalnode-lv-102> SELECT * FROM TEST.RVC_TEST
> WHERE (COLONE, COLTWO) IN (1,2) AND COLTHREE=3;
> Error: ERROR 201 (22000): Illegal data. Expected length of at least 8 bytes,
> but had 1 (state=22000,code=201)
> java.sql.SQLException: ERROR 201 (22000): Illegal data. Expected length of at
> least 8 bytes, but had 1
> at
> org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:396)
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
> at
> org.apache.phoenix.schema.types.PDataType.checkForSufficientLength(PDataType.java:273)
> at
> org.apache.phoenix.schema.types.PLong$LongCodec.decodeLong(PLong.java:229)
> at org.apache.phoenix.schema.types.PLong.toObject(PLong.java:114)
> at org.apache.phoenix.schema.types.PLong.toObject(PLong.java:30)
> at
> org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:967)
> at
> org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:971)
> at
> org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:1000)
> at
> org.apache.phoenix.schema.types.PDataType.toStringLiteral(PDataType.java:1073)
> at
> org.apache.phoenix.schema.types.PDataType.toStringLiteral(PDataType.java:1069)
> at
> org.apache.phoenix.iterate.ExplainTable.appendPKColumnValue(ExplainTable.java:194)
> at
> org.apache.phoenix.iterate.ExplainTable.appendScanRow(ExplainTable.java:270)
> at
> org.apache.phoenix.iterate.ExplainTable.appendKeyRanges(ExplainTable.java:282)
> at
> org.apache.phoenix.iterate.ExplainTable.explain(ExplainTable.java:125)
> at
> org.apache.phoenix.iterate.BaseResultIterators.explain(BaseResultIterators.java:728)
> at
> org.apache.phoenix.iterate.RoundRobinResultIterator.explain(RoundRobinResultIterator.java:153)
> at
> org.apache.phoenix.execute.BaseQueryPlan.getPlanSteps(BaseQueryPlan.java:438)
> at
> org.apache.phoenix.execute.BaseQueryPlan.iterator(BaseQueryPlan.java:295)
> at
> org.apache.phoenix.execute.BaseQueryPlan.iterator(BaseQueryPlan.java:176)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:268)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:258)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:257)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1438)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:808)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
> {code}
> I wasn't able to find any other Phoenix issues directly related to this, but
> it seems that PHOENIX-2942 and PHOENIX-2980 may be related.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3348) SELECT DISTINCT with a non-rowkey array and another column returns incorrect results
[
https://issues.apache.org/jira/browse/PHOENIX-3348?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Jaffe updated PHOENIX-3348:
--
Summary: SELECT DISTINCT with a non-rowkey array and another column returns
incorrect results (was: SELECT DISTINCT with a non-rowkey array and an id
returns incorrect results)
> SELECT DISTINCT with a non-rowkey array and another column returns incorrect
> results
>
>
> Key: PHOENIX-3348
> URL: https://issues.apache.org/jira/browse/PHOENIX-3348
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.6.0
>Reporter: Julian Jaffe
>
> Attempting to select distinct combinations from an array and another column
> returns incorrect results if the columns are not part of the primary key.
> {code:sql}
> CREATE TABLE IF NOT EXISTS TEST.DESERTEST (COLONE BIGINT NOT NULL PRIMARY
> KEY, COLTWO BIGINT, COLTHREE BIGINT[]);
> No rows affected (1.368 seconds)
> UPSERT INTO TEST.DESERTEST VALUES (10, 7, ARRAY[1,2]);
> 1 row affected (0.161 seconds)
> SELECT DISTINCT COLTWO, COLTHREE FROM TEST.DESERTEST WHERE COLONE = 10;
> +--+--+
> | COLTWO | COLTHREE
> |
> +--+--+
> | -9223372036854677504 | []
> |
> +--+--+
> 1 row selected (0.367 seconds)
> SELECT COLTWO, COLTHREE FROM TEST.DESERTEST WHERE COLONE = 10;
> +--+--+
> | COLTWO | COLTHREE
> |
> +--+--+
> | 7| [1, 2]
> |
> +--+--+
> 1 row selected (0.336 seconds)
> SELECT DISTINCT COLONE, COLTHREE FROM TEST.DESERTEST WHERE COLONE = 10;
> +--+--+
> | COLONE | COLTHREE
> |
> +--+--+
> | 10 | [1, 2]
> |
> +--+--+
> 1 row selected (0.32 seconds)
> {code}
> We've confirmed this bug in 4.6, and confirmed that it does not occur in 4.8,
> although I haven't been able to locate a corresponding JIRA. However, it will
> occur if using the 4.6 client to talk to 4.8, so it appears to be at least
> partially client-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3297) Combining an RVC query and a filter on a datatype smaller than 8 bytes causes an Illegal Data Exception
[
https://issues.apache.org/jira/browse/PHOENIX-3297?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Jaffe updated PHOENIX-3297:
--
Affects Version/s: 4.8.0
> Combining an RVC query and a filter on a datatype smaller than 8 bytes causes
> an Illegal Data Exception
> ---
>
> Key: PHOENIX-3297
> URL: https://issues.apache.org/jira/browse/PHOENIX-3297
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.6.0, 4.8.0
>Reporter: Julian Jaffe
>
> Combining an RVC query and a filter on a primary key column with a datatype
> smaller than 8 bytes causes an Illegal Data Exception.
> {code:sql}0: jdbc:phoenix:> CREATE TABLE IF NOT EXISTS TEST.RVC_TEST
> (COLONE BIGINT NOT NULL, COLTWO BIGINT NOT NULL, COLTHREE TINYINT NOT NULL,
> COLFOUR BIGINT NOT NULL, COLFIVE VARCHAR, CONSTRAINT "test_pk" PRIMARY KEY
> (COLONE, COLTWO, COLTHREE, COLFOUR));
> No rows affected (1.167 seconds)
> 0: jdbc:phoenix:>
> 0: jdbc:phoenix:> UPSERT INTO TEST.RVC_TEST VALUES (1, 2, 3, 4, 'row 1');
> 1 row affected (0.169 seconds)
> 0: jdbc:phoenix:labs-darth-journalnode-lv-102>
> 0: jdbc:phoenix:labs-darth-journalnode-lv-102> SELECT * FROM TEST.RVC_TEST
> WHERE (COLONE, COLTWO) IN (1,2) AND COLTHREE=3;
> Error: ERROR 201 (22000): Illegal data. Expected length of at least 8 bytes,
> but had 1 (state=22000,code=201)
> java.sql.SQLException: ERROR 201 (22000): Illegal data. Expected length of at
> least 8 bytes, but had 1
> at
> org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:396)
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
> at
> org.apache.phoenix.schema.types.PDataType.checkForSufficientLength(PDataType.java:273)
> at
> org.apache.phoenix.schema.types.PLong$LongCodec.decodeLong(PLong.java:229)
> at org.apache.phoenix.schema.types.PLong.toObject(PLong.java:114)
> at org.apache.phoenix.schema.types.PLong.toObject(PLong.java:30)
> at
> org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:967)
> at
> org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:971)
> at
> org.apache.phoenix.schema.types.PDataType.toObject(PDataType.java:1000)
> at
> org.apache.phoenix.schema.types.PDataType.toStringLiteral(PDataType.java:1073)
> at
> org.apache.phoenix.schema.types.PDataType.toStringLiteral(PDataType.java:1069)
> at
> org.apache.phoenix.iterate.ExplainTable.appendPKColumnValue(ExplainTable.java:194)
> at
> org.apache.phoenix.iterate.ExplainTable.appendScanRow(ExplainTable.java:270)
> at
> org.apache.phoenix.iterate.ExplainTable.appendKeyRanges(ExplainTable.java:282)
> at
> org.apache.phoenix.iterate.ExplainTable.explain(ExplainTable.java:125)
> at
> org.apache.phoenix.iterate.BaseResultIterators.explain(BaseResultIterators.java:728)
> at
> org.apache.phoenix.iterate.RoundRobinResultIterator.explain(RoundRobinResultIterator.java:153)
> at
> org.apache.phoenix.execute.BaseQueryPlan.getPlanSteps(BaseQueryPlan.java:438)
> at
> org.apache.phoenix.execute.BaseQueryPlan.iterator(BaseQueryPlan.java:295)
> at
> org.apache.phoenix.execute.BaseQueryPlan.iterator(BaseQueryPlan.java:176)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:268)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:258)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:257)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1438)
> at sqlline.Commands.execute(Commands.java:822)
> at sqlline.Commands.sql(Commands.java:732)
> at sqlline.SqlLine.dispatch(SqlLine.java:808)
> at sqlline.SqlLine.begin(SqlLine.java:681)
> at sqlline.SqlLine.start(SqlLine.java:398)
> at sqlline.SqlLine.main(SqlLine.java:292)
> {code}
> I wasn't able to find any other Phoenix issues directly related to this, but
> it seems that PHOENIX-2942 and PHOENIX-2980 may be related.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (PHOENIX-3348) SELECT DISTINCT with a non-rowkey array and an id returns incorrect results
Julian Jaffe created PHOENIX-3348:
-
Summary: SELECT DISTINCT with a non-rowkey array and an id returns
incorrect results
Key: PHOENIX-3348
URL: https://issues.apache.org/jira/browse/PHOENIX-3348
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.6.0
Reporter: Julian Jaffe
Attempting to select distinct combinations from an array and another column
returns incorrect results if the columns are not part of the primary key.
{code:sql}
CREATE TABLE IF NOT EXISTS TEST.DESERTEST (COLONE BIGINT NOT NULL PRIMARY KEY,
COLTWO BIGINT, COLTHREE BIGINT[]);
No rows affected (1.368 seconds)
UPSERT INTO TEST.DESERTEST VALUES (10, 7, ARRAY[1,2]);
1 row affected (0.161 seconds)
SELECT DISTINCT COLTWO, COLTHREE FROM TEST.DESERTEST WHERE COLONE = 10;
+--+--+
| COLTWO | COLTHREE
|
+--+--+
| -9223372036854677504 | []
|
+--+--+
1 row selected (0.367 seconds)
SELECT COLTWO, COLTHREE FROM TEST.DESERTEST WHERE COLONE = 10;
+--+--+
| COLTWO | COLTHREE
|
+--+--+
| 7| [1, 2]
|
+--+--+
1 row selected (0.336 seconds)
SELECT DISTINCT COLONE, COLTHREE FROM TEST.DESERTEST WHERE COLONE = 10;
+--+--+
| COLONE | COLTHREE
|
+--+--+
| 10 | [1, 2]
|
+--+--+
1 row selected (0.32 seconds)
{code}
We've confirmed this bug in 4.6, and confirmed that it does not occur in 4.8,
although I haven't been able to locate a corresponding JIRA. However, it will
occur if using the 4.6 client to talk to 4.8, so it appears to be at least
partially client-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-3265) Surround columns named date with double quotes
[
https://issues.apache.org/jira/browse/PHOENIX-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15547038#comment-15547038
]
Julian Hyde commented on PHOENIX-3265:
--
CALCITE-1256 only applies if the name is ALSO a built-in function with no
arguments. I don't think it applies in this case.
> Surround columns named date with double quotes
> --
>
> Key: PHOENIX-3265
> URL: https://issues.apache.org/jira/browse/PHOENIX-3265
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: James Taylor
>Assignee: Eric Lomore
>
> In Phoenix {{date}} is not a reserved word, but perhaps in Calcite it is? If
> that's the case, we should surround occurrences of columns named {{date}} in
> double quotes to prevent the parser error.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-3265) Surround columns named date with double quotes
[
https://issues.apache.org/jira/browse/PHOENIX-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15547013#comment-15547013
]
Eric Lomore commented on PHOENIX-3265:
--
https://issues.apache.org/jira/browse/CALCITE-1256
It seems this is a known issue in Calcite, except that I was able to circumvent
that particular issue.
[~julianhyde] do you have any thoughts on this?
> Surround columns named date with double quotes
> --
>
> Key: PHOENIX-3265
> URL: https://issues.apache.org/jira/browse/PHOENIX-3265
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: James Taylor
>Assignee: Eric Lomore
>
> In Phoenix {{date}} is not a reserved word, but perhaps in Calcite it is? If
> that's the case, we should surround occurrences of columns named {{date}} in
> double quotes to prevent the parser error.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (PHOENIX-3265) Surround columns named date with double quotes
[
https://issues.apache.org/jira/browse/PHOENIX-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546993#comment-15546993
]
Eric Lomore edited comment on PHOENIX-3265 at 10/4/16 11:16 PM:
I tried a few things.
Fixing this for create table/view is very simple as that's handled by
Phoenix/Calcite grammar and pings the NonReserveredKeyWord() grammar function.
https://issues.apache.org/jira/browse/CALCITE-1241
Making use of this, I got this working:
{{CREATE TABLE t (k1 VARCHAR, DATE VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 DESC,
k2))}}
For the WHERE, GROUP BY, ON, etc. this is handled deep in the Calcite grammar,
and it does not hit the same function. There's not much of a way to change
this, unless we want phoenix to handle more of the grammar processing, but it
would be extensive to say the least.
While adding quotes around DATE does fix the problem, doing so would seemingly
have to be on the input side (the test cases). As I've seen so far, we always
try to avoid this.
Happy to hear your thoughts on this. Thanks [~jamestaylor]
was (Author: lomoree):
I tried a few things.
Fixing this for create table/view is very simple as that's handled by
Phoenix/Calcite grammar and pings the NonReserveredKeyWord() method.
https://issues.apache.org/jira/browse/CALCITE-1241
Making use of this, I got this working:
{{CREATE TABLE t (k1 VARCHAR, DATE VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 DESC,
k2))}}
For the WHERE, GROUP BY, ON, etc. this is handled deep in the Calcite grammar,
and it does not hit the same function. There's not much of a way to change
this, unless we want phoenix to handle more of the grammar processing, but it
would be extensive to say the least.
While adding quotes around DATE does fix the problem, doing so would seemingly
have to be on the input side (the test cases). As I've seen so far, we always
try to avoid this.
Happy to hear your thoughts on this. Thanks [~jamestaylor]
> Surround columns named date with double quotes
> --
>
> Key: PHOENIX-3265
> URL: https://issues.apache.org/jira/browse/PHOENIX-3265
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: James Taylor
>Assignee: Eric Lomore
>
> In Phoenix {{date}} is not a reserved word, but perhaps in Calcite it is? If
> that's the case, we should surround occurrences of columns named {{date}} in
> double quotes to prevent the parser error.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (PHOENIX-3265) Surround columns named date with double quotes
[
https://issues.apache.org/jira/browse/PHOENIX-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546993#comment-15546993
]
Eric Lomore edited comment on PHOENIX-3265 at 10/4/16 11:17 PM:
I tried a few things.
Fixing this for create table/view is very simple as that's handled by
Phoenix/Calcite grammar and pings the NonReserveredKeyWord() grammar function.
https://issues.apache.org/jira/browse/CALCITE-1241
Making use of this, I got this working:
{{CREATE TABLE t (k1 VARCHAR, DATE VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 DESC,
DATE))}}
For the WHERE, GROUP BY, ON, etc. this is handled deep in the Calcite grammar,
and it does not hit the same function. There's not much of a way to change
this, unless we want phoenix to handle more of the grammar processing, but it
would be extensive to say the least.
While adding quotes around DATE does fix the problem, doing so would seemingly
have to be on the input side (the test cases). As I've seen so far, we always
try to avoid this.
Happy to hear your thoughts on this. Thanks [~jamestaylor]
was (Author: lomoree):
I tried a few things.
Fixing this for create table/view is very simple as that's handled by
Phoenix/Calcite grammar and pings the NonReserveredKeyWord() grammar function.
https://issues.apache.org/jira/browse/CALCITE-1241
Making use of this, I got this working:
{{CREATE TABLE t (k1 VARCHAR, DATE VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 DESC,
k2))}}
For the WHERE, GROUP BY, ON, etc. this is handled deep in the Calcite grammar,
and it does not hit the same function. There's not much of a way to change
this, unless we want phoenix to handle more of the grammar processing, but it
would be extensive to say the least.
While adding quotes around DATE does fix the problem, doing so would seemingly
have to be on the input side (the test cases). As I've seen so far, we always
try to avoid this.
Happy to hear your thoughts on this. Thanks [~jamestaylor]
> Surround columns named date with double quotes
> --
>
> Key: PHOENIX-3265
> URL: https://issues.apache.org/jira/browse/PHOENIX-3265
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: James Taylor
>Assignee: Eric Lomore
>
> In Phoenix {{date}} is not a reserved word, but perhaps in Calcite it is? If
> that's the case, we should surround occurrences of columns named {{date}} in
> double quotes to prevent the parser error.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-3265) Surround columns named date with double quotes
[
https://issues.apache.org/jira/browse/PHOENIX-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546993#comment-15546993
]
Eric Lomore commented on PHOENIX-3265:
--
I tried a few things.
Fixing this for create table/view is very simple as that's handled by
Phoenix/Calcite grammar and pings the NonReserveredKeyWord() method.
https://issues.apache.org/jira/browse/CALCITE-1241
Making use of this, I got this working:
{{CREATE TABLE t (k1 VARCHAR, DATE VARCHAR, CONSTRAINT pk PRIMARY KEY (k1 DESC,
k2))}}
For the WHERE, GROUP BY, ON, etc. this is handled deep in the Calcite grammar,
and it does not hit the same function. There's not much of a way to change
this, unless we want phoenix to handle more of the grammar processing, but it
would be extensive to say the least.
While adding quotes around DATE does fix the problem, doing so would seemingly
have to be on the input side (the test cases). As I've seen so far, we always
try to avoid this.
Happy to hear your thoughts on this. Thanks [~jamestaylor]
> Surround columns named date with double quotes
> --
>
> Key: PHOENIX-3265
> URL: https://issues.apache.org/jira/browse/PHOENIX-3265
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: James Taylor
>Assignee: Eric Lomore
>
> In Phoenix {{date}} is not a reserved word, but perhaps in Calcite it is? If
> that's the case, we should surround occurrences of columns named {{date}} in
> double quotes to prevent the parser error.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546874#comment-15546874
]
Cameron Hatfield edited comment on PHOENIX-6 at 10/4/16 10:56 PM:
--
For the help with my use case question, not really, due to the round-trip per
row insert (same reasoning as the other "batch checkandput" bugs within phoenix
and HBase).
For the question about commit, and this is just me trying to understand it, if
you run an UPSERT VALUES and an UPSERT SELECT in the same commit, in that
order, the UPSERT SELECT should not see the results of UPSERT VALUES (or a
delete, or select), according to that wording. Is that correct? For example,
what happens in the following case (when using jdbc driver commands for
autocommit / commit):
{code:sql}
AUTOCOMMIT OFF;
UPSERT INTO T (PK, V) VALUES (0,0);
-- Run batch size + 1 times
UPSERT INTO entity.stuff (PK, V) SELECT PK + 1 as PK, V + 1 FROM entity.stuff;
COMMIT;
{code}
In theory, according to SQL + the docs, I would expect to see only one new row
added to the table. But if more then one batch occurs, and the same PK is in
both sides of the batch, an extra two rows should exist. The most likely time
for this to show up, of course, is multiple upsert selects within the same
commit on a large enough table. In playing with this in squirrel sql, I don't
really have enough knowledge to what would actually trigger multiple batches to
happen, so I have no repro for this :).
Anyways, this is only mildly related to this bug, as it this same problem would
be more pronounced with an on duplicate key command, as you would probably
actually be using it for things like increments / etc. if the problem exists at
all.
was (Author: cameron.hatfield):
For the help with my use case question, not really, due to the round-trip per
row insert (same reasoning as the other "batch checkandput" bugs within phoenix
and HBase).
For the question about commit, and this is just me trying to understand it, if
you run an UPSERT VALUES and an UPSERT SELECT in the same commit, in that
order, the UPSERT SELECT should not see the results of UPSERT VALUES (or a
delete, or select), according to that wording. Is that correct? For example,
what happens in the following case (when using jdbc driver commands for
autocommit / commit):
{code:sql}
AUTOCOMMIT OFF;
UPSERT INTO T (PK, V) VALUES (0,0);
-- Run batch size + 1 times
UPSERT INTO entity.stuff (PK, V) SELECT PK + 1 as PK, V + 1 FROM entity.stuff;
COMMIT;
{code}
In theory, according to SQL + the docs, I would expect to see only one new row
added to the table. But if more then one batch occurs, and the same PK is in
both sides of the batch, an extra two rows should exist. The most likely time
for this to show up, of course, is multiple upsert selects within the same
commit on a large enough table.
Anyways, this is only mildly related to this bug, as it this same problem would
be more pronounced with an on duplicate key command, as you would probably
actually be using it for things like increments / etc.
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.9.0
>
>
> To support inserting a new row only if it doesn't already exist, we should
> support the "on duplicate key" construct for UPSERT. With this construct, the
> UPSERT VALUES statement would run atomically and would thus require a read
> before write which would obviously have a negative impact on performance. For
> an example of similar syntax , see MySQL documentation at
> http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> See this discussion for more detail:
> https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J.
> A related discussion is on PHOENIX-2909.
> Initially we'd support the following:
> # This would prevent the setting of VAL to 0 if the row already exists:
> {code}
> UPSERT INTO T (PK, VAL) VALUES ('a',0)
> ON DUPLICATE KEY IGNORE;
> {code}
> # This would increment the valueS of COUNTER1 and COUNTER2 if the row already
> exists and otherwise initialize them to 0:
> {code}
> UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
> ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
> {code}
> So the general form is:
> {code}
> UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE
> =, ...] ]
> {code}
> The following restrictions will apply:
> - The may not be part of the primary key constraint - only KeyValue
> columns will be allowed.
> - If the table is immutable, the may not appear in a secondary
> index. This is because the mutations for indexes on immutable tables
Re: [DISCUSS] Making upgrade to a new minor release a manual step
+1 to the idea. Automatic upgrades on your behalf with knowledge can be scary. But, let me pose a hypothetical: say I upgrade from Phoenix X to Phoenix Y. I have some application that using Phoenix X and I want to make sure that before I finish my upgrade to Phoenix Y that things are "OK". It seems like I have no ability to actually verify that things are working before updating the catalog (and other metadata). I'm thinking about something like HDFS's Namenode upgrade/rollback process for those who have done that. Additionally, the ability for admins/vendors to set phoenix.autoupgrade.enabled=true and restore the old functionality is great. We just need to make sure the change in functionality is doc'ed. That's good. I think it would be good to put some more thought into how we can verify an upgrade before "finalizing it" (or create the ability to rollback an upgrade), but I think that is a follow-on topic. Samarth Jain wrote: (Resending email with a proper subject) Hello Phoenix folks, The purpose of this email is two fold: 1) to introduce folks about the new optional upgrade process and, 2) to get a consensus on what should the default behavior of the process be. As you may already know, when a new minor release is rolled out, in order to support new features Phoenix needs to update its internal metadata. This update is done as part of the upgrade process that is automatically kicked off when a Phoenix client for a new minor release connects to the HBase cluster. To provide more control on when this upgrade should be run, we wrote a new feature which makes this upgrade optionally a manual step (see https://issues.apache.org/jira/browse/PHOENIX-3174 for details). The upgrade behavior is controlled by a client side config named phoenix.autoupgrade.enabled. If the config is set to false, then Phoenix won't kick off the upgrade process automatically. When ready to upgrade, users can kick off the upgrade process by calling EXECUTE UPGRADE sql command. Keep in mind that till the upgrade is run, Phoenix won't let you execute any other SQL commands using the new minor release client. Clients running older versions of Phoenix though will continue to work as before. I propose that we should by default have the config phoenix.autoupgrade.enabled set to false. Providing users more control and making the upgrade process an explicit manual step is the right thing to do, IMHO. Interested to know what do you all think. Thanks, Samarth
[jira] [Commented] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546874#comment-15546874
]
Cameron Hatfield commented on PHOENIX-6:
For the help with my use case question, not really, due to the round-trip per
row insert (same reasoning as the other "batch checkandput" bugs within phoenix
and HBase).
For the question about commit, and this is just me trying to understand it, if
you run an UPSERT VALUES and an UPSERT SELECT in the same commit, in that
order, the UPSERT SELECT should not see the results of UPSERT VALUES (or a
delete, or select), according to that wording. Is that correct? For example,
what happens in the following case (when using jdbc driver commands for
autocommit / commit):
{code:sql}
AUTOCOMMIT OFF;
UPSERT INTO T (PK, V) VALUES (0,0);
-- Run batch size + 1 times
UPSERT INTO entity.stuff (PK, V) SELECT PK + 1 as PK, V + 1 FROM entity.stuff;
COMMIT;
{code}
In theory, according to SQL + the docs, I would expect to see only one new row
added to the table. But if more then one batch occurs, an extra two rows should
exist. The most likely time for this to show up, of course, is multiple upsert
selects within the same commit on a large enough table.
Anyways, this is only mildly related to this bug, as it this same problem would
be more pronounced with an on duplicate key command, as you would probably
actually be using it for things like increments / etc.
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.9.0
>
>
> To support inserting a new row only if it doesn't already exist, we should
> support the "on duplicate key" construct for UPSERT. With this construct, the
> UPSERT VALUES statement would run atomically and would thus require a read
> before write which would obviously have a negative impact on performance. For
> an example of similar syntax , see MySQL documentation at
> http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> See this discussion for more detail:
> https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J.
> A related discussion is on PHOENIX-2909.
> Initially we'd support the following:
> # This would prevent the setting of VAL to 0 if the row already exists:
> {code}
> UPSERT INTO T (PK, VAL) VALUES ('a',0)
> ON DUPLICATE KEY IGNORE;
> {code}
> # This would increment the valueS of COUNTER1 and COUNTER2 if the row already
> exists and otherwise initialize them to 0:
> {code}
> UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
> ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
> {code}
> So the general form is:
> {code}
> UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE
> =, ...] ]
> {code}
> The following restrictions will apply:
> - The may not be part of the primary key constraint - only KeyValue
> columns will be allowed.
> - If the table is immutable, the may not appear in a secondary
> index. This is because the mutations for indexes on immutable tables are
> calculated on the client-side, while this new syntax would potentially modify
> the value on the server-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546874#comment-15546874
]
Cameron Hatfield edited comment on PHOENIX-6 at 10/4/16 10:34 PM:
--
For the help with my use case question, not really, due to the round-trip per
row insert (same reasoning as the other "batch checkandput" bugs within phoenix
and HBase).
For the question about commit, and this is just me trying to understand it, if
you run an UPSERT VALUES and an UPSERT SELECT in the same commit, in that
order, the UPSERT SELECT should not see the results of UPSERT VALUES (or a
delete, or select), according to that wording. Is that correct? For example,
what happens in the following case (when using jdbc driver commands for
autocommit / commit):
{code:sql}
AUTOCOMMIT OFF;
UPSERT INTO T (PK, V) VALUES (0,0);
-- Run batch size + 1 times
UPSERT INTO entity.stuff (PK, V) SELECT PK + 1 as PK, V + 1 FROM entity.stuff;
COMMIT;
{code}
In theory, according to SQL + the docs, I would expect to see only one new row
added to the table. But if more then one batch occurs, and the same PK is in
both sides of the batch, an extra two rows should exist. The most likely time
for this to show up, of course, is multiple upsert selects within the same
commit on a large enough table.
Anyways, this is only mildly related to this bug, as it this same problem would
be more pronounced with an on duplicate key command, as you would probably
actually be using it for things like increments / etc.
was (Author: cameron.hatfield):
For the help with my use case question, not really, due to the round-trip per
row insert (same reasoning as the other "batch checkandput" bugs within phoenix
and HBase).
For the question about commit, and this is just me trying to understand it, if
you run an UPSERT VALUES and an UPSERT SELECT in the same commit, in that
order, the UPSERT SELECT should not see the results of UPSERT VALUES (or a
delete, or select), according to that wording. Is that correct? For example,
what happens in the following case (when using jdbc driver commands for
autocommit / commit):
{code:sql}
AUTOCOMMIT OFF;
UPSERT INTO T (PK, V) VALUES (0,0);
-- Run batch size + 1 times
UPSERT INTO entity.stuff (PK, V) SELECT PK + 1 as PK, V + 1 FROM entity.stuff;
COMMIT;
{code}
In theory, according to SQL + the docs, I would expect to see only one new row
added to the table. But if more then one batch occurs, an extra two rows should
exist. The most likely time for this to show up, of course, is multiple upsert
selects within the same commit on a large enough table.
Anyways, this is only mildly related to this bug, as it this same problem would
be more pronounced with an on duplicate key command, as you would probably
actually be using it for things like increments / etc.
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.9.0
>
>
> To support inserting a new row only if it doesn't already exist, we should
> support the "on duplicate key" construct for UPSERT. With this construct, the
> UPSERT VALUES statement would run atomically and would thus require a read
> before write which would obviously have a negative impact on performance. For
> an example of similar syntax , see MySQL documentation at
> http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> See this discussion for more detail:
> https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J.
> A related discussion is on PHOENIX-2909.
> Initially we'd support the following:
> # This would prevent the setting of VAL to 0 if the row already exists:
> {code}
> UPSERT INTO T (PK, VAL) VALUES ('a',0)
> ON DUPLICATE KEY IGNORE;
> {code}
> # This would increment the valueS of COUNTER1 and COUNTER2 if the row already
> exists and otherwise initialize them to 0:
> {code}
> UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
> ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
> {code}
> So the general form is:
> {code}
> UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE
> =, ...] ]
> {code}
> The following restrictions will apply:
> - The may not be part of the primary key constraint - only KeyValue
> columns will be allowed.
> - If the table is immutable, the may not appear in a secondary
> index. This is because the mutations for indexes on immutable tables are
> calculated on the client-side, while this new syntax would potentially modify
> the value on the server-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546680#comment-15546680
]
James Taylor commented on PHOENIX-6:
With Phoenix, for non transactional tables, the updates to the HBase data all
happen at commit time, so UPSERT SELECT won't see uncommitted data. It's true,
though that: "Non transactional tables have no guarantees above and beyond the
HBase guarantee of row level atomicity (see here). In addition, non
transactional tables will not see their updates until after a commit has
occurred."
Will this new ON DUPLICATE KEY syntax and semantics help with your use case,
[~cameron.hatfield]?
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.9.0
>
>
> To support inserting a new row only if it doesn't already exist, we should
> support the "on duplicate key" construct for UPSERT. With this construct, the
> UPSERT VALUES statement would run atomically and would thus require a read
> before write which would obviously have a negative impact on performance. For
> an example of similar syntax , see MySQL documentation at
> http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> See this discussion for more detail:
> https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J.
> A related discussion is on PHOENIX-2909.
> Initially we'd support the following:
> # This would prevent the setting of VAL to 0 if the row already exists:
> {code}
> UPSERT INTO T (PK, VAL) VALUES ('a',0)
> ON DUPLICATE KEY IGNORE;
> {code}
> # This would increment the valueS of COUNTER1 and COUNTER2 if the row already
> exists and otherwise initialize them to 0:
> {code}
> UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
> ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
> {code}
> So the general form is:
> {code}
> UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE
> =, ...] ]
> {code}
> The following restrictions will apply:
> - The may not be part of the primary key constraint - only KeyValue
> columns will be allowed.
> - If the table is immutable, the may not appear in a secondary
> index. This is because the mutations for indexes on immutable tables are
> calculated on the client-side, while this new syntax would potentially modify
> the value on the server-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546605#comment-15546605
]
Cameron Hatfield commented on PHOENIX-6:
Ahh, I saw where I was getting confused. I thought that Phoenix implemented
executebatch according to the JDBC contract (multiple statements will be sent
in one go to the server), however instead they send each statement separately
to the underlying db.
Would probably be worthwhile to mention that if you want to use this for
counters / etc, that each separate statement hitting the same row requires a
separate commit (just to make it more obvious that it is how it would work).
This would also be an interesting interaction with how UPSERT ... SELECT
batches inserts, when duplicates are involved, since the it would act
differently depending on where duplicates where in the batch. Technically, I
believe this is a problem now, as UPSERT .. SELECT would allow you to read data
that is not yet "committed", so its possible that the documentation for that
should be updated (as a separate bug): "Non transactional tables have no
guarantees above and beyond the HBase guarantee of row level atomicity (see
here). *In addition, non transactional tables will not see their updates until
after a commit has occurred.*"
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.9.0
>
>
> To support inserting a new row only if it doesn't already exist, we should
> support the "on duplicate key" construct for UPSERT. With this construct, the
> UPSERT VALUES statement would run atomically and would thus require a read
> before write which would obviously have a negative impact on performance. For
> an example of similar syntax , see MySQL documentation at
> http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> See this discussion for more detail:
> https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J.
> A related discussion is on PHOENIX-2909.
> Initially we'd support the following:
> # This would prevent the setting of VAL to 0 if the row already exists:
> {code}
> UPSERT INTO T (PK, VAL) VALUES ('a',0)
> ON DUPLICATE KEY IGNORE;
> {code}
> # This would increment the valueS of COUNTER1 and COUNTER2 if the row already
> exists and otherwise initialize them to 0:
> {code}
> UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
> ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
> {code}
> So the general form is:
> {code}
> UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE
> =, ...] ]
> {code}
> The following restrictions will apply:
> - The may not be part of the primary key constraint - only KeyValue
> columns will be allowed.
> - If the table is immutable, the may not appear in a secondary
> index. This is because the mutations for indexes on immutable tables are
> calculated on the client-side, while this new syntax would potentially modify
> the value on the server-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546487#comment-15546487
]
James Taylor commented on PHOENIX-6:
Yes, agreed, [~cameron.hatfield]. If these two statements are in the same
commit batch, the result would be 0, 0:
{code}
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
-- With auto commit off, the result would be 'a', 0, 0
{code}
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.9.0
>
>
> To support inserting a new row only if it doesn't already exist, we should
> support the "on duplicate key" construct for UPSERT. With this construct, the
> UPSERT VALUES statement would run atomically and would thus require a read
> before write which would obviously have a negative impact on performance. For
> an example of similar syntax , see MySQL documentation at
> http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> See this discussion for more detail:
> https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J.
> A related discussion is on PHOENIX-2909.
> Initially we'd support the following:
> # This would prevent the setting of VAL to 0 if the row already exists:
> {code}
> UPSERT INTO T (PK, VAL) VALUES ('a',0)
> ON DUPLICATE KEY IGNORE;
> {code}
> # This would increment the valueS of COUNTER1 and COUNTER2 if the row already
> exists and otherwise initialize them to 0:
> {code}
> UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
> ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
> {code}
> So the general form is:
> {code}
> UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE
> =, ...] ]
> {code}
> The following restrictions will apply:
> - The may not be part of the primary key constraint - only KeyValue
> columns will be allowed.
> - If the table is immutable, the may not appear in a secondary
> index. This is because the mutations for indexes on immutable tables are
> calculated on the client-side, while this new syntax would potentially modify
> the value on the server-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15543705#comment-15543705
]
Cameron Hatfield edited comment on PHOENIX-6 at 10/4/16 7:45 PM:
-
At what point will the expressions be evaluated in the context of update
statement?
Specifically, intra-query order (Notice the use of COUNTER1 and COUNTER2 on the
RHS of each expression):
Statements ran in order, in two separate execute round trips to Phoenix/HBase
{code:sql}
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
{code}
After the second upsert, which would be the result?
COUNTER1, COUNTER2
1: 1, 1
2: 1, 2
3: 2, 1
Last time I looked at this during the update statement thread, I believe it was
number 1 that should be the result according to the SQL standard.
inter-query (Same query as example query in description, twice):
Multiple queries, within the same batch sent to the Phoenix coprocessor
{code:sql}
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
{code}
After the batch is run, which would be the result?
COUNTER1, COUNTER2
1: 0,0
2: 1,1
This is the one we had issues with choosing how we wanted to do it with the CAS
change. Since they aren't in the memstore until the whole batch is finished,
you have to keep state within the coprocessor, for each edit, to ensure it came
out with number 2. On top of that, if you have two different queries that will
have very different results in different orders, then batching can readily
affect the outcome. Right now, even though all of the warnings within HBase
talk about ordering not guaranteed for batching, for a single row, it seems to
be ordered in batch order as sent by the client. Aside from that, since
batching is usually a performance optimization, I wouldn't want the
optimization actually affecting the results.
>From a SQL standard direction, I believe 2 would be correct here, as there is
>no idea of a "transaction" for a batch in phoenix (even per key), so I would
>assume that it would be equiv. to auto commit after each statement.
was (Author: cameron.hatfield):
At what point will the expressions be evaluated in the context of update
statement?
Specifically, intra-query order (Notice the use of COUNTER1 and COUNTER2 on the
RHS of each expression):
Statements ran in order, in two separate execute round trips to Phoenix/HBase
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
After the second upsert, which would be the result?
COUNTER1, COUNTER2
1: 1, 1
2: 1, 2
3: 2, 1
Last time I looked at this during the update statement thread, I believe it was
number 1 that should be the result according to the SQL standard.
inter-query (Same query as example query in description, twice):
Multiple queries, within the same batch sent to the Phoenix coprocessor
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
After the batch is run, which would be the result?
COUNTER1, COUNTER2
1: 0,0
2: 1,1
This is the one we had issues with choosing how we wanted to do it with the CAS
change. Since they aren't in the memstore until the whole batch is finished,
you have to keep state within the coprocessor, for each edit, to ensure it came
out with number 2. On top of that, if you have two different queries that will
have very different results in different orders, then batching can readily
affect the outcome. Right now, even though all of the warnings within HBase
talk about ordering not guaranteed for batching, for a single row, it seems to
be ordered in batch order as sent by the client. Aside from that, since
batching is usually a performance optimization, I wouldn't want the
optimization actually affecting the results.
>From a SQL standard direction, I believe 2 would be correct here, as there is
>no idea of a "transaction" for a batch in phoenix (even per key), so I would
>assume that it would be equiv. to auto commit after each statement.
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue
[jira] [Comment Edited] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546436#comment-15546436
]
Cameron Hatfield edited comment on PHOENIX-6 at 10/4/16 7:43 PM:
-
Except, in the second case (two statements, same batch), the value of row 'a'
of would non-existent, as they have never been inserted before. So wouldn't the
result be 0,0 if it was in the same batch?
was (Author: cameron.hatfield):
Except, in the second case (two statements, same batch), the value of row 'a'
of would non-existent, as they have never been interested before. So wouldn't
the result be 0,0 if it was in the same batch?
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.9.0
>
>
> To support inserting a new row only if it doesn't already exist, we should
> support the "on duplicate key" construct for UPSERT. With this construct, the
> UPSERT VALUES statement would run atomically and would thus require a read
> before write which would obviously have a negative impact on performance. For
> an example of similar syntax , see MySQL documentation at
> http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> See this discussion for more detail:
> https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J.
> A related discussion is on PHOENIX-2909.
> Initially we'd support the following:
> # This would prevent the setting of VAL to 0 if the row already exists:
> {code}
> UPSERT INTO T (PK, VAL) VALUES ('a',0)
> ON DUPLICATE KEY IGNORE;
> {code}
> # This would increment the valueS of COUNTER1 and COUNTER2 if the row already
> exists and otherwise initialize them to 0:
> {code}
> UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
> ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
> {code}
> So the general form is:
> {code}
> UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE
> =, ...] ]
> {code}
> The following restrictions will apply:
> - The may not be part of the primary key constraint - only KeyValue
> columns will be allowed.
> - If the table is immutable, the may not appear in a secondary
> index. This is because the mutations for indexes on immutable tables are
> calculated on the client-side, while this new syntax would potentially modify
> the value on the server-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546436#comment-15546436
]
Cameron Hatfield commented on PHOENIX-6:
Except, in the second case (two statements, same batch), the value of row 'a'
of would non-existent, as they have never been interested before. So wouldn't
the result be 0,0 if it was in the same batch?
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.9.0
>
>
> To support inserting a new row only if it doesn't already exist, we should
> support the "on duplicate key" construct for UPSERT. With this construct, the
> UPSERT VALUES statement would run atomically and would thus require a read
> before write which would obviously have a negative impact on performance. For
> an example of similar syntax , see MySQL documentation at
> http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> See this discussion for more detail:
> https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J.
> A related discussion is on PHOENIX-2909.
> Initially we'd support the following:
> # This would prevent the setting of VAL to 0 if the row already exists:
> {code}
> UPSERT INTO T (PK, VAL) VALUES ('a',0)
> ON DUPLICATE KEY IGNORE;
> {code}
> # This would increment the valueS of COUNTER1 and COUNTER2 if the row already
> exists and otherwise initialize them to 0:
> {code}
> UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
> ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
> {code}
> So the general form is:
> {code}
> UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE
> =, ...] ]
> {code}
> The following restrictions will apply:
> - The may not be part of the primary key constraint - only KeyValue
> columns will be allowed.
> - If the table is immutable, the may not appear in a secondary
> index. This is because the mutations for indexes on immutable tables are
> calculated on the client-side, while this new syntax would potentially modify
> the value on the server-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-3265) Surround columns named date with double quotes
[
https://issues.apache.org/jira/browse/PHOENIX-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546346#comment-15546346
]
James Taylor commented on PHOENIX-3265:
---
How about this? {{SELECT k1 FROM t WHERE "DATE" >= to_date('2013-01-01')}}
Note that when you double quote an identifier, it becomes case sensitive, so
you'll need to match case with the way it was named in the CREATE TABLE
statement.
> Surround columns named date with double quotes
> --
>
> Key: PHOENIX-3265
> URL: https://issues.apache.org/jira/browse/PHOENIX-3265
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: James Taylor
>Assignee: Eric Lomore
>
> In Phoenix {{date}} is not a reserved word, but perhaps in Calcite it is? If
> that's the case, we should surround occurrences of columns named {{date}} in
> double quotes to prevent the parser error.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (PHOENIX-3265) Surround columns named date with double quotes
[
https://issues.apache.org/jira/browse/PHOENIX-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546325#comment-15546325
]
Eric Lomore edited comment on PHOENIX-3265 at 10/4/16 7:04 PM:
---
An interrelated issue is the following SQL select satement failing:
{{"SELECT k1 FROM t WHERE date >= to_date('2013-01-01')";}}
Caused by: org.apache.phoenix.calcite.parser.ParseException: Encountered "date
>="
Was expecting one of...
.
.
"DATE" ...
"DATE" ...
was (Author: lomoree):
An interrelated issue is the following SQL select satement failing:
{{"SELECT k1 FROM t WHERE date >= to_date('2013-01-01')";}}
Recognition of "data >=" is failing.
> Surround columns named date with double quotes
> --
>
> Key: PHOENIX-3265
> URL: https://issues.apache.org/jira/browse/PHOENIX-3265
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: James Taylor
>Assignee: Eric Lomore
>
> In Phoenix {{date}} is not a reserved word, but perhaps in Calcite it is? If
> that's the case, we should surround occurrences of columns named {{date}} in
> double quotes to prevent the parser error.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-3265) Surround columns named date with double quotes
[
https://issues.apache.org/jira/browse/PHOENIX-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546325#comment-15546325
]
Eric Lomore commented on PHOENIX-3265:
--
An interrelated issue is the following SQL select satement failing:
{{"SELECT k1 FROM t WHERE date >= to_date('2013-01-01')";}}
Recognition of "data >=" is failing.
> Surround columns named date with double quotes
> --
>
> Key: PHOENIX-3265
> URL: https://issues.apache.org/jira/browse/PHOENIX-3265
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: James Taylor
>Assignee: Eric Lomore
>
> In Phoenix {{date}} is not a reserved word, but perhaps in Calcite it is? If
> that's the case, we should surround occurrences of columns named {{date}} in
> double quotes to prevent the parser error.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546291#comment-15546291
]
James Taylor commented on PHOENIX-3347:
---
We'll need to change to a conformance that allows SELECT without FROM. In
general, we'll need to support existing Phoenix syntax and semantics. One gray
area is operational type commands like managing UDF jars and updating
statistics.
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause. Either need to change conformance or stop supporting SELECT without
> FROM as Phoenix currently does.
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
> conformance is, say, STRICT_2003.}}
> Based on PhoenixCalciteEmbeddedDriver.java, we are using ORACLE_10
> conformance which does not support this
> {code}setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString()){code}
> Confirming this is the fact that it is specifically the SqlValidator throwing
> the exception in relevant test cases
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore updated PHOENIX-3347:
-
Description:
Current conformance settings do not allow SELECT statements without a FROM
clause. Either need to change conformance or stop supporting SELECT without
FROM as Phoenix currently does.
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
conformance is, say, STRICT_2003.}}
Based on PhoenixCalciteEmbeddedDriver.java, we are using ORACLE_10 conformance
which does not support this
{code}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString()){code}
Confirming this is the fact that it is specifically the SqlValidator throwing
the exception in relevant test cases
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
was:
Current conformance settings do not allow SELECT statements without a FROM
clause. Either need to change conformance or stop supporting SELECT without
FROM as Phoenix currently does.
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
conformance is, say, STRICT_2003.}}
Based on PhoenixCalciteEmbeddedDriver.java, we are using ORACLE_10 conformance
which I assume does not support this,
{code}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString()){code}
Confirming this is the fact that the SqlValidator throws the exception in
relevant test cases
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause. Either need to change conformance or stop supporting SELECT without
> FROM as Phoenix currently does.
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
> conformance is, say, STRICT_2003.}}
> Based on PhoenixCalciteEmbeddedDriver.java, we are using ORACLE_10
> conformance which does not support this
> {code}setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString()){code}
> Confirming this is the fact that it is specifically the SqlValidator throwing
> the exception in relevant test cases
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-3181) Run test methods in parallel to reduce test suite run time
[
https://issues.apache.org/jira/browse/PHOENIX-3181?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546279#comment-15546279
]
Hadoop QA commented on PHOENIX-3181:
{color:red}-1 overall{color}. Here are the results of testing the latest
attachment
http://issues.apache.org/jira/secure/attachment/12831576/PHOENIX-3181_v2.patch
against master branch at commit 34ba28e60f66cb7b537b60c9ef04b8a26036f010.
ATTACHMENT ID: 12831576
{color:green}+1 @author{color}. The patch does not contain any @author
tags.
{color:green}+1 tests included{color}. The patch appears to include 4 new
or modified tests.
{color:green}+1 javac{color}. The applied patch does not increase the
total number of javac compiler warnings.
{color:red}-1 javadoc{color}. The javadoc tool appears to have generated
38 warning messages.
{color:red}-1 release audit{color}. The applied patch generated 1 release
audit warnings (more than the master's current 0 warnings).
{color:green}+1 lineLengths{color}. The patch does not introduce lines
longer than 100
{color:red}-1 core tests{color}. The patch failed these unit tests:
./phoenix-core/target/failsafe-reports/TEST-[LocalIndexIT_isNamespaceMapped=false]
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.DistinctPrefixFilterIT
Test results:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/614//testReport/
Release audit warnings:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/614//artifact/patchprocess/patchReleaseAuditWarnings.txt
Javadoc warnings:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/614//artifact/patchprocess/patchJavadocWarnings.txt
Console output:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/614//console
This message is automatically generated.
> Run test methods in parallel to reduce test suite run time
> --
>
> Key: PHOENIX-3181
> URL: https://issues.apache.org/jira/browse/PHOENIX-3181
> Project: Phoenix
> Issue Type: Bug
>Reporter: James Taylor
>Assignee: James Taylor
> Attachments: PHOENIX-3181_v1.patch, PHOENIX-3181_v2.patch
>
>
> With PHOENIX-3036, the tests within a test class can be executed in parallel
> since the table names won't conflict. This should greatly reduce the time
> taken to run all of our tests.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore updated PHOENIX-3347:
-
Description:
Current conformance settings do not allow SELECT statements without a FROM
clause. Either need to change conformance or stop supporting SELECT without
FROM as Phoenix currently does.
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
conformance is, say, STRICT_2003.}}
Based on PhoenixCalciteEmbeddedDriver.java, we are using ORACLE_10 conformance
which I assume does not support this,
{code}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString()){code}
Confirming this is the fact that the SqlValidator throws the exception in
relevant test cases
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
was:
Current conformance settings do not allow SELECT statements without a FROM
clause
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
conformance is, say, STRICT_2003.}}
Based on {{PhoenixCalciteEmbeddedDriver.java}}, we are using ORACLE_10
conformance which I assume does not support this,
{code}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString()){code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause. Either need to change conformance or stop supporting SELECT without
> FROM as Phoenix currently does.
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
> conformance is, say, STRICT_2003.}}
> Based on PhoenixCalciteEmbeddedDriver.java, we are using ORACLE_10
> conformance which I assume does not support this,
> {code}setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString()){code}
> Confirming this is the fact that the SqlValidator throws the exception in
> relevant test cases
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore updated PHOENIX-3347:
-
Description:
Current conformance settings do not allow SELECT statements without a FROM
clause
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
conformance is, say, STRICT_2003.}}
Based on {{PhoenixCalciteEmbeddedDriver.java}}, we are using ORACLE_10
conformance which I assume does not support this,
{{ setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString())}}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
was:
Current conformance settings do not allow SELECT statements without a FROM
clause
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
conformance is, say, STRICT_2003.}}
{code:title=PhoenixCalciteEmbeddedDriver.java} public Connection
connect(String url, Properties info) throws SQLException {
if (!acceptsURL(url)) {
return null;
}
Properties info2 = new Properties(info);
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.TYPE_SYSTEM.camelName(),
PhoenixRelDataTypeSystem.class.getName());
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
> conformance is, say, STRICT_2003.}}
> Based on {{PhoenixCalciteEmbeddedDriver.java}}, we are using ORACLE_10
> conformance which I assume does not support this,
> {{ setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString())}}
> Note that the actual failure occurs when the SqlValidator rejects the
> statement
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
> Either need to change conformance or stop supporting SELECT without FROM as
> Phoenix currently does.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore updated PHOENIX-3347:
-
Description:
Current conformance settings do not allow SELECT statements without a FROM
clause
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
conformance is, say, STRICT_2003.}}
Based on {{PhoenixCalciteEmbeddedDriver.java}}, we are using ORACLE_10
conformance which I assume does not support this,
{code}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString()){code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
was:
Current conformance settings do not allow SELECT statements without a FROM
clause
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
conformance is, say, STRICT_2003.}}
Based on {{PhoenixCalciteEmbeddedDriver.java}}, we are using ORACLE_10
conformance which I assume does not support this,
{{ setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString())}}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
> conformance is, say, STRICT_2003.}}
> Based on {{PhoenixCalciteEmbeddedDriver.java}}, we are using ORACLE_10
> conformance which I assume does not support this,
> {code}setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString()){code}
> Note that the actual failure occurs when the SqlValidator rejects the
> statement
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
> Either need to change conformance or stop supporting SELECT without FROM as
> Phoenix currently does.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore updated PHOENIX-3347:
-
Description:
Current conformance settings do not allow SELECT statements without a FROM
clause
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
* PostgreSQL. The parser allows SELECT without FROM, but the validator fails
* if conformance is, say, STRICT_2003.}}
{code:title=PhoenixCalciteEmbeddedDriver.java} public Connection
connect(String url, Properties info) throws SQLException {
if (!acceptsURL(url)) {
return null;
}
Properties info2 = new Properties(info);
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.TYPE_SYSTEM.camelName(),
PhoenixRelDataTypeSystem.class.getName());
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
was:
Current conformance settings do not allow SELECT statements without a FROM
clause
{code:title=PhoenixCalciteEmbeddedDriver.java} public Connection
connect(String url, Properties info) throws SQLException {
if (!acceptsURL(url)) {
return null;
}
Properties info2 = new Properties(info);
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.TYPE_SYSTEM.camelName(),
PhoenixRelDataTypeSystem.class.getName());
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> * PostgreSQL. The parser allows SELECT without FROM, but the validator fails
> * if conformance is, say, STRICT_2003.}}
> {code:title=PhoenixCalciteEmbeddedDriver.java} public Connection
> connect(String url, Properties info) throws SQLException {
> if (!acceptsURL(url)) {
> return null;
> }
>
> Properties info2 = new Properties(info);
> setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.TYPE_SYSTEM.camelName(),
> PhoenixRelDataTypeSystem.class.getName());
> setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString());{code}
> Note that the actual failure occurs when the SqlValidator rejects the
> statement
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
> Either need to change conformance or stop supporting SELECT without FROM as
> Phoenix currently does.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore updated PHOENIX-3347:
-
Description:
Current conformance settings do not allow SELECT statements without a FROM
clause
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
conformance is, say, STRICT_2003.}}
{code:title=PhoenixCalciteEmbeddedDriver.java} public Connection
connect(String url, Properties info) throws SQLException {
if (!acceptsURL(url)) {
return null;
}
Properties info2 = new Properties(info);
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.TYPE_SYSTEM.camelName(),
PhoenixRelDataTypeSystem.class.getName());
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
was:
Current conformance settings do not allow SELECT statements without a FROM
clause
According to the Calcite parser,
{{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
* PostgreSQL. The parser allows SELECT without FROM, but the validator fails
* if conformance is, say, STRICT_2003.}}
{code:title=PhoenixCalciteEmbeddedDriver.java} public Connection
connect(String url, Properties info) throws SQLException {
if (!acceptsURL(url)) {
return null;
}
Properties info2 = new Properties(info);
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.TYPE_SYSTEM.camelName(),
PhoenixRelDataTypeSystem.class.getName());
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause
> According to the Calcite parser,
> {{FROM is mandatory in standard SQL, optional in dialects such as MySQL,
> PostgreSQL. The parser allows SELECT without FROM, but the validator fails if
> conformance is, say, STRICT_2003.}}
> {code:title=PhoenixCalciteEmbeddedDriver.java} public Connection
> connect(String url, Properties info) throws SQLException {
> if (!acceptsURL(url)) {
> return null;
> }
>
> Properties info2 = new Properties(info);
> setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.TYPE_SYSTEM.camelName(),
> PhoenixRelDataTypeSystem.class.getName());
> setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString());{code}
> Note that the actual failure occurs when the SqlValidator rejects the
> statement
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
> Either need to change conformance or stop supporting SELECT without FROM as
> Phoenix currently does.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore updated PHOENIX-3347:
-
Description:
Current conformance settings do not allow SELECT statements without a FROM
clause
{code:title=PhoenixCalciteEmbeddedDriver.java} public Connection
connect(String url, Properties info) throws SQLException {
if (!acceptsURL(url)) {
return null;
}
Properties info2 = new Properties(info);
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.TYPE_SYSTEM.camelName(),
PhoenixRelDataTypeSystem.class.getName());
setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
was:
Current conformance settings do not allow SELECT statements without a FROM
clause
{code:title=PhoenixCalciteEmbeddedDriver.java}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause
> {code:title=PhoenixCalciteEmbeddedDriver.java} public Connection
> connect(String url, Properties info) throws SQLException {
> if (!acceptsURL(url)) {
> return null;
> }
>
> Properties info2 = new Properties(info);
> setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.TYPE_SYSTEM.camelName(),
> PhoenixRelDataTypeSystem.class.getName());
> setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString());{code}
> Note that the actual failure occurs when the SqlValidator rejects the
> statement
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
> Either need to change conformance or stop supporting SELECT without FROM as
> Phoenix currently does.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Assigned] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore reassigned PHOENIX-3347:
Assignee: Eric Lomore
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>Assignee: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause
> {code:title=PhoenixCalciteEmbeddedDriver.java} public Connection
> connect(String url, Properties info) throws SQLException {
> if (!acceptsURL(url)) {
> return null;
> }
>
> Properties info2 = new Properties(info);
> setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.TYPE_SYSTEM.camelName(),
> PhoenixRelDataTypeSystem.class.getName());
> setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString());{code}
> Note that the actual failure occurs when the SqlValidator rejects the
> statement
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
> Either need to change conformance or stop supporting SELECT without FROM as
> Phoenix currently does.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore updated PHOENIX-3347:
-
Description:
Current conformance settings do not allow SELECT statements without a FROM
clause
{code|PhoenixCalciteEmbeddedDriver.java}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
was:
Current conformance settings do not allow SELECT statements without a FROM
clause
{code}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause
> {code|PhoenixCalciteEmbeddedDriver.java}setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString());{code}
> Note that the actual failure occurs when the SqlValidator rejects the
> statement
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
> Either need to change conformance or stop supporting SELECT without FROM as
> Phoenix currently does.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore updated PHOENIX-3347:
-
Description:
Current conformance settings do not allow SELECT statements without a FROM
clause
{code:title=PhoenixCalciteEmbeddedDriver.java}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
was:
Current conformance settings do not allow SELECT statements without a FROM
clause
{code=PhoenixCalciteEmbeddedDriver.java}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause
> {code:title=PhoenixCalciteEmbeddedDriver.java}
> setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString());{code}
> Note that the actual failure occurs when the SqlValidator rejects the
> statement
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
> Either need to change conformance or stop supporting SELECT without FROM as
> Phoenix currently does.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
[
https://issues.apache.org/jira/browse/PHOENIX-3347?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Lomore updated PHOENIX-3347:
-
Description:
Current conformance settings do not allow SELECT statements without a FROM
clause
{code=PhoenixCalciteEmbeddedDriver.java}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
was:
Current conformance settings do not allow SELECT statements without a FROM
clause
{code|PhoenixCalciteEmbeddedDriver.java}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
> Change conformance or remove SELECT statements without FROM clauses
> ---
>
> Key: PHOENIX-3347
> URL: https://issues.apache.org/jira/browse/PHOENIX-3347
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: Eric Lomore
>
> Current conformance settings do not allow SELECT statements without a FROM
> clause
> {code=PhoenixCalciteEmbeddedDriver.java}setPropertyIfNotSpecified(
> info2,
> CalciteConnectionProperty.CONFORMANCE.camelName(),
> SqlConformance.ORACLE_10.toString());{code}
> Note that the actual failure occurs when the SqlValidator rejects the
> statement
> {{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT
> must have a FROM clause}}
> Either need to change conformance or stop supporting SELECT without FROM as
> Phoenix currently does.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Created] (PHOENIX-3347) Change conformance or remove SELECT statements without FROM clauses
Eric Lomore created PHOENIX-3347:
Summary: Change conformance or remove SELECT statements without
FROM clauses
Key: PHOENIX-3347
URL: https://issues.apache.org/jira/browse/PHOENIX-3347
Project: Phoenix
Issue Type: Sub-task
Reporter: Eric Lomore
Current conformance settings do not allow SELECT statements without a FROM
clause
{code}setPropertyIfNotSpecified(
info2,
CalciteConnectionProperty.CONFORMANCE.camelName(),
SqlConformance.ORACLE_10.toString());{code}
Note that the actual failure occurs when the SqlValidator rejects the statement
{{Caused by: org.apache.calcite.sql.validate.SqlValidatorException: SELECT must
have a FROM clause}}
Either need to change conformance or stop supporting SELECT without FROM as
Phoenix currently does.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546218#comment-15546218
]
James Taylor commented on PHOENIX-6:
The UPSERT VALUES ... ON DUPLICATE KEY will be evaluated on the server side at
commit time. As with existing UPSERT VALUES calls, only the last statement
takes effect if multiple statements for the same row key are in the same batch.
So in your example, assuming auto commit is true:
{code}
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
-- Result would be: 'a', 0, 0
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
-- Result would be 'a', 1, 1
{code}
Since we do a read under lock of the row, we'd get the values of row 'a' before
any of the ON DUPLICATE KEY expressions are evaluated, and then we'd evaluate
all of the expressions against the current values of row 'a'.
I'll make sure to add a unit test like this - it's a good test.
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>Reporter: James Taylor
>Assignee: James Taylor
> Fix For: 4.9.0
>
>
> To support inserting a new row only if it doesn't already exist, we should
> support the "on duplicate key" construct for UPSERT. With this construct, the
> UPSERT VALUES statement would run atomically and would thus require a read
> before write which would obviously have a negative impact on performance. For
> an example of similar syntax , see MySQL documentation at
> http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html
> See this discussion for more detail:
> https://groups.google.com/d/msg/phoenix-hbase-user/Bof-TLrbTGg/68bnc8ZcWe0J.
> A related discussion is on PHOENIX-2909.
> Initially we'd support the following:
> # This would prevent the setting of VAL to 0 if the row already exists:
> {code}
> UPSERT INTO T (PK, VAL) VALUES ('a',0)
> ON DUPLICATE KEY IGNORE;
> {code}
> # This would increment the valueS of COUNTER1 and COUNTER2 if the row already
> exists and otherwise initialize them to 0:
> {code}
> UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
> ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
> {code}
> So the general form is:
> {code}
> UPSERT ... VALUES ... [ ON DUPLICATE KEY [IGNORE | UPDATE
> =, ...] ]
> {code}
> The following restrictions will apply:
> - The may not be part of the primary key constraint - only KeyValue
> columns will be allowed.
> - If the table is immutable, the may not appear in a secondary
> index. This is because the mutations for indexes on immutable tables are
> calculated on the client-side, while this new syntax would potentially modify
> the value on the server-side.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-2548) Local Indexing Not Looks Like Working As Expected
[
https://issues.apache.org/jira/browse/PHOENIX-2548?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546191#comment-15546191
]
James Taylor commented on PHOENIX-2548:
---
I tried the above in 4.8.0 release and got the following explain plan:
{code}
0: jdbc:phoenix:localhost> explain select devicename from devicedim_type1 where
tenant_id = 'ccd' and devicename = 'abc' and macaddress = 'afg';
+--+
| PLAN
|
+--+
| CLIENT 4-CHUNK PARALLEL 4-WAY ROUND ROBIN RANGE SCAN OVER DEVICEDIM_TYPE1
[1,'ccd','abc','afg'] |
| SERVER FILTER BY FIRST KEY ONLY
|
+--+
2 rows selected (0.047 seconds)
{code}
Although somewhat confusing (see PHOENIX-3344), the local index is being used
in this case. The local index resides in the same table as the data, so you'll
see the range scan over the data table. You'll notice the [1 in the range being
scanned over - that refers to the index of the local index (i.e. this is the
first one).
If you're using local indexes, I'd definitely recommend using Phoenix 4.8 or
above.
> Local Indexing Not Looks Like Working As Expected
> -
>
> Key: PHOENIX-2548
> URL: https://issues.apache.org/jira/browse/PHOENIX-2548
> Project: Phoenix
> Issue Type: Bug
>Affects Versions: 4.5.2
>Reporter: Gokhan Cagrici
>Assignee: Rajeshbabu Chintaguntla
>Priority: Critical
> Labels: verify
> Fix For: 4.9.0
>
>
> Hi,
> We are accessing this table using a variety of different WHERE clauses and
> for each of them, we are trying to create an appropriate index to avoid full
> table scan. Since there is going to be almost 20 indexes, we tried to proceed
> with local indexing since there will be a lot of writes.
> Here is the table definition:
> CREATE TABLE DEVICEDIM_TYPE1 (
> TENANT_ID VARCHAR NOT NULL,
> DEVICE_TYPE1_KEY BIGINT NOT NULL,
> CLASSNAME VARCHAR(64),
> DAY_IN_MONTH SMALLINT,
> MONTH_NUMBER SMALLINT,
> QUARTER_NUMBER SMALLINT,
> YEAR SMALLINT,
> WEEK_NUMBER SMALLINT,
> YEAR_FOR_WEEK SMALLINT,
> HOUR SMALLINT,
> MINUTE SMALLINT,
> IPADDRESS VARCHAR(50),
> DEVICENAME VARCHAR(255),
> MACADDRESS VARCHAR(30),
> CONSTRAINT PK PRIMARY KEY (TENANT_ID, DEVICE_TYPE1_KEY)
> ) SALT_BUCKETS=4, COMPRESSION='GZ', VERSIONS=1, MULTI_TENANT=TRUE;
> And here is the index:
> create local index gokhan_ix2 on devicedim_type1 (devicename, macaddress)
> Now if I execute this:
> explain select devicename from devicedim_type1 where tenant_id = 'ccd' and
> devicename = 'abc' and macaddress = 'afg'
> Here is the output:
> CLIENT 4-CHUNK PARALLEL 4-WAY RANGE SCAN OVER DEVICEDIM_TYPE1 [0,'ccd']
> SERVER FILTER BY (DEVICENAME = 'abc' AND MACADDRESS = 'afg')
> SERVER 100 ROW LIMIT
> CLIENT 100 ROW LIMIT
> I was expecting the index to be used. Am I wrong?
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Comment Edited] (PHOENIX-6) Support ON DUPLICATE KEY construct
[
https://issues.apache.org/jira/browse/PHOENIX-6?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15543705#comment-15543705
]
Cameron Hatfield edited comment on PHOENIX-6 at 10/4/16 6:01 PM:
-
At what point will the expressions be evaluated in the context of update
statement?
Specifically, intra-query order (Notice the use of COUNTER1 and COUNTER2 on the
RHS of each expression):
Statements ran in order, in two separate execute round trips to Phoenix/HBase
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
After the second upsert, which would be the result?
COUNTER1, COUNTER2
1: 1, 1
2: 1, 2
3: 2, 1
Last time I looked at this during the update statement thread, I believe it was
number 1 that should be the result according to the SQL standard.
inter-query (Same query as example query in description, twice):
Multiple queries, within the same batch sent to the Phoenix coprocessor
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
After the batch is run, which would be the result?
COUNTER1, COUNTER2
1: 0,0
2: 1,1
This is the one we had issues with choosing how we wanted to do it with the CAS
change. Since they aren't in the memstore until the whole batch is finished,
you have to keep state within the coprocessor, for each edit, to ensure it came
out with number 2. On top of that, if you have two different queries that will
have very different results in different orders, then batching can readily
affect the outcome. Right now, even though all of the warnings within HBase
talk about ordering not guaranteed for batching, for a single row, it seems to
be ordered in batch order as sent by the client. Aside from that, since
batching is usually a performance optimization, I wouldn't want the
optimization actually affecting the results.
>From a SQL standard direction, I believe 2 would be correct here, as there is
>no idea of a "transaction" for a batch in phoenix (even per key), so I would
>assume that it would be equiv. to auto commit after each statement.
was (Author: cameron.hatfield):
At what point will the expressions be evaluated in the context of update
statement?
Specifically, inter-query order (Notice the use of COUNTER1 and COUNTER2 on the
RHS of each expression):
Statements ran in order, in two separate execute round trips to Phoenix/HBase
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER2 + 1, COUNTER2 = COUNTER1 + 1;
After the second upsert, which would be the result?
COUNTER1, COUNTER2
1: 1, 1
2: 1, 2
3: 2, 1
Last time I looked at this during the update statement thread, I believe it was
number 1 that should be the result according to the SQL standard.
intra-query (Same querie as example query in description, twice):
Multiple queries, within the same batch sent to the Phoenix coprocessor
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
UPSERT INTO T (PK, COUNTER1, COUNTER2) VALUES ('a',0,0)
ON DUPLICATE KEY COUNTER1 = COUNTER1 + 1, COUNTER2 = COUNTER2 + 1;
After the batch is run, which would be the result?
COUNTER1, COUNTER2
1: 0,0
2: 1,1
This is the one we had issues with choosing how we wanted to do it with the CAS
change. Since they aren't in the memstore until the whole batch is finished,
you have to keep state within the coprocessor, for each edit, to ensure it came
out with number 2. On top of that, if you have two different queries that will
have very different results in different orders, then batching can readily
affect the outcome. Right now, even though all of the warnings within HBase
talk about ordering not guaranteed for batching, for a single row, it seems to
be ordered in batch order as sent by the client. Aside from that, since
batching is usually a performance optimization, I wouldn't want the
optimization actually affecting the results.
>From a SQL standard direction, I believe 2 would be correct here, as there is
>no idea of a "transaction" for a batch in phoenix (even per key), so I would
>assume that it would be equiv. to auto commit after each statement.
> Support ON DUPLICATE KEY construct
> --
>
> Key: PHOENIX-6
> URL: https://issues.apache.org/jira/browse/PHOENIX-6
> Project: Phoenix
> Issue Type: New Feature
>
[jira] [Commented] (PHOENIX-3265) Surround columns named date with double quotes
[
https://issues.apache.org/jira/browse/PHOENIX-3265?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546136#comment-15546136
]
Eric Lomore commented on PHOENIX-3265:
--
Another keyword experiencing the same issue is {{dec}}.
> Surround columns named date with double quotes
> --
>
> Key: PHOENIX-3265
> URL: https://issues.apache.org/jira/browse/PHOENIX-3265
> Project: Phoenix
> Issue Type: Sub-task
>Reporter: James Taylor
>Assignee: Eric Lomore
>
> In Phoenix {{date}} is not a reserved word, but perhaps in Calcite it is? If
> that's the case, we should surround occurrences of columns named {{date}} in
> double quotes to prevent the parser error.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Commented] (PHOENIX-2548) Local Indexing Not Looks Like Working As Expected
[ https://issues.apache.org/jira/browse/PHOENIX-2548?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15546130#comment-15546130 ] Jacobo Coll commented on PHOENIX-2548: -- Hi there, Is there any progress on this? I had also the same problem, and I was expecting the index to be used. I don't see much problem, since the data is already there, you only have to add some more filters. Is not like a global index, where you don't have all the data. > Local Indexing Not Looks Like Working As Expected > - > > Key: PHOENIX-2548 > URL: https://issues.apache.org/jira/browse/PHOENIX-2548 > Project: Phoenix > Issue Type: Bug >Affects Versions: 4.5.2 >Reporter: Gokhan Cagrici >Assignee: Rajeshbabu Chintaguntla >Priority: Critical > Labels: verify > Fix For: 4.9.0 > > > Hi, > We are accessing this table using a variety of different WHERE clauses and > for each of them, we are trying to create an appropriate index to avoid full > table scan. Since there is going to be almost 20 indexes, we tried to proceed > with local indexing since there will be a lot of writes. > Here is the table definition: > CREATE TABLE DEVICEDIM_TYPE1 ( > TENANT_ID VARCHAR NOT NULL, > DEVICE_TYPE1_KEY BIGINT NOT NULL, > CLASSNAME VARCHAR(64), > DAY_IN_MONTH SMALLINT, > MONTH_NUMBER SMALLINT, > QUARTER_NUMBER SMALLINT, > YEAR SMALLINT, > WEEK_NUMBER SMALLINT, > YEAR_FOR_WEEK SMALLINT, > HOUR SMALLINT, > MINUTE SMALLINT, > IPADDRESS VARCHAR(50), > DEVICENAME VARCHAR(255), > MACADDRESS VARCHAR(30), > CONSTRAINT PK PRIMARY KEY (TENANT_ID, DEVICE_TYPE1_KEY) > ) SALT_BUCKETS=4, COMPRESSION='GZ', VERSIONS=1, MULTI_TENANT=TRUE; > And here is the index: > create local index gokhan_ix2 on devicedim_type1 (devicename, macaddress) > Now if I execute this: > explain select devicename from devicedim_type1 where tenant_id = 'ccd' and > devicename = 'abc' and macaddress = 'afg' > Here is the output: > CLIENT 4-CHUNK PARALLEL 4-WAY RANGE SCAN OVER DEVICEDIM_TYPE1 [0,'ccd'] > SERVER FILTER BY (DEVICENAME = 'abc' AND MACADDRESS = 'afg') > SERVER 100 ROW LIMIT > CLIENT 100 ROW LIMIT > I was expecting the index to be used. Am I wrong? -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-3181) Run test methods in parallel to reduce test suite run time
[ https://issues.apache.org/jira/browse/PHOENIX-3181?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-3181: -- Attachment: PHOENIX-3181_v2.patch Passed twice, trying one more time to make sure. > Run test methods in parallel to reduce test suite run time > -- > > Key: PHOENIX-3181 > URL: https://issues.apache.org/jira/browse/PHOENIX-3181 > Project: Phoenix > Issue Type: Bug >Reporter: James Taylor >Assignee: James Taylor > Attachments: PHOENIX-3181_v1.patch, PHOENIX-3181_v2.patch > > > With PHOENIX-3036, the tests within a test class can be executed in parallel > since the table names won't conflict. This should greatly reduce the time > taken to run all of our tests. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-3181) Run test methods in parallel to reduce test suite run time
[ https://issues.apache.org/jira/browse/PHOENIX-3181?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-3181: -- Attachment: (was: PHOENIX-3181_v2.patch) > Run test methods in parallel to reduce test suite run time > -- > > Key: PHOENIX-3181 > URL: https://issues.apache.org/jira/browse/PHOENIX-3181 > Project: Phoenix > Issue Type: Bug >Reporter: James Taylor >Assignee: James Taylor > Attachments: PHOENIX-3181_v1.patch > > > With PHOENIX-3036, the tests within a test class can be executed in parallel > since the table names won't conflict. This should greatly reduce the time > taken to run all of our tests. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (PHOENIX-2827) Support OFFSET in Calcite-Phoenix
[ https://issues.apache.org/jira/browse/PHOENIX-2827?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15545942#comment-15545942 ] ASF GitHub Bot commented on PHOENIX-2827: - Github user lomoree closed the pull request at: https://github.com/apache/phoenix/pull/213 > Support OFFSET in Calcite-Phoenix > - > > Key: PHOENIX-2827 > URL: https://issues.apache.org/jira/browse/PHOENIX-2827 > Project: Phoenix > Issue Type: Task >Reporter: Maryann Xue >Assignee: Eric Lomore > Labels: calcite > -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[GitHub] phoenix pull request #213: PHOENIX-2827 Support OFFSET in Calcite-Phoenix
Github user lomoree closed the pull request at: https://github.com/apache/phoenix/pull/213 --- 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-3181) Run test methods in parallel to reduce test suite run time
[
https://issues.apache.org/jira/browse/PHOENIX-3181?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15544679#comment-15544679
]
Hadoop QA commented on PHOENIX-3181:
{color:red}-1 overall{color}. Here are the results of testing the latest
attachment
http://issues.apache.org/jira/secure/attachment/12831476/PHOENIX-3181_v2.patch
against master branch at commit 34ba28e60f66cb7b537b60c9ef04b8a26036f010.
ATTACHMENT ID: 12831476
{color:green}+1 @author{color}. The patch does not contain any @author
tags.
{color:green}+1 tests included{color}. The patch appears to include 4 new
or modified tests.
{color:green}+1 javac{color}. The applied patch does not increase the
total number of javac compiler warnings.
{color:red}-1 javadoc{color}. The javadoc tool appears to have generated
38 warning messages.
{color:red}-1 release audit{color}. The applied patch generated 1 release
audit warnings (more than the master's current 0 warnings).
{color:green}+1 lineLengths{color}. The patch does not introduce lines
longer than 100
{color:green}+1 core tests{color}. The patch passed unit tests in .
Test results:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/613//testReport/
Release audit warnings:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/613//artifact/patchprocess/patchReleaseAuditWarnings.txt
Javadoc warnings:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/613//artifact/patchprocess/patchJavadocWarnings.txt
Console output:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/613//console
This message is automatically generated.
> Run test methods in parallel to reduce test suite run time
> --
>
> Key: PHOENIX-3181
> URL: https://issues.apache.org/jira/browse/PHOENIX-3181
> Project: Phoenix
> Issue Type: Bug
>Reporter: James Taylor
>Assignee: James Taylor
> Attachments: PHOENIX-3181_v1.patch, PHOENIX-3181_v2.patch
>
>
> With PHOENIX-3036, the tests within a test class can be executed in parallel
> since the table names won't conflict. This should greatly reduce the time
> taken to run all of our tests.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
[jira] [Updated] (PHOENIX-3181) Run test methods in parallel to reduce test suite run time
[ https://issues.apache.org/jira/browse/PHOENIX-3181?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-3181: -- Attachment: PHOENIX-3181_v2.patch > Run test methods in parallel to reduce test suite run time > -- > > Key: PHOENIX-3181 > URL: https://issues.apache.org/jira/browse/PHOENIX-3181 > Project: Phoenix > Issue Type: Bug >Reporter: James Taylor >Assignee: James Taylor > Attachments: PHOENIX-3181_v1.patch, PHOENIX-3181_v2.patch > > > With PHOENIX-3036, the tests within a test class can be executed in parallel > since the table names won't conflict. This should greatly reduce the time > taken to run all of our tests. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Updated] (PHOENIX-3181) Run test methods in parallel to reduce test suite run time
[ https://issues.apache.org/jira/browse/PHOENIX-3181?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-3181: -- Attachment: (was: PHOENIX-3181_v2.patch) > Run test methods in parallel to reduce test suite run time > -- > > Key: PHOENIX-3181 > URL: https://issues.apache.org/jira/browse/PHOENIX-3181 > Project: Phoenix > Issue Type: Bug >Reporter: James Taylor >Assignee: James Taylor > Attachments: PHOENIX-3181_v1.patch, PHOENIX-3181_v2.patch > > > With PHOENIX-3036, the tests within a test class can be executed in parallel > since the table names won't conflict. This should greatly reduce the time > taken to run all of our tests. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
[jira] [Commented] (PHOENIX-2948) Snapshot support for Phoenix Tables
[ https://issues.apache.org/jira/browse/PHOENIX-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=15544486#comment-15544486 ] Sergey Soldatov commented on PHOENIX-2948: -- I suggest to improve this approach to not only taking snapshot, but make a backup table feature. There are two approaches: 1. Create snapshots for all required tables and run a separate MR job for storing data from snapshots to file/files on hdfs. Metadata for table need to be stored separately. 2. Use new HBase backup feature (hopefully it will be accepted in the nearest releases of HBase). But in this case it will work only with the latest HBase. First way is quite similar to HBase backup and implementing it may be painful (for HBase team it's already took about a year and still in progress). We may try to implement it in easier way, but not sure whether it's possible or not. Also there is a questions how to use it. Whether it should be a new command for sqlline or external tool or both. All thoughts are welcome, I will try to collect all of them and make a summary document. > Snapshot support for Phoenix Tables > --- > > Key: PHOENIX-2948 > URL: https://issues.apache.org/jira/browse/PHOENIX-2948 > Project: Phoenix > Issue Type: New Feature >Affects Versions: 4.7.0 >Reporter: Anil Gupta >Assignee: Sergey Soldatov > Labels: Backup/Restore > > As discussed in PhoenixCon, here is the description of desired Snapshot > feature in Phoenix: > Currently, Phoenix does not supports taking snapshots of Table. > HBase has support for taking snapshot of table so Phoenix should also support > taking snapshot for taking backups of data. > At present, taking backup of a Phoenix is highly manual and detail oriented > process and in some cases its not even possible to take a snapshot because > Metadata of all Phoenix tables is stored in one system table. We cant take > snapshot of entire system table for making a backup/restore of one particular > table. > These are scenarios that we should handle in Phoenix Snapshot: > 1. Handling Global and Local secondary index. > 2. Snapshotting Table schema. > 3. Handling views. I am not sure how. But just highlighting it. > I think #2 above requires most of the work. IMO, #1 should be easy to handle. -- This message was sent by Atlassian JIRA (v6.3.4#6332)
