[jira] [Updated] (PHOENIX-4833) UDF Function Undefined when using nested sub query with Aggregation
[ https://issues.apache.org/jira/browse/PHOENIX-4833?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dinesh updated PHOENIX-4833: Summary: UDF Function Undefined when using nested sub query with Aggregation (was: Function Undefined when using nested sub query) > UDF Function Undefined when using nested sub query with Aggregation > --- > > Key: PHOENIX-4833 > URL: https://issues.apache.org/jira/browse/PHOENIX-4833 > Project: Phoenix > Issue Type: Bug >Affects Versions: 4.13.1 > Environment: Hadoop 2.9.0 > Hbase 1.3.2 > Phoenix 4.13.1-Hbase1.3 >Reporter: Dinesh >Priority: Major > > Function Undefined when using nested sub query > > 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> SELECT usagekb(strstatscounts) > as usage from (select to_char(avg(strstatscount),'#') as strstatscounts from > (SELECT SUM(DOWNLOADDATA) AS strstatscount FROM JIOANDSF.TBLRAWOFFLOADDATA > HAVING COUNT(*) > 0 )); > Error: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB > (state=42F01,code=6001) > org.apache.phoenix.schema.FunctionNotFoundException: ERROR 6001 (42F01): > Function undefined. functionName=USAGEKB > at > org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.resolveFunction(FromCompiler.java:725) > at > org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:327) > at > org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:696) > at > org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:585) > at > org.apache.phoenix.parse.FunctionParseNode.accept(FunctionParseNode.java:86) > at > org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:412) > at > org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:561) > at > org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:522) > at > org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:202) > at > org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157) > at > org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:476) > at > org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:442) > at > org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:300) > at > org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:290) > at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) > at > org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:289) > at > org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283) > at > org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1742) > at sqlline.Commands.execute(Commands.java:822) > at sqlline.Commands.sql(Commands.java:732) > at sqlline.SqlLine.dispatch(SqlLine.java:813) > at sqlline.SqlLine.begin(SqlLine.java:686) > at sqlline.SqlLine.start(SqlLine.java:398) > at sqlline.SqlLine.main(SqlLine.java:291) > > > > 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> SELECT usagekb(strstatscount) > as usage FROM (SELECT to_char(SUM(DOWNLOADDATA),'#') AS strstatscount FROM > JIOANDSF.TBLRAWOFFLOADDATA HAVING COUNT(*) > 0 ) ; > +-+ > | USAGE | > +-+ > | 7.82GB | > +-+ > 1 row selected (0.053 seconds) > > > > 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> SELECT usagekb(strstatscounts) > as usage from (select to_char(strstatscount,'#') as strstatscounts from > (SELECT SUM(DOWNLOADDATA) AS strstatscount FROM JIOANDSF.TBLRAWOFFLOADDATA > HAVING COUNT(*) > 0 )) ; > +-+ > | USAGE | > +-+ > | 7.82GB | > +-+ > 1 row selected (0.041 seconds) > -- This message was sent by Atlassian JIRA (v7.6.3#76005)
[jira] [Updated] (PHOENIX-4833) Function Undefined when using nested sub query
[ https://issues.apache.org/jira/browse/PHOENIX-4833?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dinesh updated PHOENIX-4833: Description: Function Undefined when using nested sub query 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> SELECT usagekb(strstatscounts) as usage from (select to_char(avg(strstatscount),'#') as strstatscounts from (SELECT SUM(DOWNLOADDATA) AS strstatscount FROM JIOANDSF.TBLRAWOFFLOADDATA HAVING COUNT(*) > 0 )); Error: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB (state=42F01,code=6001) org.apache.phoenix.schema.FunctionNotFoundException: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.resolveFunction(FromCompiler.java:725) at org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:327) at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:696) at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:585) at org.apache.phoenix.parse.FunctionParseNode.accept(FunctionParseNode.java:86) at org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:412) at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:561) at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:522) at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:202) at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:476) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:442) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:300) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:290) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:289) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1742) at sqlline.Commands.execute(Commands.java:822) at sqlline.Commands.sql(Commands.java:732) at sqlline.SqlLine.dispatch(SqlLine.java:813) at sqlline.SqlLine.begin(SqlLine.java:686) at sqlline.SqlLine.start(SqlLine.java:398) at sqlline.SqlLine.main(SqlLine.java:291) 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> SELECT usagekb(strstatscount) as usage FROM (SELECT to_char(SUM(DOWNLOADDATA),'#') AS strstatscount FROM JIOANDSF.TBLRAWOFFLOADDATA HAVING COUNT(*) > 0 ) ; +-+ | USAGE | +-+ | 7.82GB | +-+ 1 row selected (0.053 seconds) 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> SELECT usagekb(strstatscounts) as usage from (select to_char(strstatscount,'#') as strstatscounts from (SELECT SUM(DOWNLOADDATA) AS strstatscount FROM JIOANDSF.TBLRAWOFFLOADDATA HAVING COUNT(*) > 0 )) ; +-+ | USAGE | +-+ | 7.82GB | +-+ 1 row selected (0.041 seconds) was: Function Undefined when using nested sub query 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> SELECT usagekb(strstatscount) as dd from (select to_char(SUM(strstatscount),'#') from (SELECT SUM(DOWNLOADDATA)AS strstatscount FROM JIOANDSF.TBLRAWOFFLOADDATA )) ; Error: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB (state=42F01,code=6001) org.apache.phoenix.schema.FunctionNotFoundException: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.resolveFunction(FromCompiler.java:725) at org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:327) at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:696) at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:585) at org.apache.phoenix.parse.FunctionParseNode.accept(FunctionParseNode.java:86) at org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:412) at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:561) at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:522) at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:202) at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157)
[jira] [Updated] (PHOENIX-4833) Function Undefined when using nested sub query
[ https://issues.apache.org/jira/browse/PHOENIX-4833?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Dinesh updated PHOENIX-4833: Description: Function Undefined when using nested sub query 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> SELECT usagekb(strstatscount) as dd from (select to_char(SUM(strstatscount),'#') from (SELECT SUM(DOWNLOADDATA)AS strstatscount FROM JIOANDSF.TBLRAWOFFLOADDATA )) ; Error: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB (state=42F01,code=6001) org.apache.phoenix.schema.FunctionNotFoundException: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.resolveFunction(FromCompiler.java:725) at org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:327) at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:696) at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:585) at org.apache.phoenix.parse.FunctionParseNode.accept(FunctionParseNode.java:86) at org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:412) at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:561) at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:522) at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:202) at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:476) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:442) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:300) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:290) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:289) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1742) at sqlline.Commands.execute(Commands.java:822) at sqlline.Commands.sql(Commands.java:732) at sqlline.SqlLine.dispatch(SqlLine.java:813) at sqlline.SqlLine.begin(SqlLine.java:686) at sqlline.SqlLine.start(SqlLine.java:398) at sqlline.SqlLine.main(SqlLine.java:291) 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> SELECT usagekb(strstatscount) FROM (SELECT to_char(SUM(DOWNLOADDATA),'#') AS strstatscount FROM JIOANDSF.TBLRAWOFFLOADDATA HAVING COUNT(*) > 0 ) ; +--+ |USAGEINKB(TO_CHAR(SUM(A.DOWNLOADDATA))) | +--+ |7.82GB | +--+ 1 row selected (0.033 seconds) 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> was: Function Undefined when using nested sub query 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> select usagekb(strstatscount) from (SELECT to_char(coalesce(ROUND(SUM(avgusage)/SUM(users)),0),'#') AS strstatscount FROM (SELECT SUM(DOWNLOADDATA)/COUNT(DISTINCT(OFFLOADDATE)) AS avgusage,COUNT(DISTINCT(IMEI)) as users FROM JIOANDSF.TBLIMEIPLMNWISEOFFLOAD WHERE OFFLOADCOUNT > 0 GROUP BY IMEI,OFFLOADDATE) foo) ; Error: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB (state=42F01,code=6001) org.apache.phoenix.schema.FunctionNotFoundException: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.resolveFunction(FromCompiler.java:725) at org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:327) at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:696) at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:585) at org.apache.phoenix.parse.FunctionParseNode.accept(FunctionParseNode.java:86) at org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:412) at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:561) at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:522) at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:202) at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157) at
[jira] [Created] (PHOENIX-4833) Function Undefined when using nested sub query
Dinesh created PHOENIX-4833: --- Summary: Function Undefined when using nested sub query Key: PHOENIX-4833 URL: https://issues.apache.org/jira/browse/PHOENIX-4833 Project: Phoenix Issue Type: Bug Affects Versions: 4.13.1 Environment: Hadoop 2.9.0 Hbase 1.3.2 Phoenix 4.13.1-Hbase1.3 Reporter: Dinesh Function Undefined when using nested sub query 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> select usagekb(strstatscount) from (SELECT to_char(coalesce(ROUND(SUM(avgusage)/SUM(users)),0),'#') AS strstatscount FROM (SELECT SUM(DOWNLOADDATA)/COUNT(DISTINCT(OFFLOADDATE)) AS avgusage,COUNT(DISTINCT(IMEI)) as users FROM JIOANDSF.TBLIMEIPLMNWISEOFFLOAD WHERE OFFLOADCOUNT > 0 GROUP BY IMEI,OFFLOADDATE) foo) ; Error: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB (state=42F01,code=6001) org.apache.phoenix.schema.FunctionNotFoundException: ERROR 6001 (42F01): Function undefined. functionName=USAGEKB at org.apache.phoenix.compile.FromCompiler$BaseColumnResolver.resolveFunction(FromCompiler.java:725) at org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:327) at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:696) at org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.visitLeave(ProjectionCompiler.java:585) at org.apache.phoenix.parse.FunctionParseNode.accept(FunctionParseNode.java:86) at org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:412) at org.apache.phoenix.compile.QueryCompiler.compileSingleFlatQuery(QueryCompiler.java:561) at org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:522) at org.apache.phoenix.compile.QueryCompiler.compileSelect(QueryCompiler.java:202) at org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:157) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:476) at org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:442) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:300) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:290) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:289) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1742) at sqlline.Commands.execute(Commands.java:822) at sqlline.Commands.sql(Commands.java:732) at sqlline.SqlLine.dispatch(SqlLine.java:813) at sqlline.SqlLine.begin(SqlLine.java:686) at sqlline.SqlLine.start(SqlLine.java:398) at sqlline.SqlLine.main(SqlLine.java:291) 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> SELECT usagekb(strstatscount) FROM (SELECT to_char(SUM(DOWNLOADDATA),'#') AS strstatscount FROM JIOANDSF.TBLRAWOFFLOADDATA HAVING COUNT(*) > 0 ) ; +--+ | USAGEINKB(TO_CHAR(SUM(A.DOWNLOADDATA))) | +--+ | 7.82GB | +--+ 1 row selected (0.033 seconds) 0: jdbc:phoenix:scipnode1,scipnode2,scipnode3> -- This message was sent by Atlassian JIRA (v7.6.3#76005)
