Sergey Soldatov created PHOENIX-5009: ----------------------------------------
Summary: Views doesn't handle subqueries correctly Key: PHOENIX-5009 URL: https://issues.apache.org/jira/browse/PHOENIX-5009 Project: Phoenix Issue Type: Bug Affects Versions: 4.15.0 Reporter: Sergey Soldatov We allow subqueries in views, but we don't handle them as we usually do in the regular queries. Some examples: 1. type compatibility: {code} 0: jdbc:phoenix:> create table a (id integer primary key, v varchar); No rows affected (2.357 seconds) 0: jdbc:phoenix:> create view view1 as select * from A where v > (select current_time()); No rows affected (0.046 seconds) 0: jdbc:phoenix:> select * from view1; Error: ERROR 203 (22005): Type mismatch. VARCHAR and TIME ARRAY for V > ARRAY['2018-11-09 19:33:17.977'] (state=22005,code=203) org.apache.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type mismatch. VARCHAR and TIME ARRAY for V > ARRAY['2018-11-09 19:33:17.977'] at org.apache.phoenix.schema.TypeMismatchException.newException(TypeMismatchException.java:53) at org.apache.phoenix.expression.ComparisonExpression.create(ComparisonExpression.java:133) at org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:234) at org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:146) at org.apache.phoenix.parse.ComparisonParseNode.accept(ComparisonParseNode.java:47) at org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:147) at org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:108) at org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:237) at org.apache.phoenix.execute.DelegateQueryPlan.iterator(DelegateQueryPlan.java:144) at org.apache.phoenix.execute.DelegateQueryPlan.iterator(DelegateQueryPlan.java:139) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:312) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:291) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:290) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1830) 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) {code} 2. single-row subquery: {code} 0: jdbc:phoenix:> create view view2 as select * from A where v > (select to_char(current_time())); No rows affected (0.036 seconds) 0: jdbc:phoenix:> select * from view2; Error: ERROR 203 (22005): Type mismatch. VARCHAR and VARCHAR ARRAY for V > ARRAY['2018-11-09 19:34:37.766'] (state=22005,code=203) org.apache.phoenix.schema.TypeMismatchException: ERROR 203 (22005): Type mismatch. VARCHAR and VARCHAR ARRAY for V > ARRAY['2018-11-09 19:34:37.766'] at org.apache.phoenix.schema.TypeMismatchException.newException(TypeMismatchException.java:53) at org.apache.phoenix.expression.ComparisonExpression.create(ComparisonExpression.java:133) at org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:234) at org.apache.phoenix.compile.ExpressionCompiler.visitLeave(ExpressionCompiler.java:146) at org.apache.phoenix.parse.ComparisonParseNode.accept(ComparisonParseNode.java:47) at org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:147) at org.apache.phoenix.compile.WhereCompiler.compile(WhereCompiler.java:108) at org.apache.phoenix.execute.HashJoinPlan.iterator(HashJoinPlan.java:237) at org.apache.phoenix.execute.DelegateQueryPlan.iterator(DelegateQueryPlan.java:144) at org.apache.phoenix.execute.DelegateQueryPlan.iterator(DelegateQueryPlan.java:139) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:312) at org.apache.phoenix.jdbc.PhoenixStatement$1.call(PhoenixStatement.java:291) at org.apache.phoenix.call.CallRunner.run(CallRunner.java:53) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:290) at org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:283) at org.apache.phoenix.jdbc.PhoenixStatement.execute(PhoenixStatement.java:1830) 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:> explain select * from A where v > (select to_char(current_time())); +-------------------------------------------------------------+-----------------+----------------+--------------+ | PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS | +-------------------------------------------------------------+-----------------+----------------+--------------+ | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER A | 0 | 0 | 0 | | EXECUTE SINGLE-ROW SUBQUERY | 0 | 0 | 0 | +-------------------------------------------------------------+-----------------+----------------+--------------+ 2 rows selected (0.09 seconds) 0: jdbc:phoenix:> explain select * from view2; +-------------------------------------------------------------+-----------------+----------------+--------------+ | PLAN | EST_BYTES_READ | EST_ROWS_READ | EST_INFO_TS | +-------------------------------------------------------------+-----------------+----------------+--------------+ | CLIENT 1-CHUNK PARALLEL 1-WAY ROUND ROBIN FULL SCAN OVER A | 0 | 0 | 0 | | EXECUTE MULTIPLE-ROW SUBQUERY | 0 | 0 | 0 | +-------------------------------------------------------------+-----------------+----------------+--------------+ 2 rows selected (0.079 seconds) {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)