[teiid-issues] [JBoss JIRA] (TEIID-5208) PrestoDB removes CAST from join criteria for certain datatypes
Steven Hawkins (JIRA)
issues at jboss.org
Fri Jan 12 12:11:00 EST 2018
[ https://issues.jboss.org/browse/TEIID-5208?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Steven Hawkins resolved TEIID-5208.
-----------------------------------
Resolution: Duplicate Issue
Duplicate of TEIID-5168
> PrestoDB removes CAST from join criteria for certain datatypes
> --------------------------------------------------------------
>
> Key: TEIID-5208
> URL: https://issues.jboss.org/browse/TEIID-5208
> Project: Teiid
> Issue Type: Bug
> Components: Misc. Connectors
> Affects Versions: 8.12.x-6.4
> Reporter: Jan Stastny
> Assignee: Steven Hawkins
> Attachments: presto-vdb.xml
>
>
> For certain datatypes prestodb translator removes join criteria from a query before pushing it.
> # For short data type
> For query:
> {code}
> SELECT A.BooleanValue FROM BQT1.SMallA AS A JOIN BQT2.SmallB AS B ON CONVERT(A.BooleanValue, short)=B.ShortValue
> {code}
> I get following query pushed:
> {code}
> SELECT g_0.booleanvalue FROM smalla AS g_0 INNER JOIN smallb
> AS g_1 ON g_0.booleanvalue = g_1.shortvalue
> {code}
> All entries in command log:
> {code}
> 13:09:27,827 INFO [org.teiid.COMMAND_LOG] (New I/O worker #1) 8ObxvDXnPZmp START USER COMMAND: startTime=2018-01-11 13:09:27.827 requestID=8ObxvDXnPZmp.35 txID=null sessionID=8ObxvDXnP
> Zmp applicationName=JDBC principal=user at teiid-security vdbName=PrestoDB vdbVersion=1 sql=SELECT A.BooleanValue FROM BQT1.SMallA AS A JOIN BQT2.SmallB AS B ON CONVERT(A.BooleanValue, short)=B.S
> hortValue
> 13:09:27,841 DEBUG [org.teiid.COMMAND_LOG] (Worker18_QueryProcessorQueue100) 8ObxvDXnPZmp START DATA SRC COMMAND: startTime=2018-01-11 13:09:27.84 requestID=8ObxvDXnPZmp.35 sourceCommandID=0
> executionID=21 txID=null modelName=Source translatorName=prestodb sessionID=8ObxvDXnPZmp principal=user at teiid-security sql=SELECT g_0.BooleanValue FROM Source.smalla AS g_0 INNER JOIN So
> urce.smallb AS g_1 ON CONVERT(g_0.BooleanValue, short) = g_1.ShortValue
> 13:09:27,843 DEBUG [org.teiid.COMMAND_LOG] (Worker18_QueryProcessorQueue100) 8ObxvDXnPZmp SOURCE SRC COMMAND: endTime=2018-01-11 13:09:27.843 requestID=8ObxvDXnPZmp.35 sourceCommandID=0 exe
> cutionID=21 txID=null modelName=Source translatorName=prestodb sessionID=8ObxvDXnPZmp principal=user at teiid-security sourceCommand=[SELECT g_0.booleanvalue FROM smalla AS g_0 INNER JOIN smallb
> AS g_1 ON g_0.booleanvalue = g_1.shortvalue]
> 13:09:28,171 DEBUG [org.teiid.COMMAND_LOG] (Worker18_QueryProcessorQueue100) 8ObxvDXnPZmp ERROR SRC COMMAND: endTime=2018-01-11 13:09:28.171 requestID=8ObxvDXnPZmp.35 sourceCommandID=0 exe
> cutionID=21 txID=null modelName=Source translatorName=prestodb sessionID=8ObxvDXnPZmp principal=user at teiid-security
> 13:09:28,193 DEBUG [org.teiid.COMMAND_LOG] (Worker17_QueryProcessorQueue101) 8ObxvDXnPZmp END SRC COMMAND: endTime=2018-01-11 13:09:28.193 requestID=8ObxvDXnPZmp.35 sourceCommandID=0 exe
> cutionID=21 txID=null modelName=Source translatorName=prestodb sessionID=8ObxvDXnPZmp principal=user at teiid-security finalRowCount=0 cpuTime(ns)=34953318
> 13:09:28,194 INFO [org.teiid.COMMAND_LOG] (Worker17_QueryProcessorQueue101) 8ObxvDXnPZmp ERROR USER COMMAND: endTime=2018-01-11 13:09:28.194 requestID=8ObxvDXnPZmp.35 txID=null sessionID=8
> ObxvDXnPZmp principal=user at teiid-security vdbName=PrestoDB vdbVersion=1 finalRowCount=null
> 13:09:28,210 INFO [org.teiid.COMMAND_LOG] (Worker17_QueryProcessorQueue101) 8ObxvDXnPZmp END USER COMMAND: endTime=2018-01-11 13:09:28.21 requestID=8ObxvDXnPZmp.35 txID=null sessionID=8
> ObxvDXnPZmp principal=user at teiid-security vdbName=PrestoDB vdbVersion=1 finalRowCount=0
> {code}
> # for byte datatype
> Query:
> {code}
> SELECT A.BooleanValue FROM BQT1.SMallA AS A, BQT2.SmallB AS B WHERE CONVERT(A.BooleanValue, byte)=B.ByteNum
> {code}
> Source query
> {code}
> SELECT g_0.booleanvalue FROM smalla AS g_0 INNER JOIN smallb
> AS g_1 ON g_0.booleanvalue = g_1.bytenum
> {code}
> Command log
> {code}
> 14:17:34,955 INFO [org.teiid.COMMAND_LOG] (New I/O worker #1) 8ObxvDXnPZmp START USER COMMAND: startTime=2018-01-11 14:17:34.955 requestID=8ObxvDXnPZmp.36 txID=null sessionID=8ObxvDXnP
> Zmp applicationName=JDBC principal=user at teiid-security vdbName=PrestoDB vdbVersion=1 sql=SELECT A.BooleanValue FROM BQT1.SMallA AS A, BQT2.SmallB AS B WHERE CONVERT(A.BooleanValue, byte)=B.Byt
> eNum
> 14:17:35,494 DEBUG [org.teiid.COMMAND_LOG] (Worker20_QueryProcessorQueue103) 8ObxvDXnPZmp START DATA SRC COMMAND: startTime=2018-01-11 14:17:35.494 requestID=8ObxvDXnPZmp.36 sourceCommandID=0
> executionID=22 txID=null modelName=Source translatorName=prestodb sessionID=8ObxvDXnPZmp principal=user at teiid-security sql=SELECT g_0.BooleanValue FROM Source.smalla AS g_0 INNER JOIN So
> urce.smallb AS g_1 ON CONVERT(g_0.BooleanValue, byte) = convert(g_1.ByteNum, byte)
> 14:17:35,496 DEBUG [org.teiid.COMMAND_LOG] (Worker20_QueryProcessorQueue103) 8ObxvDXnPZmp SOURCE SRC COMMAND: endTime=2018-01-11 14:17:35.496 requestID=8ObxvDXnPZmp.36 sourceCommandID=0 exe
> cutionID=22 txID=null modelName=Source translatorName=prestodb sessionID=8ObxvDXnPZmp principal=user at teiid-security sourceCommand=[SELECT g_0.booleanvalue FROM smalla AS g_0 INNER JOIN smallb
> AS g_1 ON g_0.booleanvalue = g_1.bytenum]
> 14:17:35,814 DEBUG [org.teiid.COMMAND_LOG] (Worker20_QueryProcessorQueue103) 8ObxvDXnPZmp ERROR SRC COMMAND: endTime=2018-01-11 14:17:35.814 requestID=8ObxvDXnPZmp.36 sourceCommandID=0 exe
> cutionID=22 txID=null modelName=Source translatorName=prestodb sessionID=8ObxvDXnPZmp principal=user at teiid-security
> 14:17:35,822 DEBUG [org.teiid.COMMAND_LOG] (Worker19_QueryProcessorQueue104) 8ObxvDXnPZmp END SRC COMMAND: endTime=2018-01-11 14:17:35.822 requestID=8ObxvDXnPZmp.36 sourceCommandID=0 exe
> cutionID=22 txID=null modelName=Source translatorName=prestodb sessionID=8ObxvDXnPZmp principal=user at teiid-security finalRowCount=0 cpuTime(ns)=13999084
> 14:17:35,823 INFO [org.teiid.COMMAND_LOG] (Worker19_QueryProcessorQueue104) 8ObxvDXnPZmp ERROR USER COMMAND: endTime=2018-01-11 14:17:35.823 requestID=8ObxvDXnPZmp.36 txID=null sessionID=8
> ObxvDXnPZmp principal=user at teiid-security vdbName=PrestoDB vdbVersion=1 finalRowCount=null
> 14:17:35,837 INFO [org.teiid.COMMAND_LOG] (Worker19_QueryProcessorQueue104) 8ObxvDXnPZmp END USER COMMAND: endTime=2018-01-11 14:17:35.837 requestID=8ObxvDXnPZmp.36 txID=null sessionID=8
> ObxvDXnPZmp principal=user at teiid-security vdbName=PrestoDB vdbVersion=1 finalRowCount=0
> {code}
> When the CAST is removed the queries fail with PrestoDB error similar to:
> {code}
> 13:09:28,172 WARN [org.teiid.CONNECTOR] (Worker18_QueryProcessorQueue100) Connector worker process failed for atomic-request=8ObxvDXnPZmp.35.0.21: org.teiid.translator.jdbc.JDBCExecutionException: 1 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.booleanvalue FROM smalla AS g_0 INNER JOIN smallb AS g_1 ON g_0.booleanvalue = g_1.shortvalue]
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.12.11.6_4-redhat-64-10.jar:8.12.11.6_4-redhat-64-10]
> at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:361)
> at sun.reflect.GeneratedMethodAccessor172.invoke(Unknown Source) [:1.8.0_151]
> at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_151]
> at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)
> at com.sun.proxy.$Proxy80.execute(Unknown Source)
> at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:112)
> at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:108)
> at java.util.concurrent.FutureTask.run(FutureTask.java:266) [rt.jar:1.8.0_151]
> at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:65)
> at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:284)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
> at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [rt.jar:1.8.0_151]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [rt.jar:1.8.0_151]
> at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_151]
> Caused by: java.sql.SQLException: Query failed (#20180111_120927_00021_8zhqn): line 1:89: '=' cannot be applied to boolean, smallint
> at com.facebook.presto.jdbc.PrestoResultSet.resultsException(PrestoResultSet.java:1799)
> at com.facebook.presto.jdbc.PrestoResultSet.getColumns(PrestoResultSet.java:1747)
> at com.facebook.presto.jdbc.PrestoResultSet.<init>(PrestoResultSet.java:125)
> at com.facebook.presto.jdbc.PrestoStatement.execute(PrestoStatement.java:212)
> at com.facebook.presto.jdbc.PrestoStatement.executeQuery(PrestoStatement.java:69)
> at org.jboss.jca.adapters.jdbc.WrappedStatement.executeQuery(WrappedStatement.java:344)
> at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:119) [translator-jdbc-8.12.11.6_4-redhat-64-10.jar:8.12.11.6_4-redhat-64-10]
> ... 17 more
> Caused by: com.facebook.presto.sql.analyzer.SemanticException: line 1:89: '=' cannot be applied to boolean, smallint
> at com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.getOperator(ExpressionAnalyzer.java:1156)
> at com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.visitComparisonExpression(ExpressionAnalyzer.java:428)
> at com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.visitComparisonExpression(ExpressionAnalyzer.java:258)
> at com.facebook.presto.sql.tree.ComparisonExpression.accept(ComparisonExpression.java:71)
> at com.facebook.presto.sql.tree.StackableAstVisitor.process(StackableAstVisitor.java:26)
> at com.facebook.presto.sql.analyzer.ExpressionAnalyzer$Visitor.process(ExpressionAnalyzer.java:277)
> at com.facebook.presto.sql.analyzer.ExpressionAnalyzer.analyze(ExpressionAnalyzer.java:234)
> at com.facebook.presto.sql.analyzer.ExpressionAnalyzer.analyzeExpression(ExpressionAnalyzer.java:1460)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.analyzeExpression(StatementAnalyzer.java:1828)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitJoin(StatementAnalyzer.java:1060)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitJoin(StatementAnalyzer.java:237)
> at com.facebook.presto.sql.tree.Join.accept(Join.java:90)
> at com.facebook.presto.sql.tree.AstVisitor.process(AstVisitor.java:27)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:249)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.analyzeFrom(StatementAnalyzer.java:1693)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:855)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitQuerySpecification(StatementAnalyzer.java:237)
> at com.facebook.presto.sql.tree.QuerySpecification.accept(QuerySpecification.java:127)
> at com.facebook.presto.sql.tree.AstVisitor.process(AstVisitor.java:27)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:249)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:259)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:600)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.visitQuery(StatementAnalyzer.java:237)
> at com.facebook.presto.sql.tree.Query.accept(Query.java:94)
> at com.facebook.presto.sql.tree.AstVisitor.process(AstVisitor.java:27)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer$Visitor.process(StatementAnalyzer.java:249)
> at com.facebook.presto.sql.analyzer.StatementAnalyzer.analyze(StatementAnalyzer.java:223)
> at com.facebook.presto.sql.analyzer.Analyzer.analyze(Analyzer.java:68)
> at com.facebook.presto.sql.analyzer.Analyzer.analyze(Analyzer.java:60)
> at com.facebook.presto.execution.SqlQueryExecution.doAnalyzeQuery(SqlQueryExecution.java:296)
> at com.facebook.presto.execution.SqlQueryExecution.analyzeQuery(SqlQueryExecution.java:282)
> at com.facebook.presto.execution.SqlQueryExecution.start(SqlQueryExecution.java:238)
> at com.facebook.presto.execution.QueuedExecution.lambda$start$1(QueuedExecution.java:62)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [rt.jar:1.8.0_151]
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_151]
> at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_151]
> 13:09:28,194 WARN [org.teiid.PROCESSOR] (Worker17_QueryProcessorQueue101) TEIID30020 Processing exception for request 8ObxvDXnPZmp.35 'TEIID30504 Source: 1 TEIID11008:TEIID11004 Error executing statement(s): [SQL: SELECT g_0.booleanvalue FROM smalla AS g_0 INNER JOIN smallb AS g_1 ON g_0.booleanvalue = g_1.shortvalue]'. Originally TeiidProcessingException 'line 1:89: '=' cannot be applied to boolean, smallint' ExpressionAnalyzer.java:1156. Enable more detailed logging to see the entire stacktrace.
> {code}
--
This message was sent by Atlassian JIRA
(v7.5.0#75005)
More information about the teiid-issues
mailing list