You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Samarth Jain (JIRA)" <ji...@apache.org> on 2014/08/19 21:17:19 UTC
[jira] [Updated] (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 ]
Samarth Jain updated PHOENIX-1182:
----------------------------------
Description:
{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}
was:
{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);
}
{code}
> 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
>
> {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)