[
https://issues.apache.org/jira/browse/PHOENIX-4871?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16594344#comment-16594344
]
Mehdi Salarkia edited comment on PHOENIX-4871 at 8/28/18 12:23 AM:
-------------------------------------------------------------------
Report test.
https://github.com/apache/phoenix/compare/master...m2je:PHOENIX-4871?expand=1#diff-c2268fad065a6d0069ecf6eb186aa1be
Also attached the git patch file in case you want to add this to list of
existing tests
{code:java}
@Test
public void testParameterizedJoin() throws Exception {
String tableA = "A";
String tableB = "B";
String createA = "CREATE TABLE \"" + tableA + "\" (\n" +
" \"a1\" VARCHAR PRIMARY KEY ,\n" +
" \"a2\" VARCHAR \n" +
")";
String createB = "CREATE TABLE \"" + tableB + "\" (\n" +
" \"b1\" VARCHAR PRIMARY KEY ,\n" +
" \"b2\" VARCHAR \n" +
")\n";
try (Connection conn = DriverManager.getConnection(CONN_STRING);
Statement stmt = conn.createStatement()) {
conn.setAutoCommit(false);
assertFalse(stmt.execute("DROP TABLE IF EXISTS " + tableA));
assertFalse(stmt.execute("DROP TABLE IF EXISTS " + tableB));
assertFalse(stmt.execute(createA));
assertFalse(stmt.execute(createB));
Statement statement = null;
ResultSet rs = null;
PreparedStatement ps = null;
String sql = "SELECT \""+ tableA + "\".\"a2\" " +
"FROM \"" + tableA + "\" JOIN \"" + tableB + "\" ON (\"" +
tableA + "\".\"a1\" = \""+ tableB +"\".\"b1\") " +
"WHERE (\""+ tableB +"\".\"b2\" = ?) ";
ps = conn.prepareStatement(sql);
ps.setString(1, "some value");
rs = ps.executeQuery();
assertFalse(rs.next());
}
}
{code}
was (Author: m2je):
Report test. Also attached the git patch file in case you want to add this to
list of existing tests
{code:java}
@Test
public void testParameterizedJoin() throws Exception {
String tableA = "A";
String tableB = "B";
String createA = "CREATE TABLE \"" + tableA + "\" (\n" +
" \"a1\" VARCHAR PRIMARY KEY ,\n" +
" \"a2\" VARCHAR \n" +
")";
String createB = "CREATE TABLE \"" + tableB + "\" (\n" +
" \"b1\" VARCHAR PRIMARY KEY ,\n" +
" \"b2\" VARCHAR \n" +
")\n";
try (Connection conn = DriverManager.getConnection(CONN_STRING);
Statement stmt = conn.createStatement()) {
conn.setAutoCommit(false);
assertFalse(stmt.execute("DROP TABLE IF EXISTS " + tableA));
assertFalse(stmt.execute("DROP TABLE IF EXISTS " + tableB));
assertFalse(stmt.execute(createA));
assertFalse(stmt.execute(createB));
Statement statement = null;
ResultSet rs = null;
PreparedStatement ps = null;
String sql = "SELECT \""+ tableA + "\".\"a2\" " +
"FROM \"" + tableA + "\" JOIN \"" + tableB + "\" ON (\"" +
tableA + "\".\"a1\" = \""+ tableB +"\".\"b1\") " +
"WHERE (\""+ tableB +"\".\"b2\" = ?) ";
ps = conn.prepareStatement(sql);
ps.setString(1, "some value");
rs = ps.executeQuery();
assertFalse(rs.next());
}
}
{code}
> Query parser throws exception on parameterized join
> ---------------------------------------------------
>
> Key: PHOENIX-4871
> URL: https://issues.apache.org/jira/browse/PHOENIX-4871
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.14.0
> Environment: This issue exists on version 4 and I could reproduce it
> on current git repo version
> Reporter: Mehdi Salarkia
> Priority: Major
> Attachments: PHOENIX-4871-repo.patch
>
>
> When a join select statement has a parameter, Phoenix query parser fails to
> create query metadata and fails this query :
> {code:java}
> SELECT "A"."a2" FROM "A" JOIN "B" ON ("A"."a1" = "B"."b1" ) WHERE "B"."b2" =
> ?
> {code}
> with the following exception:
>
> {code:java}
> org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while
> preparing SQL: SELECT "A"."a2" FROM "A" JOIN "B" ON ("A"."a1" = "B"."b1")
> WHERE ("B"."b2" = ?)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:54)
> at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:358)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:175)
> at
> org.apache.phoenix.end2end.QueryServerBasicsIT.testParameterizedJoin(QueryServerBasicsIT.java:377)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:498)
> at
> org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
> at
> org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
> at
> org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
> at
> org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
> at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
> at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:55)
> at org.junit.rules.RunRules.evaluate(RunRules.java:20)
> at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:78)
> at
> org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:57)
> at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
> at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
> at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
> at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
> at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
> at
> org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
> at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27)
> at org.junit.rules.ExternalResource$1.evaluate(ExternalResource.java:48)
> at org.junit.rules.RunRules.evaluate(RunRules.java:20)
> at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
> at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
> at
> com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:68)
> at
> com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
> at
> com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
> at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
> java.lang.RuntimeException: java.sql.SQLException: ERROR 2004 (INT05):
> Parameter value unbound. Parameter at index 1 is unbound
> at org.apache.calcite.avatica.jdbc.JdbcMeta.propagate(JdbcMeta.java:700)
> at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:726)
> at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:195)
> at
> org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1215)
> at
> org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1186)
> at
> org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:94)
> at
> org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:46)
> at
> org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:127)
> at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
> at
> org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
> at org.eclipse.jetty.server.Server.handle(Server.java:534)
> at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:320)
> at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
> at
> org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:283)
> at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:108)
> at
> org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
> at
> org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303)
> at
> org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148)
> at
> org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136)
> at
> org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671)
> at
> org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589)
> at java.lang.Thread.run(Thread.java:748)
> Caused by: java.sql.SQLException: ERROR 2004 (INT05): Parameter value
> unbound. Parameter at index 1 is unbound
> at
> org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:494)
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:150)
> at
> org.apache.phoenix.jdbc.PhoenixParameterMetaData.getParam(PhoenixParameterMetaData.java:89)
> at
> org.apache.phoenix.jdbc.PhoenixParameterMetaData.isSigned(PhoenixParameterMetaData.java:138)
> at org.apache.calcite.avatica.jdbc.JdbcMeta.parameters(JdbcMeta.java:276)
> at org.apache.calcite.avatica.jdbc.JdbcMeta.signature(JdbcMeta.java:288)
> at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:721)
> ... 20 more
> {code}
> As a workaround you can change the order of tables. Meaning that the table
> that has the condition in the where clause must appear first in the query.
> For the example above this will work
> {code:java}
> SELECT "A"."a2" FROM "B" JOIN "A" ON ("A"."a1" = "B"."b1" ) WHERE "B"."b2" =
> ? {code}
> After debugging this it looks like the Phoenix server splits the join in two
> separated queries and constructs two java.sql.Statement. The first of the two
> is referenced for reading the query metadata down steam and since the column
> in the condition in the example above is in table `B` rather than `A` it
> won't exists in the first query and later it fails with the error above.
> That's why changing the order of tables in the query fixes the issue.
> This issue has also been reported here:
> [https://community.hortonworks.com/questions/45896/problem-executing-joins-on-phoenix-query-server.html]
> See org.apache.phoenix.compile.QueryCompiler#compileJoinQuery for details
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
