[
https://issues.apache.org/jira/browse/PHOENIX-2644?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Taylor resolved PHOENIX-2644.
-----------------------------------
Resolution: Invalid
You need to list both tables in the FROM clause when you join. We definitely
support the ANSI compatible JOIN syntax (see
https://phoenix.apache.org/language/index.html#select_statement), but I'm not
sure if we support joining purely through the WHERE clause ([~maryannxue] would
know).
> Error on comparing a Null cell across tables
> --------------------------------------------
>
> Key: PHOENIX-2644
> URL: https://issues.apache.org/jira/browse/PHOENIX-2644
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.7.0
> Environment: Phoenix 4.7.0 on Calcite 1.5
> Reporter: Kevin Liew
>
> 1. Create two tables
> {code}
> DROP TABLE IF EXISTS AT1;
> CREATE TABLE IF NOT EXISTS AT1(
> pk INTEGER PRIMARY KEY,
> NUM_COL1 DECIMAL(18, 4),
> COL2 VARCHAR(32768),
> COL3 CHAR(254),
> LOG_COL4 BOOLEAN,
> DATE_COL5 DATE);
> DROP TABLE IF EXISTS AT2;
> CREATE TABLE IF NOT EXISTS AT2(
> pk INTEGER PRIMARY KEY,
> NUM_COL1 DECIMAL(18, 4),
> COL2 VARCHAR(32768),
> COL3 CHAR(254),
> LOG_COL4 BOOLEAN,
> DATE_COL5 DATE);
> {code}
> 2. Upsert rows in each table, with at least one row in one table having a
> Null value for COL2.
> 3. {code}select * from addr where emp.first_name = addr.first_name{code}
> Result:
> {code}
> Error: Error -1 (00000) : Error while executing SQL "select * from at1 where
> at1.col2 = at2.col2": Remote driver error: RuntimeException:
> org.apache.phoenix.schema.ColumnFamilyNotFoundException: ERROR 1001 (42I01):
> Undefined column family. familyName=AT2 -> ColumnFamilyNotFoundException:
> ERROR 1001 (42I01): Undefined column family. familyName=AT2
> (state=00000,code=-1)
> org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : Error
> while executing SQL "select * from at1 where at1.col2 = at2.col2": Remote
> driver error: RuntimeException:
> org.apache.phoenix.schema.ColumnFamilyNotFoundException: ERROR 1001 (42I01):
> Undefined column family. familyName=AT2 -> ColumnFamilyNotFoundException:
> ERROR 1001 (42I01): Undefined column family. familyName=AT2
> 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.AvaticaStatement.executeInternal(AvaticaStatement.java:143)
> at
> org.apache.calcite.avatica.AvaticaStatement.execute(AvaticaStatement.java:177)
> 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)
> java.lang.RuntimeException:
> org.apache.phoenix.schema.ColumnFamilyNotFoundException: ERROR 1001 (42I01):
> Undefined column family. familyName=AT2
> at org.apache.calcite.avatica.jdbc.JdbcMeta.propagate(JdbcMeta.java:651)
> at
> org.apache.calcite.avatica.jdbc.JdbcMeta.prepareAndExecute(JdbcMeta.java:715)
> at
> org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:187)
> at
> org.apache.calcite.avatica.remote.Service$PrepareAndExecuteRequest.accept(Service.java:868)
> at
> org.apache.calcite.avatica.remote.Service$PrepareAndExecuteRequest.accept(Service.java:842)
> at
> org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:102)
> at
> org.apache.calcite.avatica.remote.JsonHandler.apply(JsonHandler.java:43)
> at
> org.apache.calcite.avatica.server.AvaticaJsonHandler.handle(AvaticaJsonHandler.java:73)
> at
> org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
> at
> org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
> at org.eclipse.jetty.server.Server.handle(Server.java:497)
> at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310)
> at
> org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:245)
> at
> org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540)
> at
> org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
> at
> org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
> at java.lang.Thread.run(Thread.java:745)
> Caused by: org.apache.phoenix.schema.ColumnFamilyNotFoundException: ERROR
> 1001 (42I01): Undefined column family. familyName=AT2
> at
> org.apache.phoenix.schema.PTableImpl.getColumnFamily(PTableImpl.java:817)
> at
> org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.resolveColumn(FromCompiler.java:362)
> at
> org.apache.phoenix.compile.ExpressionCompiler.resolveColumn(ExpressionCompiler.java:366)
> at
> org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.resolveColumn(WhereCompiler.java:181)
> at
> org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.visit(WhereCompiler.java:169)
> at
> org.apache.phoenix.compile.WhereCompiler$WhereExpressionCompiler.visit(WhereCompiler.java:156)
> at
> org.apache.phoenix.parse.ColumnParseNode.accept(ColumnParseNode.java:56)
> at
> org.apache.phoenix.parse.CompoundParseNode.acceptChildren(CompoundParseNode.java:64)
> at
> org.apache.phoenix.parse.ComparisonParseNode.accept(ComparisonParseNode.java:45)
> at
> org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:130)
> at
> org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:100)
> at
> org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:543)
> at
> org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:495)
> at
> org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:201)
> at
> org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:158)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:399)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:373)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:266)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:261)
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:260)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1349)
> at
> org.apache.calcite.avatica.jdbc.JdbcMeta.prepareAndExecute(JdbcMeta.java:695)
> ... 15 more
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
