[
https://issues.apache.org/jira/browse/PHOENIX-2644?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15127618#comment-15127618
]
Maryann Xue commented on PHOENIX-2644:
--------------------------------------
Yes we do support non-ANSI joins (where there is no JOIN keyword and join
conditions are specified in WHERE clause). It is only that the IN clause
grammar in your WHERE condition is not supported.
> 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.
> Request:
> {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}
> Request:
> {code}select e.first_name from emp e, addr a where e.first_name in
> (a.first_name){code}
> Response:
> {code}
> java.sql.SQLException: ERROR 214 (22008): Values in IN must evaluate to a
> constant.
> at
> org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:419)
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
> at
> org.apache.phoenix.parse.InListParseNode.<init>(InListParseNode.java:46)
> at
> org.apache.phoenix.parse.ParseNodeFactory.inList(ParseNodeFactory.java:456)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.boolean_expression(PhoenixSQLParser.java:7257)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.not_expression(PhoenixSQLParser.java:6438)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.and_expression(PhoenixSQLParser.java:6283)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.or_expression(PhoenixSQLParser.java:6220)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.expression(PhoenixSQLParser.java:6185)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.single_select(PhoenixSQLParser.java:4388)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.unioned_selects(PhoenixSQLParser.java:4470)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.select_node(PhoenixSQLParser.java:4535)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.oneStatement(PhoenixSQLParser.java:766)
> at
> org.apache.phoenix.parse.PhoenixSQLParser.statement(PhoenixSQLParser.java:500)
> at org.apache.phoenix.parse.SQLParser.parseStatement(SQLParser.java:108)
> at
> org.apache.phoenix.jdbc.PhoenixStatement$PhoenixStatementParser.parseStatement(PhoenixStatement.java:1185)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.parseStatement(PhoenixStatement.java:1268)
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1339)
> 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)
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
