Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Null == 0 or what?

  1. #1

    Default Null == 0 or what?

    Hi

    With the attached example, try this:

    Make a preview.

    Click inside the field 'fieldvalue' on row number 4 (where there is an empty field). You notice that there is some whitespace inside this field.

    Now click on the 'fieldvalue' column header, to sort by this column. Notice that the content of the field turns from whitespace to 0.

    If you add a text file output step, the output contains an empty field, as it should be. Other stuff like mapping empty values to something else or 'Null if' empty works as expected as well, so the problem is with the preview itself, or with the text file input. And I only have this with this whitespace kind of fields. Regular Null fields don't turn into 0s.

    Thanks,
    Tobias
    Attached Files Attached Files

  2. #2

    Default

    This has just bitten me again, in a slightly different case. No text file input this time, so the problem must lie with the preview itself.

    From a database, I get a bunch of integers(9) that look suspiciously overflowy: -2'147'483'648
    However, that's how they come from the database, and that's how kettle stores them in the stream. When I write them to a file with text output, that's how they appear in the file.

    I now use a modified javascript step to set all those values smaller than 0 to 42, like so:

    if (val.getInteger() < 0)
    val.setValue(42)

    When I now make a preview, they appear as 42. When I hit the column tab to sort the column, they all change to 0. If I output to text, they are 42 all right.

    So, it looks like there is a problem with the preview function.

    Thanks,
    Tobias

  3. #3
    Join Date
    May 2006
    Posts
    4,882

    Default

    And do you also see it in v3.0.0-M2?

    Regards,
    Sven

  4. #4

    Default

    I'll download and try my example I attached above. I dare not use 3.x on my ETL-repository and databases though, so I won't be trying anything related to my second post.

  5. #5
    Join Date
    May 2006
    Posts
    4,882

    Default

    Most of the preview stuff had a brush up in v3.0, so just as a test. You can also create a tracker at http://jira.pentaho.org/browse/PDI for in v2.5

    Regards,
    Sven

  6. #6

    Default

    Created PDI-137 (http://jira.pentaho.org/browse/PDI-137).

    Does not happen with 3.0.0-M2.

    Thanks,
    Tobias

  7. #7

    Default

    This has just gotten a bit funnier. Up to now, when I used a text file output step, all data looked like it should when written to a textfile.

    But when I insert a sort step just before the text file output, the data is changed beyond mere sorting. See Jira task for details.

  8. #8
    Join Date
    Jul 2007
    Posts
    18

    Default

    A similar problem happens to me yesterday. I call a DB-function with a source-ID (varchar2). The return value from the function is a number. The function looks up a corresponding key in a lookup table. If the call value is null, the function should return null.

    When i call the function on the database i get the right return value, but kettle returns '0.0'.

    This is the exception in the function which should return null if there is no data is found.


    exception when NO_DATA_FOUND then
    return to_number(null);

  9. #9

    Default

    For reference:

    Matt found out that the problem was indeed with mixing types, but not of the obvious kind, because it was caused by a JS/Rhino problem:

    I had a JS script step like this

    if (somecondition)
    some_integer.setValue(another_integer)

    Notably, both Integers had the exact same type. However, setValue() silently converts the data to Number, leaving me with some rows that contained Integers (where the condition did evaluate to false), and some values as Numbers (where it evaluated to true).

    This issue is worked around in 3.0, but is not fixable in 2.5.x.
    The workaround for 2.5.x is not to use setValue() at all, or to add a redundant else block like so:

    if (somecondition)
    some_integer.setValue(another_integer)
    else
    some_integer.setValue(some_integer)

    Note: the original problem with the preview changing when one clocks on the column header to sort the column persists, but is now very minor. It is demonstrated in the first post to this thread. It might not be worth fixing it as all, since it also does not appear in 3.0.0.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.