-
Bug
-
Resolution: Done
-
Major
-
8.12.x
-
None
Teiid seems to not fully translate convert(..., date) expression. The source-specific command contains only string representation of the date. But hive treats this as string, not date.
SQL query:
SELECT BQT1.SmallA.IntKey FROM BQT1.SmallA WHERE BQT1.SmallA.DateValue IN (convert('2000-01-12', date), convert('2000-02-02', date))
Source-specific command:
SELECT g_0.intkey AS c_0 FROM smalla g_0 WHERE g_0.datevalue IN ('2000-01-12', '2000-02-02')
Exception:
Unable to find source-code formatter for language: plain. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
11:54:54,680 WARN [org.teiid.PROCESSOR] (Worker0_QueryProcessorQueue17) TEIID30020 Processing exception for request /2VwN2gwljfj.7 'TEIID30504 Source: 10014 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey AS c_0 FROM smalla g_0 WHERE g_0.datevalue IN ('2000-01-12', '2000-02-02') LIMIT 100]'. Originally TeiidProcessingException 'org.apache.hadoop.hive.ql.parse.SemanticException:Line 1:61 Wrong arguments ''2000-02-02'': The arguments for IN should be the same type! Types are: {date IN (string, string)}' TypeCheckProcFactory.java:1324.: org.teiid.core.TeiidProcessingException: TEIID30504 Source: 10014 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey AS c_0 FROM smalla g_0 WHERE g_0.datevalue IN ('2000-01-12', '2000-02-02') LIMIT 100] at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:161) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:391) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0-internal] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0-internal] at java.lang.Thread.run(Thread.java:744) [rt.jar:1.8.0-internal] Caused by: org.teiid.translator.jdbc.JDBCExecutionException: 10014 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.intkey AS c_0 FROM smalla g_0 WHERE g_0.datevalue IN ('2000-01-12', '2000-02-02') LIMIT 100] at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:364) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0-internal] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0-internal] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0-internal] at java.lang.reflect.Method.invoke(Method.java:483) [rt.jar:1.8.0-internal] at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at com.sun.proxy.$Proxy47.execute(Unknown Source) at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0-internal] at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65) [teiid-engine-8.12.5.redhat-5.jar:8.12.5.redhat-5] ... 6 more Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10014]: Line 1:61 Wrong arguments ''2000-02-02'': The arguments for IN should be the same type! Types are: {date IN (string, string)} at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:258) at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:244) at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:247) at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:378) at org.apache.hive.jdbc.HivePreparedStatement.executeQuery(HivePreparedStatement.java:109) at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462) at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) ... 18 more Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10014]: Line 1:61 Wrong arguments ''2000-02-02'': The arguments for IN should be the same type! Types are: {date IN (string, string)} at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:315) at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:112) at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:181) at org.apache.hive.service.cli.operation.Operation.run(Operation.java:257) at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:419) at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:406) at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:274) at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:486) at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1317) at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1302) at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39) at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39) at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56) at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.8.0-internal] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.8.0-internal] ... 1 more Caused by: java.lang.RuntimeException: org.apache.hadoop.hive.ql.parse.SemanticException:Line 1:61 Wrong arguments ''2000-02-02'': The arguments for IN should be the same type! Types are: {date IN (string, string)} at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.process(TypeCheckProcFactory.java:1324) at org.apache.hadoop.hive.ql.lib.DefaultRuleDispatcher.dispatch(DefaultRuleDispatcher.java:90) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatchAndReturn(DefaultGraphWalker.java:95) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatch(DefaultGraphWalker.java:79) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.walk(DefaultGraphWalker.java:133) at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.startWalking(DefaultGraphWalker.java:110) at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:213) at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:157) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genAllExprNodeDesc(SemanticAnalyzer.java:10507) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:10463) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genExprNodeDesc(SemanticAnalyzer.java:10431) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:2818) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genFilterPlan(SemanticAnalyzer.java:2799) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genBodyPlan(SemanticAnalyzer.java:8848) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9743) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genPlan(SemanticAnalyzer.java:9636) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.genOPTree(SemanticAnalyzer.java:10109) at org.apache.hadoop.hive.ql.parse.CalcitePlanner.genOPTree(CalcitePlanner.java:329) at org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.analyzeInternal(SemanticAnalyzer.java:10120) at org.apache.hadoop.hive.ql.parse.CalcitePlanner.analyzeInternal(CalcitePlanner.java:211) at org.apache.hadoop.hive.ql.parse.BaseSemanticAnalyzer.analyze(BaseSemanticAnalyzer.java:227) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:456) at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:316) at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1181) at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1175) at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:110) ... 15 more
[TEIID-4256] Hive translator - types in IN predicate should be of same type
Steven Hawkins
added a comment - It is saying that the conversion logic does support implicit for timestamp - but the previous translator logic with an explicit cast should work as well. However the ansi syntax is not an option there - https://issues.apache.org/jira/browse/HIVE-5471
So we won't rely on the implicit conversion in either case.
Steven Hawkins
added a comment - It is saying that the conversion logic does support implicit for timestamp - but the previous translator logic with an explicit cast should work as well. However the ansi syntax is not an option there - https://issues.apache.org/jira/browse/HIVE-5471
So we won't rely on the implicit conversion in either case. rhn-engineering-shawkins How do interpret the Timestamp here [1], even that does not seem to specify the JDBC escape syntax? Also there is no implicit conversion from string to Timestamp or Date
[1]https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Types#LanguageManualTypes-Timestamps
Steven Hawkins
added a comment - Even when binding is turned on, we'll only bind values that are computed. If the value comes directly from a literal in the engine, then the source query will just use the literal.
Steven Hawkins
added a comment - Even when binding is turned on, we'll only bind values that are computed. If the value comes directly from a literal in the engine, then the source query will just use the literal. So, when binding is true, this should be fine, however if that is turned off, that can be an issue. I assumed it supports the JDBC escape syntax, but I think it would be good to to ANSI as you suggest, I will re-work it.
Added support for Date to Hive not to convert to String. Impala still does not support Date
Steven Hawkins
added a comment - Just to make sure, are the JDBC escapes recognized by all relevant versions of the Hive driver? If not it looks like they do support the ansi date literals - DATE '2000-01-01'.
Steven Hawkins
added a comment - Just to make sure, are the JDBC escapes recognized by all relevant versions of the Hive driver? If not it looks like they do support the ansi date literals - DATE '2000-01-01'. Steven Hawkins
added a comment - > Teiid seems to not fully translate convert(..., date) expression
I think the issue was that date support was added to hive after the initial translator development, so the code just defaulted to the string literal form - which would work in places that allow for implicit conversion. So we'll update this to be explicit.
Steven Hawkins
added a comment - > Teiid seems to not fully translate convert(..., date) expression
I think the issue was that date support was added to hive after the initial translator development, so the code just defaulted to the string literal form - which would work in places that allow for implicit conversion. So we'll update this to be explicit. 
Thanks. Take a look at this pull, if this fine then I will update, otherwise will further fine tune. https://github.com/teiid/teiid/pull/716