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
9.88k stars 2.86k forks source link

Hive function call in a Hive view is not translated into Presto signature #5978

Open findepi opened 3 years ago

findepi commented 3 years ago

Consider example with date_add:

0: jdbc:hive2://localhost:10000/default> CREATE VIEW view_with_date_add AS SELECT *, date_add(o_orderdate, 7) AS week_after_orderdate FROM orders;
No rows affected (0.401 seconds)

0: jdbc:hive2://localhost:10000/default> SELECT week_after_orderdate FROM view_with_date_add WHERE o_orderkey < 4;
+-----------------------+--+
| week_after_orderdate  |
+-----------------------+--+
| 1996-01-09            |
| 1996-12-08            |
| 1993-10-21            |
+-----------------------+--+
presto:default> SELECT week_after_orderdate FROM view_with_date_add WHERE o_orderkey < 4;
Query 20201116_163721_00041_jfdaf failed: line 1:34: Failed analyzing stored view 'hive.default.view_with_date_add': line 1:144: Unexpected parameters (date, integer) for function date_add. Expected: date_add(varchar(x), bigint, date) , date_add(varchar(x), bigint, timestamp(p)) , date_add(varchar(x), bigint, timestamp(p) with time zone) , date_add(varchar(x), bigint, time(p)) , date_add(varchar(x), bigint, time(p) with time zone)
io.prestosql.spi.PrestoException: line 1:34: Failed analyzing stored view 'hive.default.view_with_date_add': line 1:144: Unexpected parameters (date, integer) for function date_add. Expected: date_add(varchar(x), bigint, date) , date_add(varchar(x), bigint, timestamp(p)) , date_add(varchar(x), bigint, timestamp(p) with time zone) , date_add(varchar(x), bigint, time(p)) , date_add(varchar(x), bigint, time(p) with time zone)
    at io.prestosql.sql.analyzer.SemanticExceptions.semanticException(SemanticExceptions.java:48)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.analyzeView(StatementAnalyzer.java:2587)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.createScopeForView(StatementAnalyzer.java:1355)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitTable(StatementAnalyzer.java:1211)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitTable(StatementAnalyzer.java:324)
    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:341)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.analyzeFrom(StatementAnalyzer.java:2510)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:1532)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:324)
    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:341)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:351)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1055)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:324)
    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:341)
    at io.prestosql.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:310)
    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:122)
    at io.prestosql.$gen.Presto_346_65_g5309ce1____20201116_154441_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: io.prestosql.spi.PrestoException: line 1:144: Unexpected parameters (date, integer) for function date_add. Expected: date_add(varchar(x), bigint, date) , date_add(varchar(x), bigint, timestamp(p)) , date_add(varchar(x), bigint, timestamp(p) with time zone) , date_add(varchar(x), bigint, time(p)) , date_add(varchar(x), bigint, time(p) with time zone)
    at io.prestosql.sql.analyzer.ExpressionAnalyzer$Visitor.visitFunctionCall(ExpressionAnalyzer.java:1074)
    at io.prestosql.sql.analyzer.ExpressionAnalyzer$Visitor.visitFunctionCall(ExpressionAnalyzer.java:385)
    at io.prestosql.sql.tree.FunctionCall.accept(FunctionCall.java:110)
    at io.prestosql.sql.tree.StackableAstVisitor.process(StackableAstVisitor.java:27)
    at io.prestosql.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:408)
    at io.prestosql.sql.analyzer.ExpressionAnalyzer.analyze(ExpressionAnalyzer.java:346)
    at io.prestosql.sql.analyzer.ExpressionAnalyzer.analyzeExpression(ExpressionAnalyzer.java:1913)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.analyzeExpression(StatementAnalyzer.java:2657)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.analyzeSelectSingleColumn(StatementAnalyzer.java:2473)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.analyzeSelect(StatementAnalyzer.java:2297)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:1536)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:324)
    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:341)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:351)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:1055)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:324)
    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:341)
    at io.prestosql.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:310)
    at io.prestosql.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:304)
    at io.prestosql.sql.analyzer.StatementAnalyzer$Visitor.analyzeView(StatementAnalyzer.java:2583)
    ... 32 more
Caused by: io.prestosql.spi.PrestoException: Unexpected parameters (date, integer) for function date_add. Expected: date_add(varchar(x), bigint, date) , date_add(varchar(x), bigint, timestamp(p)) , date_add(varchar(x), bigint, timestamp(p) with time zone) , date_add(varchar(x), bigint, time(p)) , date_add(varchar(x), bigint, time(p) with time zone)
    at io.prestosql.metadata.FunctionResolver.resolveFunction(FunctionResolver.java:150)
    at io.prestosql.metadata.MetadataManager.lambda$resolveFunction$27(MetadataManager.java:1760)
    at java.base/java.util.Optional.orElseGet(Optional.java:369)
    at io.prestosql.metadata.MetadataManager.resolveFunction(MetadataManager.java:1760)
    at io.prestosql.sql.analyzer.ExpressionAnalyzer$Visitor.visitFunctionCall(ExpressionAnalyzer.java:1062)
    ... 55 more
SELECT week_after_orderdate FROM view_with_date_add WHERE o_orderkey < 4

Based on the error message, the Hive function invocation date_add(o_orderdate, 7) was not translated into Presto equivalent date_add('DAY', o_orderdate, 7). In this case, it led to a failure (not supported, which is fine). In some other case, it could lead to incorrect results, therefore filing this as a "bug".

findepi commented 3 years ago

cc @laurachenyu @phd3 @wmoustafa