Row metadata and data: unable to calculate hashcode because of a data conversion prob

[pjbondi]

Hello to all:

I'm using Oracle 10gR2 on a dataset with negative numbers with PDI 3.0.2.

I have a SQL query which worked fine in 2.5.2. I'm running against the same underlying dataset that worked with 2.5.2. This query returns identifiers which are all negative numbers. I think 3.0.2 does not like the negative numbers.

This is a showstopper bug, for me.

Row metadata and data: unable to calculate hashcode because of a data conversion problem
[exec] at org.pentaho.di.core.RowMetaAndData.hashCode(RowMetaAndData.java:111)
[exec] at java.util.Hashtable.get(Hashtable.java:336)
[exec] at org.pentaho.di.trans.steps.databaselookup.DatabaseLookup.lookupValues(DatabaseLookup.java:104)
[exec] at org.pentaho.di.trans.steps.databaselookup.DatabaseLookup.processRow(DatabaseLookup.java:376)
[exec] at org.pentaho.di.trans.steps.databaselookup.DatabaseLookup.run(DatabaseLookup.java:485)
[exec] Caused by: org.pentaho.di.core.exception.KettleValueException:
[exec] Unexpected conversion error while converting value [EVENT_ID_DD Number(10)] to a Number
[exec] java.lang.Long
[exec]
[exec] at org.pentaho.di.core.row.ValueMeta.getNumber(ValueMeta.java:1374)
[exec] at org.pentaho.di.core.row.ValueMeta.hashCode(ValueMeta.java:3190)
[exec] at org.pentaho.di.core.row.RowMeta.hashCode(RowMeta.java:702)
[exec] at org.pentaho.di.core.RowMetaAndData.hashCode(RowMetaAndData.java:107)
[exec] ... 4 more
[exec] Caused by: java.lang.ClassCastException: java.lang.Long
[exec] at org.pentaho.di.core.row.ValueMeta.getNumber(ValueMeta.java:1319)
[exec] ... 7 more
[exec]

[MattCasters]

You can take one of the nightly builds of 3.0.3 over here: ftp://download.pentaho.org/client/da...egration/3.0.3
I have a suspicion that this problem is already fixed, but please do let us know if it doesn't.

Matt

[pjbondi]

At this moment, this FTP URL does not work for me. It seems the 3.0.3 directory is hidden or missing. Is this a permissions issue? Time of day issue? Build currently running issue?

[MattCasters]

We don't update the nightlies every day yet. (Some FTP issue with the provider or something, I don't know)
Well, I guess you reacted too late :-)

Matt

[pjbondi]

Still get errors that did not exist in 2.5.2 that seem to involve Oracle and datatype issues:

     [exec] ERROR 08-04 10:10:32,515 (LogWriter.java:println:403)  -Lookup SYSTEM_KEY West.0 - Unexpected error : 
     [exec] ERROR 08-04 10:10:32,515 (LogWriter.java:println:403)  -Lookup SYSTEM_KEY West.0 - java.lang.ClassCastException: java.math.BigDecimal
     [exec]     at org.pentaho.di.core.row.ValueMeta.getBigNumber(ValueMeta.java:1497)
     [exec]     at org.pentaho.di.core.row.ValueMeta.convertData(ValueMeta.java:2987)
     [exec]     at org.pentaho.di.trans.steps.databaselookup.DatabaseLookup.lookupValues(DatabaseLookup.java:168)
     [exec]     at org.pentaho.di.trans.steps.databaselookup.DatabaseLookup.processRow(DatabaseLookup.java:376)
     [exec]     at org.pentaho.di.trans.steps.databaselookup.DatabaseLookup.run(DatabaseLookup.java:485)
     [exec] 
     [exec] INFO  08-04 10:10:32,515 (LogWriter.java:println:406)  -OTN West Activity by ResourceName.0 - Finished reading query, closing connection.
     [exec] INFO  08-04 10:10:32,515 (LogWriter.java:println:406)  -EVENT_PROFILE_DIM: Database lookup West.0 - Finished processing (I=0, O=0, R=116, W=115, U=0, E=0)
     [exec] INFO  08-04 10:10:32,515 (LogWriter.java:println:406)  -Lookup SYSTEM_KEY West.0 - Finished processing (I=0, O=0, R=14, W=13, U=0, E=1)
     [exec] INFO  08-04 10:10:32,515 (LogWriter.java:println:406)  -OTN West Activity by ResourceName.0 - Finished processing (I=1115, O=0, R=0, W=1114, U=0, E=0)
     [exec] ERROR 08-04 10:10:32,515 (LogWriter.java:println:403)  -MARS-fact-02.ktr - Errors detected!
     [exec] ERROR 08-04 10:10:32,515 (LogWriter.java:println:403)  -MARS-fact-02.ktr - Errors detected!
     [exec] ERROR 08-04 10:10:32,531 (LogWriter.java:println:403)  -MARS-fact-02.ktr - Errors detected!

