You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "James Taylor (JIRA)" <ji...@apache.org> on 2014/08/23 07:00:11 UTC
[jira] [Resolved] (PHOENIX-1182) UPSERT SELECT not working with
joins
[ https://issues.apache.org/jira/browse/PHOENIX-1182?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
James Taylor resolved PHOENIX-1182.
-----------------------------------
Resolution: Fixed
Fix Version/s: 4.1
3.1
5.0.0
> UPSERT SELECT not working with joins
> ------------------------------------
>
> Key: PHOENIX-1182
> URL: https://issues.apache.org/jira/browse/PHOENIX-1182
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 5.0.0, 3.1, 4.1
> Reporter: Samarth Jain
> Assignee: Maryann Xue
> Fix For: 5.0.0, 3.1, 4.1
>
>
> {code}
> @Test
> public void testUpsertSelectWithJoin() throws Exception {
> String ddl1 = "CREATE TABLE IF NOT EXISTS SOURCE_TABLE(" +
> "TID CHAR(3) NOT NULL," +
> "A UNSIGNED_INT NOT NULL," +
> "B UNSIGNED_INT NOT NULL " +
> "CONSTRAINT pk PRIMARY KEY (TID, A, B))";
> Connection conn = DriverManager.getConnection(getUrl());
> conn.createStatement().execute(ddl1);
> conn.createStatement().execute("UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 1, 1)");
> conn.createStatement().execute("UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 1, 2)");
> conn.createStatement().execute("UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 1, 3)");
> conn.createStatement().execute("UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 2, 1)");
> conn.createStatement().execute("UPSERT INTO SOURCE_TABLE(TID, A, B) VALUES ('1', 2, 2)");
> conn.commit();
>
> String ddl2 = "CREATE TABLE IF NOT EXISTS JOIN_TABLE(" +
> "TID CHAR(3) NOT NULL," +
> "A UNSIGNED_INT NOT NULL," +
> "B UNSIGNED_INT NOT NULL," +
> "COUNT UNSIGNED_INT " +
> "CONSTRAINT pk PRIMARY KEY (TID, A, B))";
> conn.createStatement().execute(ddl2);
>
> String upsertSelect = "UPSERT INTO JOIN_TABLE(TID, A, B, COUNT)" +
> "SELECT t1.TID, " +
> "t1.A, " +
> "t2.A, " +
> "COUNT(*) " +
> "FROM SOURCE_TABLE t1 " +
> "INNER JOIN SOURCE_TABLE t2 ON t1.B = t2.B " +
> "WHERE t1.A != t2.A " +
> "AND t1.TID = '1' " +
> "AND t2.TID = '1' " +
> "GROUP BY " +
> "t1.TID, t1.A,t2.A";
>
> conn.createStatement().execute(upsertSelect);
> }
> Stacktrace:
> org.apache.phoenix.schema.ArgumentTypeMismatchException: ERROR 203 (22005): Type mismatch. expected: CHAR but was: UNSIGNED_INT at column: TID
> at org.apache.phoenix.compile.ProjectionCompiler.coerceIfNecessary(ProjectionCompiler.java:253)
> at org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:327)
> at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:327)
> at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:149)
> at org.apache.phoenix.compile.QueryCompiler.compileJoinQuery(QueryCompiler.java:194)
> at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:130)
> at org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:75)
> at org.apache.phoenix.compile.UpsertCompiler.compile(UpsertCompiler.java:394)
> at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:437)
> at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableUpsertStatement.compilePlan(PhoenixStatement.java:1)
> at org.apache.phoenix.jdbc.PhoenixStatement$3.call(PhoenixStatement.java:245)
> at org.apache.phoenix.jdbc.PhoenixStatement$3.call(PhoenixStatement.java:1)
> at org.apache.phoenix.util.PhoenixContextExecutor.call(PhoenixContextExecutor.java:54)
> at org.apache.phoenix.jdbc.PhoenixStatement.executeMutation(PhoenixStatement.java:236)
> at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:959)
> at org.apache.phoenix.end2end.UpsertSelectIT.testUpsertSelectWithJoin(UpsertSelectIT.java:844)
> {code}
--
This message was sent by Atlassian JIRA
(v6.2#6252)