Pentaho Merge Join (Left Join) missing rows

Question

i tried to do left join in pentaho using merge joins..

So when i do query

select count(*) from table_a a 

left join table_b on a.key=b.key

It gave me 26,756

But in the Pentaho it only gave me 26,721 ?

I've already sorted before joing though..

I'm using Merge Join step, and Left Outer.

this is my transformation :

My merge join :

My sort :

In the Table input i've already sorted with order by, why? Because i tried many things and the most close rows count is when i tried to sort in the table input then change "Pre Sorted" in Sort steps to 'Y'

Answer 1

If the ID is a string, it could be some weird character messing with the comparison (or case sensitive/insensitive match). If it's a number, I don't see a reason, probably something related with duplicate keys are the usual suspects in these cases.

In your case I would manually debug the differences between both outputs, looking for those pesky 25 rows of difference between the two outputs.

And yes, the sort before the Merge join is needed even if you used a ORDER BY in your query, the output of the ORDER BY could be different on how Pentaho orders the rows, depending on differences on locale between the database and the java machine, special characters and upper/lower case could be sort differently in the database and in Pentaho, messing with the Merge Join.

Answer 2

Your problem may lie in the pre-sorted flag.

When sorting on just 1 field, if you have it as Y the sort isn’t really sorting anything (you told PDI it’s already sorted). That flag is useful only when sorting on multiple fields: field1 has presorted=N, field2 has presorted=Y, so within each block of rows with a single value of field1 PDI doesn’t have to sort field2 values, as they’re already sorted.

Other than that, note that comparisons in Java and SQL are not exactly the same. Esp in strings, SQL Server compares ignoring trailing whitespace, whereas Java doesn’t; and most SQL flavours are case insensitive in string comparisons, whereas Java isn’t.

Best way to find out:

• output join result from SQL & PDI to csv files
• compare the two using a diff step
• see what’s special about the rows in the SQL join that don’t show up in PDI.