[pjbondi]

I used Kettle-3.0.3.nightly-0548.zip when I got these errors.

[MattCasters]

Thanks for the feedback Philip.

Can you let us know a little bit more about the transformation and especially about the Oracle target table (DDL or Describe)?

Thanks in advance,

Matt

[pjbondi]

Target table:

CREATE TABLE MARS_BUILD_121.EVENT_SYSTEM_FACT
(
  EVENT_SYSTEM_KEY   NUMBER(10)                 NOT NULL,
  EVENT_PROFILE_KEY  NUMBER(10),
  SYSTEM_KEY         NUMBER(10),
  SITE_IDENTIFIER    NUMBER,
  SYSTEM_ROLE        VARCHAR2(25 BYTE),
  ROLE_DESCRIPTION   VARCHAR2(100 BYTE),
  HOST_STUDIO_FLAG   CHAR(1 BYTE),
  NUM_EVENT          NUMBER(10)                 DEFAULT 1,
  CONTACT_NAME       VARCHAR2(100 BYTE),
  CONTACT_PHONE      VARCHAR2(100 BYTE),
  CONTACT_EMAIL      VARCHAR2(100 BYTE)
)
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

COMMENT ON TABLE MARS_BUILD_121.EVENT_SYSTEM_FACT IS 'This is a Fact Helper table.  See http://www.dbmsmag.com/9808d05.html';




ALTER TABLE MARS_BUILD_121.EVENT_SYSTEM_FACT ADD (
  CONSTRAINT PK_EVENT_SYSTEM_FACT
 PRIMARY KEY
 (EVENT_SYSTEM_KEY));


ALTER TABLE MARS_BUILD_121.EVENT_SYSTEM_FACT ADD (
  CONSTRAINT FK_EVENT_SY_REFERENCE_SYSTEM_D 
 FOREIGN KEY (SYSTEM_KEY) 
 REFERENCES MARS_BUILD_121.SYSTEM_DIM (SYSTEM_KEY));

ALTER TABLE MARS_BUILD_121.EVENT_SYSTEM_FACT ADD (
  CONSTRAINT FK_EVENT_SY_REFERENCE_EVENT_PR 
 FOREIGN KEY (EVENT_PROFILE_KEY) 
 REFERENCES MARS_BUILD_121.EVENT_PROFILE_DIM (EVENT_PROFILE_KEY));

Source query:

