[teiid-issues] [JBoss JIRA] (TEIID-5208) PrestoDB removes CAST from join criteria for certain datatypes

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@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@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@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@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@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@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@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@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@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@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@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@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@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@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}