From user-return-3712-apmail-phoenix-user-archive=phoenix.apache.org@phoenix.apache.org Fri Sep 11 02:59:06 2015 Return-Path: X-Original-To: apmail-phoenix-user-archive@minotaur.apache.org Delivered-To: apmail-phoenix-user-archive@minotaur.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id CD931182AD for ; Fri, 11 Sep 2015 02:59:06 +0000 (UTC) Received: (qmail 25521 invoked by uid 500); 11 Sep 2015 02:59:06 -0000 Delivered-To: apmail-phoenix-user-archive@phoenix.apache.org Received: (qmail 25479 invoked by uid 500); 11 Sep 2015 02:59:06 -0000 Mailing-List: contact user-help@phoenix.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@phoenix.apache.org Delivered-To: mailing list user@phoenix.apache.org Received: (qmail 25469 invoked by uid 99); 11 Sep 2015 02:59:06 -0000 Received: from Unknown (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Fri, 11 Sep 2015 02:59:06 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 19976180425 for ; Fri, 11 Sep 2015 02:59:06 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 2.879 X-Spam-Level: ** X-Spam-Status: No, score=2.879 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=3, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled Authentication-Results: spamd3-us-west.apache.org (amavisd-new); dkim=pass (2048-bit key) header.d=gmail.com Received: from mx1-us-west.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id 1eMBJG89vS0r for ; Fri, 11 Sep 2015 02:59:04 +0000 (UTC) Received: from mail-wi0-f170.google.com (mail-wi0-f170.google.com [209.85.212.170]) by mx1-us-west.apache.org (ASF Mail Server at mx1-us-west.apache.org) with ESMTPS id E45392308B for ; Fri, 11 Sep 2015 02:59:03 +0000 (UTC) Received: by wicgb1 with SMTP id gb1so46194509wic.1 for ; Thu, 10 Sep 2015 19:59:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:date:message-id:subject:from:to:content-type; bh=bkFu193HPo0MwR905Gvuk1imJ1WC41nxd2Fj34q6vOY=; b=OxXuaPhZ3CGkBSTLLLH7nPS/aIi7p8f7PzHLRBKJU69HhdMB/Pcj1JAGxa5499zLpn 3+B+0/Z3iUGUjkYPGtjITMEeVVusknLWaWiEnfPCzigw4CAE4tqw3nxAkqVfO+MG2M5W mL4W/sbr4ckIh6A1w8KCDcTpKOCq7Dnvi8hNxYLqxh86MdwDiVuwQxLOPhoWv1Wufbyp Fe0J03sH5es68W1BSDz2ShKMd9o3m7DyxPD/H8t+fhUSaZXzxAMeyje1JjnfnX7S8wAv CbZxr+JaDILZYxz0y0OTiiKtJ2FbObQvarVrB1MGb8Ak0SA2WErbYAC2WApJMG8U2gYL 0ZJg== MIME-Version: 1.0 X-Received: by 10.194.103.130 with SMTP id fw2mr80808253wjb.121.1441940342617; Thu, 10 Sep 2015 19:59:02 -0700 (PDT) Received: by 10.28.37.70 with HTTP; Thu, 10 Sep 2015 19:59:02 -0700 (PDT) Date: Thu, 10 Sep 2015 22:59:02 -0400 Message-ID: Subject: yet another question...perhaps dumb...JOIN with two conditions From: "M. Aaron Bossert" To: user@phoenix.apache.org Content-Type: multipart/alternative; boundary=089e0102ef54e8bcc2051f6fe5f4 --089e0102ef54e8bcc2051f6fe5f4 Content-Type: text/plain; charset=UTF-8 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.(JoinCompiler.java:459) at org.apache.phoenix.compile.JoinCompiler$JoinSpec.(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) --089e0102ef54e8bcc2051f6fe5f4 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I am trying to execute the following query, but get an err= or...is there another way to achieve the same result by restructuring the q= uery?

QUERY:

SELECT * FROM NG.AKAMAI_FORCEFIELD AS FORC INNER JOI= N NG.IPV4RANGES AS IPV4 ON FORC.SOURCE_IP >=3D IPV4.IPSTART AND FORC.SOU= RCE_IP <=3D IPV4.IPEND;


= ERROR:

Error: ERROR 217 (22017): Amibiguous or non-equi join condit= ion specified. Consider using table list with where clause. (state=3D22017,= code=3D217)

= java.sql.SQLException: ERROR 217 (22017): Amibiguous or non-equi join condi= tion specified. Consider using table list with where clause.

at org.apache.phoenix.exception.SQLExceptionCode$Facto= ry$1.newException(SQLExceptionCode.java:388)

at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExc= eptionInfo.java:145)

at org.apache.phoen= ix.compile.JoinCompiler$OnNodeVisitor.throwAmbiguousJoinConditionException(= JoinCompiler.java:961)

at org.apache.pho= enix.compile.JoinCompiler$OnNodeVisitor.leaveBooleanNode(JoinCompiler.java:= 899)

at org.apache.phoenix.compile.JoinC= ompiler$OnNodeVisitor.visitLeave(JoinCompiler.java:927)

at org.apache.phoenix.compile.JoinCompiler$OnNodeVisitor.vi= sitLeave(JoinCompiler.java:871)

at org.a= pache.phoenix.parse.ComparisonParseNode.accept(ComparisonParseNode.java:47)=

at org.apache.phoenix.parse.CompoundPar= seNode.acceptChildren(CompoundParseNode.java:64)

at org.apache.phoenix.parse.AndParseNode.accept(AndParseNode.java:= 47)

at org.apache.phoenix.compile.JoinC= ompiler$JoinSpec.<init>(JoinCompiler.java:459)

at org.apache.phoenix.compile.JoinCompiler$JoinSpec.<init&g= t;(JoinCompiler.java:442)

at org.apache.= phoenix.compile.JoinCompiler$JoinTableConstructor.visit(JoinCompiler.java:1= 97)

at org.apache.phoenix.compile.JoinC= ompiler$JoinTableConstructor.visit(JoinCompiler.java:171)

at org.apache.phoenix.parse.JoinTableNode.accept(JoinTabl= eNode.java:81)

<= span class=3D"" style=3D"white-space:pre"> at org.apache.phoenix.com= pile.JoinCompiler.compile(JoinCompiler.java:127)

at org.apache.phoenix.compile.JoinCompiler.optimize(JoinCompiler.j= ava:1158)

at org.apache.phoenix.compile.= QueryCompiler.compileSelect(QueryCompiler.java:193)

at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompil= er.java:158)

at org.apache.phoenix.jdbc.= PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.jav= a:375)

at org.apache.phoenix.jdbc.Phoeni= xStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:349)=

at org.apache.phoenix.jdbc.PhoenixState= ment$1.call(PhoenixStatement.java:255)

a= t org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:250= )

at org.apache.phoenix.call.CallRunner.= run(CallRunner.java:53)

at org.apache.ph= oenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:249)

at org.apache.phoenix.jdbc.PhoenixStatement.ex= ecute(PhoenixStatement.java:1377)

at sql= line.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:29= 2)

--089e0102ef54e8bcc2051f6fe5f4--