just turn off CBO as workaround, and register a issue on Calcite 2017-06-29 0:07 GMT+08:00 Артем Великородный <artem....@gmail.com>:
> Trying to execute the query on hive 2.1.1 its failed with exception. > > > Query: > > SELECT > account_id, > subscription_id, > previous_subscription_id, > subscription_effective > FROM > ( > SELECT > DISTINCT account_id, > subscription_id, > previous_subscription_id, > subscription_effective, > order_completed_dtm, > ROW_NUMBER()OVER(PARTITION BY ACCOUNT_ID order by order_completed_dtm > DESC) AS R1 > FROM temp.i > WHERE > subscription_status<>'ACTIVE' > AND account_id=516272263 > ) as rte WHERE R1=1; > > > If I disable CBO, query works fine > hive> set hive.cbo.enable=false; > > Also it works when remove DISTINCT clause from sub-SELECT. > > > And below query works too: > > SELECT > DISTINCT account_id, > subscription_id, > previous_subscription_id, > subscription_effective, > order_completed_dtm, > ROW_NUMBER()OVER(PARTITION BY ACCOUNT_ID order by order_completed_dtm > DESC) AS R1 > FROM temp.i > WHERE > subscription_status<>'ACTIVE' > AND account_id=516272263 > > > It looks like https://issues.apache.org/jira/browse/HIVE-13094, but it was > already committed to hive-2.1.1 > > Exception: > > Exception in thread "main" java.lang.AssertionError: Internal error: Cannot > add expression of different type to set: > set type is RecordType(BIGINT subscription_id, BIGINT > previous_subscription_id, VARCHAR(2147483647 <(214)%20748-3647>) CHARACTER > SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary" subscription_status, DATE > subscription_effective_from_dt, DATE subscription_effective_to_dt, > TIMESTAMP(9) order_completed_dtm, INTEGER r1) NOT NULL > expression type is RecordType(BIGINT subscription_id, BIGINT > previous_subscription_id, VARCHAR(2147483647 <(214)%20748-3647>) CHARACTER > SET "UTF-16LE" COLLATE "ISO-8859-1$en_US$primary" subscription_status, DATE > subscription_effective_from_dt, DATE subscription_effective_to_dt, INTEGER > order_completed_dtm, TIMESTAMP(9) r1) NOT NULL > set is rel#60:HiveAggregate.HIVE.[](input=HepRelVertex#69,group={1, 2, 3, 4, > 5, 6, 7}) > expression is HiveProject#71 > at org.apache.calcite.util.Util.newInternal(Util.java:774) > at > org.apache.calcite.plan.RelOptUtil.verifyTypeEquivalence(RelOptUtil.java:317) > at > org.apache.calcite.plan.hep.HepRuleCall.transformTo(HepRuleCall.java:57) > at > org.apache.calcite.plan.RelOptRuleCall.transformTo(RelOptRuleCall.java:224) > at > org.apache.calcite.rel.rules.AggregateProjectPullUpConstantsRule.onMatch(AggregateProjectPullUpConstantsRule.java:227) > at > org.apache.calcite.plan.AbstractRelOptPlanner.fireRule(AbstractRelOptPlanner.java:318) > at org.apache.calcite.plan.hep.HepPlanner.applyRule(HepPlanner.java:514) > at > org.apache.calcite.plan.hep.HepPlanner.applyRules(HepPlanner.java:392) > at > org.apache.calcite.plan.hep.HepPlanner.executeInstruction(HepPlanner.java:285) > at > org.apache.calcite.plan.hep.HepInstruction$RuleCollection.execute(HepInstruction.java:72) > at > org.apache.calcite.plan.hep.HepPlanner.executeProgram(HepPlanner.java:207) > at > org.apache.calcite.plan.hep.HepPlanner.findBestExp(HepPlanner.java:194) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.hepPlan(CalcitePlanner.java:1318) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.applyPreJoinOrderingTransforms(CalcitePlanner.java:1189) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:970) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:901) > at org.apache.calcite.tools.Frameworks$1.apply(Frameworks.java:113) > at > org.apache.calcite.prepare.CalcitePrepareImpl.perform(CalcitePrepareImpl.java:969) > at org.apache.calcite.tools.Frameworks.withPrepare(Frameworks.java:149) > at org.apache.calcite.tools.Frameworks.withPlanner(Frameworks.java:106) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.getOptimizedAST(CalcitePlanner.java:719) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:287) > at > org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10831) > at > org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:246) > at > org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:250) > at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:477) > at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1242) > at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1384) > at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1171) > at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1161) > at > org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:232) > at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:183) > at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:399) > at > org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:776) > at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:714) > at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:641) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:606) > at org.apache.hadoop.util.RunJar.run(RunJar.java:221) > at org.apache.hadoop.util.RunJar.main(RunJar.java:136) > > > Any idea? > -- 王海华
