[TRAFODION-2382] No plan produced when joining two TMUDFs

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.1-incubating
• Fix Version/s: 2.1-incubating
• Component/s: sql-cmp
• Environment: any
• Assignee: Hans Zeller
• Reporter: Hans Zeller
• Created: 30/Nov/16 20:13
• Updated: 23/Dec/16 19:20
• Resolved: 23/Dec/16 19:20

Description

This problem was reported by Gunnar Tapper and it uncovered three separate issues:

1. 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.
2. 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.
3. 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:

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
	
}

Compile the code:

javac join_udf.java
jar cvf join_udf.jar join_udf.class

Create the UDF and a source table, then try to compile the query (stay in the same directory and use the sqlci tool):

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'
)
;

This will produce the following error:

*** 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.

Issue Links

• links to: GitHub Pull Request #882