You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@phoenix.apache.org by "Mehdi Salarkia (JIRA)" <ji...@apache.org> on 2018/08/28 00:24:00 UTC
[jira] [Comment Edited] (PHOENIX-4871) Query parser throws
exception on parameterized join
[ 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)