[pjbondi]

    SELECT      -ConferenceID                                                                                       AS  event_id_dd
    ,           migration_new_gab                                                                                   AS  system_full_name
    ,           CASE    WHEN    INSTR( migration_orig_gab, REGEXP_SUBSTR( ConferenceName, '([[:digit:]]{3,})' ) )
                                                                                                                !=    0
                        THEN    CASE    WHEN    REGEXP_INSTR( ConferenceReference, '^S?A' )     !=  0
                                        THEN    'HOST'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?E' )     !=  0
                                        THEN    'HOST'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?C' )     !=  0
                                        THEN    'CONSULTANT'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^HOLD', 1, 1, 0, 'i' )  !=  0
                                        THEN    'HOLD'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^t(est)?', 1, 1, 0, 'i' )  !=  0
                                        THEN    'TEST'
                                ELSE    'Unknown'
                                END                                                                                                     
                        WHEN    INSTR( REPLACE( migration_system_number, '-' ), REGEXP_SUBSTR( ConferenceName, '([[:digit:]]{3,})' ) ) 
                                                                                                                !=    0
                        THEN    CASE    WHEN    REGEXP_INSTR( ConferenceReference, '^S?A' )     !=  0
                                        THEN    'HOST'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?E' )     !=  0
                                        THEN    'HOST'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?C' )     !=  0
                                        THEN    'CONSULTANT'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^HOLD', 1, 1, 0, 'i' )  !=  0
                                        THEN    'HOLD'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^t(est)?', 1, 1, 0, 'i' )  !=  0
                                        THEN    'TEST'
                                ELSE    'Unknown'
                                END
                        WHEN    REGEXP_INSTR( ConferenceName, '(HOLD|TEST)', 1, 1, 0, 'i' )                     !=  0
                        THEN    'WEST'
                        ELSE    CASE    WHEN    REGEXP_INSTR( ConferenceReference, '^S?A' )     !=  0
                                        THEN    'PARTICIPANT'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?E' )     !=  0
                                        THEN    'PARTICIPANT'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?C' )     !=  0
                                        THEN    'PATIENT'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^HOLD', 1, 1, 0, 'i' )  !=  0
                                        THEN    'HOLD'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^t(est)?', 1, 1, 0, 'i' )  !=  0
                                        THEN    'TEST'
                                ELSE    'Unknown'
                                END
                END                                                                                                 AS  System_Role
    ,                                                                                                                                 
                CASE    WHEN    INSTR( migration_orig_gab, REGEXP_SUBSTR( ConferenceName, '([[:digit:]]{3,})' ) ) 
                                                                                                                !=    0
                        THEN    CASE    WHEN    REGEXP_INSTR( ConferenceReference, '^S?A' )     !=  0
                                        THEN    'Nominated HOST based on Original GAB in Migration Database'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?E' )     !=  0
                                        THEN    'Nominated HOST based on Original GAB in Migration Database'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?C' )     !=  0
                                        THEN    'Nominated CONSULTANT based on Original GAB in Migration Database'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^HOLD', 1, 1, 0, 'i' )  !=  0
                                        THEN    'HOLD'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^t(est)?', 1, 1, 0, 'i' )  !=  0
                                        THEN    'TEST'
                                ELSE    'Unknown'
                                END
                        WHEN    INSTR( REPLACE( migration_system_number, '-' ), REGEXP_SUBSTR( ConferenceName, '([[:digit:]]{3,})' ) ) 
                                                                                                                !=    0
                        THEN    CASE    WHEN    REGEXP_INSTR( ConferenceReference, '^S?A' )     !=  0
                                        THEN    'Nominated HOST based on System Number in Migration Database'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?E' )     !=  0
                                        THEN    'Nominated HOST based on System Number in Migration Database'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?C' )     !=  0
                                        THEN    'Nominated CONSULTANT based on System Number in Migration Database'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^HOLD', 1, 1, 0, 'i' )  !=  0
                                        THEN    'HOLD'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^t(est)?', 1, 1, 0, 'i' )  !=  0
                                        THEN    'TEST'
                                ELSE    'Unknown'
                                END
                        WHEN    REGEXP_INSTR( ConferenceName, '(HOLD|TEST)', 1, 1, 0, 'i' )                     != 0
                        THEN    'Nominated as Host or Test conference based on the word hold or test in the ConferenceName'
                        ELSE    CASE    WHEN    REGEXP_INSTR( ConferenceReference, '^S?A' )     !=  0
                                        THEN    'Nominated PARTICIPANT'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?E' )     !=  0
                                        THEN    'Nominated PARTICIPANT'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?C' )     !=  0
                                        THEN    'Nominated PATIENT'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^HOLD', 1, 1, 0, 'i' )  !=  0
                                        THEN    'HOLD'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^t(est)?', 1, 1, 0, 'i' )  !=  0
                                        THEN    'TEST'
                                ELSE    'Unknown'
                                END
                END                                                                                                 AS  Role_Description
    ,           CASE    WHEN    INSTR( migration_orig_gab, REGEXP_SUBSTR( ConferenceName, '([[:digit:]]{3,})' ) ) 
                                                                                                                !=    0
                        THEN    CASE    WHEN    REGEXP_INSTR( ConferenceReference, '^S?A' )     !=  0
                                        THEN    'Y'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?E' )     !=  0
                                        THEN    'Y'
                                ELSE    'N'
                                END
                        WHEN    INSTR( REPLACE( migration_system_number, '-' ), REGEXP_SUBSTR( ConferenceName, '([[:digit:]]{3,})' ) ) 
                                                                                                                !=    0
                        THEN    CASE    WHEN    REGEXP_INSTR( ConferenceReference, '^S?A' )     !=  0
                                        THEN    'Y'
                                        WHEN    REGEXP_INSTR( ConferenceReference, '^S?E' )     !=  0
                                        THEN    'Y'
                                ELSE    'N'
                                END
                        WHEN    REGEXP_INSTR( ConferenceName, '(HOLD|TEST)', 1, 1, 0, 'i' )                     != 0
                        THEN    'N'
                        ELSE    CASE    WHEN    REGEXP_INSTR( ConferenceReference, '^S?C' )     !=  0
                                        THEN    'Y'
                                ELSE    'N'
                                END
                END                                                                                                 AS  Host_Studio_Flag
    ,           CASE 
                        WHEN    REGEXP_INSTR( ConferenceName, '(HOLD|TEST)', 1, 1, 0, 'i' )                     =   0
                        THEN    1
                        ELSE    0
                END                                                                                                 AS  num_event
    FROM        migration_database_site                         si
    ,           migration_database_studio                       st
    ,           otn_west_activity                               owa
    WHERE      (    TRIM(UPPER(migration_orig_gab))             =   TRIM(UPPER(owa.resourcename))
    OR              TRIM(UPPER(migration_new_gab))              =   TRIM(UPPER(owa.resourcename))
                )
    AND         ConferenceReference                             IS NOT NULL
    AND         ConferenceStart                                 IS NOT NULL
    AND         st.migration_otn_site_number                    =   si.migration_otn_site_number (+)
    --AND         UPPER('%%Inclusion%%')                          LIKE    '%WEST%'
ORDER BY    event_id_dd
,           system_full_name

[MattCasters]

Can you attach the XML of the failing step (or transformation) and also the fields that go into the failing step?

Thanks,

Matt