search

trinodb / trino

Official repository of Trino, the distributed SQL query engine for big data, formerly known as PrestoSQL (https://trino.io)
https://trino.io
Apache License 2.0
10.28k stars 2.96k forks source link

Support Hive VIEW with simple CASE expression #5837

Open findepi opened 3 years ago

findepi commented 3 years ago 
0: jdbc:hive2://localhost:10000/default> CREATE VIEW v AS SELECT CASE n_regionkey WHEN 0 THEN 'Africa' WHEN 1 THEN 'America' END region_name FROM nation;

0: jdbc:hive2://localhost:10000/default> SELECT * FROM v;
+----------------+--+
| v.region_name  |
+----------------+--+
| Africa         |
| America        |
| America        |
| America        |
| NULL           |
....
presto:default> SELECT * FROM v;
Query 20201105_124018_00071_zyjka failed: Failed to translate Hive view 'default.v': At line 0, column 0: Expected a boolean type
io.prestosql.spi.PrestoException: Failed to translate Hive view 'default.v': At line 0, column 0: Expected a boolean type
    at io.prestosql.plugin.hive.ViewReaderUtil$HiveViewReader.decodeViewData(ViewReaderUtil.java:155)
    at io.prestosql.plugin.hive.HiveMetadata.lambda$getView$57(HiveMetadata.java:1935)
    at java.base/java.util.Optional.map(Optional.java:265)
    at io.prestosql.plugin.hive.HiveMetadata.getView(HiveMetadata.java:1929)
    at io.prestosql.plugin.base.classloader.ClassLoaderSafeConnectorMetadata.getView(ClassLoaderSafeConnectorMetadata.java:513)
    at io.prestosql.metadata.MetadataManager.getView(MetadataManager.java:1078)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitTable(StatementAnalyzer.java:1209)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitTable(StatementAnalyzer.java:323)
    at io.prestosql.sql.tree.Table.accept(Table.java:53)
    at io.prestosql.sql.tree.AstVisitor.process(AstVisitor.java:27)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:340)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.analyzeFrom(StatementAnalyzer.java:2511)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:1533)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:323)
    at io.prestosql.sql.tree.QuerySpecification.accept(QuerySpecification.java:144)
    at io.prestosql.sql.tree.AstVisitor.process(AstVisitor.java:27)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:340)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:350)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1046)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:323)
    at io.prestosql.sql.tree.Query.accept(Query.java:107)
    at io.prestosql.sql.tree.AstVisitor.process(AstVisitor.java:27)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:340)
    at io.prestosql.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:309)
    at io.prestosql.sql.analyzer.Analyzer.analyze(Analyzer.java:83)
    at io.prestosql.sql.analyzer.Analyzer.analyze(Analyzer.java:75)
    at io.prestosql.execution.SqlQueryExecution.analyze(SqlQueryExecution.java:257)
    at io.prestosql.execution.SqlQueryExecution.<init>(SqlQueryExecution.java:183)
    at io.prestosql.execution.SqlQueryExecution$SqlQueryExecutionFactory.createQueryExecution(SqlQueryExecution.java:759)
    at io.prestosql.dispatcher.LocalDispatchQueryFactory.lambda$createDispatchQuery$0(LocalDispatchQueryFactory.java:123)
    at io.prestosql.$gen.Presto_unknown____20201105_115855_2.call(Unknown Source)
    at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:125)
    at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:69)
    at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:78)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.apache.calcite.runtime.CalciteContextException: At line 0, column 0: Expected a boolean type
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
    at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:834)
    at org.apache.calcite.sql.SqlUtil.newContextException(SqlUtil.java:819)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.newValidationError(SqlValidatorImpl.java:4867)
    at org.apache.calcite.sql.SqlCallBinding.newError(SqlCallBinding.java:271)
    at org.apache.calcite.sql.fun.SqlCaseOperator.checkOperandTypes(SqlCaseOperator.java:186)
    at org.apache.calcite.sql.SqlOperator.validateOperands(SqlOperator.java:432)
    at org.apache.calcite.sql.fun.SqlCaseOperator.deriveType(SqlCaseOperator.java:166)
    at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5626)
    at org.apache.calcite.sql.validate.SqlValidatorImpl$DeriveTypeVisitor.visit(SqlValidatorImpl.java:5613)
    at org.apache.calcite.sql.SqlCall.accept(SqlCall.java:139)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveTypeImpl(SqlValidatorImpl.java:1688)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.deriveType(SqlValidatorImpl.java:1673)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1841)
    at com.linkedin.coral.hive.hive2rel.HiveSqlValidator.inferUnknownTypes(HiveSqlValidator.java:50)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.inferUnknownTypes(SqlValidatorImpl.java:1856)
    at com.linkedin.coral.hive.hive2rel.HiveSqlValidator.inferUnknownTypes(HiveSqlValidator.java:50)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.expandSelectItem(SqlValidatorImpl.java:474)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelectList(SqlValidatorImpl.java:4104)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateSelect(SqlValidatorImpl.java:3392)
    at org.apache.calcite.sql.validate.SelectNamespace.validateImpl(SelectNamespace.java:60)
    at org.apache.calcite.sql.validate.AbstractNamespace.validate(AbstractNamespace.java:84)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateNamespace(SqlValidatorImpl.java:1005)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateQuery(SqlValidatorImpl.java:965)
    at org.apache.calcite.sql.SqlSelect.validate(SqlSelect.java:216)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validateScopedExpression(SqlValidatorImpl.java:940)
    at org.apache.calcite.sql.validate.SqlValidatorImpl.validate(SqlValidatorImpl.java:647)
    at com.linkedin.coral.hive.hive2rel.HiveSqlToRelConverter.convertQuery(HiveSqlToRelConverter.java:58)
    at com.linkedin.coral.hive.hive2rel.HiveToRelConverter.toRel(HiveToRelConverter.java:126)
    at com.linkedin.coral.hive.hive2rel.HiveToRelConverter.convertView(HiveToRelConverter.java:108)
    at io.prestosql.plugin.hive.ViewReaderUtil$HiveViewReader.decodeViewData(ViewReaderUtil.java:136)
    ... 36 more
Caused by: org.apache.calcite.sql.validate.SqlValidatorException: Expected a boolean type
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
    at org.apache.calcite.runtime.Resources$ExInstWithCause.ex(Resources.java:463)
    at org.apache.calcite.runtime.Resources$ExInst.ex(Resources.java:572)
    ... 66 more
wmoustafa commented 3 years ago

Looking into it. The current workaround we suggest for our internal users is to use the alternative CASE syntax with boolean expressions instead:

CASE WHEN n_regionkey = 0 THEN 'Africa' WHEN n_regionkey = 1 THEN 'America' END
findepi commented 3 years ago

Since it worked in Presto 344, i am marking this as a bug.

wmoustafa commented 3 years ago

Created this pull request to address this issue.

findepi commented 3 years ago

@wmoustafa thanks for the pointer.

BTW Before we close this issue, I'd want to have an integration test on Presto side too. See io.prestosql.tests.hive.TestHiveViews#testRichSqlSyntax for existing coverage.