[
https://issues.apache.org/jira/browse/TRAFODION-2382?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15773515#comment-15773515
]
ASF GitHub Bot commented on TRAFODION-2382:
-------------------------------------------
Github user asfgit closed the pull request at:
https://github.com/apache/incubator-trafodion/pull/882
> No plan produced when joining two TMUDFs
> ----------------------------------------
>
> Key: TRAFODION-2382
> URL: https://issues.apache.org/jira/browse/TRAFODION-2382
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.1-incubating
> Environment: any
> Reporter: Hans Zeller
> Assignee: Hans Zeller
>
> This problem was reported by Gunnar Tapper and it uncovered three separate
> issues:
> # The max cardinality for some TMUDFs is set to -1 and this causes the hash
> join implementation rule to trigger a condition that disables a hash join.
> This is intended for situations where the left/outer table has at most 1 row,
> which is not the case here.
> # The merge join rule doesn't fire, because the CAST functions used here are
> not considered order-preserving expressions. This problem is not addressed as
> part of this JIRA.
> # The nested join rule produces a filter, but we fail to push the filter
> predicate into the TMUDF, because the code uses the operator type to
> determine its arity, and the TMUDF uses the same operator type for different
> arities.
> In the end, none of the three join algorithms can be applied, and we fail to
> produce a plan.
> Here is how to reproduce the problem:
> Put this Java code into file join_udr.java:
> {code:java}
> import org.trafodion.sql.udr.*;
> class join_udf extends UDR {
> public join_udf() {}
>
> @Override
> public void describeParamsAndColumns(UDRInvocationInfo info)
> throws UDRException
> {
> // 1st param is vchar size desired
> int vSize = info.par().getInt(0);
> // this param needed when call is exec'd, but not info.out()'d
> info.addFormalParameter(info.par().getColumn(0));
>
> // For each following param, create an output column
> // Params are json tag names - convert these to SQL column names
> // For output, cannot distinguish data types so everything is
> VARCHAR
>
> for (int i = 1; i < info.par().getNumColumns(); i++) {
> // make formal param
> info.addFormalParameter(info.par().getColumn(i));
> // use input param string to create output param
> info.out().addVarCharColumn(info.par().getString(i),
> vSize, true);
> } // for
>
> // set as Mapper so reading can be parallelized
> info.setFuncType(UDRInvocationInfo.FuncType.MAPPER);
> }
>
> @Override
> public void processData(UDRInvocationInfo info,
> UDRPlanInfo plan)
> throws UDRException
> {
> // do nothing
> } // end processData
>
> }
> {code}
> Compile the code:
> {code}
> javac join_udf.java
> jar cvf join_udf.jar join_udf.class
> {code}
> Create the UDF and a source table, then try to compile the query (stay in the
> same directory and use the sqlci tool):
> {code:sql}
> drop function join_udf;
> drop library join_udf_lib;
> create library join_udf_lib file
> 'join_udf.jar';
> create table_mapping function join_udf()
> external name 'join_udf'
> language java
> library join_udf_lib;
> -- disable semijoin to join transformation, some more complex situations
> -- show this error without this CQD, but we are using it here to keep the
> -- test case simple
> cqd SEMIJOIN_TO_INNERJOIN_TRANSFORMATION 'off';
> create table join_udf_src(a integer);
> prepare s from
> --display
> select a,b
> from
> ( select
> -- Convert the string data to SQL data types.
> cast( a as char(4) ) as a
> , cast( b as date ) as b
> from udf
> ( join_udf(
> table( select * from join_udf_src )
> , 257
> , 'A'
> , 'B'
> )
> )
> )
> where ( a,b ) in
> ( select a,b
> from
> ( select
> cast( a as char(4) ) as a
> , cast( b as date ) as b
> from udf
> ( join_udf(
> 257
> , 'A'
> , 'B'
> )
> )
> )
> where b between date '2016-01-01' and date '2016-02-28'
> )
> ;
> {code}
> This will produce the following error:
> {code}
> *** ERROR[2235] Compiler Internal Error: Pass one skipped, but cannot produce
> a plan in pass two, originated from file ../optimizer/opt.cpp at line xxxx.
> *** ERROR[8822] The statement was not prepared.
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
