phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jaime Solano <jdjsol...@gmail.com>
Subject Re: yet another question...perhaps dumb...JOIN with two conditions
Date Fri, 11 Sep 2015 04:26:47 GMT
Hi Aaron,

The JOIN you're trying to run is a non-equi join, meaning that the ON
condition is not an equality ('>=' and '<=' in your case). This type of
join is not supported in Phoenix versions prior to 4.3

In Phoenix 4.3+, you can do something like this:

SELECT * FROM NG.AKAMAI_FORCEFIELD AS FORC, NG.IPV4RANGES AS IPV4 WHERE
FORC.SOURCE_IP >= IPV4.IPSTART AND FORC.SOURCE_IP <= IPV4.IPEND;

Best,
-Jaime

On Thu, Sep 10, 2015 at 10:59 PM, M. Aaron Bossert <mabossert@gmail.com>
wrote:

> I am trying to execute the following query, but get an error...is there
> another way to achieve the same result by restructuring the query?
>
> QUERY:
>
> SELECT * FROM NG.AKAMAI_FORCEFIELD AS FORC INNER JOIN NG.IPV4RANGES AS
> IPV4 ON FORC.SOURCE_IP >= IPV4.IPSTART AND FORC.SOURCE_IP <= IPV4.IPEND;
>
>
> ERROR:
>
> *Error: ERROR 217 (22017): Amibiguous or non-equi join condition
> specified. Consider using table list with where clause.
> (state=22017,code=217)*
>
> java.sql.SQLException: ERROR 217 (22017): Amibiguous or non-equi join
> condition specified. Consider using table list with where clause.
>
> at
> org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:388)
>
> at
> org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145)
>
> at
> org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.throwAmbiguousJoinConditionException(JoinCompiler.java:961)
>
> at
> org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.leaveBooleanNode(JoinCompiler.java:899)
>
> at
> org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:927)
>
> at
> org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:871)
>
> at
> org.apache.phoenix.parse.ComparisonParseNode.accept(ComparisonParseNode.java:47)
>
> at
> org.apache.phoenix.parse.CompoundParseNode.acceptChildren(CompoundParseNode.java:64)
>
> at org.apache.phoenix.parse.AndParseNode.accept(AndParseNode.java:47)
>
> at
> org.apache.phoenix.compile.JoinCompiler$JoinSpec.<init>(JoinCompiler.java:459)
>
> at
> org.apache.phoenix.compile.JoinCompiler$JoinSpec.<init>(JoinCompiler.java:442)
>
> at
> org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:197)
>
> at
> org.apache.phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:171)
>
> at org.apache.phoenix.parse.JoinTableNode.accept(JoinTableNode.java:81)
>
> at org.apache.phoenix.compile.JoinCompiler.compile(JoinCompiler.java:127)
>
> at org.apache.phoenix.compile.JoinCompiler.optimize(JoinCompiler.java:1158)
>
> at
> org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:193)
>
> at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:158)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:375)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:349)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:255)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:250)
>
> at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:249)
>
> at
> org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1377)
>
> 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)
>

Mime
View raw message