[teiid-issues] [JBoss JIRA] (TEIID-5208) PrestoDB removes CAST from join criteria for certain datatypes
Jan Stastny (JIRA)
issues at jboss.org
Thu Jan 11 08:22:00 EST 2018
Jan Stastny created TEIID-5208:
----------------------------------
Summary: 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
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