Uploaded image for project: 'Pentaho Data Integration - Kettle'
  1. Pentaho Data Integration - Kettle
  2. PDI-13175

Nested Copy Rows to Result and Get Rows from result

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: High
    • Resolution: Incomplete
    • Affects Version/s: 5.1.0 GA
    • Fix Version/s: Backlog
    • Component/s: Job, Transformation
    • Labels:
      None
    • Story Points:
      0
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • Operating System/s:
      Windows 7

      Description

      Hello,

      I think there are a couple of issues regarding the steps “Copy rows to result” and “Get rows from result” in Pentaho 5.1 that didn’t exist before (in 4.4) and I created a simple (as much as possible) kettle project to illustrate the problems (please see the attachment file)

      The project is made of 3 jobs and 4 transformations. Its purpose is to loop through 2 embedded result sets and display their content.

      The first (outer) result set contains the rows below:
      OuterId OuterName
      100000 Primary
      200000 Secondary

      The second (inner) one has the following data:
      InnertId InnertName
      1 Patrick
      2 James

      Both result sets are generated with the use of a Data Grid step
      Since Pentaho shows all the data from the inner set as many time as there is rows in the outer set, we should get the the the following result in the Spoon logging pane when running the Master job:

      Spoon - Starting job...
      Master - Start of job execution
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Outer Set >>
      Write to log.0 -
      Write to log.0 - OuterId = 100000
      Write to log.0 - OuterName = Primary
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Inner Set >>
      Write to log.0 -
      Write to log.0 - InnerId = 1
      Write to log.0 - InnerName = Patrick
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 2-----------------------------
      Write to log.0 - << Inner Set >>
      Write to log.0 -
      Write to log.0 - InnerId = 2
      Write to log.0 - InnerName = James
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Outer Set >>
      Write to log.0 -
      Write to log.0 - OuterId = 200000
      Write to log.0 - OuterName = Secondary
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Inner Set >>
      Write to log.0 -
      Write to log.0 - InnerId = 1
      Write to log.0 - InnerName = Patrick
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 2-----------------------------
      Write to log.0 - << Inner Set >>
      Write to log.0 -
      Write to log.0 - InnerId = 2
      Write to log.0 - InnerName = James
      Write to log.0 -
      Write to log.0 - ====================
      Master - Job execution finished
      Spoon - Job has ended.

      This works properly in Pentaho 4.4 but with V5.1, I get some unexpected lines as shown here in red:

      Spoon - Starting job...
      Master - Start of job execution
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Outer Set >>
      Write to log.0 -
      Write to log.0 - OuterId = 100000
      Write to log.0 - OuterName = Primary
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Inner Set >>
      Write to log.0 -
      Write to log.0 - InnerId = 100000
      Write to log.0 - InnerName = Primary
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 2-----------------------------
      Write to log.0 - << Inner Set >>
      Write to log.0 -
      Write to log.0 - InnerId = 1
      Write to log.0 - InnerName = Patrick
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 3-----------------------------
      Write to log.0 - << Inner Set >>
      Write to log.0 -
      Write to log.0 - InnerId = 2
      Write to log.0 - InnerName = James
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Outer Set >>
      Write to log.0 -
      Write to log.0 - OuterId = 200000
      Write to log.0 - OuterName = Secondary
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Inner Set >>
      Write to log.0 -
      Write to log.0 - InnerId = 200000
      Write to log.0 - InnerName = Secondary
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 2-----------------------------
      Write to log.0 - << Inner Set >>
      Write to log.0 -
      Write to log.0 - InnerId = 1
      Write to log.0 - InnerName = Patrick
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 3-----------------------------
      Write to log.0 - << Inner Set >>
      Write to log.0 -
      Write to log.0 - InnerId = 2
      Write to log.0 - InnerName = James
      Write to log.0 -
      Write to log.0 - ====================
      Master - Job execution finished
      Spoon - Job has ended.

      As we may see, Pentaho V5.1 displays rows from the outer set as part of the inner set and its seems that we have now 3 rows in the inner set which is not true.
      This issue happens only when the transformation in charge of copying the rows into the inner set is called through an intermediate job as designed in ForEachOuterRows.kjb.
      If you enable/disable the hops in order to call the transformation directly rather than through the intermediate job, the issue will not occur
      Another issue found in Pentaho V5.1 occurs when the inner set is empty. To make the inner set empty, you just need to enable/disable the hops in Inner_CopyRowsToResult.ktr so the Data Grid 2 takes the place of Data Grid 1
      When the inner set is empty, Pentaho 4.4 shows the following result as expected:

      Spoon - Starting job...
      Master - Start of job execution
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Outer Set >>
      Write to log.0 -
      Write to log.0 - OuterId = 100000
      Write to log.0 - OuterName = Primary
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Outer Set >>
      Write to log.0 -
      Write to log.0 - OuterId = 200000
      Write to log.0 - OuterName = Secondary
      Write to log.0 -
      Write to log.0 - ====================
      Master - Job execution finished
      Spoon - Job has ended.

      With V5.1, Pentaho shows the errors below... no matter the transformation in charge of copying the rows into the inner set is called through an intermediate job or not

      Spoon - Starting job...
      Master - Start of job execution
      Write to log.0 -
      Write to log.0 - -----------> Linenr 1-----------------------------
      Write to log.0 - << Outer Set >>
      Write to log.0 -
      Write to log.0 - OuterId = 100000
      Write to log.0 - OuterName = Primary
      Write to log.0 -
      Write to log.0 - ====================
      Write to log.0 - ERROR (version 5.1.0.0, build 1 from 2014-06-19_19-02-57 by buildguy) :
      Can not find field [InnerId] in the input stream!
      Write to log.0 - ERROR (version 5.1.0.0, build 1 from 2014-06-19_19-02-57 by buildguy) :
      Unexpected error
      Write to log.0 - ERROR (version 5.1.0.0, build 1 from 2014-06-19_19-02-57 by buildguy) : org.pentaho.di.core.exception.KettleException:
      Write to log.0 - Can not find field [InnerId] in the input stream!
      Write to log.0 -
      Write to log.0 - at org.pentaho.di.trans.steps.writetolog.WriteToLog.processRow(WriteToLog.java:85)
      Write to log.0 - at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
      Write to log.0 - at java.lang.Thread.run(Thread.java:722)
      Inner_GetRowsFromResult - ERROR (version 5.1.0.0, build 1 from 2014-06-19_19-02-57 by buildguy) : Errors detected!
      Inner_GetRowsFromResult - Transformation detected one or more steps with errors.
      Inner_GetRowsFromResult - Transformation is killing the other steps!
      Inner_GetRowsFromResult - ERROR (version 5.1.0.0, build 1 from 2014-06-19_19-02-57 by buildguy) : Errors detected!
      Master - Job execution finished
      Spoon - Job has ended.

      Perhaps there is a kettle variable missing or not configured properly in kettle.properties that would explain this issue. Maybe a step somewhere in the project is not set up correctly. But I found nothing so far.
      Help would be appreciate

      Regards

      Jocelyn

      P-S: The attachment file is located at the following URL
      http://forums.pentaho.com/showthread.php?169250-PDI-5-1-Issues-with-embedded-Copy-Rows-to-Result-and-Get-Rows-from-result

        Attachments

          Activity

            People

            Assignee:
            Unassigned
            Reporter:
            jgrondin Jocelyn Grondin
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: